From: Neil Hepworth <nhepworth@gmail.com>
To: pgsql-performance@postgresql.org
Subject: High CPU Usage - PostgreSQL 7.3
Date: Mon, 10 Jul 2006 10:52:42 +1000
Message-ID: <5a6f767a0607091752y5018f9eaw474dc5cf7de3c4c3@mail.gmail.com> (raw)
Hi,
I am running PostgreSQL 7.3 on a Linux box (RHEL 2.1 - Xeon 2.8GHz
with 1GB of RAM) and seeing very high CPU usage (normally over 90%)
when I am running the following queries, and the queries take a long
time to return; over an hour!
CREATE TEMPORARY TABLE fttemp1600384653 AS SELECT * FROM ftoneway LIMIT 0;
INSERT INTO fttemp1600384653 SELECT epId, TO_TIMESTAMP(start,
'YYYY-MM-DD HH24:00:00.0')::timestamp AS start, 60 AS consolidation,
SUM(cnt) AS cnt FROM ftone WHERE consolidation = 0 AND start <
TO_TIMESTAMP('2006-06-27 18:43:27.391103+1000', 'YYYY-MM-DD
HH24:00:00.0')::timestamp;
DELETE FROM ONLY ftone WHERE ftoneway.epId= fttemp1600384653.epId;
The only changes I've made to the default postgresql.comf file are listed below:
LC_MESSAGES = 'en_US'
LC_MONETARY = 'en_US'
LC_NUMERIC = 'en_US'
LC_TIME = 'en_US'
tcpip_socket = true
max_connections = 20
effective_cache_size = 32768
wal_buffers = 128
fsync = false
shared_buffers = 3000
max_fsm_relations = 10000
max_fsm_pages = 100000
The tables are around a million rows but when when I run against
tables of a few hundred thousand rows it still takes tens of minutes
with high CPU. My database does have a lot of tables (can be several
thousand), can that cause performance issues?
Thanks,
Neil
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nhepworth@gmail.com
Subject: Re: High CPU Usage - PostgreSQL 7.3
In-Reply-To: <5a6f767a0607091752y5018f9eaw474dc5cf7de3c4c3@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox