MySQL优化

硬件层优化 |   系统层优化   |   软件层优化  |  表结构、SQL语句、索引优化


性能优化
  硬件层:
		CPU
			双路CPU，单8核换双4核
		内存
			双路内存： 单16G换双8G
		硬盘	
			SSD
		RAID卡
			建议RAID0 或者RAID10  H730阵列卡带缓存
		
   系统层
		硬盘I/O调度算法(centos默认值就是Deadline)
			#echo "Deadline" > /sys/block/sda/queue/scheduler
			
		减少SWAP分区使用（内存足够的情况下）
			#echo "0" > /proc/sys/vm/swappiness
			
		增大打开文件大小
			#ulimit -a      #查看系统限制
			直接追加到最后
			#vi /etc/security/limits.conf    
			* soft nofile 65535
			* hard nofile 65535
			
		内核参数调优
		    #sysctl -a     #列出系统内核参数的值
			
			#sysctl -a | grep net     #列出net系统参数
			
			
			
			net.ipv4.tcp_tw_reuse = 1
			net.ipv4.tcp_tw_recycle = 1
			net.ipv4.tcp_max_tw_buckets = 4096
			net.ipv4.tcp_max_syn_backlog = 4096
			net.ipv4.tcp_syncookies = 1
			net.ipv4.tcp_fin_timeout = 20
			
			
			系统设置生效
			#sysctl -a | grep net.ipv4.tcp_syncookies
			
			#sysctl -w net.ipv4.tcp_syncookies=1  #等号左右不能有空格
			
			配置文件（直接追加到尾部）
			#vi /etc/sysctl.conf
			net.ipv4.tcp_tw_reuse = 1
			net.ipv4.tcp_tw_recycle = 1
			net.ipv4.tcp_max_tw_buckets = 4096
			net.ipv4.tcp_max_syn_backlog = 4096
			net.ipv4.tcp_syncookies = 1
			net.ipv4.tcp_fin_timeout = 20
			
			
			查找参数
			#find /proc/ -name "tcp_fin_timeout"
			
			配置文件生效
			#sysctl -p
			
			
			
			
			
			
			
	软件层调优
		max_conncetions =10240
		open_files_limit=65535
		innodb_buffer_pool_instances=4       #缓存池的数量
		innodb_buffer_pool_size=4G           #专用数据库机器物理内存的75%-80%，其他的可以设置50%
		innodb_flush_log_at_trx_commit=1     #写入磁盘日志文件，0是性能最高的，1对数据一致性和安全性能较高时设置，2是性能安全适中的方案
		sync_binlog=1						 #主从复制建议写1，每一个事务写一次binlog日志。备份建议写0，
		innodb_log_file_size=256M
		innodb_flush_method=O_DIRECT
		interactive_timeout=1800
		wait_timeout=1800
		slave-parallel-type = LOGICAL_CLOCK 
		slave-parallel-workers = 8
		master-info-repository=TABLE 
		relay-log-info-repository=TABLE
		
		
		#vi /usr/local/mysql/etc/my.cnf
		[mysqld]
		daemonize=on
		user=mysql
		port=3306
		basedir=/usr/local/mysql
		datadir=/usr/local/mysql/data
		socket=/tmp/mysql.sock
		bind-address=0.0.0.0
		pid-file=/usr/local/mysql/mysqld.pid
		character-set-server=utf8
		collation-server=utf8_general_ci
		max_connections=1024
		log-error=/usr/local/mysql/logs/mysqld.log

		gtid_mode=ON
		server_id=1
		log-bin=mysql-bin           #slave可以不用开启
		enforce_gtid_consistency=ON
		#log_slave_updates=ON       #backup需要开启
		
		
		#slave开启的参数
		slave-parallel-type=LOGICAL_CLOCK
		slave-parallel-workers=8       #开启线程数量
		master-info-repository=TABLE
		relay-log-info-repository=TABLE
		
		
		#性能调优
		open_files_limit=65535
		innodb_buffer_pool_instances=4       #缓存池的数量
		innodb_buffer_pool_size=4G           #专用数据库机器物理内存的75%-80%，其他的可以设置50%
		innodb_flush_log_at_trx_commit=1     #写入磁盘日志文件，0是性能最高的，1对数据一致性和安全性能较高时设置，2是性能安全适中的方案
		sync_binlog=1						 #主从复制建议写1，备份建议写0，没一个事务写一次binlog日志
		innodb_log_file_size=256M
		innodb_flush_method=O_DIRECT
		interactive_timeout=1800
		wait_timeout=1800
		
		

性能优化	
		SQL及索引优化
			可以通过mysql慢查询日志，查询哪些语句不行效率
			mysql> show variables like 'slow_query%';
			+---------------------+------------------------------------------+
			| Variable_name       | Value                                    |
			+---------------------+------------------------------------------+
			| slow_query_log      | OFF                                      |
			| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
			+---------------------+------------------------------------------+
			mysql> set global slow_query_log=ON;
			mysql> set global slow_query_log_file='/usr/local/mysql/logs/mysql-slow.log';
			
			超过多少秒算慢查询，默认
			mysql> show variables where variable_name='long_query_time';
			+-----------------+-----------+
			| Variable_name   | Value     |
			+-----------------+-----------+
			| long_query_time | 10.000000 |
			+-----------------+-----------+
			
			分析慢查询
			安装依赖库
			#yum install perl
			#mysqldumpslow ../logs/mysql-slow.log
			
			
			
			pt-query-digest查询工具
			#yum install https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/redhat/7/x86_64/percona-toolkit-3.0.5-1.el7.x86_64.rpm
			#pt-query-digest /usr/local/mysql/logs/mysql-slow.log
			
			
		分库
			
		
		分表
		
		
		
		分区
		
		
		
		#vmstat
		
		
		#iostat    #系统io状态