MySQL vs. MariaDB:默认配置对比，mysqlmariadb

原文：MySQL vs. MariaDB: Default Configuration Differences
作者：Bradley Mickel
翻译：lloog

译者注：作者介绍MySQL与MariaDB默认配置选项的区别和使用方法。

在这篇博文中，我将讨论一些MySQL和MariaDB的默认配置差异，重点放在MySQL 5.7和MariaDB 10.2上。

MariaDB服务器(简称为MariaDB)与MySQL的Percona服务器有相同的起源，但它很快就脱离了与MySQL的兼容性，并自行发展。几个操作系统中已经默认安装MariaDB(例如Red Hat Enterprise Linux/CentOS/ Fedora)。默认变量的改变对数据库性能上产生很大的影响，所以知道彼此之间的区别是很重要的。

随着MariaDB的发展，并不能100%兼容MySQL，默认配置的设置已经不像以前一样。它可能使用不同的变量名或者以新的方式实现相同的变量。同时还需要考虑到MariaDB使用它自己的Aria存储引擎，存在许多在MySQL中不存在的配置选项。

注意:在这个博客中，我关注的是MySQL或MariaDB共同拥有的，但有不同默认值的常见变量而不是针对MySQL或MariaDB各自的变量(除了在optimizer_switch内不同的交换机)。

二进制日志

Variable	MariaDB Default	MySQL Default
sync_binlog	0	1
binlog_format	Mixed	Row

MySQL在二进制日志上采取了更为保守的态度。在MySQL 5.7的最新版本中，为了确保所有提交数据完整和一致，该版本只更新了两个变量。为了防止非确定性语句产生不同的结果，在MySQL中更新了binlog_format。基于行的复制在执行许多较小的更新时也会有所帮助。MariaDB默认为混合格式。除非为了满足某些条件，才会把混合格式改为状态格式。在这种情况下，它使用行格式。这里可以看到使用行格式时的详细标准。

另一个可能对性能产生重大影响的差异与sync_binlog有关。在将二进制日志同步到磁盘之前，sync_binlog控制提交组的数量。MySQL将此更改为1，这意味着每个事务在提交之前都被刷新到磁盘。这保证不可能有未记录的提交事务(即使在系统故障期间)。这可能会对性能产生很大的影响，正如Roel Van de Paar在他的文章中所示。

MariaDB使用了一个值为0的sync_binlog，它允许操作系统确定何时需要刷新binlog。

MyISAM

Variable	MariaDB Default	MySQL Default
myisam_recover_options	BACKUP, QUICK	OFF
key_buffer_size	134217728	8388608

InnoDB取代MyISAM作为默认的存储引擎已经有一段时间了，但它仍然被用于许多系统表。因为它没有被大量使用，MySQL已经调优了MyISAM设置。

当mysqld打开一个表时，它检查表是否被标记为崩溃或未正确关闭，并基于myisam_recover_options设置对其进行检查。MySQL默认禁用这一功能，防止恢复。MariaDB启用了备份和快速恢复选项。备份导致table_name-datetime。当数据文件在恢复期间发生更改时，将创建bak文件。如果没有删除块，那么QUICK导致mysqld不检查表中的行，确保恢复的速度更快。

MariaDB 10.2增加了key_buffer_size。这允许在内存中存储更多的索引块。所有的线程都使用这个缓冲区，所以一个小缓冲区可以使信息更快地进入和流出。MariaDB 10.2使用的缓冲区是MySQL 5.7:134217728,MySQL 5.7的8388608。

InnoDB

Variable	MariaDB Default	MySQL Default
innodb_max_undo_log_size	10485760 (10 MiB)	1073741824 (1024 MiB)

在MariaDB 10.2和MySQL 5.7之间，InnoDB的变量基本不变。MariaDB从10.2.6开始减少了innodb_max_undo_log_size。这从MySQL默认的1073741824(1024 MiB)减少到10485760(10 MiB)。这些大小反映了撤销表空间在被标记为截断之前的最大大小。在启用innodb_undo_log_truncate的情况下，表空间不会被截断，默认情况下，它在MySQL 5.7和MariaDB 10.2中禁用。

日志

Variable	MariaDB Default	MySQL Default
log_error		/var/log/mysqld.log
log_slow_admin_statements	ON	OFF
log_slow_slave_statements	ON	OFF
lc_messages_dir		/usr/share/mysql

日志对于解决任何问题都非常重要，因此MySQL 5.7和MariaDB 10.2的日志记录的不同选择非常有趣。

log_error变量允许您控制错误的记录。MariaDB 10.2将此变量为空，将所有错误写入stderr。MySQL 5.7在/ var/ log/mysqld.log中使用显式创建的文件。

MariaDB 10.2还启用了额外的慢语句日志记录。log_slow_admin_statements为没有写入到binlog的任何管理语句创建一个记录。log_slow_slave_statements记录从master发送的复制语句，如果它们运行缓慢。MySQL 5.7不允许在默认情况下记录这些语句。

lc_messages_dir是包含各种语言的错误消息文件的目录。在MariaDB 10.2中，可变的默认值可能会有一点误导。lc_messages_dir在默认情况下是空的，尽管它仍然使用与MySQL 5.7相同的路径。默认情况下，两个数据库都位于/ usr/ share/mysql中。

性能模式

Variable	MariaDB Default	MySQL Default
performance_schema	OFF	ON
performance_schema_setup_actors_size	100	-1 (auto adjusted)
performance_schema_setup_objects_size	100	-1 (auto adjusted)

性能模式是一种工具，旨在帮助解决各种性能问题。MySQL 5.7在默认情况下启用了性能模式和许多工具。MySQL甚至可以检测许多性能模式变量的适当值，而不是设置静态默认值。性能模式确实带来了一些开销，并且有许多博客关于这对性能有多大影响。我认为Sveta Smirnova说最好在她的博客上性能模式基准OLTP RW:“…测试系统上!没有通用的基准可以在你的站点上重复工作负载。

MariaDB在默认情况下禁用了性能模式，并调整了一些动态变量。注意，如果希望禁用或启用性能模式，则需要重新启动服务器，因为这些变量不是动态的。performance_schema_setup_actors_size和performance_schema_setup_objects_size都被设置为静态的100，而不是MySQL 5.7中使用的动态- 1。这些都限制了可以存储在相对表中的行数。这为这些表的增长提供了一个硬性限制，有助于减少它们的数据占用。

SSL/TLS

Variable	MariaDB Default	MySQL Default
ssl_ca		ca.pem
ssl_cert		server-cert.pem
ssl_key		server-key.pem

Secure Sockets Layer(SSL)和Transport Layer Security(TLS)是允许进行安全通信的加密协议。SSL实际上是TLS的前身，尽管它们通常被称为SSL。MySQL 5.7和MariaDB 10.2支持yaSSL和OpenSSL。在MySQL 5.7和MariaDB 10.2之间，SSL / TLS的默认配置略有不同。MySQL 5.7为ssl_ca、ssl_cert和ssl_key设置了一个特定的文件名。这些文件是在基目录中创建的，由变量basedir标识。在MariaDB 10.2中，每个变量都是空的，因此您需要在使用安全连接之前设置它们。这些变量不是动态的，所以在开始数据库之前一定要设置这些值。

查询优化器

查询优化器有几个变量，不仅会影响查询性能，还会影响您编写SQL语句的方式。在MariaDB和MySQL之间，查询优化器本质上是不同的，因此即使配置相同，您也可能看到不同的性能。

sql_mode对如何编写查询设置了限制。与MariaDB 10.2相比，MySQL 5.7有几个额外的限制。only_full_group_by要求所有字段被声明在任何select … group by子句中。优化器不会对分组进行任何假设，因此必须显式地指定它。

no_zero_date和no_zero_in_date都影响服务器如何在日期中解释零。启用no_zero_date时，允许值为000000 - 00的值，但会发出警告。如果启用了严格的模式，则不允许使用该值并产生错误。no_zero_in_date类似，但它适用于日期的任何部分(月、日或年)。对于这个禁用的，日期为0的部分，如2017 - 0016是允许的。启用时，日期更改为000000 - 00，没有警告。严格的模式可以防止被插入的日期，除非被忽略。插入“忽略”和“更新”，将“日期”插入“000000 - 00”。5.7.4改变了这一点。将no_zero_in_date合并为严格模式，并弃用显式选项。

query_prealloc_size确定用于语句解析和执行的持久缓冲区的大小。如果您经常使用复杂的查询，那么增加这个缓冲区的大小是很有用的，因为它不需要在查询解析期间分配额外的内存。MySQL 5.7将此缓冲区设置为8192，块大小为1024。MariaDB在10.1.2至24576年增加了这个值。

query_alloc_block_size指定在查询解析期间分配的任何额外块的字节大小。如果内存碎片是一个常见问题，那么您可能需要考虑增加这个值。MySQL 5.7使用8192，而MariaDB 10.2使用16384(两倍)。调整块大小时要小心:过高的消耗超过所需的内存，太低会导致严重的碎片化。

优化的switch变量包含许多不同的开关，它们影响查询优化器计划和执行不同的查询。MariaDB 10.2和MySQL 5.7在其启用选项和可用选项中有许多不同之处。您可以看到下面每个选项的简要分类。在该服务器上不支持任何带有N / A的选项。

杂项

Variable	MariaDB Default	MySQL Default
default_tmp_storage_engine	NULL	InnoDB
group_concat_max_len	1048576(1M)	1024(1K)
lock_wait_timeout	86400 (1 DAY)	31536000 (1 YEAR)
max_allowed_packet	(16777216) 16MB	4194304 (4MB)
max_write_lock_count	4294967295	18446744073709551615
old_passwords	OFF	0
open_files_limit	0	dependent on OS
pid_file	/var/lib/mysql/	/var/run/mysqld/
secure_file_priv		Varies by installation
sort_buffer_size	2097152	262144
table_definition_cache	400	autosized
table_open_cache_instances	8	16
thread_cache_size	autosized	autosized
thread_stack	292KB	192KB/256KB

有很多变量不适合放在一个组。在这我来整理一下。

当创建临时表时，如果您没有指定存储引擎，则使用默认值。在MySQL 5.7中，这将被设置为InnoDB，与default_storage_engine相同。MariaDB 10.2也使用InnoDB，但它没有显式地设置。MariaDB将default_tmp_storage_engine设置为NULL，这导致它使用default_storage_engine。如果您更改了默认的存储引擎，这一点很重要，因为它也会更改临时表的默认值。

重要提示:在MariaDB中，这只与创建临时表的表有关。内部内存中的临时表使用内存存储引擎，而内部的、磁盘上的临时表默认使用aria引擎。

如果不检查，group_concat函数会导致一些非常大的结果。您可以使用group_concat_max_len限制该函数的最大结果大小。MySQL 5.7限制到1024(1K)。MariaDBi在10.2.4版本增加到1048576(1M)。

lock_wait_timeout控制线程在尝试获取元数据锁时等待的时间。一些语句需要一个元数据锁，包括DDL和DML操作、锁表、带读锁的刷新表和处理程序语句。MySQL 5.7缺省值为最大可能值(1年)，而MariaDB 10.2则将此降低到一天。

max_allowed_packet为数据包或者是生成的中间字符串的最大大小设置了一个限制。为了检测更大的、有意不正确的数据包，这个值故意在MySQL 5.7上保持小(4MB)。MariaDB将这个值增加到16MB。如果使用任何大的BLOB字段，您需要将这个值调整到最大的BLOB的大小，即1024的倍数，否则可能会遇到错误的传递结果。

max_write_lock_count控制可以在某些读锁请求被处理之前给出的写锁的数量。在极其沉重的写负载中，你的阅读可以堆积起来等待写完成。修改max_write_lock_count可以让您在对表进行一些读操作之前，调优可以发生多少写入。MySQL 5.7将此值保持在最大值(18446744073709551615)，而MariaDB 10.2将此值降低到4294967295。值得注意的是，这仍然是MariaDB 10.2的最大值。

old_passwords控制密码函数使用的散列方法，创建用户和grant语句。这个变量在MySQL 5.7中经历了几次变化。在5.7.4中，有效的选项是MySQL 4.1原生哈希，pre - 4.1(“old”)哈希，和sha - 256哈希。版本5.7.5删除了“old”的前4.1方法，在5.7.6中，变量被弃用，意图完全删除它。MariaDB 10.2使用了这个变量的一个简单布尔值，而不是MySQL 5.7中的枚举值，尽管其意图是相同的。将old_passwords默认为OFF(或0)，并允许您在必要时启用老方法。

open_files_limit限制了mysqld可以保留的文件描述符的数量。如果设置为0(MariaDB 10.2中的默认值)，那么mysqld将保留max_connections * 5或max_connections + table_open_cache * 2，以更大的为准。应该指出的是，mysqld不能使用比操作系统所施加的硬限制更大的数量。MySQL 5.7也受到操作系统硬限制的限制，但在运行时设置为系统允许的实际值(而不是计算值)。

pid_file允许您控制存储进程ID文件的位置。这不是您通常需要的文件，但是知道它位于何处是好的，以防出现一些异常的错误。在MariaDB上，您可以在/ var/lib/mysql/上找到这个，而在MySQL 5.7中，您将在/ var/run/mysqld/中找到它。您还会注意到文件的实际名称的不同。MariaDB 10.2使用主机名作为pid的名称，而MySQL 5.7只使用进程名(mysqld . pid)。

secure_file_priv是一个安全特性，允许您限制用于数据导入和导出操作的文件的位置。当这个变量为空时，MySQL中默认值为5.7.6，没有限制。如果将值设置为NULL，则不允许导入和导出操作。唯一的有效值是文件导入导出的目录路径。MariaDB 10.2默认为空。在MySQL 5.7.6中，默认值将取决于install_layout CMAKE选项。

INSTALL_LAYOUT	DEFAULT VALUE
STANDALONE, WIN	NULL(>=MySQL 5.7.16_, empty(
DEB, RPM, SLES, SVR4	/var/lib/mysql-files
Other	Mysql-files under the CMAKE_INSTALL_PREFIX value

无论存储引擎如何，mysqld都使用排序缓冲区。每一个必须执行排序的会话都分配一个与sort_buffer_size值相等的缓冲区。这个缓冲区至少应该足够大，可以容纳15个元组。在MySQL 5.7中，此默认值为262144，而MariaDB 10.2则使用更大的值2097152。

table_definition _cache限制了可缓存的表定义的数量。如果有大量的表，mysqld需要读取.frm文件获取此信息。MySQL 5.7自动检测合适的大小，而MariaDB 10.2默认值为400。我在VM上进行测试，MySQL 5.7选择了1400的值。

table_open_cache_instances在MySQL和MariaDB的实现中有所不同。MySQL 5.7创建了table_open_cache的多个实例，每个实例都有一部分表。这有助于减少争用，因为会话需要只锁定DML语句缓存的一个实例。在MySQL 5.7.7中，默认是一个实例，但在MySQL 5.7.8(增加到16)中发生了更改。MariaDB对table_open_cache有一个更动态的方法。最初，缓存只有一个实例，而table_open_cache_instances变量是可以创建的最大实例数。如果在单个缓存中检测到争用，则创建另一个实例并记录错误。MariaDB 10.2，默认设置的最大8个实例应该支持最多100个CPU内核。

当创建新线程时，使用thread_cache_size进行控制。当客户端断开连接时，只要线程没达到最大数量，线程就存储在缓存中。虽然这通常不引人注意，但如果您的服务器每秒看到数百个连接，您应该增加这个值，以便新的连接可以使用缓存。thread_cache_size是在MySQL 5.7和MariaDB 10.2中自动检测到的变量，但是它们计算默认值的方法差异很大。MySQL使用一个公式，最大值为100:8 +(max_connections / 100)。MariaDB 10.2使用了256或max_connections大小的较小值。

thread_stack是每个线程的堆栈大小。如果堆栈大小，则限制SQL语句的复杂性，存储过程的递归深度和其他内存消耗操作。MySQL 5.7在32位平台上默认堆栈大小为192KB，在64位系统上默认为256KB。MariaDB 10.2调整了这个值几次。MariaDB 10.2.0使用290KB,10.2.1使用291KB,10.2.5使用292KB。

希望这能帮助您使用MySQL和MariaDB之间的配置选项。有问题请写在评论里。