Re: [PERFORM] Query times change by orders of magnitude as DB ages
On Wed, 25 Nov 2009, Grzegorz Jaśkiewicz wrote: the out of order data layout is primary reason for index bloat. And that happens , and gets worse over time once data is more and more distributed. (random deletes, etc). That's not index bloat. Sure, having the table not in the same order as the index will slow down an index scan, but that's a completely different problem altogether. Index bloat is caused by exactly the same mechanism as table bloat. The index needs to have an entry for every row in the table that may be visible by anyone. As with the table, it is not possible to deterministically delete the rows as they become non-visible, so the index (and the table) will be left with dead entries on delete and update. The vacuum command performs garbage collection and marks these dead rows and index entries as free, so that some time in the future more data can be written to those places. Index bloat is when there is an excessive amount of dead space in an index. It can be prevented by (auto)vacuuming regularly, but can only be reversed by REINDEX (or of course deleting the index, or adding loads of new entries to fill up the dead space after vacuuming). Matthew -- for a in past present future; do for b in clients employers associates relatives neighbours pets; do echo The opinions here in no way reflect the opinions of my $a $b. done; done -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Query times change by orders of magnitude as DB ages
Hello, 2009/11/25 Richard Neill rn...@cam.ac.uk: It's a simple query, but using a complex view. So I can't really re-order it. View is inserted directly into your query by PG, and then reordered according to from_collapse_limit. Probably, problems lies in the view? How good is it performing? Or from_collapse_limit is _too low_, so view isn't expanded right? Are you saying that this means that the query planner frequently makes the wrong choice here? Look at explain analyze. If on some step estimation from planner differs by (for start) two order of magnitude from what's really retrieved, then there's a wrong statistics count. But if, on every step, estimation is not too far away from reality - you suffer from what i've described - planner can't reoder efficiently enough query. Because of it happen sometimes - i suspect gego. Or wrong statistics. I hadn't changed it from the defaults; now I've changed it to: autovacuum_max_workers = 6 autovacuum_vacuum_scale_factor = 0.002 autovacuum_analyze_scale_factor = 0.001 If your tables are not 100mln rows, that's agressive enough. On 100mln rows, this'd analyze table every 100k changed (inserted/updated/deleted) rows. Is this enough for you? Default on large tables are definatly too low. If you get now consistent times - then you've been hit by wrong statistics. Best regards, Sergey Aleynikov -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Query times change by orders of magnitude as DB ages
Hello, 2009/11/25 Richard Neill rn...@cam.ac.uk: Also, if you find odd statistics of freshly analyzed table - try increasing statistics target, using ALTER TABLE .. ALTER COLUMN .. SET STATISTICS ... If you're using defaults - it's again low for large tables. Start with 200, for example. Best regards, Sergey Aleynikov -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Query times change by orders of magnitude as DB ages
Sergey Aleynikov wrote: Hello, 2009/11/25 Richard Neill rn...@cam.ac.uk: Also, if you find odd statistics of freshly analyzed table - try increasing statistics target, using ALTER TABLE .. ALTER COLUMN .. SET STATISTICS ... If you're using defaults - it's again low for large tables. Start with 200, for example. Thanks. I already had it set way up: 3000. Is there a good description of exactly what analyse does, and how? (in particular, what sort of statistics it gathers). Richard -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Analyse without locking?
Dear All, I'm wondering whether Vacuum/analyse (notably by the autovaccuum daemon) is responsible for some deadlocks/dropouts I'm seeing. One particular table gets hit about 5 times a second (for single row updates and inserts) + associated index changes. This is a very light load for the hardware; we have 7 CPU cores idling, and very little disk activity. The query normally runs in about 20 ms. However, the query must always respond within 200ms, or userspace gets nasty errors. [we're routing books on a sorter machine, and the book misses its exit opportunity]. Although this is a low load, it's a bit like a heartbeat. The question is, could the autovacuum daemon (running either in vacuum or in analyse mode) be taking out locks on this table that sometimes cause the query response time to go way up (exceeding 10 seconds)? I think I've set up autovacuum to do little and often, using autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = 20 but I'm not sure this is doing exactly what I think it is. In particular, the system-wide I/O (and CPU) limit of autovacuum is negligible, but it's possible that queries may be waiting on locks. In particular, I want to make sure that the autovacuum daemon never holds any lock for more than about 50ms at a time. (or will release it immediately if something else wants it) Or am I barking up the wrong tree entirely? Thanks, Richard -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Analyse without locking?
On Thu, Nov 26, 2009 at 4:20 PM, Richard Neill rn...@cam.ac.uk wrote: Dear All, I'm wondering whether Vacuum/analyse (notably by the autovaccuum daemon) is responsible for some deadlocks/dropouts I'm seeing. One particular table gets hit about 5 times a second (for single row updates and inserts) + associated index changes. This is a very light load for the hardware; we have 7 CPU cores idling, and very little disk activity. The query normally runs in about 20 ms. However, the query must always respond within 200ms, or userspace gets nasty errors. [we're routing books on a sorter machine, and the book misses its exit opportunity]. Although this is a low load, it's a bit like a heartbeat. The question is, could the autovacuum daemon (running either in vacuum or in analyse mode) be taking out locks on this table that sometimes cause the query response time to go way up (exceeding 10 seconds)? I think I've set up autovacuum to do little and often, using autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = 20 those are basically thresholds. So in essence you are forcing your autovacuum to be active pretty often, And from what I can read here, you are looking for completely opposite behaviour. Unless you think statistical image of your table will be completely invalid, after 20 modifications to it, which I am sure is not true. -- GJ
Re: [PERFORM] Analyse without locking?
Richard Neill rn...@cam.ac.uk writes: I'm wondering whether Vacuum/analyse (notably by the autovaccuum daemon) is responsible for some deadlocks/dropouts I'm seeing. One particular table gets hit about 5 times a second (for single row updates and inserts) + associated index changes. This is a very light load for the hardware; we have 7 CPU cores idling, and very little disk activity. The query normally runs in about 20 ms. However, the query must always respond within 200ms, or userspace gets nasty errors. [we're routing books on a sorter machine, and the book misses its exit opportunity]. Although this is a low load, it's a bit like a heartbeat. The question is, could the autovacuum daemon (running either in vacuum or in analyse mode) be taking out locks on this table that sometimes cause the query response time to go way up (exceeding 10 seconds)? Hmm. Autovacuum does sometimes take an exclusive lock. It is supposed to release it on demand but if I recall the details correctly, that could involve a delay of about deadlock_timeout, or 1s by default. It would be reasonable to reduce deadlock_timeout to 100ms to ensure your external constraint is met. Delays of up to 10s would not be explained by that though. Do you have usage spikes of other types? I wonder in particular if you've got checkpoints smoothed out enough. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Analyse without locking?
On Thursday 26 November 2009 17:20:35 Richard Neill wrote: Dear All, I'm wondering whether Vacuum/analyse (notably by the autovaccuum daemon) is responsible for some deadlocks/dropouts I'm seeing. One particular table gets hit about 5 times a second (for single row updates and inserts) + associated index changes. This is a very light load for the hardware; we have 7 CPU cores idling, and very little disk activity. The query normally runs in about 20 ms. However, the query must always respond within 200ms, or userspace gets nasty errors. [we're routing books on a sorter machine, and the book misses its exit opportunity]. Although this is a low load, it's a bit like a heartbeat. The question is, could the autovacuum daemon (running either in vacuum or in analyse mode) be taking out locks on this table that sometimes cause the query response time to go way up (exceeding 10 seconds)? I think I've set up autovacuum to do little and often, using autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = 20 but I'm not sure this is doing exactly what I think it is. In particular, the system-wide I/O (and CPU) limit of autovacuum is negligible, but it's possible that queries may be waiting on locks. In particular, I want to make sure that the autovacuum daemon never holds any lock for more than about 50ms at a time. (or will release it immediately if something else wants it) Or am I barking up the wrong tree entirely? I would suggest enabling log_log_wait and setting deadlock_timeout to a low value - should give you more information. Andres -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] RAID card recommendation
Steve Crawford wrote: Greg Smith wrote: Jochen Erwied wrote: - Promise Technology Supertrak ES4650 + additional BBU I've never seen a Promise controller that had a Linux driver you would want to rely on under any circumstances... +1 I haven't tried Promise recently, but last time I did I determined that they got the name because they Promise the Linux driver for your card will be available real-soon-now. One more data point, it's not confidence inspiring that google turns up Promise Technologies customers that are quite vocal about suing them. http://www.carbonite.com/blog/post/2009/03/Further-clarification-on-our-lawsuit-against-Promise-Technologies.aspx -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
