X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E105C9DD552 for ; Thu, 8 Dec 2005 13:09:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55141-09 for ; Thu, 8 Dec 2005 13:09:50 -0400 (AST) X-Greylist: delayed 01:19:43.390029 by SQLgrey- Received: from linux.spb.org (linux.spb.org [195.70.198.16]) by postgresql.org (Postfix) with ESMTP id 182EA9DD07B for ; Thu, 8 Dec 2005 13:09:48 -0400 (AST) Received: from office1.i-free.ru (unknown [81.222.216.82]) by linux.spb.org (Postfix) with ESMTP id C3680CFA52 for ; Thu, 8 Dec 2005 20:10:23 +0300 (MSK) Received: from [192.168.0.250] (helo=deepcore) by office1.i-free.ru with smtp (Exim 3.36 #1 (Debian)) id 1EkPH8-0002Zw-00 for ; Thu, 08 Dec 2005 20:09:46 +0300 Date: Thu, 8 Dec 2005 20:10:29 +0300 From: Evgeny Gridasov To: pgsql-performance@postgresql.org Subject: slow COMMITs Message-Id: <20051208201029.f36a44ca.eugrid@fpm.kubsu.ru> In-Reply-To: References: X-Mailer: Sylpheed version 2.1.8 (GTK+ 2.6.2; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/248 X-Sequence-Number: 16069 Hi everybody! My system is 2xXEON 3 GHz, 4GB RAM, RAID-10 (4 SCSI HDDs), running Postgres 8.1.0, taken from CVS REL8_1_STABLE, compiled with gcc-3.4 with options "-march=nocona -O2 -mfpmath=sse -msse3". Hyperthreading is disabled. There are about 300,000 - 500,000 transactions per day. Database size is about 14 Gigabytes. The problem is that all queries run pretty good except transaction COMMITs. Sometimes it takes about 300-500 ms to commit a transaction and it is unacceptebly slow for my application. I had this problem before, on 8.0.x and 7.4.x, but since 8.1 upgrade all queries began to work very fast except commit. BTW, I ran my own performance test, a multithreaded typical application user emulator, on 7.4 and 8.1. 8.1 performance was 8x times faster than 7.4, on the same machine and with the same config file settings. Some settings from my postgresql.conf: shared_buffers = 32768 temp_buffers = 32768 work_mem = 12228 bg_writer_delay = 400 wal_buffers = 128 commit_delay = 30000 checkpoint_segments = 8 effective_cache_size = 262144 # postgres is the one and the only application on this machine default_statistics_target = 250 all statistic collection enabled autovacuum runs every 120 seconds. vacuum is run after 2000 updates, analyze is run after 1000 updates. I've run vmstat to monitor hard disk activity. It was 50-500 Kb/sec for reading and 200-1500 Kb/sec for writing. There are some peak hdd reads and writes (10-20Mb/s) but commit time does not always depend upon them. What parameters should I tune? -- Evgeny Gridasov Software Engineer I-Free, Russia