Performance Slowly Decreasing As Database Grows

Поиск
Список
Период
Сортировка
От Lane Van Ingen
Тема Performance Slowly Decreasing As Database Grows
Дата
Msg-id EKEMKEFLOMKDDLIALABIOEBDCMAA.lvaningen@esncc.com
обсуждение исходный текст
Ответ на Re: Where to get column length  (Alvaro Herrera <alvherre@commandprompt.com>)
Ответы Re: Performance Slowly Decreasing As Database Grows
Список pgsql-admin
I am using Postgresql 8.1.4 on Windows 2003; platform is Dell Precision 330,
1.8 Ghz
CPU, 1 gByte of RAM. This database is subject to 'vacuum full analyze' once
/ day.

I am watching a recently created database grow; as it grows, I am finding
that some of the performance statistics appear to be falling out of bed. The
functions I have used
to capture this information are the standard functions that come with
PostgreSQL:

    date      commits   rollbks  dsk_reads   mem_reads  pct_mem_hits
db_size
 2006-06-19  94115102        64  553053905  13126498559   95.9600    "1674
MB"
 (server restarted)
 2006-06-20   4383600        26   24129603   1162150532   97.9700    "1471
MB"
 2006-06-21   9179799        27   56084142   2456950412   97.7700    "1587
MB"
 2006-06-22  14447111        28   89452397   3875993962   97.7400    "1710
MB"
 2006-06-23  20233946        47  128309666   5553425498   97.7400    "1858
MB"
 2006-06-26  34252036      4765  237496776   9024547005   97.4400    "2218
MB"
 2006-06-27  40290065    403368  273062334   9156477077   97.1000    "2401
MB"
 2006-06-28  46436389    870211  307983449   9260208418   96.7800    "2497
MB"
 2006-06-29  52251198   1352370  348552701   9367093206   96.4100    "2575
MB"
 2006-06-30  58585373   1839034  392822069   9477661079   96.0200    "2647
MB"
 2006-07-03  75747589   3318388  551767504   9816311112   94.6800    "2815
MB"
 2006-07-05  87768328   4524047  678763032  10076029919   93.6900    "2973
MB"
 (server restarted - shared_buffers changed)
 2006-07-06   4841006    326389   50641814    121507743   70.5800    "3031
MB"
 2006-07-07  10711794    732981  113816094    274683161   70.7000    "3076
MB"
 2006-07-10  19428743   1300797  287848710    517770353   64.2700    "3452
MB"

My questions concerning these statistics:
(1) This application monitors networks. On 6/26, it began monitoring a
number of
    new network nodes (traffic increase of 40 - 50%); but the application
itself
has not been changed, either in terms of number of users, program or
database
changes, or other changes. Yet, the rollback column has increased
substantially.
    QUESTION: is there a database setting that can affect this statistic?

(2) I noticed that pct_mem_hits was dropping in early July at about the same
time
    that the platform started monitoring additional network nodes. On late
July
5th, increased shared_buffers from default value of 1000 to 1500, but
pct_mem_hits
continue to decline.
    QUESTION: I evidently touched the wrong thing. What should I be
adjusting to
    help keep the database in memory?

All other statistics appear to be normal.



В списке pgsql-admin по дате отправления:

Предыдущее
От: KyLiE
Дата:
Сообщение: %Alter Help
Следующее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: lock a database from new connections, without modifying pg_hba.conf