1.Mysql查询缓存 查询缓存命中率 = Qcache_hits/(Qcache_hits+Com_select)
如果有大量查询缓存未命中,但是实际上绝大多数查询都被缓存了,那么可能是如下情况发生:
1、查询缓存还没有完成预热
2、查询语句之前从未执行过。如果你的应用程序不会重复执行一条查询语句,那么即使完成预热仍然会有很多缓存未命中
3、查询缓存失效操作太多了
缓存磁片、内存不足、数据修改都会造成缓存失效。如果配置了足够的缓存空间,而且query_cache_min_res_unit设置也合理的话,那么缓存失效应该主要是数据修改导致的。
可以通过参数Com_*来查看数据修改情况(包括Com_update,Com_delete)
Com_select 变量记录的是无缓存的查询次数+错误查询+权限检查查询。
Com_update Com_delete 查看数据修改情况 备注:高性能Mysql第315页
动态打开查询缓存
set global query_cache_type = 1; 变量有三个取值:0,1,2,分别代表了off、on、demand
set global query_cache_size = 33554432; 关闭时设置为0
mysql> show status like '%Qcache%'; 显示查询缓存状态
+-------------------------+-----------+
| Variable_name | Value | flush query cache 清理查询缓存碎片
+-------------------------+-----------+
| Qcache_free_blocks | 252 | 表示查询缓存中目前还有多少剩余的blocks,如果该值显示较大,则说明查询缓存中的内存碎片过多了
| Qcache_free_memory | 102288040 | 查询缓存的剩余内存大小
| Qcache_hits | 467280 | 有多少次命中缓存 通常Qcache_hit:Qcache_inserts大于3:1时查询缓存是有效的
| Qcache_inserts | 430281 | 多少次未命中然后插入
| Qcache_lowmem_prunes | 0 | 该参数记录有多少条查询因为内存不足而被移除出查询缓存
| Qcache_not_cached | 511699 | 由于query_cache_type的设置而没有被缓存的查询数量
| Qcache_queries_in_cache | 1139 | 当前缓存中缓存的查询数量。
| Qcache_total_blocks | 2573 | 缓存中块的数量
+-------------------------+-----------+
查询缓存减少碎片:
合适的query_cache_min_res_unit可以减少碎片,这个参数最合适的大小和应用程序查询结果的平均大小直接相关,
可以通过内存实际消耗(query_cache_size - Qcache_free_memory)除以Qcache_queries_in_cache计算平均缓存大小。
mysql> show variables like '%query_cache%'; 显示查询缓存
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 1024 |
| query_cache_size | 104857600 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
##########
2. key_buffer_size
key_buffer_size是对myisam表性能影响最大的一个参数
show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
show global status like 'key_%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 12820 | 未使用的缓存簇(blocks)数
| Key_blocks_used | 3675 | 曾经用到的最大的blocks数
| Key_read_requests | 2767808 | 有2767808个索引读请求
| Key_reads | 112 | 112请求在内存中没有找到直接从硬盘读取索引
| Key_write_requests | 720922 |
| Key_writes | 126410 |
+------------------------+---------+
计算索引未命中缓存的概率 key_cache_miss_rate = Key_reads / Key_read_requests * 100% =
3.排序使用情况
show global status like 'sort%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Sort_merge_passes | 3 |
| Sort_range | 1579023 |
| Sort_rows | 28793903 |
| Sort_scan | 198335 |
+-------------------+----------+
Sort_merge_passes 包括两步。MySQL 首先会尝试在内存中做排序,使用的内存大小由系统变量 Sort_buffer_size 决定,如果它的大小不够把所有的记录都读到内存中,
MySQL 就会把每次在内存中排序的结果存到临时文件中,等 MySQL 找到所有记录之后,再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。实际上
,MySQL 会用另一个临时文件来存再次排序的结果,所以通常会看到 Sort_merge_passes 增加的数值是建临时文件数的两倍。因为用到了临时文件,所以速度可能会比较慢,
增加 Sort_buffer_size 会减少 Sort_merge_passes 和 创建临时文件的次数。但盲目的增加 Sort_buffer_size 并不一定能提高速度.增加read_rnd_buffer_size
的值对排序的操作也有一点的好处
##########
4. 临时表 tmp_table_size 内存临时表的最大值,每个线程都要分配.超过这个值会被转移到磁盘上
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 |
+---------------------+-----------+
mysql> show global status like '%created_tmp%'; 查看临时表状态
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Created_tmp_disk_tables | 24794 | 在磁盘创建临时表次数
| Created_tmp_files | 37 | 在磁盘创建临时文件次数
| Created_tmp_tables | 267314 | 使用临时表总次数
+-------------------------+--------+
比较理想的配置是: Created_tmp_disk_tables / Created_tmp_tables * 100% <= 5%
TmpTable的状况主要是用于监控MySQL使用临时表的量是否过多,
是否有临时表过大而不得不从内存中换出到磁盘文件上。 a.如果:Created_tmp_disk_tables/Created_tmp_tables>10%,
则需调大tmp_table_size比较理想的配置是:Created_tmp_disk_tables/Created_tmp_tables<=25%b.如果:Created_tmp_tables非常大 ,
则可能是系统中排序操作过多,或者是表连接方式不是很优化
##########
5. Thread Pool 原理 客户端连接 => thread pool => 服务器线程
show global status like 'thread_%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 48 | thread pool中还剩余48个空闲thread
| Threadpool_threads | 51 | thread pool中允许同时运行的最大线程数
| Threads_cached | 0 |
| Threads_connected | 17 | 目前数据库一共有17个连接
| Threads_created | 421 | 创建的连接总次数
| Threads_running | 1 | 目前正在运行的线程数为1
+-------------------------+-------+
Threadpool_idle_threads + Threads_running 约等于 Threadpool_threads
show global variables like 'thread_%'; 官网https://www.percona.com/doc/percona-server/5.6/performance/threadpool.html
+-------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------+-----------------+
| thread_cache_size | 120 | 线程缓存值
| thread_concurrency | 10 | 线程并发 它只在Solaris < 9 的系统中有用 从mysql5.6.1开始,这个选项就被废了 5.7 没这个参数
| thread_handling | pool-of-threads | one-thread-per-connection 每个请求对应一个线程 thread pool线程池
| thread_pool_high_prio_mode | transactions | 有三个取值: transactions(default): 使用优先队列和普通队列,对于事务已经开启的statement(语句),放到优先队列中,否则放到普通队列中
statements: 所有的statement都可以单独进入高优先级队列,不判断是否开启事务或者ticket的值.
none: 禁止进入高优先级队列,主要针对监控之类的线程做设置.如果全局设置none的话,等同于statements
| thread_pool_high_prio_tickets | 4294967295 | 对新建立的连接分配ticket值,如果当ticket值为0,那么将被禁止进入高优先级队列
| thread_pool_idle_timeout | 60 | worker线程最大空闲时间,单位为秒,超过限制后会退出,默认60
| thread_pool_max_threads | 100000 | 限制pool里面最大的线程数量,如果达到这个限制,将不会创建新的线程
| thread_pool_oversubscribe | 10 | 一个group中线程数过载限制,当一个group中线程数超过次限制后,继续创建worker线程会被延迟,默认3
| thread_pool_size | 32 | threadpool中group数量,默认为cpu核心数,server启动时自动计算 如果主存储引擎是innodb,thread_pool_size设置在16至36之间,大多数情况设置在24到36
| thread_pool_stall_limit | 500 | Thread pool里线程如果超过thread_pool_stall_limit声明的时间没响应降会重新创建.默认是500ms http://get.ftqq.com/908.get
| thread_stack | 262144 |
| thread_statistics | OFF |
+-------------------------------+-----------------+
##########
6. 文件打开数(open_files)
show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 55 |
+---------------+-------+
show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
比较合适的设置:Open_files / open_files_limit * 100% <= 75%
##########
7.表锁情况
show global status like 'table_locks%';
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| Table_locks_immediate | 219036799 | 表示立即释放表锁数
| Table_locks_waited | 5 | 表示需要等待的表锁数
+-----------------------+-----------+
##########
8.binlog_cache_size 按SESSION为单位独享分配的
一个事务,在没有提交(uncommitted)的时候,产生的日志,记录到Cache中;等到事务提交(committed)需要提交的时候,则把日志持久化到磁盘。
show global status like 'binlog_%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Binlog_cache_disk_use | 108 |
| Binlog_cache_use | 3536757 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 25447 |
+----------------------------+---------+
http://www.cnblogs.com/ggjucheng/archive/2012/11/15/2771535.html
sync_binlog:这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。
sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘
sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
##########
9.表扫描情况
show global status like 'handler_read%';
show global status like 'com_select';
表扫描率 = Handler_read_rnd_next / Com_select
如果表扫描率超过4000,说明进行了太多表扫描,很有可能索引没有建好
##########
10. Innodb Buffer pool
innodb 既缓存表又缓存索引,还有设置多个缓冲池以增加并发,很像oracle 采用LRU算法:
所有buffer块位于同一列表,其中后3/8为old,每当新读入一个数据块时,先从队尾移除同等块数然后插入到old子列的头部,如再次访问该块则将其移至new子列的头部
Innodb_buffer_pool_size: buffer pool大小
Innodb_buffer_pool_instances: 子buffer pool数量,buffer pool至少为1G时才能生效
Innodb_old_blocks_pct: 范围5 – 95, 默认为37即3/8,指定old子列的比重
Innodb_old_blocks_time: 以ms为单位,新插入old子列的buffer块必须等待指定时间后才能移入new列,适用于one-time scan频繁的操作,以避免经常访问的数据块被剔出buffer pool
可通过状态变量获知当前buffer pool的运行信息
Innodb_buffer_pool_pages_total: 缓存池总页数
Innodb_buffer_pool_bytes_data: 当前buffer pool缓存的数据大小,包括脏数据
Innodb_buffer_pool_pages_data: 缓存数据的页数量
Innodb_buffer_pool_bytes_dirty: 缓存的脏数据大小
Innodb_buffer_pool_pages_diry: 缓存脏数据页数量
Innodb_buffer_pool_pages_flush: 刷新页请求数量
Innodb_buffer_pool_pages_free: 空闲页数量
Innodb_buffer_pool_pages_latched: 缓存中被latch的页数量,这些页此刻正在被读或写;然而计算此变量比较消耗资源,只有在UNIV_DEBUG被定义了才可用
Innodb_buffer_pool_pages_misc: 用于维护诸如行级锁或自适应hash索引的内存页=总页数-空闲页-使用的页数量
Innodb_buffer_pool_read_ahead: 预读入缓存的页数量
Innodb_buffer_pool_read_ahead_evicted 预读入但是1次都没用就被剔出缓存的页
Innodb_buffer_pool_read_requests InnoDB的逻辑读请求次数
Innodb_buffer_pool_reads: 直接从磁盘读取数据的逻辑读次数
Innodb_buffer_pool_wait_free: 缓存中没有空闲页满足当前请求,必须等待部分页回收或刷新,记录等待次数
Innodb_buffer_pool_write_requests: 向缓存的写数量
转载于:https://my.oschina.net/direnjie/blog/542191