1 一键的mysql优化建议工具

https://github.com/major/MySQLTuner-perl
提出了很多对我的数据库分析的建议,例如:

 Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
    For MySQL 5.6.2 and lower, Max combined innodb_log_file_size should have a ceiling of (4096MB / log files in group) - 1MB.
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    SET innodb_stats_on_metadata = OFF
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    innodb_file_per_table=ON
    innodb_buffer_pool_size (>= 815.3M) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

2 减少sleep的mysqld进程

减少wait_timeout: wait_timeout=60 mysql连接空闲60秒就会被关闭,如果有的脚本里不带重试就会出现Mysql has gong away的报错

但是这个是show processlist;中显示的“process”,似乎和linux的nysqld “process”又是两个东西,真正占用内存的是这些mysqld的进程啊。
一般来说mysqld应该是对每个客户端的connection创建一个thread而不是process,这里这么多mysqld应该就是服务启动时候fork出来的一个进程池?有点类似的apache的一个连接一个进程。没查到怎么设置这个进程池的大小,但是对于每一个进程内存相同部分是共享空间的,只有不同部分才占用空间,所以实际的内存占用会比显示的内存小,参考:https://serverfault.com/questions/170541/too-many-mysql-processes

If you're concerned about the memory usage of each mysql process, look at /etc/mysql/my.cnf, and look at the variables in the mysqld section:

key_buffers
thread_stack
thread_cache_size
max_connections
query_cache_limit
query_cache_size

3 修改配置文件my.cnf

[mysqld]
thread_cache_size=20

wait_timeout=60

performance_schema = off
performance_schema_max_table_instances=150
table_definition_cache=150
table_open_cache=64
innodb_buffer_pool_size=2M

tmp_table_size=16M
max_heap_table_size=16M
innodb_file_per_table=ON

max_connections=

* thread_cache_size

线程池缓存大小
( 当客户端断开连接后 将当前线程缓存起来 当在接到新的连接请求时快速响应 无需创建新的线程 )
可以通过如下几个MySQL状态值来适当调整线程池的大小
Threads_cached : 当前线程池中缓存有多少空闲线程
Threads_connected : 当前的连接数 ( 也就是线程数 )
Threads_created : 已经创建的线程总数
Threads_running : 当前激活的线程数 ( Threads_connected 中的线程有些可能处于休眠状态 )

* wait_timeout

连接空闲等待时间,缺省情况下,wait_timeout的初始值是28800
wait_timeout过大有弊端,其体现就是MySQL里大量的SLEEP进程无法及时释放,拖累系统性能,不过也不能把这个值设置的过小,否则你可能会遭遇到“MySQL has gone away”之类的问题,通常来说,我觉得把wait_timeout设置为10是个不错的选择,但某些情况下可能也会出问题,比如说有一个CRON脚本,其中两次SQL查询的间隔时间大于10秒的话,那么这个设置就有问题了(当然,这也不是不能解决的问题,你可以在程序里时不时mysql_ping一下,以便服务器知道你还活着,重新计算wait_timeout时间):

* performance_schema

性能分析工具,MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况
简单概括就是能检测mysql里是不是有慢查询,长时间锁等性能问题,但同时也会占用一些资源

* tmp_table_size(max_heap_table_size)

临时表的内存缓存大小
( 临时表是指sql执行时生成临时数据表 )

默认值 16777216
最小值 1
最大值 18446744073709551615
// 单位字节 默认值也就是16M多

首先在优化sql的时候就应该尽量避免临时表
如果必须使用临时表 且同时执行大量sql 生成大量临时表时适当增加 tmp_table_size
如果生成的临时表数据量大于 tmp_table_size 则会将临时表存储与磁盘而不是内存

**临时表的大小应该根据自己数据库的使用情况设置,小网站的小数据库就可以开的比较小**

* innodb_file_per_table

独立表空间

Innodb存储引擎可将所有数据存放于ibdata*的共享表空间,也可将每张表存放于独立的.ibd文件的独立表空间。
    共享表空间以及独立表空间都是针对数据的存储方式而言的。
    共享表空间:  某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1  初始化为10M。
    独立表空间:  每一个表都将会生成以独立的文件方式来进行存储,每一个表都有一个.frm表描述文件,还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容,默认情况下它的存储位置也是在表的位置之中。

2.1 共享表空间

    优点:
    可以将表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同的文件上)。数据和文件放在一起方便管理。
    缺点:
    所有的数据和索引存放到一个文件中,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日值系统这类应用最不适合用共享表空间。

    2.2 独立表空间

    在配置文件(my.cnf)中设置: innodb_file_per_table
    优点:
    1.每个表都有自已独立的表空间。
    2.每个表的数据和索引都会存在自已的表空间中。
    3.可以实现单表在不同的数据库中移动。
    4.空间可以回收(除drop table操作处,表空不能自已回收)
        a.Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
        b.对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
        c.对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。
    缺点:
    单表增加过大,如超过100个G。
    相比较之下,使用独占表空间的效率以及性能会更高一点。

* table_open_cache

table_open_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。
通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_open_cache的值。
如果你发现open_tables等于table_open_cache,并且opened_tables在不断增长,那么你就需要增加table_open_cache的值了(上述状态值可通过SHOW GLOBAL STATUS LIKE ‘Open%tables’获得)。
注意,不能盲目地把table_open_cache设置成很大的值,设置太大超过了shell的文件描述符(通过ulimit -n查看),造成文件描述符不足,从而造成性能不稳定或者连接失败。

* table_definition_cache

理解下来,就是控制总frm文件的数量,还是个hash表,内部维护。如果打开的表实例的数量超过了table_definition_cache设置,LRU机制将开始标记表实例以进行清除,并最终将它们从数据字典缓存中删除。

简单通俗点frm文件有多少,就设置多少了。

* max_connection

Mysql允许的客户端最大连接数,每一个客户端执行connect就算一次链接,当链接数达到max_connection时,会出现MySQL: ERROR 1040: Too many connections错误;
查询当前链接:SHOW PROCESSLIST
在连接不活跃时间超过wait_timeout时,这个connection会被自动kill,客户端就会报mysql has gong away需要重新连接,wait_timeout的设置根据业务确定,但是注意客户端一定要加重试,不然直接连接断开就error了
推荐max_connection配置为最大链接数占比85%,可以用show global status like 'Max_used_connections';查询历史中的最大连接数:

MySQL [(none)]>  show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 2583  |
+----------------------+-------+
1 row in set (0.01 sec)

参考:
mysql中有大量sleep进程的原因与解决办法
减少mysql内存_减少mysql内存占用
MySQL 优化之 thread_cache_size
mysql innodb_log_file_size 和innodb_log_buffer_size参数
MySQL服务器最大连接数怎么设置才合理

Leave a comment

邮箱地址不会被公开。 必填项已用*标注