Postgres Pro
Downloads
Home   >   mailing lists

Thread: PostgreSQL configuration problem

PostgreSQL configuration problem

From
"Gandeed Phanibhushan Rao-A18356"
Date:
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.

Attachment

Re: PostgreSQL configuration problem

From
"Thomas F. O'Connell"
Date:

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™

110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

Re: PostgreSQL configuration problem

From
"Andy"
Date:
Do 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 -----
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