UPDATE performance degradation (6.5.1) - Mailing list pgsql-hackers

From Oleg Bartunov
Subject UPDATE performance degradation (6.5.1)
Date
Msg-id Pine.GSO.3.96.SK.990727110403.29708C-100000@ra
Whole thread Raw
Responses Re: [HACKERS] UPDATE performance degradation (6.5.1)
List pgsql-hackers
Hi,

after I got DBIlogging work, I run several tests and noticed performance 
degradation when doing  sequential updating of *one* row.

I have 5 processes updated the same row. I use
LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE

When I run 200 requests I got about 16 req/sec, which  is quite enough
for my purposes. I expected the same speed if I just increase a number of 
requests, but it  decreases. for 2000 requests I got about 10 req/sec
and for 20,000 - about  2.5 req/sec !
I see no reason for such performance degradation - no way to use
postgres for logging in 24*7*365 Web-site. Probably this is very 
specific case when several processes updates only one row,
but again, I see no reason for such big degradation.
Table hits itself contains only 1 row !
I'll try to elimanate httpd, perl in my test bench to  test only 
postgres, I dont' have right now such a tool, probable someone
already did this ? What tool I can use for testing concurrent update
Regards,    Oleg


This is my home machine, Linux 2.2.10. postgres 6.5.1
Load is about 2-2.5

Typical output of ps:

11:21[om]:/usr/local/apache/logs>psg disc1036  ?  S   24:17 /usr/local/pgsql/bin/postgres localhost httpd discovery
LOCK1040 ?  R   24:09 /usr/local/pgsql/bin/postgres localhost httpd discovery idle1042  ?  S   24:02
/usr/local/pgsql/bin/postgreslocalhost httpd discovery LOCK1044  ?  R   23:51 /usr/local/pgsql/bin/postgres localhost
httpddiscovery idle1046  ?  S   23:49 /usr/local/pgsql/bin/postgres localhost httpd discovery LOCK1048  ?  S   23:47
/usr/local/pgsql/bin/postgreslocalhost httpd discovery LOCK
 

I see only one process with SELECT, this is what I expected when use
IN SHARE ROW EXCLUSIVE MODE. Right ?

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83




pgsql-hackers by date:

Previous
From: Vince Vielhaber
Date:
Subject: Re: [HACKERS] postgres Web problem
Next
From: Tom Ivar Helbekkmo
Date:
Subject: Re: [HACKERS] Fwd: Joins and links