Re: [HACKERS] UPDATE performance degradation (6.5.1) - Mailing list pgsql-hackers

Probably I found the problem. After running my test, whiich  became
very slow I looked at /usr/local/pgsql/data/base/discovery

-rw-------   1 postgres users     5070848 Jul 27 16:14 hits
-rw-------   1 postgres users     1409024 Jul 27 16:14 hits_pkey

This is for table with one row  after a lot of updates.
Too much. vacuum analyze this table was a good  medicine !
Is this a design problem ? 
Regards,    Oleg

On Tue, 27 Jul 1999, Oleg Bartunov wrote:

> Date: Tue, 27 Jul 1999 12:51:07 +0400 (MSD)
> From: Oleg Bartunov <oleg@sai.msu.su>
> To: pgsql-hackers@postgreSQL.org
> Subject: [HACKERS] UPDATE performance degradation (6.5.1)
> 
> 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 disc
>  1036  ?  S   24:17 /usr/local/pgsql/bin/postgres localhost httpd discovery LOCK
>  1040  ?  R   24:09 /usr/local/pgsql/bin/postgres localhost httpd discovery idle
>  1042  ?  S   24:02 /usr/local/pgsql/bin/postgres localhost httpd discovery LOCK
>  1044  ?  R   23:51 /usr/local/pgsql/bin/postgres localhost httpd discovery idle
>  1046  ?  S   23:49 /usr/local/pgsql/bin/postgres localhost httpd discovery LOCK
>  1048  ?  S   23:47 /usr/local/pgsql/bin/postgres localhost 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
> 
> 
> 

_____________________________________________________________
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