Re: [PERFORM] CPU load
Thank's for your response. The situation is that the top result is when the server is already exhibiting problems. 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. I forget to mention that php scrip is executed as a web application (Apache web server 2.2.3, php installed as a Server API Apache 2.0 Handler) called periodically each 8 seconds. After the restart of the postgres server everything works fine for several hours, the web application has a fast response when opening a web page. But after some time postmaster process (sometimes two postmaster process both owned by postgres user) rises and response time of the web application becomes slow, i.e. opening a php page with postgres access last for 8-10 seconds or even more. The php configuration for the postgres is default PostgreSQL Support enabled PostgreSQL(libpq) Version 8.1.8 Multibyte character support enabled SSL support enabled Active Persistent Links 1 Active Links1 Directive Local Value Master Value pgsql.allow_persistent On On pgsql.auto_reset_persistent Off Off pgsql.ignore_notice Off Off pgsql.log_noticeOff Off pgsql.max_links Unlimited Unlimited pgsql.max_persistentUnlimited Unlimited >> If that's what it looks like your server is running just fine. Load >> of 1.31, 85+% idle, no wait time. Or is that top and vmstat output >> from when the server is running fine? > > Don't forget that there are 8 CPUs, and the backend will only run on one > of them. > > But I concur that this seems ok. > How many rows are returned? Is 0.6 seconds an unacceptable time for that? > > If there is a lot of sorting going on and the pages are residing in the > buffer, I would expect high CPU load. > > Normally, I am quite happy if my database is CPU bound. I start worrying > if I/O wait grows too high. > > 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] 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 '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
