“tuning-primer”优化工具 得出这样的结果，正常吗？

huifeideluotuo
“tuning-primer”优化工具 得出这样的结果，正常吗？

[color=black]环境：[/color]

[color=red]OS：Redhat AS4.2（64）[/color]
[color=red]Memory：8G[/color]
[color=red]Engine：INNODB[/color]
[color=#ff0000][/color]
[color=red]第一点：[/color]

MEMORY USAGE
Max Memory Ever Allocated : 294 G
Configured Max Per-thread Buffers : 290 G
Configured Max Global Buffers : 4 G
Configured Max Memory Limit : 294 G
Physical Memory : 7.78 G
Max memory limit exceeds 90% of physical memory


[color=red]第二点：[/color]

TABLE LOCKING
Current Lock Wait ratio = 1 : 3356
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

[color=#ff0000]第三点：[/color]
[color=#ff0000][/color]
QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 27 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 21.20 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size



现在这3点不是多明白，

1，现在 innodb_buffer_pool_size = 3G ，这个是影响mysql的主要参数，我想把他加到5G，查资料好像在64位的系统下，最大支持4G，根据上面第一点的“Configured Max Global Buffers : 4 G，是不是最大支持4G？

2，当前这个比值是否健康，这台机器是Master，主要写入，后面他给的一些说明，好像平时没怎么碰到过？

3，现在query_cache_limit 是2M，那么我的缓存结果就<2M，设置 query_cache_size = 128 M 也没有用，因为只缓存了2M的结果集？


还请高手帮小弟分析下，谢谢了！
”

[[i] 本帖最后由 huifeideluotuo 于 2008-6-2 15:06 编辑 [/i]]

yueliangdao0608
MySQL won't cache query results that are larger than query_cache_limit in size

yueliangdao0608
Pay attention to the following.

1、32-bit
2、Max memory limit exceeds 90% of physical memory
3、MySQL won't cache query results that are larger than query_cache_limit in size

huifeideluotuo
hi, yueliangdao0608 ,
thank you for help，

1，32-bit ，can you describe detail?
2, I known, i have increase memory ,

right?

voxxu
1，现在 innodb_buffer_pool_size = 3G ，这个是影响mysql的主要参数，我想把他加到5G，查资料好像在64位的系统下，最大支持4G，根据上面第一点的“Configured Max Global Buffers : 4 G，是不是最大支持4G？


这个应该是32位的系统才会有4G的问题,64位不存在这个问题

3，现在query_cache_limit 是2M，那么我的缓存结果就<2M，设置 query_cache_size = 128 M 也没有用，因为只缓存了2M的结果集？

Your query_cache_size seems to be too high.
你的query_cache_size设太高了
Current query_cache_size = 128 M
Current query_cache_used = 27 M

Current Query cache Memory fill ratio = 21.20 %
只用到了20%,可以减小一些

yueliangdao0608
Don't consider too much about this report result.

It suggests you should decrease the memory configuration in order to be used with other environment now.


But you have to consider it in the long term.

[[i] 本帖最后由 yueliangdao0608 于 2008-6-2 17:25 编辑 [/i]]

huifeideluotuo
现在有个单表已经 7500W 条记录了，所以想好好优化下，今天晚上动手术:mrgreen: