MySQL vs. MariaDB:默认配置对比

原文: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之间的配置选项。有问题请写在评论里。

没有更多推荐了,返回首页