黄金国度俄罗斯Mysql配置调优

连接数

mysql> show variables like 'max_connections'; 
+-----------------+-------+ 
| Variable_name | Value | 
+-----------------+-------+ 
| max_connections | 16384 | 
+-----------------+-------+  
mysql> show global status like ‘Max_used_connections’;
Max_used_connections 160

Max_used_connections / max_connections * 100% ≈ 85%

最大连接数占上限连接数的85%左右,如果发现比例在10%以下,MySQL服务器连接数上限设置的过高了。

建议修改

max_connections = 800 

Open Table情况

mysql> show global status like 'open%tables%'; 
Variable_name	Value
Open_tables	64
Opened_tables	118466

Open_tables表示打开表的数量,

Opened_tables表示打开过的表数量

如果Opened_tables数量过大,说明配置中table_open_cache值可能太小,我们查询一下服务器table_open_cache值:

mysql> show variables like 'table_cache'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| table_open_cache | 64 | 

建议修改

table_open_cache=2048

表扫描情况

mysql> show global status like 'handler_read%'; 
Variable_name	Value
Handler_read_first	474469
Handler_read_key	13780308
Handler_read_next	36029595
Handler_read_prev	2945
Handler_read_rnd	801894
Handler_read_rnd_next	2087790735

调出服务器完成的查询请求次数:

mysql> show global status like 'com_select'; 
+---------------+-----------+ 
| Variable_name | Value | 
+---------------+-----------+ 
| Com_select | 4537851 | 
+---------------+-----------+  

计算表扫描率:

表扫描率 = Handler_read_rnd_next / Com_select = 460

如果表扫描率超过4000,说明进行了太多表扫描,很有可能索引没有建好,增加read_buffer_size值会有一些好处,但最好不要超过8MB。

建议

read_buffer_size=8M

进程使用情况

mysql> show global status like ‘Thread%’; 

Variable_name	Value
Threads_cached	0
Threads_connected	86
Threads_created	249
Threads_running	1

Threads_created 表示创建过的线程数,如果发现Threads_created值过大的话,表明MySQL服务器一直在创建线程,

这也是比较耗资源,可以适当增加配置文件中thread_cache_size值,查询服务器thread_cache_size配置:

mysql> show variables like 'thread_cache_size'; 
+-------------------+-------+ 
| Variable_name | Value | 
+-------------------+-------+ 
| thread_cache_size | 0 | 
+-------------------+-------+  

建议

thread_cache_size = 64

在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那么thread_concurrency的应该为4; 2个双核的cpu, thread_concurrency的值应为8.

大陆为4个双核CPU

thread_concurrency=32

查询缓存(query cache)

mysql> show global status like 'qcache%'; 
+-------------------------+-----------+ 
| Variable_name | Value | 
+-------------------------+-----------+ 
Qcache_free_blocks	78112
Qcache_free_memory	1782343960
Qcache_hits	5153348
Qcache_inserts	3987356
Qcache_lowmem_prunes	0
Qcache_not_cached	951427
Qcache_queries_in_cache	186610
Qcache_total_blocks	451886
+-------------------------+-----------+  

Qcache_free_memory:缓存中的空闲内存。

我们再查询一下服务器关于query_cache的配置:

mysql> show variables like 'query_cache%'; 
+------------------------------+-----------+ 
| Variable_name | Value | 
+------------------------------+-----------+ 
query_cache_limit	16777216
query_cache_min_res_unit	4096
query_cache_size	2147483648
query_cache_type	ON
query_cache_wlock_invalidate	OFF
 +——————————+———–+

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100% = 17%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;

建议

query_cache_size=512M

临时表

mysql> show global status like 'created_tmp%';  
Variable_name	Value
Created_tmp_disk_tables	39944
Created_tmp_files	5
Created_tmp_tables	40200

每次创建临时表,Created_tmp_tables增加,如果是在磁盘上创建临时表,Created_tmp_disk_tables也增加

Created_tmp_files表示MySQL服务创建的临时文件文件数,比较理想的配置是:

  Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25% 

比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% = 99%

我们再看一下MySQL服务器对临时表的配置:

mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 
Variable_name	Value
max_heap_table_size	1073741824
tmp_table_size	1073741824

只有256MB以下的临时表才能全部放内存,超过的就会用到硬盘临时表。

建议修改

max_heap_table_size=256M
tmp_table_size=256M

最终建议修改参数如下

max_connections = 800 
table_open_cache=2048
read_buffer_size=8M
thread_cache_size = 64
thread_concurrency=32
query_cache_size=512M
max_heap_table_size=256M
tmp_table_size=256M

转载请注明原地址: http://blog.noark.xyz/article/2012/7/8/俄罗斯mysql配置调优/