mysql 常用的一些命令
1, 查看MySQL服务器配置信息
mysql> show variables;
2, 查看MySQL服务器运行的各种状态值
mysql> show global status;
3, 慢查询
mysql> show variables like '%slow%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | log_slow_queries | OFF | | slow_launch_time | 2 | +------------------+-------+ mysql> show global status like '%slow%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Slow_launch_threads | 0 | | Slow_queries | 279 | +---------------------+-------+
配置中关闭了记录慢查询(最好是打开,方便优化),超过2秒即为慢查询,一共有279条慢查询
4, 连接数
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 500 | +-----------------+-------+ mysql> show global status like 'max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 498 | +----------------------+-------+
设置的最大连接数是500,而响应的连接数是498
max_used_connections / max_connections * 100% = 99.6% (理想值 ≈ 85%)
5, key_buffer_size
key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb
mysql> show variables like 'key_buffer_size'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | key_buffer_size | 67108864 | +-----------------+----------+ mysql> show global status like 'key_read%'; +-------------------+----------+ | Variable_name | Value | +-------------------+----------+ | Key_read_requests | 25629497 | | Key_reads | 66071 | +-------------------+----------+
一共有25629497个索引读取请求,有66071个请求在内存中没有找到直接从硬盘读取索引,计算索引未命中缓存的概率:
key_cache_miss_rate = Key_reads / Key_read_requests * 100% =0.27%
需要适当加大key_buffer_size
mysql> show global status like 'key_blocks_u%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Key_blocks_unused | 10285 | | Key_blocks_used | 47705 | +-------------------+-------+
Key_blocks_unused表示未使用的缓存簇(blocks)数,Key_blocks_used表示曾经用到的最大的blocks数
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% (理想值 ≈ 80%)
6, 临时表
+-------------------------+---------+ mysql> show global status like 'created_tmp%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Created_tmp_disk_tables | 4184337 | | Created_tmp_files | 4124 | | Created_tmp_tables | 4215028 | +-------------------------+---------+
每次创建临时表,Created_tmp_tables增加,如果是在磁盘上创建临时表,Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数:
Created_tmp_disk_tables / Created_tmp_tables * 100% = 99% (理想值<= 25%)
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); +---------------------+-----------+ | Variable_name | Value | +---------------------+-----------+ | max_heap_table_size | 134217728 | | tmp_table_size | 134217728 | +---------------------+-----------+
需要增加tmp_table_size
7,open table 的情况
mysql> show global status like 'open%tables%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_tables | 1024 | | Opened_tables | 1465 | +---------------+-------+
Open_tables 表示打开表的数量,Opened_tables表示打开过的表数量,如果Opened_tables数量过大,说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小,我们查询一下服务器table_cache值
my