sysbench mysql插入数据 mysql中sys

MySQL5.7新库sys的一些实用功能

sys schema数据主要源自performance_schema。其目标是把查询performance_schema的复杂度降低，让DBA能更好地利用这个库里的数据，更快地了解MySQL的运行情况。sys schema包含了一些视图、函数和存储过程，sys schema用以帮助DBA及开发更方便的分析定位问题。

        那么对于我们DBA来说，sys schema的一些主要用途有哪些？

1、哪个用户或者哪个IP的客户端使用了最多的资源？

2、数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的？

3、数据库中哪些SQL被频繁执行？

4、哪个文件产生了最多的IO，读多还是写多？

5、哪个表上的IO请求最多？

6、哪个表被访问的最多？

7、哪些语句延迟比较严重？

8、哪些SQL执行了全表扫描或执行了排序操作？

9、哪些SQL使用了临时表，又有哪些SQL用到了磁盘临时表？

10、哪个库/表占用了最多的buffer pool？

11、每个连接分配多少内存？

12、自增长字段的最大值和当前已经使用到的值？

13、索引使用情况如何？有哪些冗余索引和无用索引？

14、内部有多个线程在运行？

 

下面一一举例说明：

1、哪个用户或者哪个IP的客户端使用了最多的资源？
mysql> select * from host_summary limit 1\G
*************************** 1. row ***************************
host: 10.10.4.201    //客户端主机
statements: 207    //执行的语句总数
statement_latency: 16.49 w    //语句等待时间，y-年 w-周 d-天 h-小时 m-分钟
statement_avg_latency: 13.39 h    //语句平均等待时间
table_scans: 0    //表扫描次数
file_ios: 22290615    //IO事件总数
file_io_latency: 3.67 m    //IO等待时间
current_connections: 1    //当前连接数
total_connections: 19    //总连接数
unique_users: 1    //连接过来的唯一用户数
current_memory: 0 bytes    //当前分配内存
total_memory_allocated: 0 bytes    //总共分配内存
 
2、数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的？
2.1、查看当前连接情况
mysql> select host, current_connections, statements from host_summary limit 1\G
*************************** 1. row ***************************
host: 10.10.4.201    //客户端主机
current_connections: 1    //当前连接数
statements: 207    //执行的语句总数
2.2、查看当前正在执行的SQL（和执行show full processlist的效果相当）
mysql> select conn_id, user, current_statement, last_statement from session where current_statement is not null limit 1\G
*************************** 1. row ***************************
conn_id: 115882553    //连接的ID，对应show processlist中的ID列
user: root@localhost    //该线程创建的用户名
current_statement: select conn_id, user, current_ ... _statement is not null limit 1    //该线程执行的语句
last_statement: NULL    //此线程最后一次执行的语句
 
3、数据库中哪些SQL被频繁执行？
查询执行次数top10的SQL：select db,exec_count,query from statement_analysis order by exec_count desc limit 10;
 
4、哪个文件产生了最多的IO，读多还是写多？
mysql> select * from io_global_by_file_by_bytes limit 1\G
*************************** 1. row ***************************
file: @@datadir/dedecms/dede_archives.MYD    //被操作的文件名
count_read: 16112940323    //总共有多少次读
total_read: 58.90 TiB    //总共读了多少字节
avg_read: 3.93 KiB    //平均每次读多少字节
count_write: 8830747    //总共多少次写
total_written: 1.89 GiB    //总共写了多少字节
avg_write: 230 bytes    //平均每次写多少字节
total: 58.90 TiB    //总共读写了多少字节
write_pct: 0.00    //写占比
 
5、哪个表上的IO请求最多？
通过以下sql查出被操做的文件名，然后就可以知道对应的是那个表：
mysql> select * from io_global_by_file_by_bytes limit 1\G
*************************** 1. row ***************************
file: @@datadir/dedecms/dede_archives.MYD    //被操作的文件名
count_read: 16112940323    //总共有多少次读
total_read: 58.90 TiB    //总共读了多少字节
avg_read: 3.93 KiB    //平均每次读多少字节
count_write: 8830747    //总共多少次写
total_written: 1.89 GiB    //总共写了多少字节
avg_write: 230 bytes    //平均每次写多少字节
total: 58.90 TiB    //总共读写了多少字节
write_pct: 0.00    //写占比
 
6、哪个表被访问的最多？
可以通过下面先查询执行最多的sql，然后找出对应的表：
mysql> select * from statement_analysis order by exec_count desc limit 10\G
*************************** 1. row ***************************
query: SELECT * FROM `dede_archives` WHERE `id` = ? LIMIT ?, ...    //归一化的SQL
db: dedecms    //在哪个DB中执行
full_scan:    //全表扫描的次数
exec_count: 5781519    //该SQL执行的总次数
err_count: 0    //执行错误的次数
warn_count: 0    //执行警告的次数
total_latency: 54.19 m    //总共发生延迟的时间
max_latency: 6.89 s    //最大延迟时间
avg_latency: 562.42 us    //平均延迟时间
lock_latency: 38.70 m    //因锁等待占用的总时间
rows_sent: 5478992    //执行该SQL返回的总行数
rows_sent_avg: 1    //每次执行该SQL平均返回的行数
rows_examined: 5478992    //该语句扫描的次数
rows_examined_avg: 1    //每次执行该SQL平均扫描的次数
rows_affected: 0    //该语句影响到的行数
rows_affected_avg: 0    //每次执行该语句平均影响到的行数
tmp_tables: 0    //该SQL形成内存临时表的总次数
tmp_disk_tables: 0    //该SQL形成文件临时表的总次数
rows_sorted: 0    //该SQL总共排序的行数
sort_merge_passes: 0    //用于排序中合并的总次数
digest: 874bf766d714c115ff67c6ecc85d7172    //该语句的hash值
first_seen: 2017-04-06 19:28:07    //该SQL最早出现的时间
last_seen: 2018-01-23 11:28:26    //该SQL最近出现的时间
 
7、哪些语句延迟比较严重？
查看statement_analysis中avg_latency的最高的SQL：
mysql> select * from statement_analysis order by avg_latency desc limit 1\G
*************************** 1. row ***************************
query: SELECT SQL_NO_CACHE * FROM `order_pay`    //归一化的SQL
db: dyfb2c    //在哪个DB中执行
full_scan: *    //全表扫描的次数
exec_count: 330    //该SQL执行的总次数
err_count: 0    //发生错误的次数
warn_count: 0    //发生警告的次数
total_latency: 329.22 ms    //总共发生的延迟时间
max_latency: 2.03 ms    //最大延迟时间
avg_latency: 997.63 us    //平均延迟时间
lock_latency: 13.50 ms    //因锁等待占用的总时间
rows_sent: 310860    //执行该SQL返回的总行数
rows_sent_avg: 942    //执行该SQL平均返回的行数
rows_examined: 310860    //该语句扫描的次数
rows_examined_avg: 942    //每次执行该SQL平均扫描的次数
rows_affected: 0    //该语句影响到的行数
rows_affected_avg: 0    //每次执行该语句平均影响到的行数
tmp_tables: 0    //该SQL形成内存临时表的总次数
tmp_disk_tables: 0    //该SQL形成文件临时表的总次数
rows_sorted: 0    //该SQL总共排序的行数
sort_merge_passes: 0    //用于排序中合并的总次数
digest: 874bf766d714c115ff67c6ecc85d7172    //该语句的hash值
first_seen: 2017-04-06 19:28:07    //该SQL最早出现的时间
last_seen: 2018-01-23 11:28:26    //该SQL最近出现的时间
 
8、哪些SQL执行了全表扫描或执行了排序操作？
8.1、全表扫描：
mysql> select * from statements_with_full_table_scans limit 1\G
*************************** 1. row ***************************
query: SELECT `usertype` , `userid` , ... ERE `userid` = ? LIMIT ?, ...
db: dedecms
exec_count: 2576
total_latency: 996.10 ms
no_index_used_count: 2576
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 0
rows_examined: 36496
rows_sent_avg: 0
rows_examined_avg: 14
first_seen: 2017-03-08 16:12:55
last_seen: 2018-01-23 11:33:44
digest: c5b6efc92a51003d6f495e1c4233bdc4
8.2、排序操作：
mysql> select * from statements_with_sorting limit 1\G
*************************** 1. row ***************************
query: SELECT `arc` . `id` , `arc` .  ...  `senddate` DESC LIMIT ?, ...
db: dedecms
exec_count: 2329617
total_latency: 10.44 h
sort_merge_passes: 0
avg_sort_merges: 0
sorts_using_scans: 2329610
sort_using_range: 0
rows_sorted: 5731397
avg_rows_sorted: 2
first_seen: 2017-06-05 19:59:08
last_seen: 2018-01-23 11:54:53
digest: dec0ec92c5aad756414d4b26c7e17245
 
9、哪些SQL使用了临时表，又有哪些SQL用到了磁盘临时表？
查看statement_analysis中哪个SQL的tmp_tables 、tmp_disk_tables值大于0即可：
mysql> select db, query, tmp_tables, tmp_disk_tables  from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 1\G
*************************** 1. row ***************************
db: dedecms
query: SELECT `arc` . `id` , `arc` .  ...  `senddate` DESC LIMIT ?, ...
tmp_tables: 1101140    //该SQL形成内存临时表的总次数
tmp_disk_tables: 0    //该SQL形成文件临时表的总次数
 
10、哪个库/表占用了最多的buffer pool？
库：
mysql> select * from innodb_buffer_stats_by_schema order by allocated desc limit 1\G
*************************** 1. row ***************************
object_schema: tpshop    //库名
allocated: 583.83 MiB    //分配的buffer pool大小
data: 468.04 MiB    //实际缓存的数据大小
pages: 37365    //缓存的page数
pages_hashed: 37365    //Buffer  pool中进行hash 索引的page
pages_old: 37365    //Buffer pool中的旧页，可能被置换出去
rows_cached: 320285    //Buffer pool中以行为单位的缓存
表：
mysql> select * from innodb_buffer_stats_by_table order by allocated desc limit 1\G
*************************** 1. row ***************************
object_schema: 160dyf_dede    //库名
object_name: dede_arctype    //表名
allocated: 96.00 KiB    //基于表分配的buffer pool大小
data: 37.80 KiB    //基于表实际缓存的数据大小
pages: 6    //缓存的page数
pages_hashed: 6    //Buffer  pool中进行hash 索引的page
pages_old: 6    //Buffer pool中的旧页，可能被置换出去
rows_cached: 37    //Buffer pool中以行为单位的缓存
 
11、每个连接分配多少内存？
利用session表和memory_by_thread_by_current_bytes分配表进行关联查询：
mysql> select b.user, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated,current_statement from memory_by_thread_by_current_bytes a, session b where a.thread_id = b.thd_id limit 1\G
*************************** 1. row ***************************
user: rep@10.8.2.198    //该线程创建的用户名
current_count_used: 0    //当前使用的内存块还没有释放
current_allocated: 0 bytes    //当前分配的内存大小（字节）而且没有被释放出来
current_avg_alloc: 0 bytes    //平均分配的blocks
current_max_alloc: 0 bytes    //当前线程分配的最多内存
total_allocated: 0 bytes    //当前连接总共分配的内存大小
current_statement: NULL
 
12、自增长字段的最大值和当前已经使用到的值？
select * from schema_auto_increment_columns order by auto_increment_ratio desc limit 3;
 
13、索引使用情况如何？有哪些冗余索引和无用索引？
13.1、索引使用情况：
mysql> select * from schema_index_statistics limit 1\G
*************************** 1. row ***************************
table_schema: dedecms
table_name: dede_archives
index_name: mainindex
rows_selected: 12142972982
select_latency: 6.66 h
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 0
update_latency: 0 ps
rows_deleted: 0
delete_latency: 0 ps
13.2、冗余索引：
mysql> select * from schema_redundant_indexes limit 1\G
*************************** 1. row ***************************
table_schema: 160dyf_dede    //
table_name: dede_member_group
redundant_index_name: id
redundant_index_columns: id
redundant_index_non_unique: 1
dominant_index_name: PRIMARY
dominant_index_columns: id
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `160dyf_dede`.`dede_member_group` DROP INDEX `id`
13.3、无用索引：
mysql> select * from schema_unused_indexes limit 1\G
*************************** 1. row ***************************
object_schema: 160dyf_dede
object_name: dede_addonimages
index_name: imagesMain
 
14、内部有多个线程在运行？
select user, count(*) from processlist group by user;