Hello,
since 2 days ago we're facing an increased load on our database server
(opensuse10.3-64bit, PostgreSQL 8.3.5, 8GB Ram). This high load stays the whole
working day.
==================
current situation:
==================
#>top
top - 14:09:46 up 40 days, 8:08, 2 users, load average: 7.60, 7.46, 7.13
...
Mem: 8194596k total, 5716680k used, 2477916k free, 185516k buffers
Swap: 4200988k total, 204k used, 4200784k free, 5041448k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17478 postgres 15 0 610m 455m 444m R 52 5.7 0:08.78 postmaster
17449 postgres 15 0 606m 497m 489m S 37 6.2 0:16.35 postmaster
22541 postgres 16 0 607m 522m 516m R 31 6.5 123:25.17 postmaster
17491 postgres 15 0 618m 447m 435m S 22 5.6 0:03.97 postmaster
17454 postgres 15 0 616m 474m 457m S 18 5.9 0:15.88 postmaster
22547 postgres 15 0 608m 534m 527m S 18 6.7 100:12.01 postmaster
17448 postgres 16 0 616m 517m 501m S 17 6.5 0:15.60 postmaster
17451 postgres 15 0 611m 491m 479m S 11 6.1 0:25.04 postmaster
17490 postgres 15 0 606m 351m 344m S 10 4.4 0:02.69 postmaster
22540 postgres 15 0 607m 520m 513m S 2 6.5 33:46.47 postmaster
17489 postgres 15 0 604m 316m 311m S 2 4.0 0:03.34 postmaster
I assume the problem is caused by heavy writing slows down the
server....?!?...why?=>
1.) there are no long running queries:
SELECT current_query, COUNT(current_query)
FROM pg_stat_activity
WHERE query_start < now() - interval '1 min'
AND current_query != '<IDLE>'
GROUP BY current_query;
current_query | count
---------------+-------
(0 Zeilen)
2.) we get wal archives written every 2-3min.
3.) we have no high-performant hardware layout, data and log on the same disk=>
#>iostat 2 5
Linux 2.6.22.5-31-default 03.04.2009
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 13,42 38,57 391,86 134436221 1365849137
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 36,21 0,00 994,02 0 2992
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 67,67 0,00 1621,33 0 4864
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 40,00 0,00 989,33 0 2968
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 26,91 18,60 948,84 56 2856
#>vmstat 2 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
5 0 204 2449652 185692 5046168 0 0 2 24 1 1 2 0 95 2
3 0 204 2448496 185692 5046184 0 0 0 518 2984 18683 24 5 65 6
3 0 204 2430864 185692 5046192 0 0 0 344 2083 10004 34 3 58 5
2 0 204 2434600 185700 5046200 0 0 0 386 2084 23592 33 3 57 7
3 0 204 2425612 185700 5046220 0 0 0 372 2352 2905 36 2 57 5
5 0 204 2424828 185700 5046256 0 0 0 600 2372 33094 36 12 48 4
4 0 204 2405516 185700 5046256 0 0 4 992 1747 29035 33 8 52 6
3 0 204 2419368 185708 5046272 0 0 4 660 2735 24732 36 7 51 6
2 0 204 2419244 185712 5046296 0 0 0 360 2251 3193 9 1 84 5
3 0 204 2407096 185712 5046296 0 0 0 332 2319 3269 20 3 72 5
Can I check further system/database details ?
Can we lower the load by reducing the amount of written wal archives, is this
somehow possible ?
Since buying and installing new hardware is a huge effort any other solutions
highly welcome :-))
thanks in advance...GERD...
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
