Re: [PERFORM] CPU load
kiki wrote: The number of rows returned by the query varies, right now is: 49 row(s) Total runtime: 3,965.718 ms The table currently has 971582 rows. But the problem is that when database server is restarted everything works fine and fast. No heavy loads of the processor and as time passes situation with the processor is worsen. It would be interesting to know the result of EXPLAIN ANALYZE for the query, both when it performs well and when it doesn't. One thing I see right away when I look at your postgresql.conf is that you have set shared_buffers to an awfully small value of 2000, when you have enough memory on the machine (vmstat reports 2GB free memory, right?). Does the situation improve if you set it to a higher value? Yours, Laurenz Albe -- 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] Slow updates, poor IO
Hi, On Fri, Sep 26, 2008 at 07:24:55AM +1200, John Huttley wrote: I've just had an interesting encounter with the slow full table update problem that is inherent with MVCC Quite apart from the other excellent observations in this thread, what makes you think this is an MVCC issue exactly? A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] CPU load
It would be useful to confirm that this is a backend process. With top, hit the 'c' key to show the full path / description of the process. Backend postgres processes should then have more useful descriptions of what they are doing and identifying themselves. You can also confirm what query is causing that by lining up the process id from top with the one returned by: select current_query, procpid from pg_stat_activity where current_query not like 'IDLE%'; Or by simply using the process id for the where clause (where procpid = ). How often is the table being queried modified? Between the startup when the query is fast, and when it slows down, is there a lot of modification to its rows? On Fri, Sep 26, 2008 at 5:52 AM, Albe Laurenz [EMAIL PROTECTED]wrote: kiki wrote: The number of rows returned by the query varies, right now is: 49 row(s) Total runtime: 3,965.718 ms The table currently has 971582 rows. But the problem is that when database server is restarted everything works fine and fast. No heavy loads of the processor and as time passes situation with the processor is worsen. It would be interesting to know the result of EXPLAIN ANALYZE for the query, both when it performs well and when it doesn't. One thing I see right away when I look at your postgresql.conf is that you have set shared_buffers to an awfully small value of 2000, when you have enough memory on the machine (vmstat reports 2GB free memory, right?). Does the situation improve if you set it to a higher value? Yours, Laurenz Albe -- 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] Slow updates, poor IO
Hi Andrew, There are two problems. The first is the that if there is a table with a index and an update is performed on a non indexed field, the index is still re indexed. this is part of the trade-offs of MVCC. Apparently this is documented under 'MVCC' in the manual. It should be documented under 'performance' We should reasonably expect that the total amount of IO will go up, over a non-indexed table. The second thing is that the disk IO throughput goes way down. This is not an issue with MVCC, as such, except that it exposes the effect of a write to an indexed field. --even if you don't expect it. --john Andrew Sullivan wrote: Hi, On Fri, Sep 26, 2008 at 07:24:55AM +1200, John Huttley wrote: I've just had an interesting encounter with the slow full table update problem that is inherent with MVCC Quite apart from the other excellent observations in this thread, what makes you think this is an MVCC issue exactly? A
Re: [PERFORM] Slow updates, poor IO
Hi Greg, I've got 32M shared on a 1G machine and 16 checkpoint segments. I'll run some tests against 64 segments and see what happens. Your previous postings were extremely helpful wrt the MVCC issue. I thank you! -john Greg Smith wrote: On Fri, 26 Sep 2008, John Huttley wrote: runningupdate file set perms='0664' took about 10 mins What do you have checkpoint_segments and shared_buffers set to? If you want something that's doing lots of updates to perform well, you need to let PostgreSQL have a decent size chunk of memory to buffer the index writes with, so it's more likely they'll get combined into larger and therefore more easily sorted blocks rather than as more random ones. The randomness of the writes is why your write rate is so slow. You also need to cut down on the frequency of checkpoints which are very costly on this type of statement. Also: which version of PostgreSQL? 8.3 includes an improvement aimed at updates like this you might benefit from. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
