mysql参数【优化】

1.设置主从时，由于binlog日志频繁记录操作，开销非常大，需要把binlog日志放到其它硬盘分区上：

2.操作系统的优化
网卡 bonding 技术
设置tcp 连接数量限制，优化系统打开文件的最大限制。
使用64位操作系统，64位系统可以分给单个进程更多的内存。 计算更快 。
禁用不必要启动的服务
文件系统调优，给数据仓库一个单独的文件系统，推荐使用XFS，一般效率更高、更可靠。
ext3

最小化原则:
1） 安装系统最小化
2） 开启程序服务最小化原则
3） 操作最小化原则
4） 登录最小化原则
5） 权限最小化
3.引擎
myisam 引擎，表级锁，表级锁开销小，影响范围大，适合读多写少的表，不支持事务。 表锁定不存在死锁 （也有例外）
innodb 引擎，行级锁，锁定行的开销要比锁定全表要大。影响范围小，适合写操作比较频繁的数据表。行级锁可能存在死锁。

4.
日志优化
1)
#开启后会将所有的死锁记录到error_log中 错误日志在my.cnf配置为

log-error=/var/log/mysqld.log
innodb_print_all_deadlocks=1
innodb_sort_buffer_size=16M

 

mysql> show processlist ; #显示mysql系统中正在运行的所有线程。 可以看到每个客户端正在执行的命令
2)
启用 mysql 慢查询：---分析 sql 语句，找到影响效率的 SQL
[root@xuegod63 ~]# vim /etc/my.cnf
[mysqld]

slow_query_log=1 #开启慢查询日志
slow-query-log-file=/var/lib/mysql/slow.log #这个路径对 mysql 用户具有可写权限 
long_query_time=5 #查询超过 5 秒钟的语句记录下来
log-queries-not-using-indexes=1 #没有使用索引的查询

 

5. my.cnf 内参数的优化
优化总原则：给 mysql 的资源太少，则 mysql 施展不开：给 mysql 的资源太多，可能会拖累整个 OS。
40%资源给OS， 60%-70% 给mysql （内存和CPU）

validate-password=off #密码复杂关闭

 

1)
启动查询缓存：
vim /etc/my.cnf 添加：
[mysqld] #在此字段中添加

query_cache_size=32M #至少4M以存储数据结构，可扩展。整体100G，若此服务器只运行mysql服务器。70-80G给mysql

 

2)强制限制mysql 资源设置您可以在mysqld中强制一些限制来确保系统负载不会导致资源耗尽的情况出现。

max_connections=500 #最大连接数，在服务器没有崩溃之前确保只建立服务允许数目的连接
wait_timeout=10 #mysqld将终止等待时间（空闲时间）超过10秒的连接
max_connect_errors=100

 

#如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到执行：
mysql> FLUSH HOSTS;
之后才能运行。默认情况下，10 次失败就足以导致锁定了。将这个值修改为100 会给服务器足够的时间来从问题中恢复。如果重试100 次都无法建立连接
，那么使用再高的值也不会有太多帮助，可能它根本就无法连接

总结：
mysql有超过100个可以调节的设置，要记住那么多基本是不可能的，但是幸运的是你只需要记住很少一部分你就可以基本满足你的需求了，我们还可以通
过“SHOW STATUS”命令来查看mysql是否按照我们的期望在运行。
3）

table_open_cache=23 #缓存23个表
key_buffer_size=512M #只跑一个mysql服务。结合所有缓存，mysql整体使用的缓存可以使用物理内存的80%

 

#设置超时时间,能避免长连接

wait_timeout=20
skip-name-resolve #不进行域名反解析,注意由此带来的权限/授权问题。关闭mysql 的dns 反查功能。这样速度就快了!

 字符集设置

注意5.7版本中的 default-character-set=utf8 这个配置，它现在是个NG的配置，我们要将其删除，只要保留 character_set_server=utf8 即可