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的形式

本文永久更新链接地址：