Re: [PERFORM] High CPU Usage - PostgreSQL 7.3
Thanks for the reply.
The database is vacuum analysed regularly and during my testing I
tried running the vacuum analyse full immediately before the running
through the set of queries (which does help a bit - reduces the time
to about 80% but is is still over an hour, with basically 100% CPU).
I'll get back to you with the full explain analyse output (I need to
re-create my test database back to its original state and that takes a
while) but I assume the part you're after is that all queries are
sequential scans, which I initially thought was the problem. But it
is my understanding that I cannot make them index scans because a
large percentage of the table is being returned by the query
(typically 30%) so the planner will favour a sequential scan over an
index scan for such a query, correct? If the queries had been disk
bound (due to retrieving large amounts of data) I would have
understood but I am confused as to why a sequential scan would cause
such high CPU and not high disk activity.
Yes, I wish I could upgrade to the latest version of PostgreSQL but at
the moment my hands are tied due to dependencies on other applications
running on our server (obviously we need to update certain queries,
e.g. delete .. using.. and test with 8.1 first) - I will be pushing
for an upgrade as soon as possible. And the fsync=false is a
"compromise" to try to improve performance (moving to 8.1 would be
better compromise).
Neil
On 10/07/06, Jeff Frost <[EMAIL PROTECTED]> wrote:
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,
> '-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', '-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 = 1
> max_fsm_pages = 10
>
> 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 <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PERFORM] High CPU Usage - PostgreSQL 7.3
I should also explain that I run through these queries on multiple
tables and with some slightly different parameters for the
"consolidation" so I run through those 3 queries (or similar) 9 times
and this takes a total of about 2 hours, with high CPU usage. And I
am running the queries from a remote Java application (using JDBC),
the client is using postgresql-8.0-311.jdbc3.jar. The explain analyse
results I have provided below are from running via pgAdmin, not the
Java app (I did a vacuum analyse of the db before running them):
*** For the create ***:
-- Executing query:
BEGIN;
EXPLAIN ANALYZE CREATE TABLE fttemp1643 AS SELECT * FROM ftone LIMIT 0;
;
ROLLBACK;
ERROR: parser: parse error at or near "CREATE" at character 25
Now that surprised me! I hadn't done an explain on that query before
as it was so simple. Perhaps not permitted for creates? If I just
run the create:
-- Executing query:
CREATE TABLE fttemp1643 AS SELECT * FROM ftone LIMIT 0;
Query returned successfully with no result in 48 ms.
*** For the insert ***:
Subquery Scan "*SELECT*" (cost=59690.11..62038.38 rows=23483
width=16) (actual time=16861.73..36473.12 rows=560094 loops=1)
-> Aggregate (cost=59690.11..62038.38 rows=23483 width=16) (actual
time=16861.72..34243.63 rows=560094 loops=1)
-> Group (cost=59690.11..61451.32 rows=234827 width=16)
(actual time=16861.62..20920.12 rows=709461 loops=1)
-> Sort (cost=59690.11..60277.18 rows=234827 width=16)
(actual time=16861.62..18081.07 rows=709461 loops=1)
Sort Key: eppairdefnid, "start"
-> Seq Scan on ftone (cost=0.00..36446.66
rows=234827 width=16) (actual time=0.45..10320.91 rows=709461 loops=1)
Filter: ((consolidation = 60) AND ("start" <
(to_timestamp('2006-07-10 18:43:27.391103+1000'::text,
'-MM-DDHH24:00:00.0'::text))::timestamp without time zone))
Total runtime: 55378.68 msec
*** For the delete ***:
Hash Join (cost=0.00..30020.31 rows=425 width=14) (actual
time=3767.47..3767.47 rows=0 loops=1)
Hash Cond: ("outer".eppairdefnid = "inner".eppairdefnid)
-> Seq Scan on ftone (cost=0.00..23583.33 rows=1286333 width=10)
(actual time=0.04..2299.94 rows=1286333 loops=1)
-> Hash (cost=0.00..0.00 rows=1 width=4) (actual
time=206.01..206.01 rows=0 loops=1)
-> Seq Scan on fttemp1600384653 (cost=0.00..0.00 rows=1
width=4) (actual time=206.00..206.00 rows=0 loops=1)
Total runtime: 3767.52 msec
Thanks,
Neil
On 10/07/06, Neil Hepworth <[EMAIL PROTECTED]> wrote:
Thanks for the reply.
The database is vacuum analysed regularly and during my testing I
tried running the vacuum analyse full immediately before the running
through the set of queries (which does help a bit - reduces the time
to about 80% but is is still over an hour, with basically 100% CPU).
I'll get back to you with the full explain analyse output (I need to
re-create my test database back to its original state and that takes a
while) but I assume the part you're after is that all queries are
sequential scans, which I initially thought was the problem. But it
is my understanding that I cannot make them index scans because a
large percentage of the table is being returned by the query
(typically 30%) so the planner will favour a sequential scan over an
index scan for such a query, correct? If the queries had been disk
bound (due to retrieving large amounts of data) I would have
understood but I am confused as to why a sequential scan would cause
such high CPU and not high disk activity.
Yes, I wish I could upgrade to the latest version of PostgreSQL but at
the moment my hands are tied due to dependencies on other applications
running on our server (obviously we need to update certain queries,
e.g. delete .. using.. and test with 8.1 first) - I will be pushing
for an upgrade as soon as possible. And the fsync=false is a
"compromise" to try to improve performance (moving to 8.1 would be
better compromise).
Neil
On 10/07/06, Jeff Frost <[EMAIL PROTECTED]> wrote:
> 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;
>
Re: [PERFORM] suggested RAID controller for FreeBSD 6.1 + PostgreSQL 8.1
On Jul 5, 2006, at 7:46 PM, Kenji Morishige wrote: like to know what an ideal RAID controller that would be compatible with FreeBSD 6.1 would be these days. LSI MegaRAID 320-2X and put half disks on one channel, half on the other, and MIRROR+STRIPE them (ie, RAID10). There is nothing faster for FreeBSD 6. Just make sure you don't buy a Sun Fire X4100 to put it in, as it will not fit :-( smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] High CPU Usage - PostgreSQL 7.3
On Mon, 10 Jul 2006, Neil Hepworth wrote:
I should also explain that I run through these queries on multiple
tables and with some slightly different parameters for the
"consolidation" so I run through those 3 queries (or similar) 9 times
and this takes a total of about 2 hours, with high CPU usage. And I
am running the queries from a remote Java application (using JDBC),
the client is using postgresql-8.0-311.jdbc3.jar. The explain analyse
results I have provided below are from running via pgAdmin, not the
Java app (I did a vacuum analyse of the db before running them):
Neil, did you ever answer which version of 7.3 this is?
BTW, you mentioned that this takes 2 hours, but even looping over this 9 times
seems like it would only take 9 minutes (55 seconds for the SELECT and 4
seconds for the DELETE = 59 seconds times 9). Perhaps you should post the
explain analyze for the actual query that takes so long as the planner output
will likely be quite different.
One thing I noticed is that the planner seems quite incorrect about the number
of rows it expects in the SELECT. If you ran vacuum analyze before this,
perhaps your fsm settings are incorrect? What does vacuumdb -a -v output at
the end? I'm looking for something that looks like this:
INFO: free space map: 109 relations, 204 pages stored; 1792 total pages
needed
DETAIL: Allocated FSM size: 1000 relations + 2 pages = 182 kB shared
memory.
I see your fsm settings are non-default, so it's also possible I'm not used to
reading 7.3's explain analyze output. :-)
Also, what does vmstat output look like while the query is running? Perhaps
you're running into some context switching problems. It would be interesting
to know how the query runs on 8.1.x just to know if we're chasing an
optimization that's fixed already in a later version.
Subquery Scan "*SELECT*" (cost=59690.11..62038.38 rows=23483
width=16) (actual time=16861.73..36473.12 rows=560094 loops=1)
-> Aggregate (cost=59690.11..62038.38 rows=23483 width=16) (actual
time=16861.72..34243.63 rows=560094 loops=1)
-> Group (cost=59690.11..61451.32 rows=234827 width=16)
(actual time=16861.62..20920.12 rows=709461 loops=1)
-> Sort (cost=59690.11..60277.18 rows=234827 width=16)
(actual time=16861.62..18081.07 rows=709461 loops=1)
Sort Key: eppairdefnid, "start"
-> Seq Scan on ftone (cost=0.00..36446.66
rows=234827 width=16) (actual time=0.45..10320.91 rows=709461 loops=1)
Filter: ((consolidation = 60) AND ("start" <
(to_timestamp('2006-07-10 18:43:27.391103+1000'::text,
'-MM-DDHH24:00:00.0'::text))::timestamp without time zone))
Total runtime: 55378.68 msec
*** For the delete ***:
Hash Join (cost=0.00..30020.31 rows=425 width=14) (actual
time=3767.47..3767.47 rows=0 loops=1)
Hash Cond: ("outer".eppairdefnid = "inner".eppairdefnid)
-> Seq Scan on ftone (cost=0.00..23583.33 rows=1286333 width=10)
(actual time=0.04..2299.94 rows=1286333 loops=1)
-> Hash (cost=0.00..0.00 rows=1 width=4) (actual
time=206.01..206.01 rows=0 loops=1)
-> Seq Scan on fttemp1600384653 (cost=0.00..0.00 rows=1
width=4) (actual time=206.00..206.00 rows=0 loops=1)
Total runtime: 3767.52 msec
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] High CPU Usage - PostgreSQL 7.3
On Sun, 2006-07-09 at 19:52, Neil Hepworth wrote:
> 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,
> '-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', '-MM-DD
> HH24:00:00.0')::timestamp;
I don't need to see an explain analyze to make a guess here...
start < TO_TIMESTAMP('2006-06-27 18:43:27.391103+1000', '-MM-DD
HH24:00:00.0')::timestamp
is gonna be a problem because while you and I know that to_timestamp...
is gonna be a constant, pg 7.3 doesn't. I've run into this before.
Just run a query ahead of time with a simple:
select TO_TIMESTAMP('2006-06-27 18:43:27.391103+1000', '-MM-DD
HH24:00:00.0')::timestamp as starttime
and then pull that out and stick it into your query. do the same for
any other parts of the query like that.
That's assuming the issue here is that you're getting seq scans cause of
that part of the query.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
