On Mon, 10 Jul 2006, Neil Hepworth wrote:
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!
First off, when is the last time you vacuum analyzed this DB and how often does the vacuum analyze happen. Please post the EXPLAIN ANALYZE output for each of the queries below.
Also, I would strongly urge you to upgrade to a more recent version of postgresql. We're currently up to 8.1.4 and it has tons of excellent performance enhancements as well as helpful features such as integrated autovacuum, point in time recovery backups, etc.
Also, I see that you're running with fsync = false. That's quite dangerous especially on a production system.
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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
-- Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
