pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL 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