Dear All,
We are using Postgres 8.3.7 in our java application. We are doing performances
tuning and load testing in our setup. we have noticed that ,some of our queries
to the database taking long time to return the results.Please find our setup
details belows.
We observed that postgres is running in windows is slower than the linux .
Machine & Database Details :
Windows configuration:
4 GB RAM
4*1.6 GHZ
windows 2008 server standard edition
Postgresql configuration:
shared_buffers: 1 GB
Effective_cache_size: 2GB
fsync: off (even we tested this parameter is on ,we observed the same
slowness )
Database Details :
Postgres Database : PostgreSQL 8.3.7.1
Driver Version : PostgreSQL 8.3 JDBC4 with SSL (build 604)
We are using 40 database connections.
We have few tables which will be having more amount data.While running our
application STATSDATA table will be created daily with table name with date.
like as STATSDATA8_21_2009
Schema for STATSDATA table
create table STATSDATA8_21_2009(
POLLID Numeric(19),
INSTANCE varchar(100),
TTIME Numeric(19),
VAL Numeric(13)) ;CREATE INDEX POLLID%_ndx on STATSDATA%(POLLID)
Schema for PolledData
create table PolledData(
"NAME" varchar(50) NOT NULL ,
"ID" BIGINT NOT NULL ,
"AGENT" varchar(50) NOT NULL ,
"COMMUNITY" varchar(100) NOT NULL ,
"PERIOD" INTEGER NOT NULL,
"ACTIVE" varchar(10),
"OID" varchar(200) NOT NULL,
"LOGDIRECTLY" varchar(10),
"LOGFILE" varchar(100),
"SSAVE" varchar(10),
"THRESHOLD" varchar(10),
"ISMULTIPLEPOLLEDDATA" varchar(10),
"PREVIOUSSEVERITY" INTEGER,
"NUMERICTYPE" INTEGER,
"SAVEABSOLUTES" varchar(10),
"TIMEAVG" varchar(10),
"PORT" INTEGER,
"WEBNMS" varchar(100),
"GROUPNAME" varchar(100),
"LASTCOUNTERVALUE" BIGINT ,
"LASTTIMEVALUE" BIGINT ,
"TIMEVAL" BIGINT NOT NULL ,
"POLICYNAME" varchar(100),
"THRESHOLDLIST" varchar(200),
"DNSNAME" varchar(100),
"SUFFIX" varchar(20),
"STATSDATATABLENAME" varchar(100),
"POLLERNAME" varchar(200),
"FAILURECOUNT" INTEGER,
"FAILURETHRESHOLD" INTEGER,
"PARENTOBJ" varchar(100),
"PROTOCOL" varchar(50),
"SAVEPOLLCOUNT" INTEGER,
"CURRENTSAVECOUNT" INTEGER,
"SAVEONTHRESHOLD" varchar(10),
"SNMPVERSION" varchar(10),
"USERNAME" varchar(30),
"CONTEXTNAME" varchar(30),
PRIMARY KEY ("ID","NAME","AGENT","OID"),
index PolledData0_ndx ( "NAME"),
index PolledData1_ndx ( "AGENT"),
index PolledData2_ndx ( "OID"),
index PolledData3_ndx ( "ID"),
index PolledData4_ndx ( "PARENTOBJ"),
)
We have 300k row's in PolledData Table.In each STATSDATA table ,we have almost
12 to 13 million rows. Every one minute interval ,we insert data into to
STATSDATA table. In our application ,we use insert and select query to
STATSDATA table at regular interval. Please let us know why the below query
takes more time to return the results. is there any thing we need to do to tune
the postgres database ?
Please find explain analyze output.
First Query :
postgres=# explain analyze SELECT COUNT(*) FROM ( SELECT ID, PolledData.AGENT, N
AME, INSTANCE, TTIME, VAL FROM PolledData, STATSDATA8_21_2009 WHERE ( (
PolledDa
ta.ID=STATSDATA8_21_2009.POLLID) AND ( ( TTIME >= 1250838027454) AND (
TTIME <=
1250838079654) ) ) ) t1;
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------------------------------------------
Aggregate (cost=773897.12..773897.13 rows=1 width=0) (actual time=17818.410..1
7818.412 rows=1 loops=1)
-> Merge Join (cost=717526.23..767505.06 rows=2556821 width=0) (actual time
=17560.469..17801.790 rows=13721 loops=1)
Merge Cond: (statsdata8_21_2009.pollid = ((polleddata.id)::numeric))
-> Sort (cost=69708.44..69742.49 rows=13619 width=8) (actual time=239
2.659..2416.093 rows=13721 loops=1)
Sort Key: statsdata8_21_2009.pollid
Sort Method: quicksort Memory: 792kB
-> Seq Scan on statsdata8_21_2009 (cost=0.00..68773.27 rows=136
19 width=8) (actual time=0.077..2333.132 rows=13721 loops=1)
Filter: ((ttime >= 1250838027454::numeric) AND (ttime <= 12
50838079654::numeric))
-> Materialize (cost=647817.78..688331.92 rows=3241131 width=8) (actu
al time=15167.767..15282.232 rows=21582 loops=1)
-> Sort (cost=647817.78..655920.61 rows=3241131 width=8) (actua
l time=15167.756..15218.645 rows=21574 loops=1)
Sort Key: ((polleddata.id)::numeric)
Sort Method: external merge Disk: 736kB
-> Seq Scan on polleddata (cost=0.00..164380.31 rows=3241
131 width=8) (actual time=1197.278..14985.665 rows=23474 loops=1)
Total runtime: 17826.511 ms
(14 rows)
Second Query :
postgres=# explain analyze SELECT COUNT(*) FROM ( SELECT ID, PolledData.AGENT,
N
AME, INSTANCE, TTIME, VAL FROM PolledData, STATSDATA8_20_2009 WHERE ( (
PolledDa
ta.ID=STATSDATA8_20_2009.POLLID) AND ( ( TTIME >= 1250767134601) AND (
TTIME <=
1250767384601) ) ) ) t1;
QUERY PLAN
--------------------------------------------------------------------------------
-----------------------------------------------------------------
Aggregate (cost=1238144.31..1238144.32 rows=1 width=0) (actual time=111796.187
..111796.188 rows=1 loops=1)
-> Merge Join (cost=1034863.23..1212780.47 rows=10145533 width=0) (actual t
ime=111685.204..111783.670 rows=13126 loops=1)
Merge Cond: (statsdata8_20_2009.pollid = ((polleddata.id)::numeric))
-> Sort (cost=387045.44..387168.91 rows=49389 width=8) (actual time=1
09756.892..109770.670 rows=13876 loops=1)
Sort Key: statsdata8_20_2009.pollid
Sort Method: quicksort Memory: 799kB
-> Seq Scan on statsdata8_20_2009 (cost=0.00..382519.60 rows=49
389 width=8) (actual time=16.898..109698.188 rows=13876 loops=1)
Filter: ((ttime >= 1250767134601::numeric) AND (ttime <= 12
50767384601::numeric))
-> Materialize (cost=647817.78..688331.92 rows=3241131 width=8) (actu
al time=1928.266..1960.672 rows=13915 loops=1)
-> Sort (cost=647817.78..655920.61 rows=3241131 width=8) (actua
l time=1928.253..1941.423 rows=5830 loops=1)
Sort Key: ((polleddata.id)::numeric)
Sort Method: external merge Disk: 744kB
-> Seq Scan on polleddata (cost=0.00..164380.31 rows=3241
131 width=8) (actual time=195.961..1724.824 rows=23474 loops=1)
Total runtime: 111805.644 ms
(14 rows)
Third Query
postgres=# explain analyze SELECT COUNT(*) FROM ( SELECT ID, PolledData.AGENT,
N
AME, INSTANCE, TTIME, VAL FROM PolledData, STATSDATA8_21_2009 WHERE ( (
PolledDa
ta.ID=STATSDATA8_21_2009.POLLID) AND ( ( TTIME >= 1250838027454) AND (
TTIME <=
1250838027454) ) ) union all SELECT ID, PolledData.AGENT, NAME, INSTANCE, TTIM
E, VAL FROM PolledData, STATSDATA8_20_2009 WHERE ( (
PolledData.ID=STATSDATA8_20
_2009.POLLID) AND ( ( TTIME >= 1250767134601) AND ( TTIME <=
1250767134601) ) )
)t1 ;
QUERY PLAN
--------------------------------------------------------------------------------
-----------------------------------------------------------------
Aggregate (cost=719553.16..719553.17 rows=1 width=0) (actual time=603669.894..
603669.895 rows=1 loops=1)
-> Append (cost=0.00..719553.15 rows=2 width=0) (actual time=12736.956..603
668.946 rows=228 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..203804.22 rows=1 width=0) (
actual time=12736.953..506562.673 rows=227 loops=1)
-> Nested Loop (cost=0.00..203804.20 rows=1 width=78) (actual t
ime=12736.949..506561.858 rows=227 loops=1)
Join Filter: ((public.polleddata.id)::numeric = statsdata8_
21_2009.pollid)
-> Seq Scan on statsdata8_21_2009 (cost=0.00..70574.88 ro
ws=1 width=32) (actual time=0.047..29066.227 rows=227 loops=1)
Filter: ((ttime >= 1250838027454::numeric) AND (ttime
<= 1250838027454::numeric))
-> Seq Scan on polleddata (cost=0.00..132939.93 rows=1929
3 width=54) (actual time=362.780..2066.030 rows=23474 loops=227)
-> Subquery Scan "*SELECT* 2" (cost=0.00..515748.94 rows=1 width=0) (
actual time=4855.541..97105.635 rows=1 loops=1)
-> Nested Loop (cost=0.00..515748.92 rows=1 width=78) (actual t
ime=4855.537..97105.628 rows=1 loops=1)
Join Filter: ((public.polleddata.id)::numeric = statsdata8_
20_2009.pollid)
-> Seq Scan on statsdata8_20_2009 (cost=0.00..382519.60 r
ows=1 width=32) (actual time=3136.008..93985.540 rows=1 loops=1)
Filter: ((ttime >= 1250767134601::numeric) AND (ttime
<= 1250767134601::numeric))
-> Seq Scan on polleddata (cost=0.00..132939.93 rows=1929
3 width=54) (actual time=371.394..3087.391 rows=23474 loops=1)
Total runtime: 603670.065 ms
(15 rows)
Please let me know if you need any more details in this.
Regards,
Pari
