mariadb优化

 

引擎参数调优

一、查看默认引擎:

show global variables like '%engine%';

二、更换引擎:

vim /etc/my.cnf.d/server.cnf
#添加一下内容然后重启数据库
[mysqld]
default_storage_engine=MyISAM

 

三、Innodb和MyISAM区别和优缺点:

 

应用场景:

1.数据库经常使用什么样的查询模式？在写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM。 
2.数据库的数据有多大？ 大尺寸倾向于innodb，因为事务日志，故障恢复。 

线程参数调优

--查看mariadb的设置的最大连接数
show global variables like 'max_connections';

--MariaDB启动后的累计连接数
show global status like 'Connections';

--mariaDB启动后的最大同时连接数
show global status like 'Max_used_connections';

--MariaDB线程信息
show global status like 'Thread_%';
--Threads_cached:备用的线程数(线程是可以再利用的)
--Threads_connected:现在的连接数
--Threads_created:备用的线程不足时，会生成新线程(这个值不断变大时，表示Threads_cached不足)
--Threads_running:正在执行中的线程,也可以说是不在Sleep状态的线程
--Threads_cached + Threads_connected < thread_cache_size是理想的状态。

--查看线程缓存池大小
show global variables like ‘thread_cache_size’;

 

缓冲池参数调优

【 innodb_buffer_pool_size 】
 用于缓存 索引 和 数据的内存大小, 这个当然是越多越好, 数据读写在内存中非常快, 减少了对磁盘的读写。 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。
 然而内存还有操作系统或数据库其他进程使用, 一般设置 buffer pool 大小为总内存的  3/4 至 4/5。 若设置不当, 内存使用可能浪费或者使用过多。 
 对于繁忙的服务器, buffer pool 将划分为多个实例以提高系统并发性, 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响, 
 当然影响较小。


【 innodb_buffer_pool_instances 】
buffer pool 被划分为多个缓存实例的数量, 为固定值,不动态变更。当较多数据加载到内存时, 使用多缓存实例能减少缓存争用情况。当 innodb_buffer_pool_size 大于 1GB 时, 
innodb_buffer_pool_instances 默认为 8。

 

查询参数调优

--只有在query_cache_type=1的时候query_cache_size才生效
--设置成1代表查询的sql语句将被缓存，并且在下次调用相同的sql语句时直接从缓存里调，加快查询速度
--前提是你有大量相同的查询的sql语句，否则没必要使用
query_cache_type=1   query_cache_size=128M

 

事务日志参数调优

INNODB_LOG_FILE_SIZE
这个参数默认为5M，如果在高并发量的时候，存在大量事物的时候，这个数值就比较低了，应该适情况修改数值，一般为1G-2G

 

索引调优

如果你的数据库有大量的查询操作，应该适当添加一些索引，可以加快读取速度

主机名解析调优

SKIP_NAME_RESOLVE
这一项不得不提及，因为仍然有很多人没有添加这一项。你应该添加skip_name_resolve来避免连接时DNS解析。
大多数情况下你更改这个会没有什么感觉，因为大多数情况下DNS服务器解析会非常快。不过当DNS服务器失败时，它会出现在你服务器上出现“unauthenticated connections” ，
而就是为什么所有的请求都突然开始慢下来了。
所以不要等到这种事情发生才更改。现在添加这个变量并且避免基于主机名的授权。

 

以上都属于软调优，并且以上标题带参数两个字的，更改他们的值都可以vim /etc/my.cnf.d/server.cnf ，然后设置他们的值，然后重启mariadb服务，比如(如下):

[mysqld]
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=12
skip_name_resolve
query_cache_type=1

 设置慢查询日志

作用:可以把数据库执行速度比较慢的sql语句记录到文件当中，方便分析

[mysqld]
slow_query_log=1#开启慢查询日志
slow_query_log_file=/tmp/mysql_query.log  #这是记录慢查询日志的路径
long_query_time=2 #超过2秒的sql语句将被记录

测试:

select sleep(3);

查看慢查询日志tail -f/tmp/mysql_query.log

# Time: 190420 16:55:28
# User@Host: root[root] @ localhost []
# Thread_id: 8  Schema:   QC_hit: No
# Query_time: 2.001316  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 63
SET timestamp=1555750528;
select sleep(2);