character-set-server = utf8mb4
#tunning
skip_name_resolve = 1
open_files_limit = 65535
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
#table_open_cache_instances = 64 #MySQL 5.6
sort_buffer_size = 4M
join_buffer_size = 4M
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
#master_info_repository = TABLE #MySQL 5.6
#relay_log_info_repository = TABLE #MySQL 5.6
#gtid_mode = on
#enforce_gtid_consistency = 1
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
lock_wait_timeout = 3600
#explicit_defaults_for_timestamp = 1 #MySQL 5.6
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2867M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
#innodb_data_file_path = ibdata1:1G:autoextend
#正確做法，如果原本己經有了 http://www.cnblogs.com/ivictor/p/5340822.html
#innodb_data_file_path = ibdata1:原始大小;ibdata2:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
#innodb_max_undo_log_size = 4G
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
#innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
#ineinternal_tmp_disk_storage_engine = InnoDB
innodb_status_file = 1
innodb_status_output = 1
innodb_status_output_locks = 1
innodb_stats_on_metadata = 0
#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'
#innodb monitor 5.7後禁用
#innodb_monitor_enable="module_innodb"
#innodb_monitor_enable="module_server"
#innodb_monitor_enable="module_dml"
#innodb_monitor_enable="module_ddl"
#innodb_monitor_enable="module_trx"
#innodb_monitor_enable="module_os"
#innodb_monitor_enable="module_purge"
#innodb_monitor_enable="module_log"
#innodb_monitor_enable="module_lock"
#innodb_monitor_enable="module_buffer"
#innodb_monitor_enable="module_index"
#innodb_monitor_enable="module_ibuf_system"
#innodb_monitor_enable="module_buffer_page"
#innodb_monitor_enable="module_adaptive_hash"
key_buffer_size = 32M #16M
max_allowed_packet = 32M #16M
thread_stack = 512K #192K
thread_cache_size = 150 #8
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
query_cache_type = 0
query_cache_limit = 1M
query_cache_size = 0 #16M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
log_slow_rate_limit = 1000
log_slow_verbosity = query_plan
server-id = 3306
log_bin = /var/log/mysql/mysql-bin.log
sync_binlog = 1
expire_logs_days = 7
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1
程序猿都该知道的MySQL秘籍 - 叶金荣@-----
thread_handling=pool-of-threads
http://www.cnblogs.com/nocode/archive/2013/05/25/3098317.html
-----
檔案系統 xfs > ext4 >ext 2 or 3
innodb_buffer_pool_size 約物理內存的50%~70%
innodb_data_file_path 分始化大小至少1G
innodb_log_file_size 1G以上
innodb_flush_log_at_trx_commit 0=>最快、數據最不安全， 1=>最慢最安全， 2=>折中
innodb_max_dirty_pages_pct 25~50(25%~50%)為宜
innodb_io_capacity HDD=>1,000左右，SSD=>10,000左右，PCIe SSD=>20,000以上
**kernel io scheduler => dedline SSD=>noop
key_buffer_size 32M以下
sync_binlog 0=>最快，數據最不安全，系統自決定刷新binlog的頻率；1=>最慢最安全，每個event刷新一次；N=>每N個事務刷一次binglog
long_query_time 建議設置小于0.5秒
open_files_limit & innodb_open_files 建議65535
max_connections 突發最大連接數的80%為宜，過大容易導致全部卡死
query_cache_size=0 & query_cache_type=0 關閉
-----
http://imysql.com/2016/03/11/mysql-faq-why-ibdata1-size-growup.shtml
ibdata1 文件儲存了什麼內容？
Data dictionary
Double write bufer
Insert buffer
Rollback segments
UNDO space
Foreign key constraint system tables
但當 啟用獨立表空間，ibdata1只存放系統數據
innodb_file_per_table = 1
-----
連接數過高
大部份的不正確做法：殺掉多餘的連接，加人連接數
正確做法：
．限制連接數： max_user_connections
-----
http://imysql.com/2016/01/13/mysql-optimization-case-howto-find-performance-bottleneck.shtml
檢查指令
vmstat -S m 1
sar -d 1 or w
iotop
mysqladmin pr|grep -v Sleep
-----
http://imysql.com/2015/12/30/mysql-faq-howto-stat-response-time-shtml.shtml
http://imysql.com/2015/11/04/mysql-faq-some-important-in-explain.shtml
explain
-----
http://imysql.com/category/%E6%95%B0%E6%8D%AE%E5%BA%93/page/3
将操作日志记入syslog并且发送到远程log server上，坚决不能只存储在本地；
设置密码安全策略，MD5之后再作为正式密码，32位长度的安全程度够高吧；
应用账号只赋予SELECT、UPDATE、INSERT权限，取消DELETE权限。把需要DELETE权限的逻辑改成用UPDATE实现，避免被物理删除；
需要真正删除时，交由DBA先备份后再物理删除；
-----
https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/
-----
Monitor MySQL Performance
https://www.tecmint.com/mysql-performance-monitoring/
- Mytop
https://www.packtpub.com/mapt/book/application_development/9781783981601/2/ch02lvl1sec25/mytop
http://15jb.net/mariadb%E6%80%A7%E8%83%BD%E8%AA%BF%E5%84%AA%E5%B7%A5%E5%85%B7mytop%E7%9A%84%E4%BD%BF%E7%94%A8%E8%A9%B3%E8%A7%A3
- innotop
- mysqlsla
https://www.packtpub.com/mapt/book/application_development/9781783981601/2/ch02lvl1sec27/mysqlsla
- mysqladmin status
- MySQL性能查询脚本
http://www.cnblogs.com/ivictor/p/5345217.html
==============SQL===============
SHOW GLOBAL VARIABLES where
Variable_Name in (
'table_open_cache',
'table_definition_cache',
'sort_buffer_size',
'join_buffer_size',
'interactive_timeout',
'wait_timeout',
'tmp_table_size',
'max_heap_table_size',
'read_buffer_size',
'read_rnd_buffer_size',
'bulk_insert_buffer_size',
'lock_wait_timeout',
'innodb_thread_concurrency',
'innodb_sync_spin_loops',
'innodb_spin_wait_delay',
'transaction_isolation',
'innodb_additional_mem_pool_size',
'innodb_buffer_pool_size',
'innodb_buffer_pool_instances',
'innodb_buffer_pool_load_at_startup',
'innodb_buffer_pool_dump_at_shutdown',
'innodb_flush_log_at_trx_commit',
'innodb_log_buffer_size',
'innodb_log_file_size',
'innodb_log_files_in_group',
'innodb_write_io_threads',
'innodb_read_io_threads',
'innodb_purge_threads',
'innodb_open_files',
'innodb_max_dirty_pages_pct',
'innodb_flush_method',
'innodb_lru_scan_depth',
'innodb_checksum_algorithm',
'innodb_lock_wait_timeout',
'innodb_rollback_on_timeout',
'innodb_print_all_deadlocks',
'innodb_file_per_table',
'innodb_online_alter_log_max_size',
'innodb_status_file',
'innodb_status_output',
'innodb_status_output_locks',
'innodb_stats_on_metadata',
'performance_schema',
'performance_schema_instrument',
'key_buffer_size',
'max_allowed_packet',
'thread_stack',
'thread_cache_size',
'query_cache_type',
'query_cache_limit',
'query_cache_size',
'server-id',
'log_bin',
'sync_binlog',
'expire_logs_days',
'binlog_cache_size',
'max_binlog_cache_size',
'max_binlog_size',
'binlog_format',
'relay_log_recovery',
'relay-log-purge'
)
