Search Postgresql Archives

Getting all tables into memory

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux