深度优化LNMP之MySQL

最新推荐文章于 2024-07-04 11:21:35 发布
最新推荐文章于 2024-07-04 11:21:35 发布
阅读量2.2k 收藏 2
点赞数

MySQL数据库优化框架体系

1.硬件层面优化 
2.操作系统层面优化 
3.MySQL数据库层面优化 
4.MySQL安全优化 
5.网站集群架构上的优化 
6.MySQL流程、制度控制优化

硬件层面优化

1、数据库物理机采购

　　CPU： 64位CPU，一台机器2-16颗CPU。至少2-4颗，L2（缓存）越大越好 
　　内存： 96-128G，MySQL 3-4个实例。32-64G，1-2实例 
　　硬盘：机械：选SAS，数量越多越好，转速越高越好15k 
性能：SSD（高并发） > SAS（普通业务线上） >SATA（线下） 
选SSD：使用SSD或者PCIe SSD设备，可提升上千倍的IOPS效率。 
随机IO：SAS单盘能力300IOPS SSD随机IO：单盘能力可达35000IOPS Flashcache HBA卡 
　　raid磁盘阵列： 4快盘：RAID0>RAID1(推荐)>RAID5(少用)>RAID1 
主库选择raid10，从库可选raid5/raid0/raid10，从库配置等于或大于主库 
　　网卡：使用多块网卡bond，以及buffer，tcp优化 
千兆网卡及千兆、万兆交换机 
提示： 
数据库属于IO密集型服务，硬件尽量不要使用虚拟化。 
Slave硬件要等于或大于Master的性能

2、企业案例：

百度：某部门IBM服务器为48核CPU，内存96GB，一台服务器跑3~4个实例： 
sina：服务器是DELL R510巨多，CPU是E5210，48GB内存，硬盘12*300G SAS，做RAID10

3、服务器硬件配置调整

(1)服务器BIOS调整： 
　　提升CPU效率参考设置： 
a.打开Perfirmance Per Watt Optimeized（DAPC）模式，发挥CPU最大性能，数据库通常需要高运算量 
b.打开CIE和C States等选项，目的也是为了提升CPU效率 
c. Memory Frequency（内存频率）选择Maximum Performance（最佳性能） 
d.内存设置菜单中，启动Node Interleaving，避免NUMA问题 
(2)阵列卡调整： 
a.购置阵列卡同时配备CACHE及BBU模块(机械盘) 
b.设置阵列写策略为WEB，甚至OFRCE WB （对数据安全要求高）（wb指raid卡的写策略：会写（write back）） 
c.严禁使用WT策略，并且关闭阵列预读策略

操作系统层面优化

1.操作系统及MySQL实例选择

1.一定要选择x86_64系统，推荐使用CentOS6.8 linux，关闭NUMA特性 
2.将操作系统和数据分开，不仅仅是逻辑上，还包括物理上 
3.避免使用Swap交换分区 
4.避免使用软件磁盘阵列 
5.避免使用LVM逻辑卷 
6.删除服务器上未使用的安装包和守护进程

2.文件系统层优化

(1)调整磁盘Cache mode

 
 
  1. 启用WCE=1(Write Cache Enable),RCD=0(Read Cache Disable)模式
  2. 命令：sdparm -s WCE=1,RCD=0 -S /dev/sdb

(2)采用Linux I/O scheduler算法deadline 
deadline调度参数 
　　对于Centos Linux建议 read_expire = 1/2 write_expire

 
 
  1. echo 500 > /sys/block/sdb/queue/iosched/read_expire
  2. echo 1000 > /sys/block/sdb/queue/iosched/write_expire

Linux I/O调度方法 Linux deadline io 调度算法

(3)采用xfs文件系统 
　　业务量不是很大也可采用ext4，业务量很大，推荐使用xfs：调整XFS文件系统日志和缓冲变量 
XFS高性能设置 
(4)mount挂载文件系统 
　　增加：async,noatime,nodiratime,nobarrier等

noatime

　　访问文件时不更新inode的时间戳，高并发环境下，推线显示应用该选项，可以提高系统I/O性能

async

　　写入时数据会先写到内存缓冲区,只到硬盘有空档才会写入磁盘,这样可以提升写入效率！风险为若服务器宕机或不正常，会损失缓冲区中未写入磁盘的数据 解决办法：服务器主板电池或加UPS不间断电源

nodiratime

　　不更新系统上的directory inode时间戳，高并发环境，推荐显示该应用，可以提高系统I/O性能 
　　 
nobarrier

　　不使用raid卡上电池 
(5)Linux 内核参数优化 
　　1.将vm，swappiness设置为0-10 
　　2.将vm，dirty_background_ratio设置为5-10，将vm，dirty_ratio设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间I/O写，产生严重等待 
　　3.优化TCP协议栈

 
 
  1. #减少TIME_WAIT，提高TCP效率
  2. net.ipv4.tcp_tw_recyle=1
  3. net.ipv4.tcp_tw_reuse=1
  4. #减少处于FIN-WAIT-2连接状态的时间，使系统可以处理更多的连接
  5. net.ipv4.tcp_fin_timeout=2
  6. #减少TCP KeepAlived连接侦测的时间，使系统可以处理更多的连接。
  7. net.ipv4.tcp_keepalived_time=600
  8. #提高系统支持的最大SYN半连接数（默认1024）
  9. net.ipv4.tcp_max_syn_backlog = 16384
  10. #减少系统SYN连接重试次数(默认5)
  11. net,ipv4.tcp_synack_retries = 1
  12. net.ipv4.tcp_sync_retries = 1
  13. #在内核放弃建立的连接之前发送SYN包的数量
  14. net.ipv4.ip_local_prot_range = 4500 65535
  15. #允许系统打开的端口范围

4.网络优化 
#优化系统套接字缓冲区

 
 
  1. #Increase TCP max buffer size
  2. net.core.rmem_max=16777216 #最大socket读buffer
  3. net.core.wmem_max=16777216 #最大socket写buffer
  4. net.core.wmem_default = 8388608 #该文件指定了接收套接字缓冲区大小的缺省值(以字节为单位)
  5. net.core.rmem_default = 8388608

#优化TCP接收/发送缓冲区

 
 
  1. # Increase Linux autotuning TCP buffer limits
  2. net.ipv4.tcp_rmem=4096 87380 16777216
  3. net.ipv4.tcp_wmem=4096 65536 16777216
  4. net.ipv4.tcp_mem = 94500000 915000000 927000000

#优化网络设备接收队列

 
 
  1. net.core.netdev_max_backlog=3000

5.其他优化

 
 
  1. net.ipv4.tcp_timestamps = 0 
  2. net.ipv4.tcp_max_orphans = 3276800
  3. net.ipv4.tcp_max_tw_buckets = 360000

提示：面试的时候说框架，然后说一两个小的优化参数即可 
更多内核可以参考“跟老男孩学运维书的第三章”以及我们的博客，近期将会更新

MySQL数据库层面优化

my.cnf参数优化

此优化主要针对innodb引擎

 
 
  1. 如果采用MyISAM引擎，需要key_buffer_size加大。
  2. 强烈推荐采用innodb引擎，default-storage-engine=Innodb
  3. 调整innodb_buffer_pool_size大小，考虑设置为物理内存的50%-60%左右
  4. 根据实际需要设置inno_flush_log_at_trx_commit，sync_binlog的值。如果要需要数据不能丢失，那么两个都设为1.如果允许丢失大一点数据，则可分别设为2和0，在slave上可设为0
  5. 设置innodb_file_per_table = 1，使用独立表空间
  6. 设置innodb_data_file_path = ibdata1:1G:autoextend,不要使用默认的10%
  7. 设置innodb_log_file_size=256M,设置innodb_log_files_in_group=2，基本可满足90%以上的场景；
  8. 不要将innodb_log_file_size参数设置太大，这样可以更快同时又更多的磁盘空间，丢掉多的日志通常是好的，在数据库崩溃后可以降低恢复数据库的事件
  9. 设置long_query_time = 1记录那些执行较慢的SQL，用于后续的分析排查；
  10. 根据业务实际需要，适当调整max_connection（最大连接数）max_connection_error（最大错误数，建议设置为10万以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache这几个参数则可设为约10倍于max_connection的大小；）不要设置太大，会将数据库撑爆
  11. tmp_table_szie、max_heap_table_size、sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等都是每个连接session分配的，因此不能设置过大
  12. 建议关闭query cache功能或降低设置不要超过512M

更多内核参数：my-innodb-heavy-4G.cnf 配置文件参数介绍 
MySQL工具mysqlreport 我们可以使用工具来分析MySQL的性能 
如何才能做到网站高并发访问? 生产环境中对于防范DDOS攻击的讨论 
面试可能会问到DOOS 攻击防护

关于库表的设计规范

 
 
  1. 推荐utf-8字符集，虽然有人说谈没有latin1快
  2. 固定字符串的列尽可能多用定长char，少用varchar
  3.     存储可变长度的字符串使用VARCHAR而不是CAHR---节省空间，因为固定长度的CHAR，而VARCHAR长度不固定（UTF8不愁此影响）
  4. 所有的InnoDB表都设计一个无业务的用途的自增列做主键
  5. 字段长度满足需求前提下，尽可能选择长度小的
  6. 字段属性尽量都加NOT NULL约束
  7.     对于某些文本字段，例如“省份”或者“性别”我们可以将他们定义为ENUM类型
  8. 尽可能不使用TEXT/BLOB类型，确实需要的话，建议拆分到子表中，不要和主表放在一起，避免SELECT *的时候读性能太差。
  9. 读取数据时，只选取所需要的列，不要每次都SELECT * 避免产生严重的随机读问题，尤其是读到一些TEXT/BLOB类型，确实需要的话，建议拆分到子表中，不要和主表放在一起，避免SELECT *的时候读性能太差
  10. 对一个VARCHAR（N）列创建索引时，通常取其50%（甚至更小）左右长度创建前缀索引就足以满足80%以上的查询需求了，没必要创建整列的全长度索引。
  11. 多用符合索引，少用多个独立索引，尤其是一些基础（Cardinality）太小（如果说：该列的唯一值总数少于255）的列就不要创建独立索引了。

SQL语句的优化

索引优化 
1)白名单机制一百度，项目开发啊，DBA参与，减少上线后的慢SQL数据 
抓出慢SQL,配置my.cnf

 
 
  1. long_query_time = 2
  2. log-slow-queries=/data/3306/slow-log.log
  3. log_queries_not_using_indexs
  4. 按天轮询：slow-log.log

2)慢查询的日志分析工具——mysqlsla或pt-query-digest（推荐）

 
 
  1. 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语句 
　　子查询，JOIN连表查询，某个表4000万条记录 
数据库是存储数据的地方，但不是计算数据的地方 
　　对数据计算，应用类处理，都要拿到前端应用解决。禁止在数据库上处理 
搜索功能，like ‘%oldboy%’ 一般不要用MySQL数据库 
　　使用连接(JOIN)来代替子查询（Sub_Queries） 
　　避免在整个表上使用cout(*)，它可能锁住整张表 
　　多表联接查询时，关联字段类型尽量一致，并且都要有索引。 
　　在WHERE子句中使用UNION代替子查询 
　　多表连接查询时，把结果集小的表（注意，这里是指过滤后的结果集，不一样是全表数据量小的）作为驱动表

爬虫获取数据的过程

1.png-81.8kB
2.png-40.2kB
3.png-179.4kB

**网站集群架构上的优化

1.服务器上跑多实例，2-4个（具体需要看服务器的硬件信息）
2.主从复制一主无从，采用mixed模式，尽量不要跨机房同步（进程远程读本地写）
3.定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异
4.业务拆分：搜索功能，like '%oldboy% ' 一般不要用MySQL数据库
5.业务拆分：某些业务应用使用nosql持久化存储，例如：memcached、redis、ttserver
  例如粉丝关注，好友关系等
6.数据库前端必须要加cache，例如：memcached，用户登录，商品查询
7.动态的数据库静态化，整个文件静态化，页面片段静态化
8.数据库集群与读写分离。一主多从，通过程序或dbproxy进行集群读写分离
9.单表超过800万，拆库拆表。人工拆表拆库（登录、商品、订单）
10.百度、阿里国内前三公司，会选择从库进行备份，对数据库进行分库分表

MySQL 流程、制度控制优化

任何一次人为数据库记录的更新，都要走一个流程：

a.人的流程：开发-->核心开发-->运维或DBA
b.测试流程：内网测试-->IDC测试-->线上执行
c.客户端管理，phpmyadmin

MySQL基础安全

1.启动程序700，属主和用户组为MySQL
2.为MySQL超级用户root设置密码
3.如果要求严格可以删除root用户，创建其他管理用户，例如admin
4.登录时尽量不要在命令行暴露密码，备份脚本中如果有密码，给设置700，属主和密码组为mysql或root
5.删除默认存在的test库
6.初始删除无用的用户，只保留
| root | 127.0.0.1 |
| root | localhost |
7.不要一个用户管理所有的库，尽量专库专用户
8.清理mysql操作日志文件~/.mysql_history（权限600，可以不删）
9.禁止开发获得到web连接的密码，禁止开发连接操作生产对外的库
10.phpmyadmin安全
11.服务器禁止设置外网IP
12.防SQL注入（WEB）php.ini或web开发插件监控，waf控制
linuxlsq
关注
[网络安全自学篇] 九十一.阿里云搭建LNMP环境及实现PHP自定义网站IP访问 (1)
杨秀璋的专栏
07-25 6337
这是作者网络安全自学教程系列，主要是关于安全工具和实践操作的在线笔记，特分享出来与博友们学习，希望您喜欢，一起进步。本文主要分享作阿里云搭建LNMP环境及实现PHP自定义网站IP访问，同时详细介绍走过的坑，还发了朋友圈求助大家，再次感谢。本来想重新设置一个专栏分享基于阿里云的PHP网站搭建，但考虑到搞安全的也会面临各种靶场及个人网站搭建的情况，所以将这篇文章放置在“网络安全自学篇”中，后续还会分享PHP如何记录后台IP及Python定位经纬度等知识，希望对您有所帮助~
OpenResty实现LNMP的缓存前移（到达nginx前端层面）
weixin_42566251的博客
08-05 142
前言 我们都知道Nginx有很多的特性和好处，但是在Nginx上开发成了一个难题，Nginx模块需要用C开发，而且必须符合一系列复杂的规则，最重要的用C开发模块必须要熟悉Nginx的源代码，使得开发者对其望而生畏。为了开发人员方便，所以接下来我们要介绍一种整合了Nginx和lua的框架，那就是OpenResty，它帮我们实现了可以用lua的规范开发，实现各种业务，并且帮我们弄清楚各个模块的编译顺...
lnmp架构之mysql部署
cjzcc1996的博客
06-26 787
解压源码包：编译mysql用的是cmake(mysql8需要cmake3.5以上)：我们用的是mysql5.7的，所以我们直接安装源生的cmake即可： 编译还需要安装gcc，gcc-c++： 编译安装mysql还得靠官方文档啊： MySQL :: MySQL 5.7 Reference Manual :: 2.9.3 MySQL Layout for Source Installation编译选项参数：MySQL :: MySQL 5.7 Reference Manual :: 2.9.7 MySQL S
“LNMP环境搭建实战指南：从零开始配置CentOS 7下的Nginx、MySQL与PHP“
最新发布
Eucalyptus's blog
07-04 921
在当今快速发展的互联网时代，高性能、高效率的网站部署环境成为了开发者和运维人员不可或缺的工具。LNMP（Linux, Nginx, MySQL, PHP）栈，作为一种流行且强大的Web服务解决方案，凭借其卓越的并发处理能力、资源高效利用以及灵活的可扩展性，成为众多企业和开发者的首选平台。本实战指南旨在手把手教你如何在CentOS 7操作系统下从零开始搭建LNMP环境。
lnmp mysql 优化_LNMP优化
weixin_28893221的博客
01-27 262
LNMP优化LNMP优化从系统安全，系统资源占用率，及web服务并发负载这三个方面体现，并且主要体现在web服务并发负载这一方面。1:首先进行linux优化加固Linux优化加固最好的办法就是提升硬件配置，比如提高CPU运算能力，增大内存容量，提高硬盘吞吐率等。本文谈的Linux优化加固是在不提升硬件配置的情况下，通过优化内核配置，从而提高linux服务效率，从三个方面进行：安全加固、内核调优、优...
lnmp mysql 优化_全面优化—配置高性能LNMP架构
weixin_34439450的博客
02-01 247
本文不涉及大型web架构，旨在单台服务器上通过各种手段最大化的优化和加速lnmp，适合单台服务器或者vps站长们使用。笔者水平有限，要是有更好的优化手段请留言，我会不断完善此架构。笔者罗列以下主要的方式进行优化，一些小细节的优化在下面的配置中再具体说明：(有更好的招数欢迎大家留言)1.利用fastcgi_cache缓存，减少nginx与PHP交互，减轻php和数据库(MySQL)的压力2.为zen...
linux运维笔记：LNMP之深度优化MySQL
一个好知识的传播者
10-15 310
9）读取数据时，只选取所需要的列，不要每次都SELECT * 避免产生严重的随机读问题，尤其是读到一些TEXT/BLOB类型，确实需要的话，建议拆分到子表中，不要和主表放在一起，避免SELECT *的时候读性能太差。2.将vm，dirty_background_ratio设置为5-10，将vm，dirty_ratio设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间I/O写，产生严重等待。innodb_additional_mem_pool_size #小于2G内存的机器，推荐值是20M。
LNMP环境搭建之Mysql篇
汪同学的博客
09-03 881
在搭建mysql数据库的时候，一开始，我是直接在Centos服务器上通过yum -y install mysql 搭建，结果发现搭建的是mariadb数据库，不是mysql数据库。后来，通过查询了解到，从最新版本的linux系统开始，默认的数据库是 Mariadb而不是mysql了。 所以，如果我们想在Centos 7 环境中搭建mysql数据库的话，需要通过oracle官网下载mysql数据库的...
基于LNMP的WordPress搭建与速度优化实践
java060515的专栏
11-07 670
本文是滴滴云开源框架教程系列文章的一篇。 前言 WordPress是一款非常流行的内容管理系统（CMS），基于MySQL和PHP开发而成。互联网用户可以利用其快速搭建个人博客。 本文将演示：如何基于LNMP（Linux, Nginx, MySQL, and PHP）快速搭建一套WordPress，并尝试优化它的访问速度。 准备 本文基于Linux发行版本Centos7.x配合流行的包管理工具yu...
Nginx安装及深度优化（3）
看清所以看轻
01-03 392
博文大纲： 一、编译安装前的优化 二、Nginx配置项优化 1、调整Nginx运行工作进程个数 2、Nginx运行CPU亲和力 3、Nginx事件处理模型 4、开启高效传输模式 5、连接超时时间 6、fastcgi优化 7、gzip调优 8、expires缓存调优 9、配置防盗链 10、内核参数优化 11、关于系统连接数的优化 三、验证上述优化及Nginx服务器压力测试 1、测试防盗链功能 2、压...
基于PHP与MySQL的农产品追溯购买一体化平台建设.pdf
10-10
系统架构采用LNMP（Linux + Nginx + MySQL + PHP）模式，这种架构在资源消耗低、并发性能高的特点下，能有效支持高流量网站的需求。前端采用Bootstrap框架，确保页面设计简洁、直观，增加动态元素，实现控件模块化，...
lnmp架构之Mysql
qq_34912956的博客
05-16 434
一.Mysql的安装
mysql 性能提高配置 修改 my.conf
freewebsys的专栏
03-22 341
##################################################### mysql 性能提高配置 Begin##################################################### skip-name-resolve #禁止MySQL对外部连接进行DNS解析!!所有远程主机连接授权都要使用IP地址方式 back_lo...
lnmp mysql 优化_超详解的LNMP搭建并优化
weixin_35682097的博客
01-27 91
环境为Centos7 nginx1.14 mysql5.7 php7一，安装Nginx (yum装，快速)yum install nginx二，优化nginx (方便后期工作，如果纯为测试的话，不用)1, cat nginx.confuser www;worker_processes auto;error_log /var/log/nginx/error.log;pid /run/nginx.pi...
LNMP中安装mysql服务
weixin_45059041的博客
07-06 207
下载地址：www.mysql.com，本篇博客使用的是5.7.20版本 首先 下载依赖包 yum -y install ncurses ncurses-devel bison cmake 然后创建一个用户来管理mysql useradd -s /sbin/nologin mysql 然后的然后解压mysql包并进入目录执行 cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ //指定安装路径 -DMYSQL_U...
lnmp mysql_lnmp环境搭建之mysql篇
weixin_31623707的博客
01-18 173
本篇文章是在虚拟机上实现的，使用putty工具远程连接服务器，所以会感觉一片漆黑。。服务器版本，使用的是最小化安装：cat /etc/redhat-releaseCentOS Linux release 7.3.1611 (Core)注意：mysql5.7.18后没有my.cnf因为Linux安装完后就会生成/etc/my.cnf文件，无论哪种方式安装前最好先把/etc/my.cnf先备份一下，这...
搭建LNMP的测试环境之MySQL
weixin_45638534的博客
05-07 152
1、离线安装MySQL：通过命令wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm下载安装包，通过命令rpm -ivh mysql-community-release-el7-5.noarch.rpm 进行安装MySQL应用程序 2、安装MySQL服务器端：通过命令yum install mysql-server -y进行安装 3、运行：通过命令systemctl start mysqld运行，可以通过systemctl
lnmp之mysql
dwly12345的博客
07-24 310
 lnmp指的是：linux系统+nginx轻量网站服务+mysql数据库+php网页 lamp指的是：linux系统+apache网站服务+MySQL数据库+PHP网页 #####################################mysql############################### 一，mysql安装 1. 安装包：mysql-boost
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值