Thread: Getting all tables into memory
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
In response to Robert Fitzpatrick <lists@webtent.net>: > 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 What does "top -m io" look like? > 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. This is the wrong approach. You've not yet determined that the (alleged) low use of memory is actually the problem. Turn on query time logging for a few hours, then grab the PG log files and run them through pgFouine. > 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... http://www.freebsd.org/doc/en_US.ISO8859-1/books/faq/troubleshoot.html#PAE > 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, Says who? You seem to be making a lot of assumptions here. What evidence do you have to show that 4G is "plenty" of memory? > it just does not seem to be using it considering my > disk status above? Huh? > How can I tell if PgSQL is using memory or not and > how much? Well, top is helpful. Also, consider installing the pg_buffercache addon so you can see how much of your shared_buffers is being used. I'm not entirely convinced that memory is your problem, as you've shown no evidence. vacuuming every night may not be sufficient, please show us the output of "vacuum analyze verbose" Also, once you've gathered some information on slow queries (using pgFouine as described above) run explain analyze on the slowest ones and see if you would benefit from adding some indexes. Besides, you've already mentioned RAID5, if the system is slow because it's blocking on disk writes, you can add 32T of RAM and it's not going to speed things up any. > 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) -- Bill Moran http://www.potentialtech.com
On Thu, 2008-01-24 at 10:46 -0500, Bill Moran wrote:
> In response to Robert Fitzpatrick <lists@webtent.net>:
>
> > How can I tell if PgSQL is using memory or not and
> > how much?
>
> Well, top is helpful. Also, consider installing the pg_buffercache addon
> so you can see how much of your shared_buffers is being used.
Well, all of it I guess from looking below? Again, just learning here...
maia=# select count(*) from pg_buffercache;
count
-------
64000
(1 row)
maia=# select count(*) from pg_buffercache where relfilenode is null;
count
-------
0
(1 row)
maia=# SELECT c.relname, count(*) AS buffers
FROM pg_class c INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode INNER JOIN pg_database d
ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;
relname | buffers
-------------------------+---------
bayes_token | 16684
bayes_token_idx1 | 10264
maia_sa_rules | 8501
pg_toast_70736 | 5898
maia_mail | 4361
maia_sa_rules_triggered | 3913
maia_mail_recipients | 3603
bayes_token_pkey | 3199
maia_stats | 2545
token_idx | 2442
(10 rows)
Thanks again for any insight?
--
Robert
Robert Fitzpatrick wrote: > 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... The SA SQL-bayes implementation is one prime example of a real life application that can benefit from HOT because it is basically updating a fairly limited set of non-indexes columns at an insane rate. I have seen real live installations that could barly keep up with bloat even on a tight 3min vacuum cycle and the 8.3B4 test instance I have here can take at least 4 times the load than 8.1 could using that kind of workload. Stefan