《高性能Mysql》读书笔记02
Table of Contents
1 简单说明
这篇文章主要记录了一些具体的性能剖析。书中定义性能最有效的方法是效应时间。
2 查看当前正在执行的sql
[root@localhost ~]# mysqladmin -uroot -proot -h127.0.0.1 pr|grep -v Sleep
Warning: Using a password on the command line interface can be insecure.
| Id | User | Host | db | Command | Time | State | Info |
| 18 | root | localhost:27124 | zycDB | Execute | 0 | updating | UPDATE tt set k=k+1 where id=? |
| 19 | root | localhost:27125 | zycDB | Execute | 0 | updating | UPDATE tt set k=k+1 where id=? |
| 20 | root | localhost:27126 | zycDB | Execute | 0 | init | COMMIT |
| 21 | root | localhost:27127 | zycDB | Execute | 0 | updating | UPDATE tt set k=k+1 where id=? |
| 22 | root | localhost:27128 | zycDB | Execute | 0 | init | COMMIT |
| 23 | root | localhost:27129 | zycDB | Execute | 0 | init | COMMIT |
| 24 | root | localhost:27130 | zycDB | Execute | 0 | init | COMMIT |
| 25 | root | localhost:27131 | zycDB | Execute | 0 | updating | UPDATE tt set k=k+1 where id=? |
| 26 | root | localhost:27140 | Query | 0 | init | show processlist |
关注time列参数，分析用户进程占用mysql连接数时长，如果运行的SQL语句太多，运行时间太长，说明mysql效率有问题。必要时可以将相应的进程kill掉。
3 mysql参数检测
- connect_timeout：指定mysql服务等待应答连接的最大时间，单位s，默认10秒，超过这个时间mysql向客户端返回bad handshake。内网高并发环境下，建议设置10-15s，避免bad handshake。并且关注thread_cache_size，设置为非0，具体大小视环境定
- skip-name-resolve：避免域名解析，可以大大加快用户获取连接的速度，特别是在网络较差的情况下。mysql收到用户发来的请求连接时，会根据请求包中获得的ip来反向查询请求者的主机名，然后会更具返回的主机名有一次去查询ip，如果两次获得的ip相同，则成功建立连接。在DNS不稳定或者局域网主机过多的情况下，建立一次成功的请求连接必然会消耗很多不必要的时间。但是加入mysql服务器的ip是广域网地址，最好不要设置skip-name-resolve.
- slave-net-timeout=seconds：当slave从master读取binlog失败后，等待多长时间，重新与master建立连接，并获取数据。默认3600s，如果保证数据能够及时同步，该参数应设置在10s以下。show variables like ‘%timeout%‘;set global slave_net_timeout = 5;
- master-connect-retry=seconds：当主从连接建立失败，间隔多久重试。默认60s，合理设置该参数
- 连接数max_connections：如果连接数达到最大连接数，那不管剩余多少资源，用户的连接请求都会阻塞在外面。max_connections，最大连接数，默认151，一般经验设置3000。win服务器连接数支持1500-1800，linux服务器可以支持8000个左右。另外设置max_user_connections=0，表示不限制用户的最大连接数，其最大值可以等于max_connections show variables like '%connections%'; show global status like 'max_used_connections';
4 事务和锁
- 事务等待状况
事务等待状况
SELECT r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
- 查看未关闭的事务
SELECT a.trx_id,a.trx_state,a.trx_started,a.trx_query,b.ID,b.USER,b.HOST,b.DB,b.COMMAND,b.TIME,b.STATE,b.INFO FROM information_schema.INNODB_TRX aLEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.idWHE
- 某段时间未关闭的事务
SELECT trx_id,trx_started,trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_started < date_sub(now(), INTERVAL 1 MINUTE) AND trx_operation_state IS NULL AND trx_query IS NULL;
- 系统锁状况
show status like '%lock%';
5 慢查询
mysql> show variables like '%slow%’;
| Variable_name | Value |
| log_slow_queries | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /data0/mysql/3306/data/mysql1-slow.log |
6 key_buffer_size索引缓存
key_buffer_size是对myISAM表性能影响最大的一个参数。
mysql> show global status like 'key_read%';
| Variable_name | Value |
|---|---|
| Key_read_requests | 13915643 |
| Key_reads | 129954 |
key_read_requests表示总共的索引请求数，key_reads表示请求在内存中没有找到，直接从硬盘读取索引，计算索引未命中缓存： key_cache_miss_rate=key_reads/key_read_requests*100%, 保持key_cache_miss_rate在0.1%以下都是很好的，如果在0.01%以下的话，key_buffer_size分配过的，适当减少。
7 created_tmp_tables 创建临时表
mysql> show global status like 'created_tmp%';
| Variable_name | Value |
|---|---|
| Created_tmp_disk_tables | 540 |
| Created_tmp_files | 211 |
| Created_tmp_tables | 2302 |
3 rows in set (0.00 sec）
每次创建临时表，created_tmp_tables增加（内存&磁盘创建临时表），created_tmp_disk_tables磁盘创建的临时表，比较理想的配置：
created_tmp_disk_tables/created_tmp_tables*100%<=25%
临时表配置参数：
show variables where variable_name in ('tmp_table_size','max_heap_table_size');
| Variable_name | Value |
|---|---|
| max_heap_table_size | 16777216 |
| tmp_table_size | 16777216 |
2 rows in set (0.00 sec)
只有16MB的临时表才能全部放内存，超过的就会用到硬盘临时表。
8 open_tables打开表数量
mysql> show global status like '%open%table%';
| Variable_name | Value |
|---|---|
| Com_show_open_tables | 0 |
| Open_table_definitions | 64 |
| Open_tables | 64 |
| Opened_table_definitions | 76 |
| Opened_tables | 5402 |
| Slave_open_temp_tables | 0 |
6 rows in set (0.00 sec)
open_tables表示打开表的数量,opened_tables表示曾经打开过的表数量。
如果opened_tables数量过大，说明配置table_open_cache缓存值可能太小，查询table_open_cache参数设置值：
mysql> show variables like 'table%cache%';
| Variable_name | Value |
|---|---|
| table_definition_cache | 400 |
| table_open_cache | 64 |
2 rows in set (0.00 sec)
比较合适的值：85% <= open_tables / opened_tables <= 95%
9 thread_cache_size 线程缓存
mysql> show global status like 'thread%';
| Variable_name | Value |
|---|---|
| Threadpool_idle_threads | 0 |
| Threadpool_threads | 0 |
| Threads_cached | 0 |
| Threads_connected | 48 |
| Threads_created | 15023416 |
| Threads_running | 6 |
6 rows in set (0.00 sec)
如果配置thread_cache_size后，当客户端端口连接后，服务器处理客户的线程将会缓存到thread_cache中，以响应下一个客户，而不是销毁（前提缓存数未达到thread_cache_size上线）。threads_created表示曾经创建的线程数，如果发现threads_created值过大，表明mysql服务器一直在创建线程，这个比较耗资源，可以适当增加thread_cache_size值
mysql> show variables like 'thread_cache_size';
| Variable_name | Value |
|---|---|
| thread_cache_size | 0 |
1 row in set (0.00 sec
10 query cache 查询缓存
mysql> show global status like 'qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
8 rows in set (0.00 sec)
- Qcache_free_blocks表示缓存中相邻内存块的个数，数目越大可能有碎片，flush query cache会对缓存中的碎片进行整理。
- Qcache_free_memory 表示缓存中空闲内存
- Qcache_hits命中查询缓存的次数
- Qcache_inserts插入一个查询到缓存的次数，命中次数除以插入次数就是不中比率。
- Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况)
- Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
- Qcache_queries_in_cache：当前缓存的查询(和响应)的数量。
- Qcache_total_blocks：缓存中块的数量。
mysql> show variables like 'query_cache%';
| Variable_name | Value |
|---|---|
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
6 rows in set (0.00 sec)
各字段的解释：
- query_cache_limit：超过此大小的查询将不缓存
- query_cache_min_res_unit：缓存块的最小大
- query_cache_size：查询缓存大小
- query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
- query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%,如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服务器 查询缓存碎片率 = 20.46%，查询缓存利用率 = 62.26%，查询缓存命中率 = 1.94%，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。