Re: Getting all tables into memory

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