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@xxxxxxxxxxxxxx>
To: "Alex Wang" <alex@xxxxxxxxxxxx>
Cc: "postgres performance list" <pgsql-performance@xxxxxxxxxxxxxx>
Sent: Saturday, November 19, 2005 7:12 PM
Subject: Re: 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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
