转载

2016年04月03日

MySQL的优化点总结---一些散落的点

2016-04-03 21:16:15 | 分类：数据库 | 标签： | 举报 | 字号大中小订阅

用微信 “扫一扫”

将文章分享到朋友圈。

用易信 “扫一扫”

将文章分享到朋友圈。

下载LOFTER 我的照片书 |

非原创，源自源杨朋友

----------------------------------------------

MySQL启用SSD存储

有时OS读写慢会降低MySQL服务器的性能，尤其是OS与MySQL使用同一磁盘时。故最好是让MySQL使用单独的磁盘，能使用SSD更好。

既可以让整个MySQL数据都在SSD上，也可以对某些数据库使用SSD。? innodb_page_size = 4K

innodb_flush_neighbors = 0

读写分离

用Replication建立，1个Master，1到多个Slave

写请求都发送到Master，读请求都发送到Slave

可以使用MySQL JDBC配置。

合理的优化系统的参数

更改文件句柄 ulimit –n 默认1024 太小

进程数限制 ulimit –u 不同版本不一样

禁掉NUMA numctl –interleave=all

选择合适的内存分配算法

默认的内存分配就是c的malloc 现在也出现许多优化的内存分配算法:

jemalloc and tcmalloc

选择良好的文件系统

推荐XFS, Ext4,如果还在使用ext2,ext3的同学请尽快升级别。推荐XFS，这个也是今后一段时间Linux会支持一个文件系统。

优化挂载文件系统的参数

挂载XFS参数：(rw, noatime,nodiratime,nobarrier)

挂载ext4参数：(rw,noatime,nodiratime,nobarrier,data=ordered)

选择适合的IO调度

正常请下请使用deadline 默认是noop

echo dealine >/sys/block/{DEV-NAME}/queue/scheduler

合理选择LOG刷新机制

Redo Logs:

– innodb_flush_log_at_trx_commit = 1 // 最安全

– innodb_flush_log_at_trx_commit = 2 // 较好性能

– innodb_flush_log_at_trx_commit = 0 // 最好的情能

binlog :

binlog_sync = 1 需要group commit支持，如果没这个功能可以考虑binlog_sync=0来获得较佳性能。

innodb_flush_method = O_DIRECT

设置较大的Redo log

以前Percona 5.5和官方MySQL 5.5比拼性能时，胜出的一个Tips就是分配了超过4G的Redo log ,而官方MySQL5.5 redo log不能超过4G. 从 MySQL 5.6后可以超过4G了，通常建Redo log加起来要超过500M。可以通过观查redo log产生量，分配Redo log大于一小时的量即可

连接请求的变量：max_connections

MySQL的最大连接数，增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

数值过小会经常出现ERROR 1040: Too many connections错误，可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。

max_used_connections / max_connections * 100% （理想值≈ 85%）如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

连接请求的变量：back_logMySQL能暂存的连接数量

当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到 max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。当观察你主机进程列表（mysql> show full processlist），发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log 的值了。默认数值是50，可调优为128，对系统设置范围为小于512的整数。3) interactive_timeout一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE 选项的客户。默认数值是28800，可调优为7200。

全局缓冲：key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值 Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。

key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，设置在1/1000左右较好

默认配置数值是8388600(8M)，主机有4GB内存，可以调优值为268435456(256MB)。

每个连接的缓冲：record_buffer_size

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。

默认数值是131072(128K)，可改为16773120 (16M)

每个连接的缓冲：read_rnd_buffer_size

随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

一般可设置为16M

每个排序的缓冲：sort_buffer_size

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。

默认数值是2097144(2M)，可改为16777208 (16M)。

每个连接的缓冲：join_buffer_size