DB cache size strategies
От | Ed L. |
---|---|
Тема | DB cache size strategies |
Дата | |
Msg-id | 200401301433.51138.pgsql@bluepolka.net обсуждение исходный текст |
Ответы |
Re: DB cache size strategies
|
Список | pgsql-general |
Here's some of my current notions on pgsql performance tuning strictly as it relates to pgsql tuning parameters in the context of a dedicated linux or hpux server. I'm particularly focusing on the shared_buffers setting. I invite any corrective or confirming feedback. I realize there are many other hugely important performance factors outside this scope. One key aspect of pgsql performance tuning is to adjust the memory consumption settings (shared_buffers, sort_mem, etc) large enough to hold as much of the database in shared memory as possible while not causing page swap-ins. I understand that both page swap-outs and swap space usage is normal and OK, but lots of page swap-ins are bad). In other words, for absolute fastest performance, we want a database buffer cache hit rate of as close to 100% as possible. Now, how do we accurately measure this cache hit rate at any one point? Well, here's what I currently know as the best measure for a given cluster of databases: SELECT SUM(pg_stat_get_db_blocks_hit(d.oid)) / SUM(pg_stat_get_db_blocks_fetched(d.oid)) AS hit_rate FROM pg_database d; How do we identify when we have sub-optimal pgsql configuration parameters in this regard? My understanding: to whatever extent the hit rate is below 100%, our shared buffers are not large enough to hold the data we're accessing. How do we fix the problem? We want to increase the shared_buffers setting and/or maybe some combination sort_mem, wal_buffers, vacuum_mem, reset our stats, and resume monitoring. I assume which of these we increase for maximum benefit depends on the sources of the cache overflow (routine queries, sorting, vacuuming), but that shared_buffers is the most impactful in practice. Again, we want to do this without causing page swap-ins. When you see swap-ins, you've gone too far. If we experience swap-ins and less than 100% cache hit rate by any significant amount, then we need more RAM or less RAM consumption. The end result is that page swap-ins (sar -W) and cache hit rate (query above) are two very key measures to use as warning bells. Glad to hear your thoughts. Ed
В списке pgsql-general по дате отправления: