Re: VERY slow after many updates

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.
>

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.