[PERFORM] What is postmaster doing?
Folks, I am running into a problem with the postmaster: from time to time, it runs for a long time. E.g., from top: 23425 postgres 20 0 22008 10m 10m R 99.9 0.5 21:45.87 postmaster I'd like to figure out what it is doing. How can I figure out what statement causes the problem? is there a way I can log all SQL statements to a file, together with the time it took to execute them? -- Dimi Paun d...@lattica.com Lattica, Inc. -- 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] What is postmaster doing?
On Wed, 2010-10-20 at 14:44 -0400, Dimi Paun wrote: Folks, is there a way I can log all SQL statements to a file, together with the time it took to execute them? -- Dimi Paun d...@lattica.com Lattica, Inc. This is controlled by settings in the postgresql.conf file. see the appropriate doc pagevv for your version http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html
Re: [PERFORM] What is postmaster doing?
On Wed, 2010-10-20 at 15:24 -0400, Reid Thompson wrote: This is controlled by settings in the postgresql.conf file. see the appropriate doc pagevv for your version http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html Thanks for the link Reid, this seems to be doing what I need. Too bad I couldn't figure out what was going on when I was experiencing the high load, but now that I have the logging enabled, it shouldn't be a problem to figure things out. -- Dimi Paun d...@lattica.com Lattica, Inc. -- 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] What is postmaster doing?
On Wed, 2010-10-20 at 14:44 -0400, Dimi Paun wrote: 23425 postgres 20 0 22008 10m 10m R 99.9 0.5 21:45.87 postmaster I'd like to figure out what it is doing. How can I figure out what statement causes the problem? It seems strange that the postmaster is eating 99% cpu. Is there a chance that it's flooded with connection attempts? Usually the work is done by backend processes, not the postmaster. The postmaster just does some management like accepting connections and starting new processes. Regards, Jeff Davis -- 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] What is postmaster doing?
Jeff Davis pg...@j-davis.com writes: On Wed, 2010-10-20 at 14:44 -0400, Dimi Paun wrote: 23425 postgres 20 0 22008 10m 10m R 99.9 0.5 21:45.87 postmaster I'd like to figure out what it is doing. How can I figure out what statement causes the problem? It seems strange that the postmaster is eating 99% cpu. Is there a chance that it's flooded with connection attempts? It's probably a backend process, not the postmaster --- I suspect the OP is using a version of ps that only tells you the original process name by default. ps auxww or ps -ef (depending on platform) is likely to be more informative. Looking into pg_stat_activity, even more so. 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] What is postmaster doing?
Dimi Paun wrote: Folks, I am running into a problem with the postmaster: from time to time, it runs for a long time. E.g., from top: 23425 postgres 20 0 22008 10m 10m R 99.9 0.5 21:45.87 postmaster I'd like to figure out what it is doing. How can I figure out what statement causes the problem? is there a way I can log all SQL statements to a file, together with the time it took to execute them? You can do one better: you can even explain the statements, based on the execution time. There is a module called auto explain: http://www.postgresql.org/docs/8.4/static/auto-explain.html For the log files, you can parse them using pgfouine and quickly find out the most expensive SQL statements. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions -- 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] What is postmaster doing?
On Wed, 2010-10-20 at 16:26 -0400, Tom Lane wrote: It seems strange that the postmaster is eating 99% cpu. Is there a chance that it's flooded with connection attempts? Maybe, I'll try to figure that one out next time it happens. It's probably a backend process, not the postmaster --- I suspect the OP is using a version of ps that only tells you the original process name by default. ps auxww or ps -ef (depending on platform) is likely to be more informative. Looking into pg_stat_activity, even more so. I'm running CentOS 5.5, using procps-3.2.7-16.el5. I cannot check more at this point as postmaster seems to have finished whatever it was doing, but I'll try to investigate better next time. -- Dimi Paun d...@lattica.com Lattica, Inc. -- 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] What is postmaster doing?
Dimi Paun d...@lattica.com writes: On Wed, 2010-10-20 at 16:26 -0400, Tom Lane wrote: It's probably a backend process, not the postmaster --- I suspect the OP is using a version of ps that only tells you the original process name by default. I'm running CentOS 5.5, using procps-3.2.7-16.el5. Hm, what ps options did you use? I'm having a hard time reproducing your display format on Fedora 13 (procps-3.2.8-7.fc13.x86_64). 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] What is postmaster doing?
On Wed, 2010-10-20 at 16:45 -0400, Tom Lane wrote: Hm, what ps options did you use? I'm having a hard time reproducing your display format on Fedora 13 (procps-3.2.8-7.fc13.x86_64). Sorry, it wasn't a ps output, it was a line from top(1). My to header says: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 23425 postgres 20 0 22008 10m 10m R 99.9 0.5 21:45.87 postmaster -- Dimi Paun d...@lattica.com Lattica, Inc. -- 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] What is postmaster doing?
Dimi Paun d...@lattica.com writes: On Wed, 2010-10-20 at 16:45 -0400, Tom Lane wrote: Hm, what ps options did you use? I'm having a hard time reproducing your display format on Fedora 13 (procps-3.2.8-7.fc13.x86_64). Sorry, it wasn't a ps output, it was a line from top(1). Oh, yeah, top typically doesn't give you the up-to-date process command line. Next time try ps, or pg_stat_activity. 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] What is postmaster doing?
On Wed, Oct 20, 2010 at 2:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dimi Paun d...@lattica.com writes: On Wed, 2010-10-20 at 16:45 -0400, Tom Lane wrote: Hm, what ps options did you use? I'm having a hard time reproducing your display format on Fedora 13 (procps-3.2.8-7.fc13.x86_64). Sorry, it wasn't a ps output, it was a line from top(1). Oh, yeah, top typically doesn't give you the up-to-date process command line. Next time try ps, or pg_stat_activity. Or use htop. it identifies all the basic postgresql processes by job, like logger process, writer process and so on. -- 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] What is postmaster doing?
On Wed, Oct 20, 2010 at 3:47 PM, Scott Marlowe scott.marl...@gmail.com wrote: Or use htop. it identifies all the basic postgresql processes by job, like logger process, writer process and so on. FYI, htop is available from the epel repo. -- To understand recursion, one must first understand recursion. -- 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] What is postmaster doing?
Dimi Paun wrote: Sorry, it wasn't a ps output, it was a line from top(1). My to header says: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 23425 postgres 20 0 22008 10m 10m R 99.9 0.5 21:45.87 postmaster Use top -c instead. On Linux that will show you what each of the clients is currently doing most of the time, the ones that are running for a long time at least. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, PostgreSQL 9.0 High Performance: http://www.2ndquadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
