Re: PostgreSQL configuration problem

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)