X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E59A3D1B4DA for ; Fri, 13 Feb 2004 01:01:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 84355-03 for ; Thu, 12 Feb 2004 21:01:33 -0400 (AST) Received: from morework.geizhals.at (home.geizhals.at [213.229.14.34]) by svr1.postgresql.org (Postfix) with ESMTP id 291B5D1B4D2 for ; Thu, 12 Feb 2004 21:01:29 -0400 (AST) Received: from localhost (morework [127.0.0.1]) by morework.geizhals.at (Postfix) with ESMTP id C5B8052413B for ; Fri, 13 Feb 2004 02:01:29 +0100 (CET) Received: from morework.geizhals.at ([127.0.0.1]) by localhost (morework [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16427-09 for ; Fri, 13 Feb 2004 02:01:29 +0100 (CET) Received: from geizhals.at (unknown [10.0.0.33]) by morework.geizhals.at (Postfix) with ESMTP id 81FF7524138 for ; Fri, 13 Feb 2004 02:01:29 +0100 (CET) Message-ID: <402C213A.4030105@geizhals.at> Date: Fri, 13 Feb 2004 01:58:34 +0100 From: "Marinos J. Yannikos" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b) Gecko/20031204 Thunderbird/0.4RC2 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: optimization ideas for frequent, large(ish) updates in frequently accessed DB? Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p5 at geizhals.at X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/152 X-Sequence-Number: 5676 Hi, one of our tables has to be updated frequently, but concurrently running SELECT-queries must also have low latency times (it's being accessed through a web interface). I'm looking for ideas that might improve the interactive performance of the system, without slowing down the updates too much. Here are the characteristics of the table and its use: - approx. 2 million rows - approx. 4-5 million rows per day are replaced in short bursts of 1-200k rows (average ~3000 rows per update) - the table needs 6 indexes (not all indexes are used all the time, but keeping them all the time slows the system down less than re-creating some of them just before they're needed and dropping them afterwards) - an "update" means that 1-200k rows with a common value in a particular field are replaced with an arbitrary number of new rows (with the same value in that field), i.e.: begin transaction; delete from t where id=5; insert into t (id,...) values (5,...); ... [1-200k rows] end; The problem is, that a large update of this kind can delay SELECT queries running in parallel for several seconds, so the web interface used by several people will be unusable for a short while. Currently, I'm using temporary tables: create table xyz as select * from t limit 0; insert into xyz ... ... begin transaction; delete from t where id=5; insert into t select * from xyz; end; drop table xyz; This is slightly faster than inserting directly into t (and probably faster than using COPY, even though using that might reduce the overall load on the database). What other possibilities are there, other than splitting up the 15 columns of that table into several smaller tables, which is something I'd like to avoid? Would replication help? (I doubt it, but haven't tried it yet...) Writing to one table (without indexes) and creating indexes and renaming it to the "read table" periodically in a double buffering-like fashion wouldn't work either(?), since the views and triggers would have to be re-created every time as well and other problems might arise. The postgresql.conf options are already reasonably tweaked for performance(IMHO), but perhaps some settings are particularly critical: shared_buffers=100000 (I tried many values, this seems to work well for us - 12GB RAM) wal_buffers=500 sort_mem=800000 checkpoint_segments=16 effective_cache_size=1000000 etc. Any help/suggestions would be greatly appreciated... Even if it's something like "you need a faster db box, there's no other way" ;-) Regards, Marinos