pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PostgreSQL configuration problem
3+ messages / 3 participants
[nested] [flat]

* PostgreSQL configuration problem
@ 2005-09-19 13:31 Gandeed Phanibhushan Rao-A18356 <phani@motorola.com>
  2005-09-19 19:31 ` Re: PostgreSQL configuration problem Thomas F. O'Connell <tfo@sitening.com>
  2005-09-24 11:22 ` Re: PostgreSQL configuration problem Andy <frum@ar-sd.net>
  0 siblings, 2 replies; 3+ messages in thread

From: Gandeed Phanibhushan Rao-A18356 @ 2005-09-19 13:31 UTC (permalink / raw)
  To: pgsql-admin

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.

Attachments:

  postgresql.conf (application/octet-stream, 5.3K) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PostgreSQL configuration problem
  2005-09-19 13:31 PostgreSQL configuration problem Gandeed Phanibhushan Rao-A18356 <phani@motorola.com>
@ 2005-09-19 19:31 ` Thomas F. O'Connell <tfo@sitening.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Thomas F. O'Connell @ 2005-09-19 19:31 UTC (permalink / raw)
  To: Gandeed Phanibhushan Rao-A18356 <phani@motorola.com>; +Cc: pgsql-admin


On Sep 19, 2005, at 8:31 AM, Gandeed Phanibhushan Rao-A18356 wrote:

> 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.

128 MB RAM is not very much for the sort of workload you seem to be  
requiring. Your second listing of vmstat output indicates regular  
swapping.

I'm not sure why there's a one-day buildup of performance loss. It  
might be related to a lack of vacuuming. Are you using pg_autovacuum  
or do you have a regularly schedule vacuum of the high-write-volume  
tables?

It might also be that your data eventually grows large enough that it  
cannot be accessed concurrently without swapping.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)=

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PostgreSQL configuration problem
  2005-09-19 13:31 PostgreSQL configuration problem Gandeed Phanibhushan Rao-A18356 <phani@motorola.com>
@ 2005-09-24 11:22 ` Andy <frum@ar-sd.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Andy @ 2005-09-24 11:22 UTC (permalink / raw)
  To: Gandeed Phanibhushan Rao-A18356 <phani@motorola.com>; pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-09-24 11:22 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-19 13:31 PostgreSQL configuration problem Gandeed Phanibhushan Rao-A18356 <phani@motorola.com>
2005-09-19 19:31 ` Thomas F. O'Connell <tfo@sitening.com>
2005-09-24 11:22 ` Andy <frum@ar-sd.net>

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