Mysql通过缓冲降低CPU使用率
我的 Mysql 服务器负载很重，现在平均 300 qps。
它平均使用 50% 的 CPU，仅 700MB 的内存。我的服务器有 8GB，还有超过 3GB 的可用空间。慢查询日志看起来不错。这种情况很少，而且也不常见。
我想确保它返回缓存的结果并且不要不必要地接触磁盘。
我认为 Linux 操作系统缓存了 innodb 文件，但我可以相信这一点吗？ 有没有什么好的做法可以通过缓冲或缓存来降低 cpu 使用率？
innodb_buffer_pool_size 设置为默认值。 (8mb)
我混合了 Innodb、MyIsam 和 Memory 表。
这是调谐器脚本的输出
INNODB STATUS
Current InnoDB index space = 238 M
Current InnoDB data space = 294 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
KEY BUFFER
Current MyISAM index space = 113 M
Current key_buffer_size = 192 M
Key cache miss rate is 1 : 63
Key buffer free ratio = 74 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 256 M
Current query_cache_used = 19 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 7.64 %
Current query_cache_min_res_unit = 4 K
Query Cache is 28 % fragmented
My Mysql server is heavily loaded, now 300 qps average.
It uses %50 Cpu in average and just 700MB of ram. My server has 8GB and it has over 3GB free. The slow query log seems fine. There are very few and not frequent ones.
I want to be sure that it is returning the cached results and do not touch the disk unnecessarily.
I think the linux OS caches the innodb file but can I trust on that?
And is there any good practice to lower cpu usage through buffering or caching?
innodb_buffer_pool_size is set to default value. (8mb)
I have Innodb, MyIsam and Memory tables mixed.
Here is an output from a tuner script
INNODB STATUS
Current InnoDB index space = 238 M
Current InnoDB data space = 294 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
KEY BUFFER
Current MyISAM index space = 113 M
Current key_buffer_size = 192 M
Key cache miss rate is 1 : 63
Key buffer free ratio = 74 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 256 M
Current query_cache_used = 19 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 7.64 %
Current query_cache_min_res_unit = 4 K
Query Cache is 28 % fragmented
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于您有 3GB 可用空间，因此请增加您的
innodb_buffer_pool_size以容纳整个 innodb 数据集（数据 + 索引）。给它一个 1G，这样它就有一些喘息的空间。你不会后悔的。 :)
Since you have 3GB free, boost your
innodb_buffer_pool_sizeto hold your entire innodb dataset (data + index).Give it a 1G so it has some breathing room. You won't regret it. :)