Re: PostgreSQL configuration problem

From: "Andy" <frum(at)ar-sd(dot)net>
To: "Gandeed Phanibhushan Rao-A18356" <phani(at)motorola(dot)com>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: PostgreSQL configuration problem
Date: 2005-09-24 11:22:42
Message-ID: 007901c5c0fa$491d2150$0b00a8c0@forge
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)postgresql(dot)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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Aldor 2005-09-24 11:40:57 Slony1-1.1.0: UNSUBSCRIBE SET and SUBSCRIBE SET
Previous Message Aldor 2005-09-24 11:21:16 Re: could not create IPv6 socket !