X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id BAFF0D1B53E for ; Mon, 8 Sep 2003 10:51:29 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 17492-01 for ; Mon, 8 Sep 2003 07:51:01 -0300 (ADT) Received: from mail.ipv6.estpak.ee (tunnelbroker.ipv6.estpak.ee [194.126.101.242]) by svr1.postgresql.org (Postfix) with ESMTP id 1AEDAD1B4F1 for ; Mon, 8 Sep 2003 07:50:58 -0300 (ADT) Received: from andri.ipv6.estpak.ee ([2001:7d0:0:10:202:a5ff:fe67:9c13]) by mail.ipv6.estpak.ee with esmtp (Exim 4.12) id 19wJbl-000MMg-00; Mon, 08 Sep 2003 13:50:57 +0300 From: Andri Saar To: Tom Lane , Hannu Krosing Subject: Re: Serious issues with CPU usage Date: Mon, 8 Sep 2003 13:50:23 +0300 User-Agent: KMail/1.5.2 Cc: pgsql-performance@postgresql.org References: <20030905215635.68C1173595@MX2.estpak.ee> <28675.1062806700@sss.pgh.pa.us> In-Reply-To: <28675.1062806700@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200309081350.23312.andris@estpak.ee> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/118 X-Sequence-Number: 3484 Hope that you don't find it too distracting, I decided to answer to emails in one go. ---- On Saturday 06 September 2003 03:05, Tom Lane wrote: > indexscans. If you've also got sequential-scan queries, and you're > doing many zillion updates between vacuums, the answer is to vacuum > more often. A decent rule of thumb is to vacuum whenever you've updated > more than about 10% of the rows in a table since your last vacuum. Basically I do this: 1) select about ~700 ID's I have to poll 2) poll them 3) update those 700 rows in that "table" I used (~2700 rows total). And I do this cycle once per minute, so yes, I've got a zillion updates. 700 of 2700 is roughly 25%, so I'd have to vacuum once per minute? The manual actually had a suggestion of vacuuming after big changes, but I didn't think it was that bad. ----- On Saturday 06 September 2003 12:10, Hannu Krosing wrote: > Could it be that FSM is too small for your vacuum interval ? > > Also, you could try running REINDEX (instead of or in addition to plain > VACUUM) and see if this is is an index issue. VACUUM ANALYZE helped to lessen the load. Not as much as VACUUM FULL, but still bring it down to reasonable level. > 1. What types of queries do you run, and how often ? First, cycle posted above; second, every 5 minutes ~40 SELECTs that include that table. I left the once-per-minute poller offline this weekend, and the CPU usage didn't creep up. > 2. How is your database tuned (postgresql.conf settings) ? shared_buffers = 13000 max_fsm_relations = 100000 max_fsm_pages = 1000000 max_locks_per_transaction = 256 wal_buffers = 64 sort_mem = 32768 vacuum_mem = 16384 fsync = false effective_cache_size = 60000 Using these settings I was able to bring CPU usage down to a more reasonable level: http://andri.estpak.ee/cpu1.png This is much better than the first graph (see http://andri.estpak.ee/cpu0.png ), but you can still see CPU usage creeping up. VACUUM FULL was done at 03:00 and 09:00. The small drop at ~12:45 is thanks to VACUUM ANALYZE. If this is the best you can get with postgres right now, then I'll just have to increase the frequency of VACUUMing, but that feels like a hackish solution :( > 3. How much memory does your machine have ? 1 gigabyte. -- andri