調整 MySQL 的記憶體用量

Percona 的「Best Practices for Configuring Optimal MySQL Memory Usage」這篇給了個蠻不錯的建議:

Don’t allow the mysqld process VSZ exceed 90% of the system memory

ps ax -O vsz | grep mysqld 可以看到 mysqld 吃了多少 VSZ,然後自己除整台機器的記憶體大小,就可以算出來目前吃了多少,然後調整 innodb_buffer_pool_size 的數字。

另外 performance schema 也會有不少影響:

MySQL is workload related – if you have many connections active at the same time that run heavy selects using a lot of memory for sorting or temporary tables, you might need a lot of memory (especially if Performance Schema is enabled). In other cases this amount of memory is minimal. You’ll generally need somewhere between 1 and 10GB for this purpose.

記憶體碎裂問題應該是在調整時就會考慮進去:

Another thing you need to account for is memory fragmentation. Depending on the memory allocation library you’re using (glibc, TCMalloc, jemalloc, etc.), the operating system settings such as Transparent Huge Pages (THP) and workload may show memory usage to grow over time (until it reaches some steady state). Memory fragmentation can also account for 10% or more of additional memory usage.

其實就是沒有一定的定論,在不同的系統上會有不同的反應...

Leave a Reply

Your email address will not be published. Required fields are marked *