Re: VERY slow after many updates

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Alex Wang <alex(at)alexwang(dot)com>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: VERY slow after many updates
Date: 2005-11-19 12:12:52
Message-ID: 1132402372.10890.487.camel@coppola.muc.ecircle.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Just for clarification, update is actually equal to delete+insert in
Postgres. So if you update rows, it's the same as you would delete the
row and insert a new version. So the table is bloating also in this
situation.
I think there is an added problem when you update, namely to get to a
row, postgres will traverse all dead rows matching the criteria... so
even if you have an index, getting 1 row which was updated 10000 times
will access 10000 rows only to find 1 which is still alive. So in this
case vacuuming should happen even more often, to eliminate the dead
rows.
And the index was probably only helping because the table was really
bloated, so if you vacuum it often enough you will be better off without
the index if the row count will stay low.

Cheers,
Csaba.

On Sat, 2005-11-19 at 13:05, Alex Wang wrote:
> Hi Csaba,
>
> Thanks for your reply.
>
> Yes, it's a "queue" table. But I did not perform many insert/delete before
> it becomes slow. After insert 10 records, I just do get/update continuously.
> After 24 hour, the whole database become very slow (not only the
> download_queue table but other tables, too). But you are right. Full vacuum
> fixes the problem. Thank you very much!
>
> I expect there will be less than 1000 records in the table. The index does
> obvous improvement on "SELECT task_id, username FROM download_queue WHERE
> username > '%s'" even there are only 100 records.
>
> Thanks,
> Alex
>
> ----- Original Message -----
> From: "Csaba Nagy" <nagy(at)ecircle-ag(dot)com>
> To: "Alex Wang" <alex(at)alexwang(dot)com>
> Cc: "postgres performance list" <pgsql-performance(at)postgresql(dot)org>
> Sent: Saturday, November 19, 2005 7:12 PM
> Subject: Re: [PERFORM] VERY slow after many updates
>
>
> > Alex,
> >
> > I suppose the table is a kind of 'queue' table, where you
> > insert/get/delete continuously, and the life of the records is short.
> > Considering that in postgres a delete will still leave you the record in
> > the table's file and in the indexes, just mark it as dead, your table's
> > actual size can grow quite a lot even if the number of live records will
> > stay small (you will have a lot of dead tuples, the more tasks
> > processed, the more dead tuples). So I guess you should vacuum this
> > table very often, so that the dead tuples are reused. I'm not an expert
> > on this, but it might be good to vacuum after each n deletions, where n
> > is ~ half the average size of the queue you expect to have. From time to
> > time you might want to do a vacuum full on it and a reindex.
> >
> > Right now I guess a vacuum full + reindex will help you. I think it's
> > best to do:
> >
> > vacuum download_queue;
> > vacuum full download_queue;
> > reindex download_queue;
> >
> > I think the non-full vacuum which is less obtrusive than the full one
> > will do at least some of the work and it will bring all needed things in
> > FS cache, so the full vacuum to be as fast as possible (vacuum full
> > locks exclusively the table). At least I do it this way with good
> > results for small queue-like tables...
> >
> > BTW, I wonder if the download_queue_user_index index is helping you at
> > all on that table ? Do you expect it to grow bigger than 1000 ?
> > Otherwise it has no point to index it.
> >
> > HTH,
> > Csaba.
> >
> > On Sat, 2005-11-19 at 08:46, Alex Wang wrote:
> >> I am using PostgreSQL in an embedded system which has only 32 or 64 MB
> >> RAM
> >> (run on PPC 266 MHz or ARM 266MHz CPU). I have a table to keep downlaod
> >> tasks. There is a daemon keep looking up the table and fork a new process
> >> to
> >> download data from internet.
> >>
> >> Daemon:
> >> . Check the table every 5 seconds
> >> . Fork a download process to download if there is new task
> >> Downlaod process (there are 5 download process max):
> >> . Update the download rate and downloaded size every 3 seconds.
> >>
> >> At begining, everything just fine. The speed is good. But after 24 hours,
> >> the speed to access database become very very slow. Even I stop all
> >> processes, restart PostgreSQL and use psql to select data, this speed is
> >> still very very slow (a SQL command takes more than 2 seconds). It is a
> >> small table. There are only 8 records in the table.
> >>
> >> The only way to solve it is remove all database, run initdb, create new
> >> database and insert new records. I tried to run vacummdb but still very
> >> slow.
> >>
> >> Any idea to make it faster?
> >>
> >> Thanks,
> >> Alex
> >>
> >> --
> >> Here is the table schema:
> >> create table download_queue (
> >> task_id SERIAL,
> >> username varchar(128),
> >> pid int,
> >> url text,
> >> filename varchar(1024),
> >> status int,
> >> created_time int,
> >> started_time int,
> >> total_size int8,
> >> current_size int8,
> >> current_rate int,
> >> CONSTRAINT download_queue_pkey PRIMARY KEY(task_id)
> >> );
> >> CREATE INDEX download_queue_user_index ON download_queue USING BTREE
> >> (username);
> >>
> >>
> >
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by MailScanner, and is
> > believed to be clean.
> >
>

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Guido Neitzer 2005-11-19 12:18:19 Re: VERY slow after many updates
Previous Message Alex Wang 2005-11-19 12:05:00 Re: VERY slow after many updates