【优化】Mysql Mariadb 配置调优

innodb_buffer_pool_size

8G

InnoDB 引擎在内存中有一个缓冲池用于缓存数据和索引,这当然有助于你更快地执行MySQL/MariaDB 查询语句。这是InnoDB 最重要的设置，对InnoDB性能有决定性的影响。默认设置只有128M，所以默认的数据库设置下面InnoDB性能很差。在只有InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。

  • 一般来说，你可以将 innodb_buffer_pool_size 设置为服务器总物理内存的 70% 到 80% 左右。对于一个 16GB 内存的服务器，一个合理的设置可能是 11GB 到 13GB 之间。
  • 如果你的应用程序对内存的其他部分有较高要求（例如缓存、应用层等），那么你可以根据需要降低这个比例。

假设你希望将 innodb_buffer_pool_size 设置为 12GB（即 12 * 1024 * 1024 * 1024 字节），可以在 MariaDB 的配置文件（通常是 /etc/mysql/my.cnf 或 /etc/my.cnf）中的 [mysqld] 部分添加以下行：需要重启

[mysqld]
innodb_buffer_pool_size = 12G
  • 1.
  • 2.

无需重启

SET GLOBAL innodb_buffer_pool_size = 10737418240;
  • 1.

查询当前值

SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';
  • 1.

【优化】Mysql Mariadb 配置调优_服务器

注意事项
  • 不要将 innodb_buffer_pool_size 设置得过大以至于服务器没有足够的内存留给其他进程或用于系统缓存。
  • 确保服务器有足够的交换空间，虽然通常建议尽量避免使用交换空间，因为这会显著降低性能。
  • 如果你的系统还运行着其他数据库实例或者应用程序，确保为它们预留足够的内存。

innodb_buffer_pool_size=8G
InnoDB 引擎在内存中有一个缓冲池用于缓存数据和索引,这当然有助于你更快地执行MySQL/MariaDB 查询语句。这是InnoDB 最重要的设置，对InnoDB性能有决定性的影响。默认设置只有128M，所以默认的数据库设置下面InnoDB性能很差。在只有InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。

innodb_buffer_pool_instances=2
内存缓冲池实例数，将innodb_buffer_pool_size配置的内存分割成N份，此参数当配置内存大小于1G时才生效，当数据库有多个会话进行数据库操作时，用于并行在多个内存块中处理任务，一般配置值<=服务器CPU的个数。

innodb_log_buffer_size=128M
日志缓冲区大小,一般不用设置太大，能存下1秒钟操作的数据日志就行了，mysql默认1秒写一轮询写一次日志到磁盘。

内存配置相关参数

read_buffer_size=8M
（数据文件存储顺序）是MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能，read_buffer_size变量控制这一提高表的顺序扫描的效率数据文件顺序。

read_rnd_buffer_size=8M
是MySQL的随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要大量数据可适当的调整该值，但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值，以免内存开销过大。表的随机的顺序缓冲提高读取的效率。

join_buffer_size=8M
应用经常会出现一些两表（或多表）join的操作需求，MySQL在完成某些join需求的时候（all row join/all index /scan join）为了减少参与join的“被驱动表”的读取次数以提高性能，需要使用到join buffer来协助完成join操作当join buffer 太小，MySQL不会将该buffer 存入磁盘文件而是先将join buffer中的结果与需求join的表进行操作，然后清空join buffer 中的数据，继续将剩余的结果集写入次buffer中，如此往复，这势必会造成被驱动表需要被多次读取，成倍增加IO访问，降低效率（执行计划中如果现实using join buffer）

sort_buffer_size=16M
sort buffer是系统中对数据进行排序的时候用到的Buffer。sort buffer同样是针对单个线程的，所以当多个线程同时进行排序的时候，系统中就会出现多个sort buffer。我们一般可以通过增大sort buffer的大小来提高order by或者group by的处理性能。系统默认大小时2MB，最大限制和join buffer一样。
上述都为每个进程分别进行设置，所以要注意配置大小

innodb_flush_log_at_trx_commit=2
当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

query_cache_type=1 ，query_cache_size=128M
只有query_cache_type 为1 时，query_cache_size 才有效。MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，
有这样的查询缓存能提高查询的速度，使查询性能得到优化，前提条件是你有大量的相同或相似的查询，而很少改变表里的数据，否则没有必要使用此功能。

tmp_table_size=2G

临时表大小