Message-ID: <002601c5ecdd$503279e0$0200a8c0@alexxp>
From: "Alex Wang" <alex@alexwang.com>
To: <pgsql-performance@postgresql.org>
Subject: VERY slow after many updates
Date: Sat, 19 Nov 2005 15:46:06 +0800
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original
Content-Transfer-Encoding: 7bit

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.