| 00:26.13 | jfindlay | jlp_znc: I have a postgres DB for storing salt returns that trigger on ~20 systems every 5 minutes. How should I tune the DB parameters for this use case? |
| 00:26.46 | jfindlay | I deleted all data before the beginning of this month (~5 M rows), but any query still takes over 1 minute |
| 00:26.56 | jfindlay | the query time has gradually increased over time |
| 01:00.37 | *** join/#utah tiwula (~lane@174-23-69-4.slkc.qwest.net) |
| 10:02.08 | *** join/#utah emcnabb (~emcnabb@hs-nc-1fff767f54-29977-1.tingfiber.com) |
| 10:08.37 | *** part/#utah strummer100[m] (strummer10@gateway/shell/matrix.org/x-xtmpacsjwivmsxwa) |
| 13:10.21 | *** join/#utah SunSparc (~jonathan@c-73-65-149-180.hsd1.ut.comcast.net) |
| 13:17.02 | BigBen212 | jfindlay: do you query the whole table every time? |
| 13:23.51 | *** join/#utah skyguy0430[m] (skyguy0430@gateway/shell/matrix.org/x-ialtevdoyysmrlxp) |
| 13:30.56 | jlp_znc | first question... do you do updates/deletes on this table, or is it strictly inserts and queries? |
| 13:32.18 | jlp_znc | if the latter, then as BigBen212 said, you probably need some better indexes. have you tried an EXPLAIN on your queries? that will show you where you are doing e.g. table scans vs. index lookups. |
| 13:33.06 | jlp_znc | if you are doing significant updates/deletes in addition to inserts, make sure you are vacuuming the table. modern pg should have autovacuum turned on in any case. |
| 13:34.34 | jlp_znc | this type of usage can also cause index bloat... rebuilding the indexes can often help. |
| 13:35.35 | jlp_znc | if your query plan from EXPLAIN looks weird, you might also try ANALYZE on the table(s). VACUUM ANALYZE is a good way to kill two birds with one operation. |
| 14:15.26 | *** join/#utah josephscott (~josephsco@74.114.153.251) |
| 14:22.37 | spangborn | deletes youtah |
| 14:23.50 | *** join/#utah tiwula (~lane@70.89.246.241-Busname-UT.hfc.comcastbusiness.net) |
| 14:36.36 | BigBen212 | spangborn: so harsh, but think of the fragmentation this may cause. Use truncate instead |
| 14:36.57 | spangborn | lol |
| 14:45.14 | *** join/#utah braxtron (~braxtron@216.160.248.178) |
| 14:54.00 | *** join/#utah josephscott (~josephsco@74.114.153.251) |
| 17:15.34 | jfindlay | jlp_znc: I'm not directly involved with either the inserts or the queries, but I will investigate |
| 17:18.43 | jfindlay | BigBen212, jlp_znc: https://wiki.postgresql.org/wiki/Show_database_bloat: iname: idx_salt_returns_*, wastedbytes: 3672178688 |
| 17:18.56 | jfindlay | that looks like a large amount of waste to me |
| 17:19.28 | jfindlay | 3.4 GiB |
| 17:23.32 | BigBen212 | that's a nice query I'll have to try out on my own databases |
| 17:24.47 | BigBen212 | I got some materialized views where the refresh takes a bit longer than I'd like so I wonder if a cleanup is in order to speed things up |
| 18:08.05 | *** join/#utah jima (jima@fedora/jima) |
| 18:22.30 | *** join/#utah josephscott (~josephsco@74.114.153.251) |
| 19:14.22 | jlp_znc | wow, that's a bit of bloat. so when you cleared out the tables did you truncate them? as BigBen212 mentioned, that's a lot better than DELETE if you don't want to preserve any data. even if you do want to preserve data, it is sometimes faster to (in a transaction) create a new table, rename the old one out of the way and rename the new one in place, then (outside of the transaction) select the records |
| 19:14.28 | jlp_znc | from the old one you want to preserve and insert them into the new one (whatever job is still writing to the table will be writing to the new one after your transaction) |
| 19:26.22 | jfindlay | I just truncated all tables |
| 19:26.33 | jfindlay | I don't care about the data beyond a few hours |
| 19:27.01 | jfindlay | it would have been interesting to look at the accumulated months, but it's just VM resource usage |
| 19:30.18 | frostyfrog | â |
| 19:40.22 | spangborn | <PROTECTED> |
| 20:34.56 | jfindlay | my screen doesn't work for the invisible ink |
| 20:55.49 | JPaul | I can totally see it. it says " " |
| 21:04.49 | jlp_znc | hunter2 |
| 21:14.34 | jlp_znc | http://bash.org/?965249 |
| 21:14.45 | jlp_znc | ^^ the problem with our society. |
| 21:14.54 | jlp_znc | only 7 guns. |
| 21:20.00 | *** join/#utah josephscott (~josephsco@74.114.153.251) |
| 21:28.29 | jfindlay | I only see ******* |
| 21:29.10 | jfindlay | ~trump youtah |
| 21:29.10 | infobot | ACTION deports youtah |
| 21:29.59 | jlp_znc | ~warren jfindlay |
| 21:29.59 | infobot | ACTION pays jfindlay 15 bucks an hour for minimum wage |