Linux 内核参数优化
交换区
swappiness的值的大小对如何使用swap分区是有着很大的联系的。
swappiness=0的时候表示最大限度使用物理内存,然后才是 swap空间,
swappiness=100的时候表示积极的使用swap分区,并且把内存上的数据及时的搬运到swap空间里面。
linux的基本默认设置为60
也就是说,你的内存在使用到100-60=40%的时候,就开始出现有交换分区的使用。这样子会加大系统io,同时造的成大量页的换进换出,严重影响系统的性能,所以我们在操作系统层面,要尽可能使用内存。
将vm.swappiness设置为0-10
具体这样做:
1) 查看你的系统里面的swappiness
1 | cat /proc/sys/vm/swappiness |
不出意外的话,你应该看到是0
2) 修改swappiness值为10
1 | sudo sysctl vm.swappiness=10 |
但是这只是临时性的修改,在你重启系统后会恢复默认的值,所以,还要做一步:
1 | vi /etc/sysctl.conf |
在这个文档的最后加上这样一行:
1 | vm.swappiness = 10 |
然后保存,这样重启之后配置也不会失效。
激活设置
1 | sysctl -p |
脏数据
查看内存中的脏数据
1 | cat /proc/vmstat | egrep "dirty|writeback" |
nr_dirty 105
nr_writeback 0
nr_writeback_temp 0
nr_dirty_threshold 596122
nr_dirty_background_threshold 198707
说明内存中有105页脏数据要写到磁盘里。
将vm.dirty_background_ratio设置为5-10,将vm.dirty_ratio设置为它的两倍左右,以确保能持续将脏数据刷新到磁盘,避免瞬间I/O写,产生严重等待
1 | cat /proc/sys/vm/dirty_background_ratio |
Mysql配置优化
表名/编码/连接数/数据包大小
设置表名不区分大小写/字符编码/连接数
修改 /etc/my.cnf
1 | vim /etc/my.cnf |
添加以下的4行
1 | [mysqld] |
设置后保存 重新启动
1 | service mysqld restart |
备份数据
备份数据(防止数据异常)
1 | mkdir /data/mysqlDump/ |
还原数据
有两种方式还原,第一种是在MySQL命令行中,第二种是使用SHELL行完成还原
在系统命令行中,输入如下实现还原:
1
mysql -uroot -p123456 < /data/mysqlDump/xhkjedu_school.sql
在登录进入mysql系统中,通过source指令找到对应系统中的文件进行还原:
1
mysql> source /data/mysqlDump/mydb.sql
my.cnf参数优化
此优化主要针对innodb引擎
创建慢查询日志文件
1 | cd /var/log/ |
打开配置文件
1 | cp /etc/my.cnf /etc/my.cnf_bak |
找到[mysqld]
下面加上
1 | [mysqld] |
慢查询
查询Mysql版本
1 | select version(); |
或者
1 | mysql --version |
获取现在的配置
1 | show variables like '%slow%'; |
开启
1 | set global long_query_time=5; |
关闭
1 | set global slow_query_log=0; |
创建文件
1 | cd /var/log/ |
设置权限
1 | chmod a+w /var/log/mysql/mysql-slow.log |
打开配置文件
1 | vi /etc/my.cnf |
找到[mysqld]
下面加上
1 | slow_query_log = 1 |
其中
long_query_time = 3
中的3表示查询超过3秒才记录;
清空日志后慢查询就不会继续写入了,重启后才能继续写入
1 | set global slow_query_log=0; |
查询缓存
是否启用mysql查询缓存,可以通过2个参数:query_cache_type
和query_cache_size
,
其中任何一个参数设置为0都意味着关闭查询缓存功能。
query_cache_type
值域为:0 -– 不启用查询缓存;
值域为:1 -– 启用查询缓存,只要符合查询缓存的要求,客户端的查询语句和记录集斗可以缓存起来,所有其他客户端共享使用;
值域为:2 -– 启用查询缓存,只要查询语句中添加了参数:SQL_CACHE,且符合查询缓存的要求,客户端的查询语句和记录集,则可以缓存起来,共其他客户端共享使用;
query_cache_size
允许设置query_cache_size的值最小为40K,对于最大值则可以几乎认为无限制,实际生产环境的应用经验告诉我们,该值并不是越大, 查询缓存的命中率就越高,也不是对服务器负载下降贡献大,反而可能抵消其带来的好处,甚至增加服务器的负载,至于该如何设置,下面的章节讲述,推荐设置 为:64M;
query_cache_limit
限制查询缓存区最大能缓存的查询记录集,可以避免一个大的查询记录集占去大量的内存区域,而且往往小查询记录集是最有效的缓存记录集,默认设置为1M,建议修改为16k~1024k之间的值域,不过最重要的是根据自己应用的实际情况进行分析、预估来设置;
query_cache_min_res_unit
设置查询缓存分配内存的最小单位,要适当地设置此参数,可以做到为减少内存块的申请和分配次数,但是设置过大可能导致内存碎片数值上升。默认值为4K,建议设置为1k~16K
query_cache_wlock_invalidate
该参数主要涉及MyISAM引擎,若一个客户端对某表加了写锁,其他客户端发起的查询请求,且查询语句有对应的查询缓存记录,是否允许直接读取查询缓存的记录集信息,还是等待写锁的释放。默认设置为0,也即允许;
修改默认连接时长
1 | show global variables like 'wait_timeout'; |
设置成10小时;
1 | set global wait_timeout=36000; |
连接优化
常见的连接参数
1 | jdbc:mysql://localhost:3306/test?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true |
参数:
characterEncoding=utf8
字符编码utf8zeroDateTimeBehavior=convertToNull
时间传入了0000-00-00 自动转换为nullrewriteBatchedStatements=true
高性能的批量插入或更新(保证5.1.13以上版本的驱动)
表优化
关于库表的设计规范
推荐utf-8字符集,虽然有人说谈没有latin1快
固定字符串的列尽可能多用定长char,少用varchar
存储可变长度的字符串使用VARCHAR而不是CAHR—节省空间,因为固定长度的CHAR,而VARCHAR长度不固定(UTF8不愁此影响)
所有的InnoDB表都设计一个无业务的用途的自增列做主键
字段长度满足需求前提下,尽可能选择长度小的
字段属性尽量都加NOT NULL约束(空的字段不能走索引,查询速度慢)
对于某些文本字段,例如“省份”或者“性别”我们可以将他们定义为ENUM类型
尽可能不使用TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT*的时候读性能太差。
读取数据时,只选取所需要的列,不要每次都SELECT 避免产生严重的随机读问题,尤其是读到一些TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT的时候读性能太差
对一个VARCHAR(N)列创建索引时,通常取其50%(甚至更小)左右长度创建前缀索引就足以满足80%以上的查询需求了,没必要创建整列的全长度索引。
多用符合索引,少用多个独立索引,尤其是一些基础(Cardinality)太小(如果说:该列的唯一值总数少于255)的列就不要创建独立索引了。
索引
参见:Mysql优化-索引
SQL语句的优化
白名单机制一百度
1)项目开发,DBA参与,减少上线后的慢SQL数据
抓出慢SQL,配置my.cnf
1
2
3slow_query_log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 2按天轮询:slow-log.log
2)慢查询的日志分析工具——mysqlsla或pt-query-digest(推荐)
pt-quey-diges,mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfileter
3) 每天晚上0点定时分析慢查询,发到核心开发,DBA分析,及高级运维,CTO的邮箱里
DBA分析给出优化建议–>核心开发确认更新–>DBA线上操作处理
4) 定期使用pt-duplicate-key-checker检查并删除重复的索引
定期使用pt-index-usage工具检查并删除使用频率很低的索引
5)使用pt-online-schema-change来完成大表的ONLINE DDL需求
6)有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX
7)使用explain及set profile优化SQL语句
大的复杂的SQL语句拆分成多个小的SQL语句
数据库是存储数据的地方,但不是计算数据的地方
搜索功能,
like '%haha%'
一般不要用MySQL数据库比如这个语句在这个数据库查询很慢:
1
select a from tms where b like ‘%haha%’ order by time limit 100;
第一种优化方法(注意:这种方法只适用于haha开头的):
将haha字段和time字段加索引(联合索引还是普通索引自己看情况)
1
select a from news where b like ‘haha%’ order by time limit 100;
第二种优化方法(注意:这种方法只适用于mysql引擎是myisam的):
语句不变,将haha字段加为全文索引,time字段变为普通索引1
select a from news where b like ‘%haha%’ order by time limit 100;
第三种方法:
这是因为我必须用%haha%
这种方式,而且还不能更换数据库引擎的情况下。
我是把like的操作放到了java程序中来处理,一次取定量数据进行筛选,如果没取够,再取,再筛选……这样就避免了完全扫表了,只扫部分数据。成功解决。使用连接(JOIN)来代替子查询(Sub_Queries)
避免在整个表上使用count(*),它可能锁住整张表
多表联接查询时,关联字段类型尽量一致,并且都要有索引。
在WHERE子句中使用UNION代替子查询
多表连接查询时,把结果集小的表(注意,这里是指过滤后的结果集,不一样是全表数据量小的)作为驱动表
LIMIT 1
如果sql语句返回只可能只有一条,一定要添加LIMIT 1,来避免全部检索。
Show Profile【重点】
是什么
mysql提供可以用来分析当前会话中语句执行的资源消耗情况。
可以用于SQL的调优的测量,相比explain,show profile展示的数据更加详尽。
怎么用
1 | -- 查看是否开启 |
网站集成架构优化
服务器上跑多实例,2-4个(具体需要看服务器的硬件信息)
主从复制一主五从,采用mixed模式(混合或行模式),尽量不要跨机房同步(进程远程读本地写),(数据要一致,拉光纤,没有网络延迟)
定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异(重构)
业务拆分:搜索功能,like ‘%oldboy% ‘ 一般不要用MySQL数据库
业务拆分:某些业务应用使用nosql持久化存储,例如:memcached、redis、ttserver
例如粉丝关注,好友关系等
数据库前端必须要加cache,例如:redis,用户登录,商品查询
动态的数据库静态化,整个文件静态化,页面片段静态化
数据库集群与读写分离。一主多从,通过程序或dbproxy进行集群读写分离
单表超过800万,拆库拆表。人工拆表拆库(登录、商品、订单)
百度、阿里国内前三公司,会选择从库进行备份,对数据库进行分库分表
MySQL基础安全
启动程序700,属主和用户组为MySQL。
为MySQL超级用户root设置密码。
如果要求严格可以删除root用户,创建其他管理用户,例如admin。
登录时尽量不要在命令行暴露密码,备份脚本中如果有密码,给设置700,属主和密码组为mysql或root。
删除默认存在的test库。
初始删除无用的用户,只保留。
1
2| root | 127.0.0.1 |
| root | localhost |不要一个用户管理所有的库,尽量专库专用户(少量库)
清理mysql操作日志文件~/.mysql_history(权限600,可以不删)
禁止开发获得到web连接的密码,禁止开发连接操作生产对外的库
服务器禁止设置外网IP
防SQL注入(WEB)php.ini或web开发插件监控,waf控制
常见问题
错误1
The total number of locks exceeds the lock table size错误
1 | show variables like "innodb_buffer_pool_size"; |
默认为8M
修改 innodb_buffer_pool_size的值为3G:
3*1024*1024*1024
,不要忘记;号
1 | SET GLOBAL innodb_buffer_pool_size=67108864; |
长时间未访问 断开
mysql有一个连接超时时间的概念。。。查询此项目的数据库的连接超时时间为28800秒,即为8小时。。
1 | mysql> show global variables like 'wait_timeout'; |
于是不难得知,肯定是由于项目长时间没有请求数据库,数据库过了8小时和这个连接超时时间之后,就会断开连接。。
而我们的项目使用的是c3p0的连接池,,过了8小时后,连接池中的连接已经被mysql断开了,即连接失效。。
但是c3p0认为此连接却依然有效,此时当我们发请求请求数据库中的数据时,由于连接失效,并不能去连接数据库操纵数据,所以服务器会抛出一个500的错误
问题的原因已经找到,那我们该怎么解决呢??
其实解决起来很简单的。。
先说第一种办法吧,就是将数据库的连接超时时间设置大一点,
1 | msyql> set global wait_timeout=1814400; |
当然这种办法我并不推荐,,这个办法不太好,弊端太多了 比如占用数据库资源,关键是这种办法并不能彻底根治mysql连接断开这种情况
所以我推荐第二种办法:设置c3p0隔多少时间自动检测与数据库的连接,如果断开则自动重连
1 | <bean id="pooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> |