MySQL 5.6/ 5.7新參數研究
使用大的innodb_buffer_pool , innodb_buffer_pool_instances=1表現最好
在多CPU環境下，表格資料不大的環境, 可以設定innodb_buffer_pool_instances 用innodb_buffer_pool_size/2GB 開始tune起
#for OLTP testing
sync_binlog=0 #default 1
sync_binlog=0 #default 1
innodb_stats_persistent = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 4
table_open_cache_instances =16
metadata_locks_hash_instances = 256 # better hash from 5.6.15, 5.7.3.
innodb_buffer_pool_size = #based on 50% ~ 75% of system memory
innodb_buffer_pool_instances = 8 or # innodb_buffer_pool_size/2G
innodb_log_buffer_size = 32M
innodb_additional_mem_pool_size = 8M #for data dictionary
innodb_flush_method=O_DSYNC
innodb_flush_log_at_trx_commit=2 #1 (Default) , 0/2 (more performance, less reliability)
innodb_flush_log_at_trx_commit=2 #1 (Default) , 0/2 (more performance, less reliability)
innodb_log_file_size = 2000M # based on data change rate
innodb_checksum_algorithm=crc32 #提升checksum速度,hardware acceleration provided by recent Intel CPUs.innodb_lock_wait_timeout=30 #default 50 second
innodb_support_xa=OFF #diable distributed transaction with better performance
innodb_compression_level=6 #default 6 , from 0-9
innodb_log_compressed_pages=1 #減少redo log寫入量, 減少checkpoint頻率 ref: http://mysqllover.com/?p=371
thread_stack=256
join_buffer_size=128K
binlog_cache_size=32k
tmp_table_size=32M #default16M
thread_cache_size=64
thread_cache_size=64
sort_buffer_size=2M #set larger at session level
open_files_limit=65535
max_connections=1024
#Ref: http://blog.51cto.com/lee90/1826658
innodb_undo_logs=128 #MySQL5.6中開始支持把undo log分離到獨立的表空間, 以前是一起放在ibdata空間去
innodb_undo_tablespaces=4 #default 0 不設定
innodb_undo_directory = /tmp/
如何得知memory使用狀況
#MySQL 5.7.x
mysql> SELECT * FROM sys.memory_global_total;
SELECT * FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G;
*************************** 1. row ***************************
event_name: memory/innodb/buf_buf_pool
current_count: 1
current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB --> current_avg_alloc
The current number of allocated bytes per memory block for the thread
high_count: 1
high_alloc: 131.06 MiB
high_avg_alloc: 131.06 MiB
mysql> select event_name,current_alloc from memory_global_by_current_bytes limit 10;
+-----------------------------------------------------------------------------+---------------+
| event_name | current_alloc |
+-----------------------------------------------------------------------------+---------------+
| memory/mysys/IO_CACHE | 482.41 MiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 198.38 MiB |
| memory/performance_schema/memory_summary_by_thread_by_event_name | 129.38 MiB |
| memory/performance_schema/events_statements_current | 80.41 MiB |
| memory/performance_schema/events_statements_history | 80.41 MiB |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name | 74.39 MiB |
| memory/performance_schema/events_statements_current.sqltext | 57.50 MiB |
| memory/performance_schema/events_statements_current.tokens | 57.50 MiB |
| memory/performance_schema/events_statements_history.tokens | 57.50 MiB |
| memory/performance_schema/events_statements_history.sqltext | 57.50 MiB |
+-----------------------------------------------------------------------------+---------------+
10 rows in set (0.04 sec)
MySQL out of memory 常發生在系統 與DB 記憶體互搶時所發生，此時database memory的sizing就很重要
否則會造成 作業系統在記憶體不足的狀況下，先kill 使用最多記憶體的process
--> 導致mysql的process被killed掉...在my.err會看到
Apr 19 20:09:30 mysql1 kernel: Out of memory: Kill process 3046 (mysqld) score 970 or sacrifice child
MySQL 5.7新特性：SYS資料庫詳解
1.統計innodb buffer 的使用量 by object
mysql> select * from innodb_buffer_stats_by_schema limit 1\G
*************************** 1. row ***************************
object_schema: kupai_notice
allocated: 2.17 GiB
data: 1.39 GiB
pages: 142454
pages_hashed: 142454
pages_old: 142454
rows_cached: 3899147
object_schema：資料庫名稱。
allocated：分配給當前資料庫的總的位元組數。
data：分配給當前資料庫的資料位元組數。
pages：分配給當前資料庫的總頁數。
pages_hashed：分配給當前資料庫的hash頁數。
pages_old：分配給當前資料庫的舊頁數。
rows_cached：當前資料庫緩存的行數。
2統計innodb buffer 的使用量 by table
mysql> select * from innodb_buffer_stats_by_table limit 1;
+---------------+-------------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------------+------------+------------+-------+--------------+-----------+-------------+
| kupai | user_wallet_log_0 | 466.53 MiB | 369.34 MiB | 29858 | 29858 | 29858 | 1849086 |
+---------------+-------------------+------------+------------+-------+--------------+-----------+-------------+
1 row in set (8.28 sec)
3.show lock status
Finding rows that are locked by InnoDB
mysql> SELECT * FROM innodb_lock_waits\G
*************************** 1. row
***************************
wait_started: 2014-11-11
13:39:20
wait_age: 00:00:07
wait_age_secs: 7
locked_table: `db1`.`t1`
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 867158
waiting_trx_started: 2014-11-11 13:39:15
waiting_trx_age: 00:00:12
waiting_trx_rows_locked: 0
waiting_trx_rows_modified:
0
waiting_pid: 3
waiting_query: UPDATE t1 SET val
= val + 1 WHERE id = 2
waiting_lock_id: 867158:2363:3:3
waiting_lock_mode: X
blocking_trx_id: 867157
blocking_pid: 4
blocking_query: UPDATE t1 SET val
= val + 1 + SLEEP(10) WHERE id = 2
blocking_lock_id: 867157:2363:3:3
blocking_lock_mode: X
blocking_trx_started: 2014-11-11 13:39:11
blocking_trx_age: 00:00:16
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4
```
4.列出那些SQL產生且耗用臨時表格
mysql> select * from statements_with_temp_tables limit 1\G
*************************** 1. row ***************************
query: SHOW FULL FIELDS FROM `kupai` . `bid_goods`
db: kupai
exec_count: 8923776
total_latency: 2.16 h
memory_tmp_tables: 8923889
disk_tmp_tables: 8923893
avg_tmp_tables_per_query: 1
tmp_tables_to_disk_pct: 100
first_seen: 2016-09-05 00:13:42
last_seen: 2017-03-28 17:33:49
digest: e38e3ce8bf6664adc76060c06737ddd3
1 row in set (0.09 sec)
5.資料庫庫連接來自哪裡，以及這些連接對資料庫的請求情況是怎樣的？
查看即時連接情況。
mysql> select host, current_connections, statements from host_summary;
6.查看當前正在執行的SQL和執行show full processlist的效果相當。
mysql> select conn_id, user, current_statement, last_statement from session;
mysql> select host, current_connections, statements from host_summary;
+------------+---------------------+------------+
| host | current_connections | statements |
+------------+---------------------+------------+
| background | 18 | NULL |
| centos7 | 0 | 2 |
| localhost | 2 | 489 |
+------------+---------------------+------------+
3 rows in set (0.01 sec)
+------------+---------------------+------------+
| host | current_connections | statements |
+------------+---------------------+------------+
| background | 18 | NULL |
| centos7 | 0 | 2 |
| localhost | 2 | 489 |
+------------+---------------------+------------+
3 rows in set (0.01 sec)
7.查看statement_analysis中avg_latency的最高的SQL。
mysql> select * from statement_analysis order by avg_latency desc limit 10;
8.哪個表格佔用了最多的buffer pool?
mysqlm > select * from innodb_buffer_stats_by_table order by allocated desc limit 10;
9.MySQL索引使用情況統計？
mysql> select * from schema_index_statistics;
10.MySQL有哪些冗餘索引和無用索引？
mysql> select * from schema_redundant_indexes;
mysql> select * from schema_unused_indexes;
#或者在my.cnf設定log-queries-not-using-indexes參數, 把沒有使用index的SQL寫到slow-query-log
ref:
https://blog.csdn.net/crazyhacking/article/details/20549577
https://blog.csdn.net/longxibendi/article/details/49357763
https://blog.csdn.net/longxibendi/article/details/49357763
MySQL 5.7 Log Messages: page_cleaner: 1000ms intended loop took 8120ms.(Doc ID 2014477.1)
Recommended Settings for MySQL 5.6, 5.7 Server for Online Transaction Processing (OLTP) and Benchmarking (Doc ID 1531329.1)
How do I know if MySQL has a memory leak? (Doc ID 1962842.1)
MySQL 5.7內存使用分析 http://www.ywnds.com/?p=7658
沒有留言:
張貼留言