X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6EF1F52A4F for ; Tue, 22 Mar 2005 18:09:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18144-09 for ; Tue, 22 Mar 2005 18:08:53 +0000 (GMT) Received: from felix.adpcl.com (felix.adpcl.com [194.98.137.100]) by svr1.postgresql.org (Postfix) with ESMTP id 2E35056741 for ; Tue, 22 Mar 2005 18:08:50 +0000 (GMT) Received: from PCPATRICK (unknown [150.175.11.40]) by felix.adpcl.com (Postfix) with SMTP id 73FC97806 for ; Tue, 22 Mar 2005 19:11:49 +0100 (NFT) Message-ID: <002601c52f0a$2312a4d0$280baf96@PCPATRICK> From: "Patrick Vedrines" To: "performance pgsql" Subject: CPU 0.1% IOWAIT 99% for decisonnal queries Date: Tue, 22 Mar 2005 19:08:23 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0023_01C52F12.84BCCE10" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.163 tagged_above=0 required=5 tests=AWL, HTML_50_60, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200503/459 X-Sequence-Number: 11164 This is a multi-part message in MIME format. ------=_NextPart_000_0023_01C52F12.84BCCE10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi everyone, I'm developping a web decisonnal application based on=20 -Red Hat 3 ES -Postgresql 8.0.1=20 -Dell poweredge 2850, Ram 2Gb, 2 procs, 3 Ghz, 1Mb cache and 4 disks = ext3 10,000 r/mn I am alone in the box and there is not any crontab. I have 2 databases (A and B) with exactly the same schemas:=20 -one main table called "aggregate" having no indexes and supporting only = SELECT statements (loaded one time a month with a new bundle of datas). = Row size # 200 bytes (50 columns of type char(x) or integer)=20 -and several small 'reference' tables not shown by the following example = for clarity reasons. -Database A : aggregate contains 2,300,000 records ( 500 Mb) -Database B : aggregate contains 9,000,000 records ( 2 Gb) There is no index on the aggregate table since the criterias, their = number and their scope are freely choosen by the customers. The query : select sum(ca) =20 from aggregate =20 where (issue_date >=3D '2004-01' and issue_date <=3D '2004-02' = ); takes 5s on database A ( 5mn30s* the first time, probably to fill the = cache)=20 and 21mn* on database B (whatever it is the first time or not). explain shows sequential scan of course: Aggregate (cost=3D655711.85..655711.85 rows=3D1 width=3D4) -> Seq Scan on "aggregate" (cost=3D0.00..647411.70 rows=3D3320060 = width=3D4) Filter: ((issue_date >=3D '2004-01'::bpchar) AND (issue_date = <=3D '2004-02'::bpchar)) *Here is the 'top' display for these response times: 91 processes: 90 sleeping, 1 running, 0 zombie, 0 stopped CPU states: cpu user nice system irq softirq iowait idle total 0,0% 0,0% 0,2% 0,1% 0,0% 48,6% 51,0% cpu00 0,0% 0,0% 0,0% 0,0% 0,0% 0,0% 100,0% cpu01 0,0% 0,0% 1,0% 0,0% 0,0% 99,0% 0,0% cpu02 0,0% 0,0% 0,0% 0,5% 0,0% 0,0% 99,5% cpu03 0,0% 0,0% 0,0% 0,0% 0,0% 95,5% 4,5% Mem: 2061424k av, 2043944k used, 17480k free, 0k shrd, 6104k = buff 1551692k actv, 172496k in_d, 30452k in_c Swap: 2096440k av, 0k used, 2096440k free 1792852k = cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU = COMMAND 21983 postgres 20 0 9312 9312 8272 D 0,2 0,4 0:00 1 = postmaster 1 root 15 0 488 488 432 S 0,0 0,0 0:06 2 init 2 root RT 0 0 0 0 SW 0,0 0,0 0:00 0 = migration/0 For the 5s response time, the 'top' command shows 0% iowait and 25% cpu. - I guess this is a cache issue but how can I manage/control it ?=20 Is Postgres managing it's own cache or does it use the OS cache ? - Is using the cache is a good approach?=20 It does not seem to work for large databases : I tryed several different = values for postgres.conf and /proc/sys/kernel/shmmax without detecting = any response time enhancement (For example : shared_buffers =3D 190000 , = sort_mem =3D 4096 , effective_cache_size =3D 37000 and = kernel/shmmax=3D1200000000 ) Do I have to upgrade the RAM to 6Gb or/and buy faster HD (of what type?) = ? Moreover, a query on database B will destroy the cache previously build = for database A, increasing the response time for the next query on = database A. And I have in fact 15 databases ! - In my case, what should be the best parameters combination between = postgres.conf and /proc/sys/kernel/shmmax ? - is there a way to reduce the size of the "aggregate" table files (1Gb = + 1Gb + 1 Gb + 0.8Gb =3D 3.8Gb for the "aggregate" table instead of 2Gb = =3D 200 * 9,000,000 records) by playing with the data types or others = parameters (fillfactor ?).=20 Vacuum (even full) seems to be useless since the aggregate table = supports only 'copy aggregate from' and 'select'. - is it possible to define a sort of RAM filesystem (as it exists in = DOS/Windows) which I could create and populate my databases into ? = ...since the databases does not support updates for this application. Sorry for my naive questions and my poor english but any help or advise = will be greatly appreciated ! Patrick Vedrines PS (maybe of interest for some users like me) :=20 I created a partition on a new similar disk but on the last cylinders = (near the periphery) and copied the database B into it: the response = time is 25% faster (i.e. 15mn instead of 21mn). But 15 mn is still too = long for my customers (5 mn would be nice). ------=_NextPart_000_0023_01C52F12.84BCCE10 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi everyone,
 
I'm developping a web decisonnal = application based=20 on
-Red Hat 3 ES
-Postgresql 8.0.1
-Dell poweredge 2850, Ram 2Gb, 2 procs, = 3 Ghz, 1Mb=20 cache and 4 disks ext3 10,000 r/mn
I am alone in the box and there is not = any=20 crontab.
 
I have 2 databases (A and B) with = exactly the same=20 schemas:
-one main table called "aggregate" = having no=20 indexes and supporting only SELECT statements (loaded one time a month = with a=20 new bundle of datas). Row size # 200 bytes (50 columns of type char(x) = or=20 integer) 
-and several small 'reference' tables = not shown by=20 the following example for clarity reasons.
-Database A : aggregate contains = 2,300,000 records=20 ( 500 Mb)
-Database B : aggregate contains = 9,000,000=20 records ( 2 Gb)
 
There is no index on the aggregate = table since the=20 criterias, their number and their scope are freely choosen by the=20 customers.
 
The query :
        = select =20 sum(ca) 
        from=20 aggregate   
       =20 where  (issue_date >=3D '2004-01' and issue_date <=3D = '2004-02'=20 )
;
takes 5s on database A ( 5mn30s* the = first time,=20 probably to fill the cache)
and  21mn* on=20 database B (whatever it is the first time or not).
 
explain shows sequential scan of=20 course:
 Aggregate =20 (cost=3D655711.85..655711.85 rows=3D1 width=3D4)
   = ->  Seq Scan=20 on "aggregate"  (cost=3D0.00..647411.70 rows=3D3320060=20 width=3D4)
         Filter:=20 ((issue_date >=3D '2004-01'::bpchar) AND (issue_date <=3D=20 '2004-02'::bpchar))
 
*Here is the 'top' display for these = response=20 times:
91 processes: 90 sleeping, 1 running, = 0 zombie, 0=20 stopped
CPU states:  cpu    = user   =20 nice  system    irq  softirq =20 iowait   =20 idle
          =20 total    0,0%    0,0%   =20 0,2%   0,1%     0,0%   = 48,6%  =20 51,0%
          =20 cpu00    0,0%    0,0%   =20 0,0%   0,0%     0,0%    = 0,0% =20 100,0%
          =20 cpu01    0,0%    0,0%   =20 1,0%   0,0%     0,0%   = 99,0%
   =20 0,0%
          =20 cpu02    0,0%    0,0%   =20 0,0%   0,5%     0,0%   =20 0,0%  =20 99,5%
          =20 cpu03    0,0%    0,0%   =20 0,0%   0,0%     0,0%   = 95,5%    = 4,5%
Mem: =20 2061424k av, 2043944k used,   17480k=20 free,       0k shrd,    = 6104k=20 buff
           = ;       =20 1551692k actv,  172496k in_d,   30452k in_c
Swap: = 2096440k=20 av,       0k used, 2096440k=20 free           &nb= sp;    =20 1792852k cached
  PID = USER     PRI =20 NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU=20 COMMAND
21983 postgres  20   0  9312 9312  = 8272=20 D     0,2 =20 0,4   0:00   1 postmaster
    1=20 root      15   0   = 488 =20 488   432 S     0,0  0,0  =20 0:06   2 init
    2=20 root      RT   = 0    =20 0    0     0 SW    = 0,0 =20 0,0   0:00   0 migration/0
For the 5s response time, the 'top' = command shows=20 0% iowait and 25% cpu.
 
 
- I guess this is a cache issue but how = can I=20 manage/control it ?
Is Postgres managing it's own cache or does it use the OS cache = ?
 
- Is using the cache is a good approach?
It does not seem to work for large databases : I tryed several = different=20 values for postgres.conf and /proc/sys/kernel/shmmax without detecting = any=20 response time enhancement (For example : shared_buffers =3D = 190000 , sort_mem=20 =3D 4096 , effective_cache_size =3D 37000 = and kernel/shmmax=3D1200000000=20 )
Do I have to upgrade the RAM to 6Gb or/and buy faster HD (of = what=20 type?) ?
Moreover, a query on database B will destroy the cache previously = build for=20 database A, increasing the response time for the next query on database = A. And I=20 have in fact 15 databases !
 
- In my case, what should be the best parameters combination = between=20 postgres.conf and /proc/sys/kernel/shmmax ?
 
- is there a way to reduce the size of the "aggregate" table files = (1Gb +=20 1Gb + 1 Gb + 0.8Gb =3D 3.8Gb for the "aggregate" table instead of 2Gb = =3D 200 *=20 9,000,000 records) by playing with the data types or others parameters=20 (fillfactor ?).
Vacuum (even full) seems to be useless since the aggregate table = supports=20 only 'copy aggregate from' and 'select'.
 
- is it possible to define a sort of RAM filesystem (as = it exists=20 in DOS/Windows) which I could create and populate my databases into=20 ? ...since the databases does not support updates for this=20 application.
 
Sorry for my naive questions and my poor english but any help or = advise=20 will be greatly appreciated !
 
Patrick Vedrines
 
PS (maybe of interest for some users like me) :
I created a partition on a new similar disk but on the last = cylinders (near=20 the periphery) and copied the database B into it: the response = time is 25%=20 faster (i.e. 15mn instead of 21mn). But 15 mn is still too long for my = customers=20 (5 mn would be nice).
 
 
 
 
------=_NextPart_000_0023_01C52F12.84BCCE10--