pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andy <frum@ar-sd.net>
To: Gandeed Phanibhushan Rao-A18356 <phani@motorola.com>
To: pgsql-admin@postgresql.org
Subject: Re: PostgreSQL configuration problem
Date: Sat, 24 Sep 2005 14:22:42 +0300
Message-ID: <007901c5c0fa$491d2150$0b00a8c0@forge> (raw)
References: <0B28F3C8F2581D419709A71814CA59D7032A88@ZMY16EXM67.ds.mot.com>

MessageDo you vacuum the database? It is very important if you insert and delete lot of rows. 
I think this is your problem. 

Best regards, 
Andy.
  ----- Original Message ----- 
  From: Gandeed Phanibhushan Rao-A18356 
  To: pgsql-admin@postgresql.org 
  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

view thread (3+ messages)

Message-ID: <007901c5c0fa$491d2150$0b00a8c0@forge>
Permalink:  ../007901c5c0fa$491d2150$0b00a8c0@forge/
Also on:    postgresql.org/message-id/007901c5c0fa$491d2150$0b00a8c0@forge

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: frum@ar-sd.net, phani@motorola.com
  Subject: Re: PostgreSQL configuration problem
  In-Reply-To: <007901c5c0fa$491d2150$0b00a8c0@forge>
  References: <0B28F3C8F2581D419709A71814CA59D7032A88@ZMY16EXM67.ds.mot.com> <007901c5c0fa$491d2150$0b00a8c0@forge>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox