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