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