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 C283FD1BA71 for ; Sat, 6 Sep 2003 09:11:02 +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 60787-05 for ; Sat, 6 Sep 2003 06:10:32 -0300 (ADT) Received: from fuji.krosing.net (unknown [194.204.44.104]) by svr1.postgresql.org (Postfix) with ESMTP id BBBF1D1B97B for ; Sat, 6 Sep 2003 06:10:24 -0300 (ADT) Received: from fuji.krosing.net (localhost.localdomain [127.0.0.1]) by fuji.krosing.net (8.12.8/8.12.8) with ESMTP id h869AFEx006606; Sat, 6 Sep 2003 12:10:15 +0300 Received: (from hannu@localhost) by fuji.krosing.net (8.12.8/8.12.8/Submit) id h869AE48006604; Sat, 6 Sep 2003 12:10:14 +0300 X-Authentication-Warning: fuji.krosing.net: hannu set sender to hannu@tm.ee using -f Subject: Re: Serious issues with CPU usage From: Hannu Krosing To: andris@neti.ee Cc: pgsql-performance@postgresql.org In-Reply-To: <20030905215635.68C1173595@MX2.estpak.ee> References: <20030905215635.68C1173595@MX2.estpak.ee> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1062839412.4982.11.camel@fuji.krosing.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4 Date: Sat, 06 Sep 2003 12:10:13 +0300 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/112 X-Sequence-Number: 3478 andris@neti.ee kirjutas L, 06.09.2003 kell 00:58: > Hi, > > i'm having _serious_ issues of postgres hogging up the CPU over time. A graph > showing this can be seen at http://andri.estpak.ee/cpu0.png . > > The database is running on Redhat 9 (stock 2.4.20-8 kernel), on a reiserfs > partition (~8% usage - no problem there), and this problem has been with > PostgreSQL 7.3.2 (both package provided by Redhat and self-rebuilt package) > and 7.3.4 (i used the 7.3.4 SRPM available at postgres ftp site). > > A VACUUM FULL is a remedy to this problem, but a simple VACUUM isn't. 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. > This can be reproduced, I think, by a simple UPDATE command: > > database=# EXPLAIN ANALYZE UPDATE table SET random_int_field = 1, last_updated > = NOW() WHERE primary_key = 3772; > QUERY PLAN > --------------------------------------------------------------------------------------------------------------------------------- > Index Scan using table_pkey on table (cost=0.00..6.81 rows=1 width=83) > (actual time=0.09..0.10 rows=1 loops=1) > Index Cond: (primary_key = 3772) > Total runtime: 0.37 msec > > When I repeat this command using simple , I can see the "Total > runtime" time grow ever so slightly - creeping from 0.37 to 0.38, then 0.39 > etc. Would probably get higher if I had the patience. :) > > The table "table" used in this example has 2721 rows, so size isn't an issue here. Due to the MVCC the raw table size (file size) can be much bigger if you dont VACUUM often enough. > Any comments or suggestions are welcome. If more information is needed, let me > know and I'll post the needed details. 1. What types of queries do you run, and how often ? 2. How is your database tuned (postgresql.conf settings) ? 3. How much memory does your machine have ? BTW, are you sure that this is postgres that is using up the memory ? I've read that reiserfs is a CPU hog, so this may be something that does intensive disk access, so some IO stats would be useful as well as real data and index file sizes. You could also set up logging and then check if there are some pathological queries that run for several hour doing nested seqscans ;) ----------------------- Hannu