I have a couple of servers running Postfix with amavisd-maia+SA+clamav
on FreeBSD 6.2 which use a central db server running PgSQL 8.2.4. My
issue is the bayes database causing SA TIMED OUT in the logs and want to
make sure I am getting everything into memory. The disk activity is high
on the db server, this is the average systat status...
/0 /1 /2 /3 /4 /5 /6 /7 /8 /9 /10
Load Average |
/0 /10 /20 /30 /40 /50 /60 /70 /80 /90 /100
cpu user|
nice|
system|
interrupt|
idle|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
/0 /10 /20 /30 /40 /50 /60 /70 /80 /90 /100
aacd0 MB/s
tps|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
I know I'm running RAID-5 and that is set to change, right now I'm just
focusing on getting my tables into memory. I have 4GB of memory in the
db server now, but the server indicates about only 3GB in dmesg, not
sure why this is, FreeBSD warns a small amount of over 4GB will not be
used when booting...
real memory = 3220635648 (3071 MB)
avail memory = 3150565376 (3004 MB)
Here is my conf...
mx1# cat postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 250
shared_buffers = 500MB # min 128kB or max_connections*16kB
work_mem = 64MB # min 64kB
maintenance_work_mem = 256MB # min 1MB
max_fsm_pages = 256000 # min max_fsm_relations*16, 6 bytes each
checkpoint_segments = 100 # (value * 2 + 1) * 16MB
effective_cache_size = 1000MB
log_destination = 'syslog'
silent_mode = on
stats_start_collector = on # needed for block or row stats
stats_row_level = on
autovacuum = off # enable autovacuum subprocess?
datestyle = 'iso, mdy'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
I vacuum every night and expire my bayes db for SA. While I have plenty
of memory installed, it just does not seem to be using it considering my
disk status above? How can I tell if PgSQL is using memory or not and
how much? Excuse my ignorance on the matter, just learning how to
properly tune PostgreSQL.
My top 20 tables sizes are as follows...
maia=> SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC limit 20;
relname | reltuples | relpages
---------------------------------------+-------------+----------
pg_toast_70736 | 846647 | 257452
maia_mail | 375574 | 63639
maia_sa_rules_triggered | 4.52118e+06 | 38526
bayes_token | 447008 | 20033
maia_sa_rules_triggered_pkey | 4.52118e+06 | 17821
bayes_token_idx1 | 447008 | 11437
maia_mail_recipients | 377340 | 9867
maia_sa_rules | 1578 | 8501
token_idx | 377340 | 8053
envelope_to_received_date_idx | 375574 | 7202
pg_toast_70736_index | 846647 | 4719
maia_mail_idx_received_date | 375574 | 3703
maia_mail_recipients_pkey | 377340 | 3471
bayes_token_pkey | 447008 | 3200
awl_pkey | 189259 | 2965
maia_mail_recipients_idx_recipient_id | 377340 | 2696
awl | 189259 | 2599
maia_stats | 185 | 2545
bayes_seen_pkey | 174501 | 2433
bayes_seen | 174501 | 2238
(20 rows)
--
Robert
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
