MySQL效能查看和配置整理大全

MySQL效能查看和配置整理大全

MySQL資料庫效能的查看：
Show proceelist  查看資料庫連接的狀態和串連的總數
show globalstatus like 'Max_used_connections';  查看資料庫最大的響應串連，最大串連數在10%以下的mysql的串連數偏高
Show processlist  查看串連數狀態
 showglobal status like 'Thread%';查看進程使用方式
show globalstatus like 'qcache%';  查看緩衝的使用方式
show statuslike ‘key_read%’  比例key_reads/key_read_requests應該儘可能的低，至少是1:100或者是1:1000
show statuslike “open%”  查看開啟的資料庫的表

目前生產線上的資料庫普遍佔用記憶體很大，主要的原因是因為資料庫設定檔相關參數配置不當的原因，導致讀取的緩衝空間很大：
相關的參數可以參考一下的配置：
資料庫的配置的一些重要參數：
[mysqld]
Skip-external-locking  //跳過外部鎖定，用於多進程下的myisam的資料表進行鎖定
Key_buffer_size=384M//指定索引的緩衝區大小，4G記憶體的索引一般為384M或者512M，對於該值得配置一般使用查看狀態值key_read_request來查看是否配置合理，比例在1:100和1:1000左右。
table_open_cache =2048  //mysql開啟資料庫表的時候都會讀取一些資料到table_open_cachede中，當mysql在這個資料庫中找不到資料的時候才會去資料庫讀取，可以減少檔案的開啟關閉次數，配置是否合理，對於該數的配置的一般查看開啟的表的數量。使用show global status like ‘open%_tables’查看開啟表的數量在進行設定，一般建議為open_tables/opened_tables>=0.85左右。
read_buffer_size= 2M  //讀查詢用到的緩衝區的大小，這個參數只用於myisam表有效
read_rnd_buffer_size= 8M  //對所有的儲存引擎都有限
myisam_sort_buffer_size= 64M // 重建索引的最大檔案的大小
thread_concurrency= 4  //這個值得大小一般是cpu的核心數的雙倍，這個值屬於重要設定物件5.5以上版本該值無效
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir          = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages    = en_US
skip-name-resolve
lower_case_table_names  // #忽略表名大小寫
connect_timeout        = 15      //預設就好
wait_timeout            = 600      //預設就好
max_allowed_packet      = 16M    //預設為16M，但遇到大欄位提示資訊包過大的問題的時候再修改，一般增加為32M
thread_cache_size      = 128    //緩衝中儲存線程的數量，8G的記憶體一般設定為64或者128左右，3G為32，2G的為16
sort_buffer_size        = 8M      //排序緩衝  100串連的時候會消耗0.8G的記憶體
bulk_insert_buffer_size = 16M      //批量插入的緩衝 預設為8M
tmp_table_size          = 256 M  //預設為32M
max_heap_table_size    = 256M
query_cache_limit    = 0 #不使用資料緩衝，有需要一般使用memcache外部緩衝，預設為1M
query_cache_size    = 0  #一般用於mysqlisam的最佳化，建議關閉該功能
log_bin                = /var/log/mysql/mariadb-bin        //注意log-bin存放的空間大小
log_bin_index          = /var/log/mysql/mariadb-bin.index 
expire_logs_days        = 10        //存放log-bin的時間周期
max_binlog_size        = 100M      //最大的binlog大小，不用設定也可以   
innodb_buffer_pool_size = 16384M    //一般配置為系統記憶體的50%-80%
innodb_log_buffer_size  = 32M    //確定日記檔案所用的記憶體
innodb_file_per_table  = 1
innodb_open_files      = 400
innodb_io_capacity      = 400
innodb_flush_method    = O_DIRECT
innodb_log_file_size    = 512M  //確定資料檔案的大小，大的設定可以提高效能但是也會增加恢複故障資料庫的所需要的時間
 
在innode的配置中比較重要的兩個參數是innodb_buffer_pool_size和innodb_log_file_size
在mysql的官方文檔中一般建議把innodb_buffer_pool_sixe配置為記憶體的50%到80%左右
show status like 'Innodb_buffer_pool_%'; 查看innode的效能
 
 
[mysqldump]
quick //在匯出巨大的表的時候需要開啟、一般20G以後備份的就不要使用mydqldump的形式

本文永久更新連結地址：