pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat happens on a commit?
5+ messages / 2 participants
[nested] [flat]
* What happens on a commit?
@ 2007-05-22 17:25 Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Hoover @ 2007-05-22 17:25 UTC (permalink / raw)
To: pgsql-admin
I am trying to diagnose a slowness issue in some of my databases. I think
the issue is due to a hardware/network upgrade over the weekend, but like
all dba's, I have to prove it.
Right now, I have almost all commits being issues taking between 1 and 5
seconds to complete. The can take as long as 20 seconds. What exactly
happens when a commit is issues to PostgreSQL?
Is there a way to trace what is going on internally? I do have a large
process that I can start that will run many transactions during a single
session.
Anyway, any help would be appreciated.
Thanks,
Chris
PG 8.1.3
RH 4.0 AS
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: What happens on a commit?
@ 2007-05-22 18:10 Chris Hoover <revoohc@gmail.com>
parent: Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Hoover @ 2007-05-22 18:10 UTC (permalink / raw)
To: pgsql-admin
I found an e-mail showing how to strace and here is what I have found. Does
it help/mean anything to those who can decode this?
We are installing PostgreSQL from the official rpms.
Chris
time strace -c -p 2690
Process 2690 attached - interrupt to quit
Process 2690 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
42.80 13.003288 251 51790 recv
17.47 5.306686 30 175916 send
16.23 4.930150 14 342118 stat64
10.26 3.117593 18 174533 write
8.22 2.497767 9 292070 gettimeofday
2.63 0.798323 462 1727 fdatasync
1.83 0.554745 6 92179 _llseek
0.46 0.138782 11 12896 brk
0.09 0.026920 19 1433 semop
0.02 0.006847 4 1734 time
0.00 0.000494 16 31 read
0.00 0.000253 23 11 open
0.00 0.000088 7 12 close
0.00 0.000013 13 1 kill
0.00 0.000010 10 1 sigreturn
0.00 0.000001 1 1 fsync
------ ----------- ----------- --------- --------- ----------------
100.00 30.381960 1146453 total
real 27m21.090s
user 0m5.379s
sys 0m23.073s
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: What happens on a commit?
@ 2007-05-22 19:30 Alvaro Herrera <alvherre@commandprompt.com>
parent: Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2007-05-22 19:30 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover escribió:
> I found an e-mail showing how to strace and here is what I have found. Does
> it help/mean anything to those who can decode this?
>
> We are installing PostgreSQL from the official rpms.
>
> Chris
>
> time strace -c -p 2690
> Process 2690 attached - interrupt to quit
> Process 2690 detached
> % time seconds usecs/call calls errors syscall
> ------ ----------- ----------- --------- --------- ----------------
> 42.80 13.003288 251 51790 recv
> 17.47 5.306686 30 175916 send
This means that 60% of the time spent by system calls is due to network
operations. But they account to 18.3 seconds, whereas this
> real 27m21.090s
> user 0m5.379s
> sys 0m23.073s
says that the total time spent is 27:21. So you have to explain the
remaining 27:03 which are elsewhere.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: What happens on a commit?
@ 2007-05-23 14:13 Chris Hoover <revoohc@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Hoover @ 2007-05-23 14:13 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-admin
Ok, we are looking more into this. In the mean time, can someone please
explain to me (or point me to the correct documentation) what happens in
PostgreSQL when a commit is issued. What exactly does the database do?
Thanks,
Chris
On 5/22/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> Chris Hoover escribió:
> > I found an e-mail showing how to strace and here is what I have
> found. Does
> > it help/mean anything to those who can decode this?
> >
> > We are installing PostgreSQL from the official rpms.
> >
> > Chris
> >
> > time strace -c -p 2690
> > Process 2690 attached - interrupt to quit
> > Process 2690 detached
> > % time seconds usecs/call calls errors syscall
> > ------ ----------- ----------- --------- --------- ----------------
> > 42.80 13.003288 251 51790 recv
> > 17.47 5.306686 30 175916 send
>
> This means that 60% of the time spent by system calls is due to network
> operations. But they account to 18.3 seconds, whereas this
>
> > real 27m21.090s
> > user 0m5.379s
> > sys 0m23.073s
>
> says that the total time spent is 27:21. So you have to explain the
> remaining 27:03 which are elsewhere.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: What happens on a commit?
@ 2007-05-23 14:18 Alvaro Herrera <alvherre@commandprompt.com>
parent: Chris Hoover <revoohc@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2007-05-23 14:18 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover escribió:
> Ok, we are looking more into this. In the mean time, can someone please
> explain to me (or point me to the correct documentation) what happens in
> PostgreSQL when a commit is issued. What exactly does the database do?
It writes a commit WAL record and flushes (fsync) the WAL up to that
point. This is the more important bit. Also, async notifies are sent
(those you started with NOTIFY), and files corresponding to some
commands are deleted (e.g. TRUNCATE, CLUSTER, REINDEX). Temp tables
with ON COMMIT setting may be operated upon.
It entirely depends on what the transaction did.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-05-23 14:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-22 17:25 What happens on a commit? Chris Hoover <revoohc@gmail.com>
2007-05-22 18:10 ` Chris Hoover <revoohc@gmail.com>
2007-05-22 19:30 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-05-23 14:13 ` Chris Hoover <revoohc@gmail.com>
2007-05-23 14:18 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox