Postgresql - How to improve low cache hit ratio-CodePudding

If the cache hit ratio for a database is below 50%. Are there other ways than increasing the shared_buffers to improve this ratio

CodePudding user response：

see: Re: Increased shared_buffer setting = lower hit ratio ?

what exactly do you mean by hit ratio - is that the page cache hit ratio (filesystem cache), or shared buffers hit ratio (measured e.g. using pg_buffercache)?

Regarding the unexpected decrease of performance after increasing shared_buffers - that's actually quite common behavior. First, the management of shared buffers is not free, and the more pieces you need to manage the more expensive it is. Also, by using larger shared buffers you make that memory unusable for page cache etc. There are also other negative consequences - double buffering, accumulating more changes for a checkpoint etc.

The common wisdom (which some claim to be obsolete) is not to set shared buffers over ~10GB of RAM. It's however very workload-dependent so your mileage may vary.

To get some basic idea of the shared_buffers utilization, it's possible to compute stats using pg_buffercache. Also pg_stat_bgwriter contains useful data.

BTW, it's difficult to say why a query is slow - can you post explain analyze of the query with both shared_buffers settings?

And just to check - what kind of hardware/kernel version is this? Do you have numa / transparent huge pages or similar trouble-indicing issues?

So why do you want to increase cache hit ratio ?
What do you think is a good ratio?

CodePudding user response：

Look at your explain plans. Hit and Read are cache and disk reads respectively. There can be a number of reasons for this the most common being lack of indexes, or bloated indexes. Poorly vacuumed/analyzed tables which have a lot of dead rows misleading the optimiser.

Look at the pg_stat_statements view. This will show you the blocks hit and read for the most common statements that are executed. You can use this information to determine appropriate index usage.

Good management of your tables and queries will help lower your read over hit rate.