X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 35090D6EB0 for ; Sat, 24 Sep 2005 08:23:04 -0300 (ADT) 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 97728-02 for ; Sat, 24 Sep 2005 11:22:58 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id 3BB0FD8F15 for ; Sat, 24 Sep 2005 08:22:57 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 327B833733; Sat, 24 Sep 2005 14:08:20 +0300 (EEST) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09391-05; Sat, 24 Sep 2005 14:08:19 +0300 (EEST) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id EB50E33731; Sat, 24 Sep 2005 14:08:18 +0300 (EEST) Message-ID: <007901c5c0fa$491d2150$0b00a8c0@forge> From: "Andy" To: "Gandeed Phanibhushan Rao-A18356" , References: <0B28F3C8F2581D419709A71814CA59D7032A88@ZMY16EXM67.ds.mot.com> Subject: Re: PostgreSQL configuration problem Date: Sat, 24 Sep 2005 14:22:42 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0076_01C5C113.6D0E4BC0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.305 required=5 tests=[AWL=0.304, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200509/280 X-Sequence-Number: 19017 This is a multi-part message in MIME format. ------=_NextPart_000_0076_01C5C113.6D0E4BC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MessageDo you vacuum the database? It is very important if you insert = and delete lot of rows.=20 I think this is your problem.=20 Best regards,=20 Andy. ----- Original Message -----=20 From: Gandeed Phanibhushan Rao-A18356=20 To: pgsql-admin@postgresql.org=20 Sent: Monday, September 19, 2005 4:31 PM Subject: [ADMIN] PostgreSQL configuration problem HI, I have a 128MB RAM based Linux (Redhat 9.0) desktop. I have installed = Postgres 8.0.3 server in my system, for my application usage. A bit novice, so not much aware of the configuration of tuning the = database. My application inserts the data into the database at the frequency of = 1 second and parallely at the period of once per day, a purging of the data for half-a-day older data. After a day or so (when the system has almost 200,000 records in the = database table), the system goes abnormally slow, and load increases to almost 18. The vmstat output when the system was normal is like this: procs memory swap io system = cpu r b w swpd free buff cache si so bi bo in cs = us sy id 0 0 0 124088 7208 1972 30428 0 1 6 49 230 400 = 8 8 85 0 0 0 124360 6884 1960 30228 0 15 8 49 232 435 = 9 9 82 3 0 0 125052 6904 1972 29612 0 4 0 40 228 454 = 9 10 81 0 0 0 126144 6744 1988 28484 0 3 0 41 231 452 = 9 10 82 0 1 1 124212 6608 1980 30728 0 4 674 37 307 592 = 8 10 83 0 0 0 124156 6848 1932 30444 0 8 604 61 311 575 = 9 9 81 But just before the load starts increasing, a sample of vmstat is like = this. r b w swpd free buff cache si so bi bo in cs = us sy id 0 1 1 133280 6772 1684 11048 2 7 19 55 232 406 = 8 8 84 0 4 1 133920 7024 1536 10812 142 1132 536 1182 392 594 = 4 3 93 1 2 1 134016 6964 1608 13512 188 306 550 344 369 698 = 7 6 87 3 0 1 133876 6956 1628 13868 39 154 101 198 275 584 = 11 9 81 1 7 2 131344 6384 1412 17052 313 351 1343 393 504 816 = 6 6 88 0 3 1 128748 7152 1516 15372 555 573 1226 616 579 1067 = 5 5 90 After the load has gone almost to 17, the sample of vmstat is as = below. r b w swpd free buff cache si so bi bo in cs = us sy id 0 11 2 139492 6468 1416 7196 36 40 93 84 244 22 = 7 7 85 0 7 2 139912 6368 1396 7500 580 331 1191 349 363 283 = 0 1 99 0 5 1 139596 6368 1516 7528 683 259 1285 280 382 309 = 0 1 98 0 10 1 139496 6368 1380 7228 525 242 1014 256 347 281 = 0 1 99 0 10 1 139832 6380 1432 6808 663 305 1282 312 383 287 = 0 1 98 0 10 1 139420 6368 1412 8048 568 241 1308 250 396 312 = 0 1 99 I am attaching the postgres configuration file used in our system. Any help to suggest what could be the reason for this load increase.. Thanks Phani. -------------------------------------------------------------------------= ----- ---------------------------(end of = broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ------=_NextPart_000_0076_01C5C113.6D0E4BC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message
Do you vacuum the database? It is = very=20 important if you insert and delete lot of rows.
I think this is your problem. =
 
Best regards,
Andy.
----- Original Message -----
From:=20 Gandeed=20 Phanibhushan Rao-A18356
Sent: Monday, September 19, = 2005 4:31=20 PM
Subject: [ADMIN] PostgreSQL = configuration=20 problem

HI,
 
I = have a 128MB RAM=20 based Linux (Redhat 9.0) desktop. I have installed Postgres 8.0.3 = server in my=20 system,
for = my application=20 usage.
 
A = bit novice, so=20 not much aware of the configuration of tuning the=20 database.
 
My = application=20 inserts the data into the database at the frequency of 1 second and = parallely=20 at the period of
once = per day, a=20 purging of the data for half-a-day older data.
 
After a day or so=20 (when the system has almost 200,000 records in the database table), = the system=20 goes abnormally
slow, and load=20 increases to almost 18.
 
The = vmstat output=20 when the system was normal is like this:
  =20 procs           &n= bsp;         =20 memory     =20 swap         =20 io    =20 system      cpu

 r  b  w   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy=20 id

 0  0  0 124088   7208   1972  30428    0    1     6    49  230   400  8  8 = 85

 0  0  0 124360   6884   1960  30228    0   15     8    49  232   435  9  9 = 82

 3  0  0 125052   6904   1972  29612    0    4     0    40  228   454  9 10 = 81

 0  0  0 126144   6744   1988  28484    0    3     0    41  231   452  9 10 = 82

 0  1  1 124212   6608   1980  30728    0    4   674    37  307   592  8 10 = 83

 0  0  0 124156   6848   1932  30444    0    8   604    61  311   575  9  9 81

 

But just before the load = starts=20 increasing, a sample of vmstat is like = this.

 

 r  b  w   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy=20 id

 0  1  1 133280   6772   1684  11048    2    7    19    55  232   406  8  8 = 84

 0  4  1 133920   7024   1536  10812  142 1132   536  1182  392   594  4  3 = 93

 1  2  1 134016   6964   1608  13512  188  306   550   344  369   698  7  6 = 87

 3  0  1 133876   6956   1628  13868   39  154   101   198  275   584 11  9 = 81

 1  7  2 131344   6384   1412  17052  313  351  1343   393  504   816  6  6 = 88

 0  3  1 128748   7152   1516  15372  555  573  1226   616  579  1067  5  5 = 90

 

After the load = has gone=20 almost to 17, the sample of vmstat is as below.

 r =20 b  w   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy=20 id

 0 11  2 139492   6468   1416   7196   36   40    93    84  244    22  7  7 85

 0 =20 7  2 139912   6368   1396   7500  580  331  1191   349  363   283  0  1 99

 0 =20 5  1 139596   6368   1516   7528  683  259  1285   280  382   309  0  1 98

 0 10  1 139496   6368   1380   7228  525  242  1014   256  347   281  0  1 99

 0 10  1 139832   6380   1432   6808  663  305  1282   312  383   287  0  1 98

 0 10  1 139420   6368   1412   8048  568  241  1308   250  396   312  0  1 99

 

 

I am attaching = the postgres=20 configuration file used in our system.

 

Any help to = suggest what=20 could be the reason for this load increase..

 

Thanks

Phani.



---------------------------(end of=20 broadcast)---------------------------
TIP 3: Have you checked our = extensive=20 = FAQ?

          &= nbsp;   =20 http://www.postgresql.org/docs/faq
------=_NextPart_000_0076_01C5C113.6D0E4BC0--