pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL strugling during high load
21+ messages / 12 participants
[nested] [flat]
* PostgreSQL strugling during high load
@ 2005-05-13 12:52 Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:00 ` Re: PostgreSQL strugling during high load Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 21+ messages in thread
From: Mindaugas Riauba @ 2005-05-13 12:52 UTC (permalink / raw)
To: pgsql-performance
Hello,
We have problems with one postgresql database with high
data change rate. Actually we are already under pressure
to change postgresql to Oracle.
I cannot post schema and queries to list but can do this
privately.
Tables are not big (20000-150000 rows each) but have very high
turnover rate - 100+ updates/inserts/deletes/selects per second.
So contents of database changes very fast. Problem is that when
pg_autovacuum does vacuum those changes slows down too much.
And we keep autovacuum quite aggressive (-v 1000 -V 0.5 -a 1000
-A 0.1 -s 10) to not bloat database and to avoid bigger impact.
analyze seems not to impact performance too much.
Tables have 2-3 indexes each and one table have foreign key
contraint. Postgresql is 8.0.1. vmstat shows that IO and CPU are
not saturated. DB is on RAID1+0 controller with battery backed write
cache.
What can we tune to improve performance in our case? Please help
to defend PostgreSQL against Oracle in this case :).
Thanks,
Mindaugas
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
@ 2005-05-13 13:00 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
1 sibling, 0 replies; 21+ messages in thread
From: Steinar H. Gunderson @ 2005-05-13 13:00 UTC (permalink / raw)
To: pgsql-performance
On Fri, May 13, 2005 at 03:52:38PM +0300, Mindaugas Riauba wrote:
> Tables are not big (20000-150000 rows each) but have very high
> turnover rate - 100+ updates/inserts/deletes/selects per second.
> So contents of database changes very fast. Problem is that when
> pg_autovacuum does vacuum those changes slows down too much.
> And we keep autovacuum quite aggressive (-v 1000 -V 0.5 -a 1000
> -A 0.1 -s 10) to not bloat database and to avoid bigger impact.
> analyze seems not to impact performance too much.
Are you using the bgwriter?
See http://developer.postgresql.org/~wieck/vacuum_cost/ for details.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
@ 2005-05-13 13:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 17:33 ` Re: PostgreSQL strugling during high load Mischa Sandberg <mischa.sandberg@telus.net>
1 sibling, 2 replies; 21+ messages in thread
From: Tom Lane @ 2005-05-13 13:42 UTC (permalink / raw)
To: Mindaugas Riauba <mind@bi.lt>; +Cc: pgsql-performance
"Mindaugas Riauba" <mind@bi.lt> writes:
> ... So contents of database changes very fast. Problem is that when
> pg_autovacuum does vacuum those changes slows down too much.
The "vacuum cost" parameters can be adjusted to make vacuums fired
by pg_autovacuum less of a burden. I haven't got any specific numbers
to suggest, but perhaps someone else does.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-05-13 14:10 ` Mindaugas Riauba <mind@bi.lt>
2005-05-13 14:12 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 15:23 ` Re: PostgreSQL strugling during high load Cosimo Streppone <cosimo@streppone.it>
2005-05-16 02:26 ` Re: PostgreSQL strugling during high load Matthew T. O'Connor <matthew@zeut.net>
1 sibling, 3 replies; 21+ messages in thread
From: Mindaugas Riauba @ 2005-05-13 14:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> > ... So contents of database changes very fast. Problem is that when
> > pg_autovacuum does vacuum those changes slows down too much.
>
> The "vacuum cost" parameters can be adjusted to make vacuums fired
> by pg_autovacuum less of a burden. I haven't got any specific numbers
> to suggest, but perhaps someone else does.
It looks like that not only vacuum causes our problems. vacuum_cost
seems to lower vacuum impact but we are still noticing slow queries "storm".
We are logging queries that takes >2000ms to process.
And there is quiet periods and then suddenly 30+ slow queries appears in
log within the same second. What else could cause such behaviour? WAL log
switch? One WAL file seems to last <1 minute.
And also in slow queries log only function call is shown. Maybe it is
possible
to get exact query which slows everything down in the serverlog?
Thanks,
Mindaugas
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
@ 2005-05-13 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:45 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-19 16:54 ` Re: PostgreSQL strugling during high load Donald Courtney <Donald.Courtney@Sun.COM>
2 siblings, 2 replies; 21+ messages in thread
From: Tom Lane @ 2005-05-13 14:12 UTC (permalink / raw)
To: Mindaugas Riauba <mind@bi.lt>; +Cc: pgsql-performance
"Mindaugas Riauba" <mind@bi.lt> writes:
> It looks like that not only vacuum causes our problems. vacuum_cost
> seems to lower vacuum impact but we are still noticing slow queries "storm".
> We are logging queries that takes >2000ms to process.
> And there is quiet periods and then suddenly 30+ slow queries appears in
> log within the same second. What else could cause such behaviour?
Checkpoints? You should ensure that the checkpoint settings are such
that checkpoints don't happen too often (certainly not oftener than
every five minutes or so), and make sure the bgwriter is configured
to dribble out dirty pages at a reasonable rate, so that the next
checkpoint doesn't have a whole load of stuff to write.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 14:12 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-05-13 14:45 ` Mindaugas Riauba <mind@bi.lt>
2005-05-13 16:12 ` Re: PostgreSQL strugling during high load Steinar H. Gunderson <sgunderson@bigfoot.com>
1 sibling, 1 reply; 21+ messages in thread
From: Mindaugas Riauba @ 2005-05-13 14:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> > It looks like that not only vacuum causes our problems. vacuum_cost
> > seems to lower vacuum impact but we are still noticing slow queries
"storm".
> > We are logging queries that takes >2000ms to process.
> > And there is quiet periods and then suddenly 30+ slow queries appears
in
> > log within the same second. What else could cause such behaviour?
>
> Checkpoints? You should ensure that the checkpoint settings are such
> that checkpoints don't happen too often (certainly not oftener than
> every five minutes or so), and make sure the bgwriter is configured
> to dribble out dirty pages at a reasonable rate, so that the next
> checkpoint doesn't have a whole load of stuff to write.
bgwriter settings are default. bgwriter_delay=200, bgwriter_maxpages=100,
bgwriter_percent=1. checkpoint_segments=8, checkpoint_timeout=300,
checkpoint_warning=30.
But there's no checkpoint warnings in serverlog. And btw we are running
with fsync=off (yes I know the consequences).
Database from the viewpoint of disk is practically write only since amount
of data is smaller than memory available. I also added some 'vmstat 1'
output.
How to get more even load. As you see neither disk nor cpu looks too busy.
Thanks,
Mindaugas
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
1 0 0 194724 12140 10220 1045356 0 1 33 24 60 20 13 3
83
2 0 0 194724 11988 10228 1045464 0 0 12 0 1147 6107 13 4
82
0 2 0 194724 12172 10284 1046076 0 0 244 20692 2067 3117 8 8
84
1 0 0 194724 12164 10280 1045912 0 0 0 4 876 8831 15 11
74
3 0 0 194724 11704 10328 1045952 0 0 24 2116 928 5122 13 12
75
1 0 0 194724 11444 10236 1046264 0 0 340 0 1048 6538 19 10
71
1 0 0 194724 11924 10236 1045816 0 0 0 0 885 7616 14 20
66
0 0 0 194724 11408 10252 1044824 0 0 28 5488 959 4749 11 14
75
1 0 0 194724 11736 10296 1042992 0 0 460 2868 1001 4116 12 12
75
0 0 0 194724 12024 10296 1043064 0 0 36 0 903 5081 13 12
76
1 0 0 194724 12404 10240 1043440 0 0 280 0 899 4246 12 12
75
1 0 0 194724 13128 10236 1043472 0 0 0 0 1016 5394 12 10
78
0 4 0 194724 13064 10244 1043652 0 0 0 14736 1882 9290 10 15
74
0 4 0 194724 13056 10252 1043660 0 0 0 6012 1355 2378 2 3
95
12 21 0 194724 13140 10220 1043640 0 0 8 4 723 2984 5 3
92
1 0 0 194724 13712 10228 1043956 0 0 200 0 1144 10310 30 21
50
0 0 0 194724 13100 10220 1043992 0 0 4 0 840 4676 15 14
71
0 0 0 194724 13048 10296 1041212 0 0 4 6132 918 4074 10 10
80
0 0 0 194724 12688 10324 1041508 0 0 240 1864 849 3873 12 11
77
2 0 0 194724 12544 10240 1041944 0 0 32 0 1171 4844 14 7
78
1 0 0 194724 12384 10232 1041756 0 0 4 0 973 6063 16 9
75
1 0 0 194724 12904 10244 1042116 0 0 264 6052 1049 4762 15 14
71
0 0 0 194724 12616 10236 1042164 0 0 8 0 883 4748 13 8
79
2 0 0 194724 12576 10288 1042460 0 0 252 3136 857 3929 13 15
73
2 0 0 194724 12156 10284 1042504 0 0 0 0 858 8832 13 6
81
2 0 0 194724 12024 10284 1042556 0 0 0 0 834 4229 16 10
74
3 1 0 194724 12024 10364 1043096 0 0 316 10328 1024 5686 14 7
80
0 5 0 194724 12024 10352 1043116 0 0 4 7996 2156 2816 4 5
90
0 4 0 194724 12024 10360 1043124 0 0 4 8560 1369 2700 6 5
90
3 0 0 194724 12024 10264 1043124 0 0 0 4 1037 5132 14 15
71
1 1 0 194724 11876 10264 1043176 0 0 4 0 932 7761 20 20
6
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 14:12 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:45 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
@ 2005-05-13 16:12 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 21+ messages in thread
From: Steinar H. Gunderson @ 2005-05-13 16:12 UTC (permalink / raw)
To: pgsql-performance
On Fri, May 13, 2005 at 05:45:45PM +0300, Mindaugas Riauba wrote:
> But there's no checkpoint warnings in serverlog. And btw we are running
> with fsync=off (yes I know the consequences).
Just a note here; since you have battery-backed hardware cache, you
probably won't notice that much of a slowdown with fsync=on. However, you are
already pushed, so... :-)
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 14:12 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-05-19 16:54 ` Donald Courtney <Donald.Courtney@Sun.COM>
1 sibling, 0 replies; 21+ messages in thread
From: Donald Courtney @ 2005-05-19 16:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom
Thanks for the post - I think I am getting this problem for
a synthetic workload at high connection loads. The whole
system seems to stop.
Can you give some examples on what to try out in the .conf file?
I tried
bgwriter_all_percent = 30, 10, and 3
Which I understand to mean 30%, 10% and 3% of the dirty pages should be
written out *between* checkpoints.
I didn't see any change in effect.
/regards
Don C.
Tom Lane wrote:
>"Mindaugas Riauba" <mind@bi.lt> writes:
>
>
>> It looks like that not only vacuum causes our problems. vacuum_cost
>>seems to lower vacuum impact but we are still noticing slow queries "storm".
>>We are logging queries that takes >2000ms to process.
>> And there is quiet periods and then suddenly 30+ slow queries appears in
>>log within the same second. What else could cause such behaviour?
>>
>>
>
>Checkpoints? You should ensure that the checkpoint settings are such
>that checkpoints don't happen too often (certainly not oftener than
>every five minutes or so), and make sure the bgwriter is configured
>to dribble out dirty pages at a reasonable rate, so that the next
>checkpoint doesn't have a whole load of stuff to write.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
@ 2005-05-13 15:23 ` Cosimo Streppone <cosimo@streppone.it>
2005-05-13 15:41 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2 siblings, 1 reply; 21+ messages in thread
From: Cosimo Streppone @ 2005-05-13 15:23 UTC (permalink / raw)
To: Mindaugas Riauba <mind@bi.lt>; +Cc: pgsql-performance
Mindaugas Riauba wrote:
>>The "vacuum cost" parameters can be adjusted to make vacuums fired
>>by pg_autovacuum less of a burden. I haven't got any specific numbers
>>to suggest, but perhaps someone else does.
>
> It looks like that not only vacuum causes our problems. vacuum_cost
> seems to lower vacuum impact but we are still noticing slow queries "storm".
> We are logging queries that takes >2000ms to process.
> And there is quiet periods and then suddenly 30+ slow queries appears in
> log within the same second. What else could cause such behaviour?
I've seen that happen when you're placing (explicitly or
*implicitly*) locks on the records you're trying to update/delete.
If you're willing to investigate, `pg_locks' system view holds
information about db locks.
--
Cosimo
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 15:23 ` Re: PostgreSQL strugling during high load Cosimo Streppone <cosimo@streppone.it>
@ 2005-05-13 15:41 ` Mindaugas Riauba <mind@bi.lt>
2005-05-13 16:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Mindaugas Riauba @ 2005-05-13 15:41 UTC (permalink / raw)
To: Cosimo Streppone <cosimo@streppone.it>; +Cc: pgsql-performance
> >>The "vacuum cost" parameters can be adjusted to make vacuums fired
> >>by pg_autovacuum less of a burden. I haven't got any specific numbers
> >>to suggest, but perhaps someone else does.
> >
> > It looks like that not only vacuum causes our problems. vacuum_cost
> > seems to lower vacuum impact but we are still noticing slow queries
"storm".
> > We are logging queries that takes >2000ms to process.
> > And there is quiet periods and then suddenly 30+ slow queries appears
in
> > log within the same second. What else could cause such behaviour?
>
> I've seen that happen when you're placing (explicitly or
> *implicitly*) locks on the records you're trying to update/delete.
>
> If you're willing to investigate, `pg_locks' system view holds
> information about db locks.
Hm. Yes. Number of locks varies quite alot (10-600). Now what to
investigate
further? We do not use explicit locks in our functions. We use quite simple
update/delete where key=something;
Some sample (select * from pg_locks order by pid) is below.
Thanks,
Mindaugas
| | 584302172 | 11836 | ExclusiveLock | t
17236 | 17230 | | 11836 | AccessShareLock | t
17236 | 17230 | | 11836 | RowExclusiveLock | t
127103 | 17230 | | 11836 | RowExclusiveLock | t
127106 | 17230 | | 11836 | RowExclusiveLock | t
127109 | 17230 | | 11836 | AccessShareLock | t
127109 | 17230 | | 11836 | RowExclusiveLock | t
127109 | 17230 | | 11837 | AccessShareLock | t
127109 | 17230 | | 11837 | RowExclusiveLock | t
17236 | 17230 | | 11837 | AccessShareLock | t
17236 | 17230 | | 11837 | RowExclusiveLock | t
127106 | 17230 | | 11837 | RowExclusiveLock | t
127103 | 17230 | | 11837 | RowExclusiveLock | t
| | 584302173 | 11837 | ExclusiveLock | t
127103 | 17230 | | 11838 | RowExclusiveLock | t
17236 | 17230 | | 11838 | RowExclusiveLock | t
127109 | 17230 | | 11838 | RowExclusiveLock | t
| | 584302174 | 11838 | ExclusiveLock | t
17285 | 17230 | | 11838 | AccessShareLock | t
17251 | 17230 | | 11838 | AccessShareLock | t
130516 | 17230 | | 11838 | AccessShareLock | t
127106 | 17230 | | 11838 | RowExclusiveLock | t
17278 | 17230 | | 11838 | AccessShareLock | t
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 15:23 ` Re: PostgreSQL strugling during high load Cosimo Streppone <cosimo@streppone.it>
2005-05-13 15:41 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
@ 2005-05-13 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-16 10:05 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2005-05-13 16:42 UTC (permalink / raw)
To: Mindaugas Riauba <mind@bi.lt>; +Cc: Cosimo Streppone <cosimo@streppone.it>; pgsql-performance
"Mindaugas Riauba" <mind@bi.lt> writes:
> Hm. Yes. Number of locks varies quite alot (10-600). Now what to
> investigate
> further? We do not use explicit locks in our functions. We use quite simple
> update/delete where key=something;
> Some sample (select * from pg_locks order by pid) is below.
The sample doesn't show any lock issues (there are no processes waiting
for ungranted locks). The thing that typically burns people is foreign
key conflicts. In current releases, if you have a foreign key reference
then an insert in the referencing table takes an exclusive row lock on
the referenced (master) row --- which means that two inserts using the
same foreign key value block each other.
You can alleviate the issue by making all your foreign key checks
deferred, but that just shortens the period of time the lock is held.
There will be a real solution in PG 8.1, which has sharable row locks.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 15:23 ` Re: PostgreSQL strugling during high load Cosimo Streppone <cosimo@streppone.it>
2005-05-13 15:41 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 16:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-05-16 10:05 ` Mindaugas Riauba <mind@bi.lt>
0 siblings, 0 replies; 21+ messages in thread
From: Mindaugas Riauba @ 2005-05-16 10:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Cosimo Streppone <cosimo@streppone.it>; pgsql-performance
> > Hm. Yes. Number of locks varies quite alot (10-600). Now what to
> > investigate
> > further? We do not use explicit locks in our functions. We use quite
simple
> > update/delete where key=something;
> > Some sample (select * from pg_locks order by pid) is below.
>
> The sample doesn't show any lock issues (there are no processes waiting
> for ungranted locks). The thing that typically burns people is foreign
> key conflicts. In current releases, if you have a foreign key reference
> then an insert in the referencing table takes an exclusive row lock on
> the referenced (master) row --- which means that two inserts using the
> same foreign key value block each other.
>
> You can alleviate the issue by making all your foreign key checks
> deferred, but that just shortens the period of time the lock is held.
> There will be a real solution in PG 8.1, which has sharable row locks.
In such case our foreign key contraint should not be an issue since it
is on msg_id which is pretty much unique among concurrent transactions.
And I noticed that "storms" happens along with higher write activity. If
bo in vmstat shows 25+MB in 2s then most likely I will get "storm" of slow
queries in serverlog. How to even write activity? fsync=off, bgwriter
settings
are default.
And is it possible to log which query in function takes the longest time
to complete?
Also do not know if it matters but PG database is on ext3 partition with
data=journal option.
Thanks,
Mindaugas
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
@ 2005-05-16 02:26 ` Matthew T. O'Connor <matthew@zeut.net>
2005-05-16 02:45 ` Re: PostgreSQL strugling during high load Thomas F. O'Connell <tfo@sitening.com>
2 siblings, 1 reply; 21+ messages in thread
From: Matthew T. O'Connor @ 2005-05-16 02:26 UTC (permalink / raw)
To: Mindaugas Riauba <mind@bi.lt>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Mindaugas Riauba wrote:
>>The "vacuum cost" parameters can be adjusted to make vacuums fired
>>by pg_autovacuum less of a burden. I haven't got any specific numbers
>>to suggest, but perhaps someone else does.
>>
>>
>
> It looks like that not only vacuum causes our problems. vacuum_cost
>seems to lower vacuum impact but we are still noticing slow queries "storm".
>We are logging queries that takes >2000ms to process.
> And there is quiet periods and then suddenly 30+ slow queries appears in
>log within the same second. What else could cause such behaviour? WAL log
>switch? One WAL file seems to last <1 minute.
>
>
How long are these quite periods? Do the "strom" periods correspond to
pg_autovacuum loops? I have heard from one person who had LOTS of
databases and tables that caused the pg_autovacuum to create a noticable
load just updateing all its stats. The solution in that case was to add
a small delay insidet the inner pg_autovacuum loop.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Re: PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-16 02:26 ` Re: PostgreSQL strugling during high load Matthew T. O'Connor <matthew@zeut.net>
@ 2005-05-16 02:45 ` Thomas F. O'Connell <tfo@sitening.com>
0 siblings, 0 replies; 21+ messages in thread
From: Thomas F. O'Connell @ 2005-05-16 02:45 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Mindaugas Riauba <mind@bi.lt>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Actually, that solution didn't work so well. Even very small delays
in the loop caused the entire loop to perform too slowly to be useful
in the production environment. I ended up producing a small patch out
of it :P, but we ended up using pgpool to reduce connections from
another part of the app, which made the pg_autovacuum spikes less
troublesome overall.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On May 15, 2005, at 9:26 PM, Matthew T. O'Connor wrote:
> Mindaugas Riauba wrote:
>
>
>>> The "vacuum cost" parameters can be adjusted to make vacuums fired
>>> by pg_autovacuum less of a burden. I haven't got any specific
>>> numbers
>>> to suggest, but perhaps someone else does.
>>>
>>>
>>
>> It looks like that not only vacuum causes our problems. vacuum_cost
>> seems to lower vacuum impact but we are still noticing slow
>> queries "storm".
>> We are logging queries that takes >2000ms to process.
>> And there is quiet periods and then suddenly 30+ slow queries
>> appears in
>> log within the same second. What else could cause such behaviour?
>> WAL log
>> switch? One WAL file seems to last <1 minute.
>>
>>
>
> How long are these quite periods? Do the "strom" periods
> correspond to pg_autovacuum loops? I have heard from one person
> who had LOTS of databases and tables that caused the pg_autovacuum
> to create a noticable load just updateing all its stats. The
> solution in that case was to add a small delay insidet the inner
> pg_autovacuum loop.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:42 ` Re: PostgreSQL strugling during high load Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-05-13 17:33 ` Mischa Sandberg <mischa.sandberg@telus.net>
1 sibling, 0 replies; 21+ messages in thread
From: Mischa Sandberg @ 2005-05-13 17:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mindaugas Riauba <mind@bi.lt>; pgsql-performance
Quoting Tom Lane <tgl@sss.pgh.pa.us>:
> "Mindaugas Riauba" <mind@bi.lt> writes:
> > ... So contents of database changes very fast. Problem is that
> when
> > pg_autovacuum does vacuum those changes slows down too much.
>
> The "vacuum cost" parameters can be adjusted to make vacuums fired
> by pg_autovacuum less of a burden. I haven't got any specific
> numbers
> to suggest, but perhaps someone else does.
I solved one problem by cranking sleep scaling to -S 20.
It made pg_autovacuum back off longer during extended periods of heavy
disk-intensive query activity. Our update activity is near-constant
insert rate, then once or twice a day, massive deletes.
--
Dreams come true, not free.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
@ 2005-05-19 18:12 Anjan Dave <adave@vantage.com>
2005-05-19 18:29 ` Re: PostgreSQL strugling during high load Donald Courtney <Donald.Courtney@Sun.COM>
2005-05-19 19:43 ` Re: PostgreSQL strugling during high load Vivek Khera <vivek@khera.org>
2005-05-19 19:57 ` Re: PostgreSQL strugling during high load Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 21+ messages in thread
From: Anjan Dave @ 2005-05-19 18:12 UTC (permalink / raw)
To: Donald Courtney <Donald.Courtney@Sun.COM>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
What platform is this?
We had similar issue (PG 7.4.7). Raising number of checkpoint segments to 125, seperating the WAL to a different LUN helped, but it's still not completely gone.
As far as disk I/O is concerned for flushing the buffers out, I am not ruling out the combination of Dell PERC4 RAID card, and the RH AS 3.0 Update3 being a problem.
Thanks,
Anjan
-----Original Message-----
From: Donald Courtney [mailto:Donald.Courtney@Sun.COM]
Sent: Thu 5/19/2005 12:54 PM
To: Tom Lane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL strugling during high load
Tom
Thanks for the post - I think I am getting this problem for
a synthetic workload at high connection loads. The whole
system seems to stop.
Can you give some examples on what to try out in the .conf file?
I tried
bgwriter_all_percent = 30, 10, and 3
Which I understand to mean 30%, 10% and 3% of the dirty pages should be
written out *between* checkpoints.
I didn't see any change in effect.
/regards
Don C.
Tom Lane wrote:
>"Mindaugas Riauba" <mind@bi.lt> writes:
>
>
>> It looks like that not only vacuum causes our problems. vacuum_cost
>>seems to lower vacuum impact but we are still noticing slow queries "storm".
>>We are logging queries that takes >2000ms to process.
>> And there is quiet periods and then suddenly 30+ slow queries appears in
>>log within the same second. What else could cause such behaviour?
>>
>>
>
>Checkpoints? You should ensure that the checkpoint settings are such
>that checkpoints don't happen too often (certainly not oftener than
>every five minutes or so), and make sure the bgwriter is configured
>to dribble out dirty pages at a reasonable rate, so that the next
>checkpoint doesn't have a whole load of stuff to write.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-19 18:12 Re: PostgreSQL strugling during high load Anjan Dave <adave@vantage.com>
@ 2005-05-19 18:29 ` Donald Courtney <Donald.Courtney@Sun.COM>
2 siblings, 0 replies; 21+ messages in thread
From: Donald Courtney @ 2005-05-19 18:29 UTC (permalink / raw)
To: Anjan Dave <adave@vantage.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Anjan Dave wrote:
>What platform is this?
>
>
>
Its a DELL RH 4 with the xlog on a seperate external mounted file system.
The data directory is on a external mounted file system as well.
>We had similar issue (PG 7.4.7). Raising number of checkpoint segments to 125, seperating the WAL to a different LUN helped, but it's still not completely gone.
>
>
>
I'll try raising the number. I guess the bg* config variables don't do
much?
thanks
>As far as disk I/O is concerned for flushing the buffers out, I am not ruling out the combination of Dell PERC4 RAID card, and the RH AS 3.0 Update3 being a problem.
>
>Thanks,
>Anjan
>
> -----Original Message-----
> From: Donald Courtney [mailto:Donald.Courtney@Sun.COM]
> Sent: Thu 5/19/2005 12:54 PM
> To: Tom Lane
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] PostgreSQL strugling during high load
>
>
>
> Tom
>
> Thanks for the post - I think I am getting this problem for
> a synthetic workload at high connection loads. The whole
> system seems to stop.
>
> Can you give some examples on what to try out in the .conf file?
>
> I tried
> bgwriter_all_percent = 30, 10, and 3
>
> Which I understand to mean 30%, 10% and 3% of the dirty pages should be
> written out *between* checkpoints.
>
> I didn't see any change in effect.
>
> /regards
> Don C.
>
> Tom Lane wrote:
>
> >"Mindaugas Riauba" <mind@bi.lt> writes:
> >
> >
> >> It looks like that not only vacuum causes our problems. vacuum_cost
> >>seems to lower vacuum impact but we are still noticing slow queries "storm".
> >>We are logging queries that takes >2000ms to process.
> >> And there is quiet periods and then suddenly 30+ slow queries appears in
> >>log within the same second. What else could cause such behaviour?
> >>
> >>
> >
> >Checkpoints? You should ensure that the checkpoint settings are such
> >that checkpoints don't happen too often (certainly not oftener than
> >every five minutes or so), and make sure the bgwriter is configured
> >to dribble out dirty pages at a reasonable rate, so that the next
> >checkpoint doesn't have a whole load of stuff to write.
> >
> > regards, tom lane
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-19 18:12 Re: PostgreSQL strugling during high load Anjan Dave <adave@vantage.com>
@ 2005-05-19 19:43 ` Vivek Khera <vivek@khera.org>
2 siblings, 0 replies; 21+ messages in thread
From: Vivek Khera @ 2005-05-19 19:43 UTC (permalink / raw)
To: pgsql-performance
On May 19, 2005, at 2:12 PM, Anjan Dave wrote:
> As far as disk I/O is concerned for flushing the buffers out, I am
> not ruling out the combination of Dell PERC4 RAID card
>
That'd be my first guess as to I/O speed issues. I have some dell
hardware that by all means should be totally blowing out my other
boxes in speed, but the I/O sucks out the wazoo. I'm migrating to
opteron based DB servers with LSI branded cards (not the Dell re-
branded ones).
Vivek Khera, Ph.D.
+1-301-869-4449 x806
Attachments:
smime.p7s (application/pkcs7-signature, 2.4K) download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-19 18:12 Re: PostgreSQL strugling during high load Anjan Dave <adave@vantage.com>
@ 2005-05-19 19:57 ` Josh Berkus <josh@agliodbs.com>
2005-05-19 20:10 ` Re: PostgreSQL strugling during high load Steve Poe <spoe@sfnet.cc>
2 siblings, 1 reply; 21+ messages in thread
From: Josh Berkus @ 2005-05-19 19:57 UTC (permalink / raw)
To: Anjan Dave <adave@vantage.com>; +Cc: Donald Courtney <Donald.Courtney@sun.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Anjan,
> As far as disk I/O is concerned for flushing the buffers out, I am not
> ruling out the combination of Dell PERC4 RAID card, and the RH AS 3.0
> Update3 being a problem.
You know that Update4 is out, yes?
Update3 is currenly throttling your I/O by about 50%.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
2005-05-19 18:12 Re: PostgreSQL strugling during high load Anjan Dave <adave@vantage.com>
2005-05-19 19:57 ` Re: PostgreSQL strugling during high load Josh Berkus <josh@agliodbs.com>
@ 2005-05-19 20:10 ` Steve Poe <spoe@sfnet.cc>
0 siblings, 0 replies; 21+ messages in thread
From: Steve Poe @ 2005-05-19 20:10 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
Josh Berkus wrote:
>Anjan,
>
>
>
>>As far as disk I/O is concerned for flushing the buffers out, I am not
>>ruling out the combination of Dell PERC4 RAID card, and the RH AS 3.0
>>Update3 being a problem.
>>
>>
>
>You know that Update4 is out, yes?
>Update3 is currenly throttling your I/O by about 50%.
>
>
Is that 50% just for the Dell PERC4 RAID on RH AS 3.0? Sound like
severe context switching.
Steve Poe
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL strugling during high load
@ 2005-05-19 20:19 Anjan Dave <adave@vantage.com>
0 siblings, 0 replies; 21+ messages in thread
From: Anjan Dave @ 2005-05-19 20:19 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Donald Courtney <Donald.Courtney@sun.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Yes, I am using it another DB/application. Few more days and I'll have a
free hand on this box as well.
Thanks,
Anjan
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, May 19, 2005 3:58 PM
To: Anjan Dave
Cc: Donald Courtney; Tom Lane; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL strugling during high load
Anjan,
> As far as disk I/O is concerned for flushing the buffers out, I am not
> ruling out the combination of Dell PERC4 RAID card, and the RH AS 3.0
> Update3 being a problem.
You know that Update4 is out, yes?
Update3 is currenly throttling your I/O by about 50%.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2005-05-19 20:19 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-13 12:52 PostgreSQL strugling during high load Mindaugas Riauba <mind@bi.lt>
2005-05-13 13:00 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-05-13 13:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:10 ` Mindaugas Riauba <mind@bi.lt>
2005-05-13 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 14:45 ` Mindaugas Riauba <mind@bi.lt>
2005-05-13 16:12 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-05-19 16:54 ` Donald Courtney <Donald.Courtney@Sun.COM>
2005-05-13 15:23 ` Cosimo Streppone <cosimo@streppone.it>
2005-05-13 15:41 ` Mindaugas Riauba <mind@bi.lt>
2005-05-13 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-16 10:05 ` Mindaugas Riauba <mind@bi.lt>
2005-05-16 02:26 ` Matthew T. O'Connor <matthew@zeut.net>
2005-05-16 02:45 ` Thomas F. O'Connell <tfo@sitening.com>
2005-05-13 17:33 ` Mischa Sandberg <mischa.sandberg@telus.net>
2005-05-19 18:12 Re: PostgreSQL strugling during high load Anjan Dave <adave@vantage.com>
2005-05-19 18:29 ` Donald Courtney <Donald.Courtney@Sun.COM>
2005-05-19 19:43 ` Vivek Khera <vivek@khera.org>
2005-05-19 19:57 ` Josh Berkus <josh@agliodbs.com>
2005-05-19 20:10 ` Steve Poe <spoe@sfnet.cc>
2005-05-19 20:19 Re: PostgreSQL strugling during high load Anjan Dave <adave@vantage.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