慢查询是记录查询超过mysql阀值的sql语句。默认2s。不建议设置过长
mysql> show variables like '%slow%';
+---------------------+-----------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------------------------------------------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
+---------------------+-----------------------------------------------------------------------------------------------------+
打开慢查询:
set global slow_query_log = on;
查看记录:
mysql> show global status like'%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
+---------------------+-------+
如果数据库访问量很大,可以适当提高max_connection限制。
查询使用连接:
mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 1 |
+----------------------+-------+
理想比例:max_used_connections / max_connections * 100% ≈ 85%
http://www.001pp.com/chengxuyouhua/mysql%20xingnengyouhua2183.html
二进制文件:
打开:在my.ini的mysqld目录下加入log-bin。重启mysql服务。
mysql> show global variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
[img]http://dl.iteye.com/upload/picture/pic/115014/62d18248-abe7-329a-a8c4-e20903be225f.png[/img]
Mysql优化:
[url]http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/[/url]
二进制日志管理:
[url]http://www.blogjava.net/dongbule/archive/2010/09/04/331050.html[/url]
InnoDB优化
[url]http://man.ddvip.com/database/innodbzh/9.htm[/url]