Re: [HACKERS] UPDATE performance degradation (6.5.1)

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] UPDATE performance degradation (6.5.1)
Date: 1999-07-27 14:52:56
Message-ID: Pine.GSO.3.96.SK.990727184533.29708J-100000@ra
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, 27 Jul 1999, Tom Lane wrote:

> Date: Tue, 27 Jul 1999 10:39:40 -0400
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
> Cc: pgsql-hackers(at)postgreSQL(dot)org
> Subject: Re: [HACKERS] UPDATE performance degradation (6.5.1)
>
> Oleg Bartunov <oleg(at)sai(dot)msu(dot)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 Jul 18: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:48 1999 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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Zeugswetter Andreas IZ5 1999-07-27 14:57:06 Re: [HACKERS] UPDATE performance degradation (6.5.1)
Previous Message Thomas Lockhart 1999-07-27 14:47:34 i386 RPMs available for v6.5.1