Обсуждение: 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 <dimi@lattica.com> Lattica, Inc.
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 <dimi@lattica.com>
> Lattica, Inc.
This is controlled by settings in the postgresql.conf file.
see the appropriate doc page vv for your version
http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html
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 page vv 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 <dimi@lattica.com> Lattica, Inc.
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
Jeff Davis <pgsql@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
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
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 <dimi@lattica.com> Lattica, Inc.
Dimi Paun <dimi@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
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 %MEM TIME+ COMMAND
23425 postgres 20 0 22008 10m 10m R 99.9 0.5 21:45.87 postmaster
--
Dimi Paun <dimi@lattica.com>
Lattica, Inc.
Dimi Paun <dimi@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
On Wed, Oct 20, 2010 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dimi Paun <dimi@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.
On Wed, Oct 20, 2010 at 3:47 PM, Scott Marlowe <scott.marlowe@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.
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 %MEM TIME+ 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 greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance":
http://www.2ndquadrant.com/books