X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7389DDBB80 for ; Sat, 19 Nov 2005 08:30:22 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13970-10 for ; Sat, 19 Nov 2005 12:30:24 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from alexwang.com (alexwang.com [220.132.178.72]) by svr1.postgresql.org (Postfix) with ESMTP id 03C7ADBC17 for ; Sat, 19 Nov 2005 08:30:18 -0400 (AST) Received: from alexxp ([192.168.0.2]) (authenticated bits=0) by alexwang.com (8.13.5/8.13.5) with ESMTP id jAJCUX2x018898; Sat, 19 Nov 2005 20:30:38 +0800 (CST) Message-ID: <006b01c5ed04$f640ef70$0200a8c0@alexxp> From: "Alex Wang" To: "Csaba Nagy" Cc: "postgres performance list" References: <002601c5ecdd$503279e0$0200a8c0@alexxp> <1132398732.10890.480.camel@coppola.muc.ecircle.de> <005201c5ed01$7f15c310$0200a8c0@alexxp> <1132402372.10890.487.camel@coppola.muc.ecircle.de> Subject: Re: VERY slow after many updates Date: Sat, 19 Nov 2005 20:29:47 +0800 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2527 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-twbsd-MailScanner-Information: Please contact the ISP for more information X-twbsd-MailScanner: Found to be clean X-twbsd-MailScanner-From: alex@alexwang.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=2.415 required=5 tests=[AWL=-1.449, RCVD_IN_NJABL_DUL=1.713, RCVD_IN_WHOIS_INVALID=2.151] X-Spam-Score: 2.415 X-Spam-Level: ** X-Archive-Number: 200511/397 X-Sequence-Number: 15654 Great infomation. I didn't know that update is equal to delete+insert in Postgres. I would be more careful on designing the database access method in this case. Thanks, Alex ----- Original Message ----- From: "Csaba Nagy" To: "Alex Wang" Cc: "postgres performance list" Sent: Saturday, November 19, 2005 8:12 PM Subject: Re: [PERFORM] VERY slow after many updates > 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" >> To: "Alex Wang" >> Cc: "postgres performance list" >> 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. > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.