Re: [HACKERS] UPDATE performance degradation (6.5.1)

On Tue, 27 Jul 1999, Tom Lane wrote:

> Date: Tue, 27 Jul 1999 10:39:40 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] UPDATE performance degradation (6.5.1) 
> 
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > 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 !
> 
> If the table contains only one row, why are you bothering with an
> index on it?
> 

This table with one row is just for test. In production it will
contain many thousands of msg_id. I didn't test yet waht will happens
if I populate table by thousands of row. But could imagine how long
it will be updated. Ooh. 


> > Is this a design problem ? 
> 
> Only that space in tables and indexes can't be re-used until vacuum.
> I'm not sure if there's any good way around that or not...

So, I need a cron job to vaccuum database. I'm curious how mysql works
so fast and has no problem in Web environment. I know some sites with
mysql logging and millions of updates every day.
Oleg

18:54[om]:/usr/local/apache/comps/discovery/db>psql discovery -c 'select * from hits'
msg_id|count|first_access                |last_access                 
------+-----+----------------------------+---------------------------- 1463|44417|Tue 27 Jul 10:30:18 1999 MSD|Tue 27
Jul18:44:31 1999 MSD  123|58814|Mon 26 Jul 22:54:54 1999 MSD|Tue 27 Jul 10:29:54 1999 MSD    4|  219|Mon 26 Jul
22:48:481999 MSD|Mon 26 Jul 22:49:02 1999 MSD    2|  418|Mon 26 Jul 22:47:28 1999 MSD|Mon 26 Jul 22:48:12 1999 MSD
1| 211|Mon 26 Jul 22:46:44 1999 MSD|Mon 26 Jul 22:47:09 1999 MSD   13|    1|Sat 24 Jul 23:56:57 1999 MSD|
             1464|    1|Tue 27 Jul 18:17:51 1999 MSD|                            
 
(7 rows)

and after vacuum analyze:

-rw-------   1 postgres users        8192 Jul 27 18:54 hits
-rw-------   1 postgres users     1703936 Jul 27 18:54 hits_pkey

Why hits_pkey is so big ? I have only 7 rows in the table.


> 
>             regards, tom lane
> 

_____________________________________________________________
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