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’    

)