PostgreSQL: 9.1
OS: Red Hat 6
This PostgreSQL instance is used for dynamic web content. It runs on a dedicated server.
So I need some PostgreSQL monitoring advice. There are two basic strategies that I am aware of for configuring PostgreSQL:
1) In Memory: With an in memory option you give PostgreSQL 70% or more of the memory by setting the shared buffers. You are relying on PostgreSQL to put into memory the information within the database. The only access to the disk from my understanding should be for the initial read of data into a block of memory and when updates are made to data blocks. The advantage of this strategy is that if you notice an increase in the Linux swap file then you know you need to increase the memory on the server as well as PostgreSQL.
2) Disk Caching: With this approach you are relying on the operating system to cache disk files in memory. PostgreSQL will scan the disk cache for the data it needs. In order to use this strategy you set the amount of shared buffers to a low number like 1G or less. You also want to make sure to set effective cache size to the amount of memory that you expect your server’s OS to use for disk caching. The only major drawback for me with this strategy is “how do I know when I need more memory for the OS to use when caching my files?”
If I were to use option #2 above what type of monitoring would you suggest I use to tell me when I need to add more memory?
Thanks,
Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382
