万维网网站服务的名称,怎么做网站扫码支付,百度最怕哪个投诉电话,市北建筑建网站哪家好作者#xff1a;惨绿少年https://www.cnblogs.com/clsn/p/8214048.html在进行MySQL的优化之前#xff0c;必须要了解的就是MySQL的查询过程#xff0c;很多查询优化工作实际上就是遵循一些原则#xff0c;让MySQL的优化器能够按照预想的合理方式运行而已。图-MySQL查询过程… 作者惨绿少年https://www.cnblogs.com/clsn/p/8214048.html在进行MySQL的优化之前必须要了解的就是MySQL的查询过程很多查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。图-MySQL查询过程一、优化的哲学注优化有风险涉足需谨慎11、优化可能带来的问题优化不总是对一个单纯的环境进行还很可能是一个复杂的已投产的系统优化手段本来就有很大的风险只不过你没能力意识到和预见到任何的技术可以解决一个问题但必然存在带来一个问题的风险对于优化来说解决问题而带来的问题控制在可接受的范围内才是有成果保持现状或出现更差的情况都是失败2优化的需求稳定性和业务可持续性通常比性能更重要优化不可避免涉及到变更变更就有风险优化使性能变好维持和变差是等概率事件切记优化应该是各部门协同共同参与的工作任何单一部门都不能对数据库进行优化所以优化工作是由业务需要驱使的3优化由谁参与在进行数据库优化时应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等业务相关人员共同参与。 二、优化思路1优化什么在数据库优化上有两个主要方面即安全与性能。安全-数据可持续性性能-数据的高性能访问。2优化的范围有哪些存储、主机和操作系统方面主机架构稳定性I/O规划及配置Swap交换分区OS内核参数和网络问题。应用程序方面应用程序稳定性SQL语句性能串行访问资源性能欠佳会话管理这个应用适不适合用MySQL。数据库优化方面内存数据库结构物理逻辑实例配置。说明不管是设计系统、定位问题还是优化都可以按照这个顺序执行。3优化维度数据库优化维度有四个硬件、系统配置、数据库表结构、SQL及索引。优化选择优化成本硬件系统配置数据库表结构SQL及索引。优化效果硬件系统配置数据库表结构SQL及索引。三、优化工具有啥1数据库层面检查问题常用工具1MySQL2msyqladminMySQL客户端可进行管理操作3mysqlshow功能强大的查看shell命令4show [SESSION | GLOBAL] variables查看数据库参数信息5SHOW [SESSION | GLOBAL] STATUS查看数据库的状态信息6information_schema获取元数据的方法7SHOW ENGINE INNODB STATUSInnodb引擎的所有状态8SHOW PROCESSLIST查看当前所有连接session状态9explain获取查询语句的执行计划10show index查看表的索引信息11slow-log记录慢查询语句12mysqldumpslow分析slowlog文件的不常用但好用的工具1Zabbix监控主机、系统、数据库部署zabbix监控平台2pt-query-digest分析慢日志3MySQL slap分析慢日志4sysbench压力测试工具5MySQL profiling统计数据库整体状态工具 6Performance SchemaMySQL性能状态统计的数据7workbench管理、备份、监控、分析、优化工具比较费资源 关于Zabbix参考http://www.cnblogs.com/clsn/p/7885990.html2数据库层面问题解决思路一般应急调优的思路针对突然的业务办理卡顿无法进行正常的业务处理需要立马解决的场景。1show processlist2explain select id ,name from stu where nameclsn; # ALL id name age sexselect id,name from stu where id2-1 函数 结果集30show index from table3通过执行计划判断索引问题有没有、合不合理或者语句本身问题4show status like %lock%; # 查询锁状态kill SESSION_ID; # 杀掉有问题的session。常规调优思路针对业务周期性的卡顿例如在每天10-11点业务特别慢但是还能够使用过了这段时间就好了。1查看slowlog分析slowlog分析出查询慢的语句2按照一定优先级一个一个排查所有慢语句3分析top SQL进行explain调试查看语句执行时间4调整索引或语句本身。3系统层面Cpu方面vmstat、sar top、htop、nmon、mpstat内存free、ps-auxIO设备磁盘、网络iostat、ss、netstat、iptraf、iftop、lsofvmstat命令说明1Procsr显示有多少进程正在等待CPU时间。b显示处于不可中断的休眠的进程数量。在等待I/O。2Memoryswpd显示被交换到磁盘的数据块的数量。未被使用的数据块用户缓冲数据块用于操作系统的数据块的数量。3Swap操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1和s0最好是0。4Io每秒从设备中读入b1的写入到设备b0的数据块的数量。反映了磁盘I/O。5System显示了每秒发生中断的数量in和上下文交换cs的数量。6Cpu显示用于运行用户代码系统代码空闲等待I/O的Cpu时间。iostat命令说明实例命令iostat -dk 1 5 iostat -d -k -x 5 查看设备使用率%util和响应时间await1tps该设备每秒的传输次数。“一次传输”意思是“一次I/O请求”。多个逻辑请求可能会被合并为“一次I/O请求”。2iops 硬件出厂的时候厂家定义的一个每秒最大的IO次数3一次传输请求的大小是未知的。4kB_read/s每秒从设备drive expressed读取的数据量5KB_wrtn/s每秒向设备drive expressed写入的数据量6kB_read读取的总数据量7kB_wrtn写入的总数量数据量这些单位都为Kilobytes。4系统层面问题解决办法你认为到底负载高好还是低好呢在实际的生产中一般认为Cpu只要不超过90%都没什么问题。当然不排除下面这些特殊情况Cpu负载高IO负载低1内存不够2磁盘性能差3SQL问题---去数据库层进一步排查SQL 问题4IO出问题了磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高5tps过高大量的小数据IO、大量的全表扫描。IO负载高Cpu负载低1大量小的IO写操作autocommit产生大量小IOIO/PS磁盘的一个定值硬件出厂的时候厂家定义的一个每秒最大的IO次数。2大量大的IO 写操作SQL问题的几率比较大IO和cpu负载都很高硬件不够了或SQL存在问题。四、基础优化1优化思路定位问题点吮吸硬件--系统--应用--数据库--架构高可用、读写分离、分库分表。处理方向明确优化目标、性能和安全的折中、防患未然。2硬件优化主机方面根据数据库类型主机CPU选择、内存容量选择、磁盘选择1平衡内存和磁盘资源2随机的I/O和顺序的I/O3主机 RAID卡的BBUBattery Backup Unit关闭。CPU的选择CPU的两个关键因素核数、主频根据不同的业务类型进行选择1CPU密集型计算比较多OLTP - 主频很高的cpu、核数还要多2IO密集型查询比较OLAP - 核数要多主频不一定高的内存的选择OLAP类型数据库需要更多内存和数据获取量级有关。OLTP类型数据一般内存是Cpu核心数量的2倍到4倍没有最佳实践。存储方面1根据存储数据种类的不同选择不同的存储设备2配置合理的RAID级别raid5、raid10、热备盘3对与操作系统来讲不需要太特殊的选择最好做好冗余raid1ssd、sas、sata。4raid卡 主机raid卡选择 实现操作系统磁盘的冗余raid1 平衡内存和磁盘资源 随机的I/O和顺序的I/O 主机raid卡的BBUBattery Backup Unit要关闭。网络设备方面使用流量支持更高的网络设备交换机、路由器、网线、网卡、HBA卡注意以上这些规划应该在初始设计系统时就应该考虑好。3服务器硬件优化1物理状态灯2自带管理设备远程控制卡FENCE设备ipmi ilo idarc、开关机、硬件监控。3第三方的监控软件、设备snmp、agent对物理设施进行监控。4存储设备自带的监控平台。EMC2hp收购了、 日立hds、IBM低端OEM hds、高端存储是自己技术华为存储。4系统优化Cpu基本不需要调整在硬件选择方面下功夫即可。内存基本不需要调整在硬件选择方面下功夫即可。SWAPMySQL尽量避免使用swap。阿里云的服务器中默认swap为0。IO raid、no lvm、ext4或xfs、ssd、IO调度策略。Swap调整(不使用swap分区)/proc/sys/vm/swappiness的内容改成0临时/etc/sysctl. conf上添加vm.swappiness0永久这个参数决定了Linux是倾向于使用swap还是倾向于释放文件系统cache。在内存紧张的情况下数值越低越倾向于释放文件系统cache。当然这个参数只能减少使用swap的概率并不能避免Linux使用swap。修改MySQL的配置参数innodb_flush_ method开启O_DIRECT模式这种情况下InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘但是redo log依旧会使用文件系统cache。值得注意的是Redo log是覆写模式的即使使用了文件系统的cache也不会占用太多。IO调度策略#echo deadline/sys/block/sda/queue/scheduler 临时修改为deadline 永久修改vi /boot/grub/grub.conf更改到如下内容:kernel /boot/vmlinuz-2.6.18-8.el5 ro rootLABEL/ elevatordeadline rhgb quiet5系统参数调整Linux系统内核参数优化vim/etc/sysctl.confnet.ipv4.ip_local_port_range 1024 65535# 用户端口范围net.ipv4.tcp_max_syn_backlog 4096 net.ipv4.tcp_fin_timeout 30 fs.file-max65535# 系统最大文件句柄控制的是能打开文件最大数量 用户限制参数MySQL可以不设置以下配置vim/etc/security/limits.conf * soft nproc 65535* hard nproc 65535* soft nofile 65535* hard nofile 655356应用优化业务应用和数据库应用独立防火墙iptables、selinux等其他无用服务关闭 chkconfig --level 23456 acpid off chkconfig --level 23456 anacron off chkconfig --level 23456 autofs off chkconfig --level 23456 avahi-daemon off chkconfig --level 23456 bluetooth off chkconfig --level 23456 cups off chkconfig --level 23456 firstboot off chkconfig --level 23456 haldaemon off chkconfig --level 23456 hplip off chkconfig --level 23456 ip6tables off chkconfig --level 23456 iptables off chkconfig --level 23456 isdn off chkconfig --level 23456 pcscd off chkconfig --level 23456 sendmail off chkconfig --level 23456 yum-updatesd off安装图形界面的服务器不要启动图形界面runlevel 3。 另外思考将来我们的业务是否真的需要MySQL还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。五、数据库优化SQL优化方向执行计划、索引、SQL改写。架构优化方向高可用架构、高性能架构、分库分表。1数据库参数优化调整实例整体高级优化扩展thread_concurrency# 并发线程数量个数sort_buffer_size# 排序缓存read_buffer_size# 顺序读取缓存read_rnd_buffer_size# 随机读取缓存key_buffer_size# 索引缓存thread_cache_size# (1G—8, 2G—16, 3G—32, 3G—64)连接层基础优化设置合理的连接客户和连接方式max_connections # 最大连接数看交易笔数设置 max_connect_errors # 最大错误连接数能大则大connect_timeout # 连接超时max_user_connections # 最大用户连接数skip-name-resolve # 跳过域名解析wait_timeout # 等待超时back_log # 可以在堆栈中的连接数量SQL层基础优化query_cache_size 查询缓存 OLAP类型数据库,需要重点加大此内存缓存但是一般不会超过GB。对于经常被修改的数据缓存会立马失效。我们可以实用内存数据库redis、memecache替代他的功能。2存储引擎层innodb基础优化参数default-storage-engineinnodb_buffer_pool_size # 没有固定大小50%测试值看看情况再微调。但是尽量设置不要超过物理内存70%innodb_file_per_table(1,0)innodb_flush_log_at_trx_commit(0,1,2) # 1是最安全的0是性能最高2折中binlog_syncInnodb_flush_method(O_DIRECT, fdatasync)innodb_log_buffer_size # 100M以下innodb_log_file_size # 100M 以下innodb_log_files_in_group # 5个成员以下,一般2-3个够用iblogfile0-Ninnodb_max_dirty_pages_pct # 达到百分之75的时候刷写 内存脏页到磁盘。log_binmax_binlog_cache_size # 可以不设置max_binlog_size # 可以不设置innodb_additional_mem_pool_size #小于2G内存的机器推荐值是20M。32G内存以上100M
