首页 > 数据库技术 > 详细

数据库参数大乱炖

时间:2018-09-14 19:42:35      阅读:41      评论:0      收藏:0      [点我收藏+]

标签:lee   cte   size   fail   丢失   二进制日志   imm   可能   超过   

1、Max Connections和Max Used Connection
这两个就不细说了,分别是最大链接数限制和每个用户最大链接数限制。
2、Aborted Clients:
The number of connections that were aborted because the client died without closing the connection properly.
当ablort clients增大的时候意味着有客户端成功建立连接,但是很快就断开连接或者被终止了,这种情况一般发生在网络不稳定的环境中。主要的可能性有
  a)客户端没有主动关闭mysql连接mysql_close()。
  b)wait_timeout设置很短被mysql干掉了。
  c)客户端由于某些原因被干掉了。
3、Aborted Connection:
The number of failed attempts to connect to the MySQL server.
当有大量的链接连接不上mysql的时候,这个数值就会激增。主要的可能有:
  a)没有授权或者密码不对。一般错误日志中会有如下报错(Access denied for ‘user’@‘host’)
  b)连接数满了。一般报错包含(too many connections)
  c)超过链接时间限制,主要有这个参数控制connect_timeout(mysql默认是10s,基本除非网络环境极端不好,一般不会超时。)
4、Threads Connected:
The number of currently open connections.
也就是我们经常使用show processlist看见那个数值。
5、Connections
The number of connection attempts (successful or not) to the MySQL server.
所有尝试连接到mysql server的连接数,关键时不管成功还是失败。所以这个数值的增量并不等于show processlist的数值,这点需要注意。

?
了解以上参数之后,就可以分析这个图了,首先第一反应时连接数有增加,我们可以看到connections和thread connected都增加了,但是没有达到max connections的限制。
在仔细看图下的数据,我们发现abort connection和connections的max值和avg值基本一样,在仔细看图,可以发现蓝线和绿线基本重合了。
从上述参数含义看,在问题时间段所有尝试建立的链接都失败了,导致connections和abort connection同步增加完全重叠。
但是究竟是什么导致链接数上升,前端开始重建链接,由于当时的前端日志没有及时分析出来,故我们就不得而知了。但是有3个怀疑点:
1、由于mysql版本是5.5.12,所以可能遇到了max_connections的bug,可以见这个blog(http://www.cnblogs.com/billyxp/p/3408335.html),这种情况下,前端日志应该有非常多的too many connection是的报错。
2、短时间内有大量的大包传输,导致超过max_allow_packet的限制,导致断开连接。这个设置在server和client上都有,需要同步配置。同时前端应该报Got a packet?bigger than ‘max_allowedpacket’ bytes这个报错。
3、超过max_connect_error的限制,导致某一个ip出现问题,不停的重试。(这个可能是最不可能,首先默认数值非常大,其次单个ip不应该出现这么大的影响。max_connect_error代表某一个ip连续失败超过n之后,server会拒绝这个ip的请求,只有flush host cache才可以解封。)

Binlog_cache_disk_use  (事务类)二进志日志缓存的已经存在硬盘的条数?
Binlog_cache_use    (事务类)二进制日志已缓存的条数(内存中) ? ?注意,这个不是容量,而是事务个数。每次有一条事务提交,都会有一次增加
Binlog_stmt_cache_disk_use ?(非事务类)二进志日志缓存的已经存在硬盘的条数 ?
Binlog_stmt_cache_use??(非事务类)二进制日志已缓存的条数(内存中)?非事务型的语句,都存在这儿,比如MYISAM引擎的表,插入记录就存在这儿
Uptime_since_flush_status????????????--最近一次使用FLUSH STATUS?的时间(以秒为单位)
Uptime???????????????????????????--服务器已经运行的时间(以秒为单位)
Threads_running??????????????????--激活的(非睡眠状态)线程数。?
Threads_created??????????????????--创建用来处理连接的线程数。如果Threads_created较大,你可能要增加thread_cache_size值。?缓存访问率的计算方法Threads_created/Connections。
Threads_connected????????????????--当前打开的连接的数量。
Threads_cached???????????????????--线程缓存内的线程的数量
Table_locks_immediate表示立即释放表锁数,Table_locks_waited表示需要等待的表锁数,
如果Table_locks_immediate?/?Table_locks_waited?>?5000,最好采用InnoDB引擎,
因为InnoDB是行锁而MyISAM是表锁,对于高并发写入的应用InnoDB效果会好些。
示例中的服务器Table_locks_immediate?/?Table_locks_waited?=?235,MyISAM就足够了。
show global status like ‘Table_open_cache_misses‘; # 新打开的表的次数。 ? 不命中
show global status like ‘Table_open_cache_hits‘; # 从表缓存中拿已打开的表的次数,该状态变量 5.6 才开始存在 ?命中
show global status like ‘Opened_tables‘; # 打开表的总次数
通过已知自己数据库中有多少表,再观察 Opened_tables 的值,可以得知表缓存的数量是否合理,如果打开表的次数大于数据库中已有的表数
量,则表示 table_open_cache 的值不够,可以考虑加大。
计算表缓存的命中率:
select concat(Table_open_cache_hits /(Table_open_cache_misses + Table_open_cache_hits) * 100,’%’);
通过计算表缓存的命中率,可反映出表缓存的情况,该比例越大越好

1 读写比例:
show global status like ‘com_select‘;? 获得服务器启动到目前查询操作执行的次数;
show global status like ‘com_insert‘;? 获得服务器启动到目前插入操作执行的次数;
show global status like ‘com_update‘;? 获得服务器启动到目前更新操作执行的次数;
show global status like ‘com_delete’;? 获得服务器启动到目前删除操作执行的次数;

计算读百分比:
select concat(com_select / (com_select+com_insert+com_update+com_delete) 100,‘%‘);
计算写百分比:
select concat(com_insert+com_update+com_delete / (com_select+com_insert+com_update+com_delete)
100,’%’);
通过检查数据库读写比例,可反映出应用是读密集型还是写密集型。

2? 慢查询比例:
开启慢查询日志:
slow_query_log = 1 # 开启慢查询日志
log_output = FILE|TABLE # 指定日志存储方式,默认为 file
slow_query_log_file = slow-query.log # 指定慢查询日志文件位置
long_query_time = 1 # 执行及响应时间超过该参数设置的值记录日志

show global status like ‘Slow_queries ‘; # 获得服务器启动到目前慢查询操作记录的次数;

注意,慢查询包括 select 、 update 以及 delete ,没有 insert 。
计算慢查询比例:
select concat(Slow_queries / (Com_select+Com_update+Com_delete) * 100,’%’);
通过计算慢查询比例,可反映出数据库运行效能。

3 连接数检查:
show global status like ‘max_connections‘; # 获得数据库运行的最大连接数 ? ? ? ? ? ?//允许的最大连接数
show global status like ‘Max_used_connections‘; # 获得最大一次的连接数 ? ? ? ? ? ?//最大突发并行连接数 ?

show global status like ‘connections‘; # 获得数据库运行到目前,总共被连接了多少次 ? //登陆的次数
?

show global status like ‘Threads_connected‘; # 获得当前连接数
show global status like ‘Threads_running‘; # 获得当前正在运行的连接数

mysql> show global status like ‘Threads_connected‘; //两次 mysql -uroot -p +-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 2 |
+-------------------+-------+1 row in set (0.01 sec)

mysql> show global status like ‘Threads_running‘; +-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 1 |
+-----------------+-------+1 row in set (0.00 sec)

mysql> show processlist;+----+------+-----------+-------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+-------+------------------+
| 2 | root | localhost | mysql | Sleep | 4 | | NULL |
| 3 | root | localhost | test | Query | 0 | init | show processlist |
+----+------+-----------+-------+---------+------+-------+------------------+2 rows in set (0.00 sec)

?

计算当前连接数的比例:
select concat(Threads_connected / max_connections 100,‘%‘); # 计算最大一次的连接数比例:
select concat(Max_used_connections / max_connections
100,‘%‘);# 通过连接数检查,可得知数据库在不同时间段被请求的压力

4 线程缓存:
show global status like ‘Connections‘; # 获得数据库运行到目前,总共被连接了多少次
show global status like ‘Threads_created‘; # 获得数据库运行到目前,创建连接线程的次数
计算连接线程缓存命中率:
select concat((Connections-Threads_created) / Connections × 100,’%’);
通过计算连接线程缓存命中率,可反映出连接线程的命中情况,命中率越大越好。
如果命中率过低,则表示缓存连接线程的数量过少,可以考虑加大 thread_cache_size 的值。

5 表缓存:
show global status like ‘Table_open_cache_misses‘; # 新打开的表的次数。 ? 不命中
show global status like ‘Table_open_cache_hits‘; # 从表缓存中拿已打开的表的次数,该状态变量 5.6 才开始存在 ?命中
show global status like ‘Opened_tables‘; # 打开表的总次数
通过已知自己数据库中有多少表,再观察 Opened_tables 的值,可以得知表缓存的数量是否合理,如果打开表的次数大于数据库中已有的表数
量,则表示 table_open_cache 的值不够,可以考虑加大。
计算表缓存的命中率:
select concat(Table_open_cache_hits /(Table_open_cache_misses + Table_open_cache_hits) * 100,’%’);
通过计算表缓存的命中率,可反映出表缓存的情况,该比例越大越好

mysql> create table ttt (a int);
Query OK, 0 rows affected (0.22 sec)

mysql> show global status like ‘Table_open_cache_hits‘;+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_open_cache_hits | 31 |
+-----------------------+-------+1 row in set (0.00 sec)

mysql> show global status like ‘Table_open_cache_misses‘;+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Table_open_cache_misses | 116 |
+-------------------------+-------+1 row in set (0.00 sec)

mysql> show global status like ‘Opened_tables‘;+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 116 |
+---------------+-------+1 row in set (0.00 sec)

mysql> select * from ttt; //第一次打开
Empty set (0.01 sec)

mysql> show global status like ‘Opened_tables‘; //+1+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 117 |
+---------------+-------+1 row in set (0.02 sec)

mysql> show global status like ‘Table_open_cache_misses‘; //table缓存中没有表,丢失 +1+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Table_open_cache_misses | 117 |
+-------------------------+-------+1 row in set (0.01 sec)

mysql> show global status like ‘Table_open_cache_hits‘; //缓存未命中,不变+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_open_cache_hits | 31 |
+-----------------------+-------+1 row in set (0.01 sec)

mysql> select * from ttt; //在打开表
Empty set (0.00 sec)

mysql> show global status like ‘Table_open_cache_hits‘; //表缓存命中+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_open_cache_hits | 32 |
+-----------------------+-------+1 row in set (0.01 sec)

?

6 临时表:
show global status like ‘Created_tmp_disk_tables‘; # 查看在磁盘上创建临时表的次数
show global status like ‘Created_tmp_tables‘; # 查看创建临时表的总次数,包括在内存中和磁盘。
如果发现在磁盘上创建临时表的次数过多,则表示临时表的缓存区内存不够,可以考虑加大 tmp_table_size 和 max_heap_table_size 的值。
计算在磁盘上创建临时表的比例:
select concat(Created_tmp_disk_tables / Created_tmp_tables * 100,’%’);
通过计算在磁盘上创建临时表的比例,可反映出数据库的使用临时表的情况,该比例越小越好。

7 额外的排序:
show global status like ‘Sort_merge_passes‘; # 在磁盘中进行额外排序的次数
show global status like ‘Sort_scan‘; # 通过表扫描进行排序的总次数,也就是额外排序的总次数
如果发现在磁盘上进行排序的次数过多,则表示排序缓冲区内存不够,可以考虑加大 sort_buffer_size 的值。
计算磁盘排序的比例:
select concat(Sort_merge_passes / Sort_scan * 100,‘%‘);
通过计算在磁盘上进行额外排序的比例,可反映出数据库排序的情况,该比例越小越好。

8 binlog 缓冲:
show global status like ‘Binlog_cache_disk_use‘; # 在磁盘上创建临时文件用于保存 binlog 的次数
show global status like ‘Binlog_cache_use‘; # 缓冲 binlog 的总次数,包括 binlog 缓冲区和在磁盘上创建临时文件保存 binlog 的总次数
如果发现在磁盘上创建临时文件保存 binlog 的次数过多,则表示 binlog 缓冲区内存不够,可以考虑加大 binlog_cache_size 的值。
计算在磁盘上创建临时文件保存 binlog 的比例:
select concat(Binlog_cache_disk_use / Binlog_cache_use * 100,’%’);
通过计算在磁盘上创建临时文件保 binlog 的比例,可反映出数据库 binlog 的情况,该比例越小越好。

9 redo 日志:
select global status like ‘Innodb_log_waits‘; # 查看 innodb redo 日志等待缓冲区刷新的次数。
当 redo 缓冲区容纳不下事务产生的 redo 日志时,本次事务产生的 redo 日志在写入 redo 缓冲区之前就必须等待 redo 缓冲区有足够的空间才能写入。
如果发现 redo 日志等待刷新的次数过多,则表示 innodb redo 日志缓冲区的大小不够,可以考虑加大 innodb_log_buffer_size 的值。

10 InnoDB 缓存:
show global status like ‘Innodb_buffer_pool_read_requests‘; # 读取页的总次数
show global status like ‘Innodb_buffer_pool_read‘; # 从磁盘读取页的次数
如果发现从磁盘读取页的次数过多,则有可能是因为 innodb 缓冲池的大小不够,此时可以考虑加到 innodb_buffer_pool_size 的值。
计算 innodb 缓存命中率:
select concat((Innodb_buffer_pool_read_requests - Innodb_buffer_pool_read) / Innodb_buffer_pool_read_requests * 100,’%’);
通过计算 innodb 缓存命中率,可反映出 innodb 缓存的效率,该比例越大越好

| Innodb_buffer_pool_pages_data | 496 |包含数据的页数(脏或干净)。
| Innodb_buffer_pool_pages_dirty | 5?|当前的脏页数
| Innodb_buffer_pool_pages_flushed?| 1182773?|已经flush的页面数
| Innodb_buffer_pool_pages_free?| 0?|空页数
| Innodb_buffer_pool_pages_misc?| 16 |优先用作管理的页数
| Innodb_buffer_pool_pages_total?| 512?|总页数
| Innodb_buffer_pool_read_ahead_rnd | 515979?|随机预读的次数(读大部分数据时)
| Innodb_buffer_pool_read_ahead_seq | 3408867 |顺序预读的次数(全表扫描时)
| Innodb_buffer_pool_read_requests?| 10760142502?|InnoDB已经完成的逻辑读请求数
| Innodb_buffer_pool_reads?| 6912521 |从磁盘上一页一页的读取的页数,从缓冲池中读取页面, 但缓冲池里面没有, 就会从磁盘读取
| Innodb_buffer_pool_wait_free | 0?|缓冲池等待空闲页的次数, 当需要空闲块而系统中没有时, 就会等待空闲页面
| Innodb_buffer_pool_write_requests | 8136890?|缓冲池总共发出的写请求次数
| Innodb_data_fsyncs?| 1457304 |fsync()操作数
| Innodb_data_pending_fsyncs?| 0?|innodb当前等待的fsync次数
| Innodb_data_pending_reads?| 0?|innodb当前等待的读的次数
| Innodb_data_pending_writes?| 0?|innodb当前等待的写的次数
| Innodb_data_read?| 1009745694720 |总共读入的字节数
| Innodb_data_reads?| 11756800?|innodb完成的读的次数
| Innodb_data_writes | 2308122 |innodb完成的写的次数
| Innodb_data_written?| 40737600000?| 总共写出的字节数
| Innodb_dblwr_pages_written?| 1182773?|双写已经写好的页数
| Innodb_dblwr_writes?| 76828?|已经执行的双写操作数量
| Innodb_log_waits | 10?|因为日志缓冲区太小,我们在继续前必须先等待对它清空
| Innodb_log_write_requests | 2979109?|日志写请求数
| Innodb_log_writes?| 1252587 |向日志文件的物理写数量
| Innodb_os_log_fsyncs | 1304054?|向日志文件完成的fsync()写数量
| Innodb_os_log_pending_fsyncs?| 0?|挂起的日志文件fsync()操作数量。
| Innodb_os_log_pending_writes | 0?|挂起的日志文件写操作。
| Innodb_os_log_written | 1954254848?|写入日志文件的字节数
| Innodb_page_size | 16384 |编译的InnoDB页大小(默认16KB)
| Innodb_pages_created?| 58540 |创建的页数
| Innodb_pages_read?| 61630057 |从buffer_pool中读取的页数
| Innodb_pages_written?| 1182773 |写入的页数
| Innodb_row_lock_current_waits?| 0?|当前等待的待锁定的行数
| Innodb_row_lock_time?| 595899 |行锁定花费的总时间,单位毫秒
| Innodb_row_lock_time_avg?| 54? |行锁定的平均时间
| Innodb_row_lock_time_max?| 7241 |行锁定的最长时间
| Innodb_row_lock_waits?| 11032 |一行锁定必须等待的时间数
| Innodb_rows_deleted?| 7199?|删除
| Innodb_rows_inserted?| 736893?|插入
| Innodb_rows_read?| 10400853035?|从InnoDB表读取的行数
| Innodb_rows_updated?| 932768 |更新**

数据库参数大乱炖

标签:lee   cte   size   fail   丢失   二进制日志   imm   可能   超过   

原文:http://blog.51cto.com/13120271/2175403

(0)
(0)
   
举报
评论 一句话评论(0
0条  
登录后才能评论!
© 2014 bubuko.com 版权所有 鲁ICP备09046678号-4
打开技术之扣,分享程序人生!
             

鲁公网安备 37021202000002号