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@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954