Back

optimize Mysql config( 优化MYSQL)

发布时间: 2014-05-22 05:44:00

最近在MYSQL上跑了一组 delayed_job, 发现有问题: 同时开始6个worker时 MYSQL 的CPU使用率就占到了150%.  ( I found the 'mysql process' costing 150% cpu time when running a group of delayed_jobs -- about 10 workers , so I decide to optimize it)

优化方式: 先看看目前的各种数据: ( before the optimization, let me check its status: ) 

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     | 
| query_cache_limit            | 1048576 | 
| query_cache_min_res_unit     | 4096    | 
| query_cache_size             | 0       | 
| query_cache_type             | ON      | 
| query_cache_wlock_invalidate | OFF     | 
+------------------------------+---------+
6 rows in set (0.00 sec)

可以看到, 基本没有任何优化, query_cache_size 居然是0, 我无了个语。。。   果断修改 mysql 配置( $ vim /etc/my.conf )   ( see?  the query_cache_size = 0.... oh my god... ) 

[mysqld]
#  other items... 
query_cache_limit = 128M
query_cache_size = 512M

哦也!    ( now it works very well under pressure! ) 

p.s. 其他命令:

$ show processlist;

$ show index from <table>;

$ check slow_log: $ tail /var/log/<log_file> -f

Back