MySQL, Percona Server for MySQL and MariaDB Default Configuration Differences

MySQL and MariaDB Default ConfigurationIn this blog post, I’ll discuss some of the MySQL and MariaDB default configuration differences, focusing on MySQL 5.7 and MariaDB 10.2. Percona Server for MySQL uses the same defaults as MySQL, so I will not list them separately.

MariaDB Server is a general purpose open source database, created by the founders of MySQL. MariaDB Server (referred to as MariaDB for brevity) has similar roots as Percona Server for MySQL, but is quickly diverging from MySQL compatibility and growing on its own. MariaDB has become the default installation for several operating systems (such as Red Hat Enterprise Linux/CentOS/Fedora). Changes in the default variables can make a large difference in the out-of-box performance of the database, so knowing what is different is important.

As MariaDB grows on its own and doesn’t remain 100% compatible with MySQL, the defaults configuration settings might not mean everything or behave the way they used to. It might use different variable names, or implement the same variables in new ways. You also need to take into account that MariaDB uses it’s own Aria storage engine that has many configuration options that do not exist in MySQL.

Note: In this blog, I am looking at variables common to both MySQL or MariaDB, but have different defaults, not variables that are specific to either MySQL or MariaDB (except for the different switches inside the optimizer_switch).

Binary Logs

VariableMariaDB DefaultMySQL Default
sync_binlog01
binlog_formatMixedRow

 

MySQL has taken a more conservative stance when it comes to the binary log. In the newest versions of MySQL 5.7, they have updated two variables to help ensure all committed data remains intact and identical. Binlog_format was updated to row in MySQL in order to prevent non-deterministic statements from having different results on the slave. Row-based replication also helps when performing a lot of smaller updates. MariaDB defaults to the Mixed format. Mixed uses statement-based format unless certain criteria are met. It that case, it uses the row format. You can see the detailed criteria for when the row format is used here: https://mariadb.com/kb/en/the-mariadb-library/binary-log-formats/.

The other difference that can cause a significant impact on performance is related to sync_binlog. Sync_binlog controls the number of commit groups to collect before synchronizing the binary log to disk. MySQL has changed this to 1, which means that every transaction is flushed to disk before it is committed. This guarantees that there can never be a committed transaction that is not recorded (even during a system failure). This can create a big impact to performance, as shown by a Roel Van de Paar in his blog: https://www.percona.com/blog/2016/06/03/binary-logs-make-mysql-5-7-slower-than-5-6/

MariaDB utilizes a value of 0 for sync_binlog, which allows the operating system to determine when the binlog needs to be flushed. This provides better performance, but adds the risk that if MariaDB crashes (or power is lost) that some data may be lost.

MyISAM

VariableMariaDB DefaultMySQL Default
myisam_recover_optionsBACKUP,QUICKOFF
key_buffer_size1342177288388608

 

InnoDB replaced MyISAM as the default storage engine for some time now, but it is still used for many system tables. MySQL has tuned down the MyISAM settings, since it is not heavily used.

When mysqld opens a table, it checks whether the table is marked as crashed, or was not closed properly, and runs a check on it based on the myisam_recover_options settings. MySQL disables this by default, preventing recovery. MariaDB has enabled the BACKUP and QUICK recovery options. BACKUP causes a table_name-datetime.bak file to be created whenever a data file is changed during recovery. QUICK causes mysqld to not check the rows in a table if there are no delete blocks, ensuring recovery can occur faster.

MariaDB 10.2 increased the key_buffer_size. This allows for more index blocks to be stored in memory. All threads use this buffer, so a small buffer can cause information to get moved in and out of it more quickly. MariaDB 10.2 uses a buffer 16 times the size of MySQL 5.7: 134217728 in MariaDB 10.2 vsx 8388608 in MySQL 5.7.

Innodb

VariableMariaDB DefaultMySQL Default
innodb_max_undo_log_size10485760(10 MiB)1073741824(1024 MiB)

 

InnoDB variables have remained primarily unchanged between MariaDB 10.2 and MySQL 5.7. MariaDB has reduced the innodb_max_undo_log_size starting in 10.2.6. This was reduced from MySQL’s default of 1073741824(1024 MiB) to 10485760(10 MiB). These sizes reflect the maximum size an undo tablespace can become before it is marked for truncation. The tablespace doesn’t get truncated unless innodb_undo_log_truncate is enabled, and it is disabled in MySQL 5.7 and MariaDB 10.2 by default.

Logging

VariableMariaDB DefaultMySQL Default
log_error/var/log/mysqld.log
log_slow_admin_statementsONOFF
log_slow_slave_statementsONOFF
lc_messages_dir/usr/share/mysql

 

Logs are extremely important for troubleshooting any issues so the different choices in logging for MySQL 5.7 and MariaDB 10.2 are very interesting.

The log_error variable allows you to control where errors get logged. MariaDB 10.2 leaves this variable blank, writing all errors to stderr. MySQL 5.7 uses an explicitly created file at: /var/log/mysqld.log.

MariaDB 10.2 has also enabled additional slow statement logging. Log_slow_admin_statements create a record for any administrative statements that are not typically written to the binlog. Log_slow_slave_statements log the replicated statements sent from the master, if they are slow to complete. MySQL 5.7 does not enable logging of these statements by default.

Lc_messages_dir is the directory that contains the error message files for various languages. The variable defaults might be a little misleading in MariaDB 10.2. Lc_messages_dir is left empty by default, although it still uses the same path as MySQL 5.7. The files are located in /usr/share/mysql by default for both databases.

Performance Schema

VariableMariaDB DefaultMySQL Default
performance_schemaOFFON
performance_schema_setup_actors_size100-1 (auto adjusted)
performance_schema_setup_objects_size100-1 (auto adjusted)

 

The performance schema is an instrumentation tool that is designed to help troubleshoot various performance concerns. MySQL 5.7 enables the performance schema, and many of its instruments, by default. MySQL even goes so far as to detect the appropriate value for many Performance Schema variables instead of setting a static default. The Performance Schema does come with some overhead, and there are many blogs regarding how much this can impact performance. I think Sveta Smirnova said it best in her blog  Performance Schema Benchmarks OLTP RW: “…test on your system! No generic benchmark can exactly repeat a workload on your site.

MariaDB has disabled the Performance Schema by default, as well as adjusted a couple of the dynamic variables. Note that if you wish to disable or enable the Performance Schema, it requires a restart of the server since these variables are not dynamic. Performance_schema_setup_actors_size and performance_schema_setup_objects_size have both been set to a static 100, instead of the dynamic -1 used in MySQL 5.7. These both limit the number of rows that can be stored in relative tables. This creates a hard limit to the size these tables can grow to, helping to reduce their data footprint.

SSL/TLS

VariableMariaDB DefaultMySQL Default
ssl_caca.pem
ssl_certserver-cert.pem
ssl_keyserver-key.pem

 

Secure Sockets Layer (SSL) and Transport Layer Security (TLS) are cryptographic protocols that allow for secure communication. SSL is actually the predecessor of TLS, although both are often referred to as SSL. MySQL 5.7 and MariaDB 10.2 support both yaSSL and OpenSSL. The default configurations for SSL/TLS differ only slightly between MySQL 5.7 and MariaDB 10.2. MySQL 5.7 sets a specific file name for ssl_ca, ssl_cert, and ssl_key. These files are created in the base directory, identified by the variable basedir. Each of these variables is left blank in MariaDB 10.2, so you need to set them before using secure connections. These variables are not dynamic, so be sure to set the values before starting your database.

Query Optimizer

MariaDB 10.2MySQL 5.7OptimizationMeaningSwitch
N/AOFFBatched Key AccessControls use of BKA join algorithmbatched_key_access
N/AONBlock Nested-LoopControls use of BNL join algorithmblock_nested_loop
N/AONCondition FilteringControls use of condition filteringcondition_fanout_filter
DeprecatedONEngine Condition PushdownControls engine condition pushdownengine_condition_pushdown
ONN/AEngine Condition PushdownControls ability to push conditions down into non-mergeable views and derived tablescondition_pushdown_for_derived
ONN/AExists SubqueryAllows conversion of in statements to exists statementsexists_to_in
ONN/AExists SubqueryAllows conversion of exists statements to in statementsin_to_exists
N/AONIndex ExtensionsControls use of index extensionsuse_index_extensions
OFFN/AIndex MergeAllows index_merge for non-equality conditionsindex_merge_sort_intersection
ONN/AJoin AlgorithmsPerform index lookups for a batch of records from the join bufferjoin_cache_bka
ONN/AJoin AlgorithmsControls use of BNLH and BKAH algorithmsjoin_cache_hashed
ONN/AJoin AlgorithmsControls use of incremental algorithmsjoin_cache_incremental
ONN/AJoin AlgorithmsControls use of block-based algorithms for outer joinsouter_join_with_cache
ONN/AJoin AlgorithmsControls block-based algorithms for use with semi-join operationssemijoin_with_cache
OFFN/AJoin BufferCreates the join buffer with an estimated size based on the estimated number of rows in the resultoptimize_join_buffer_size
ONN/AMaterialized Temporary TablesAllows index creation on derived temporary tablesderived_keys
ONN/AMaterialized Temporary TablesControls use of the rowid-merge strategypartial_match_rowid_merge
ONN/AMaterialized Temporary TablesControls use of the partial_match_table-scan strategypartial_match_table_scan
OFFONMulti-Range ReadControls use of the multi-range read strategymrr
OFFONMulti-Range ReadControls use of cost-based MRR, if mrr=onmrr_cost_based
OFFN/AMulti-Range ReadEnables key ordered scans if mrr=onmrr_sort_keys
ONN/AOrder ByConsiders multiple equalities when ordering resultsordery_uses_equalities
ONN/AQuery PlanAllows the optimizer to use hidden components of InnoDB keysextended_keys
ONN/AQuery PlanControls the removal of irrelevant tables from the execution plantable_elimination
ONN/ASubqueryStores subquery results and correlation parameters for reusesubquery_cache
N/AONSubquery MaterializationControls us of cost-based materializationubquery_materialization_cost_based
N/AONSubquery Materialization &

Semi-join

Controls the semi-join duplicate weedout strategyduplicateweedout

 

The query optimizer has several variances that not only affect query performance but also how you write SQL statements. The query optimizer is substantially different between MariaDB and MySQL, so even with identical configurations you are likely to see varying performance.

The sql_mode puts restrictions on how you can write queries. MySQL 5.7 has several additional restrictions compared to MariaDB 10.2. Only_full_group_by requires that all fields in any select…group by statement are either aggregated or inside the group by clause. The optimizer doesn’t assume anything regarding the grouping, so you must specify it explicitly.

No_zero_date, and no_zero_in_date both affect how the server interprets 0’s in dates. When no_zero_date is enabled, values of ‘0000-00-00’ are permitted but produce a warning. With strict mode enabled, then the value is not permitted and produces an error. No_zero_in_date is similar, except it applies to any section of the date(month, day, or year). With this disabled, dates with 0 parts, such as ‘2017-00-16’ are allowed as is. When enabled, the date is changed to ‘0000-00-00’ without warning. Strict mode prevents the date being inserted, unless ignore is provided as well. “INSERT IGNORE” and “UPDATE IGNORE” inserts the dates as ‘0000-00-00’. 5.7.4 changed this. No_zero_in_date was consolidated with strict mode, and the explicit option is deprecated.

The query_prealloc_size determines the size of the persistent buffer used for statement parsing and execution. If you regularly use complex queries, it can be useful to increase the size of this buffer, as it does not need to allocate additional memory during the query parsing. MySQL 5.7 has set this buffer to 8192, with a block size of 1024. MariaDB increased this value in 10.1.2 up to 24576.

Query_alloc_block_size dictates the size in bytes of any extra blocks allocated during query parsing. If memory fragmentation is a common problem, you might want to look at increasing this value. MySQL 5.7 uses 8192, while MariaDB 10.2 uses 16384 (twice that). Be careful when adjusting the block sizes: going too high consumes more than the needed amount of memory, and too low causes significant fragmentation.

The optimizer_switch variable contains many different switches that impact how the query optimizer plans and performs different queries. MariaDB 10.2 and MySQL 5.7 have many differences in their enabled options, and even the available options. You can see a brief breakdown of each of the options below. Any options with N/A is not supported in that server.

Miscellaneous

VariableMariaDB DefaultMySQL Default
default_tmp_storage_engineNULLInnoDB
group_concat_max_len1048576(1M)1024(1K)
Lock_wait_timeout86400 (1 DAY)31536000 (1 YEAR)
Max_allowed_packet(16777216) 16MB4194304 (4MB)
Max_write_lock_count429496729518446744073709551615
Old_passwordsOFF0
Open_files_limit0dependent on OS
pid_file/var/lib/mysql//var/run/mysqld/
secure_file_privVaries by installation
sort_buffer_size2097152262144
table_definition_cache400autosized
table_open_cache_instances816
thread_cache_sizeautosizedautosized
thread_stack292KB192KB/256KB

 

There are many variables that do not fit well into a group. I will go over those here.

When creating temporary tables, if you do not specify a storage engine then a default is used. In MySQL 5.7 this is set to InnoDB, the same as the default_storage_engine. MariaDB 10.2 also uses InnoDB, but it is not explicitly set. MariaDB sets the default_tmp_storage_engine to NULL, which causes it to use the default_storage_engine. This is important to remember if you change your default storage engine, as it would also change the default for temporary tables. An Important note, in MariaDB this is only relevant to tables created with “CREATE TEMPORARY TABLE”. Internal in-memory temporary tables use the memory storage engine, and internal, on-disk temporary tables use the aria engine by default.

The Group_concat function can cause some very large results if left unchecked. You can restrict the maximum size of results from this function with group_concat_max_len. MySQL 5.7 limits this to 1024(1K). MariaDB increased the value in 10.2.4 up to 1048576(1M).

Lock_wait_timeout controls how long a thread waits as it attempts to acquire a metadata lock. Several statements require a metadata lock, including DDL and DML operations, Lock Tables, Flush Tables with Read Lock and Handler statements. MySQL 5.7 defaults to the maximum possible value (one year), while MariaDB 10.2 has toned this down to one day.

Max_allowed_packet sets a limit to the maximum size of a packet, or a generated/intermediate string. This value is intentionally kept small (4MB) on MySQL 5.7 in order to detect the larger, intentionally incorrect packets. MariaDB has increased this value to 16MB. If using any large BLOB fields, you need to adjust this value to the size of the largest BLOB, in multiples of 1024, or you risk running into errors transferring the results.

Max_write_lock_count controls the number of write locks that can be given before some read lock requests being processed. In extremely heavy write loads your reads can pile up while waiting for the writes to complete. Modifying the max_write_lock_count allows you to tune how many writes can occur before some reads are allowed against the table. MySQL 5.7 keeps this value at the maximum (18446744073709551615), while MariaDB 10.2 lowered this to 4294967295. One thing to note is that this is still the maximum value on MariaDB 10.2.

Old_passwords controls the hashing method used by the password function, create user and grant statements. This variable has undergone several changes in MySQL 5.7. As of 5.7.4 the valid options were MySQL 4.1 native hashing, Pre-4.1 (old) hashing, and SHA-256 hashing. Version 5.7.5 removed the “old” Pre-4.1 method, and in 5.7.6 the variable has been deprecated with the intent of removing it entirely. MariaDB 10.2 uses a simple boolean value for this variable instead of the enumerated one in MySQL 5.7, though the intent is the same. Both default the old_passwords to OFF, or 0, and allow you to enable the older method if necessary.

Open_files_limit restricts the number of file descriptors mysqld can reserve. If set to 0 (the default in MariaDB 10.2) then mysqld reserves max_connections * 5 or max_connections + table_open_cache * 2, whichever is larger. It should be noted that mysqld cannot use an amount larger than the hard limit imposed by the operating system. MySQL 5.7 is also restricted by the operating systems hard limit, but is set at runtime to the real value permitted by the system (not a calculated value).

The pid_file allows you to control where you store the process id file. This isn’t a file you typically need, but it is good to know where it is located in case some unusual errors occur. On MariaDB you can find this inside /var/lib/mysql/, while on MySQL 5.7 you will find it inside /var/run/mysqld/. You will also notice a difference in the actual name of the file. MariaDB 10.2 uses the hostname as the name of the pid, while MySQL 5.7 simply uses the process name (mysqld.pid).

Secure_file_priv is a security feature that allows you to restrict the location of files used in data import and export operations. When this variable is empty, which was the default in MySQL before 5.7.6, there is no restriction. If the value is set to NULL, import and export operations are not permitted. The only other valid value is the directory path where files can be imported from or exported to. MariaDB 10.2 defaults to empty. As of MySQL 5.7.6, the default will depend on the install_layout CMAKE option.

INSTALL_LAYOUTDEFAULT VALUE
STANDALONE,WINNULL(>=MySQL 5.7.16_,empty(<MySQL 5.7.16)
DEB,RPM,SLES,SVR4/var/lib/mysql-files
OtherMysql-files under the CMAKE_INSTALL_PREFIX value

 

Mysqld uses a sort buffer regardless of storage engine. Every session that must perform a sort allocates a buffer equal to the value of sort_buffer_size. This buffer should at minimum be large enough to contain 15 tuples. In MySQL 5.7, this defaults to 262144, while MariaDB 10.2 uses the larger value 2097152.

The table_definition_cache restricts the number of table definitions that can be cached. If you have a large number of tables, mysqld may have to read the .frm file to get this information. MySQL 5.7 auto detects the appropriate size to use, while MariaDB 10.2 defaults this value to 400. On my small test VM, MySQL 5.7 chose a value of 1400.

The table_open_cache_instances vary in implementation between MySQL and MariaDB. MySQL 5.7 creates multiple instances of the table_open_cache, each holding a portion of the tables. This helps reduce contention, as a session needs to lock only one instance of the cache for DML statements. In MySQL 5.7.7 the default was a single instance, but this was changed in MySQL 5.7.8 (increased to 16). MariaDB has a more dynamic approach to the table_open_cache. Initially there is only a single instance of the cache, and the table_open_cache_instances variable is the maximum number of instances that can be created. If contention is detected on the single cache, another instance is created and an error logged. MariaDB 10.2 suspects that the maximum eight instances it sets by default should support up to 100 CPU cores.

The thread_cache_size controls when a new thread is created. When a client disconnects the thread is stored in the cache, as long as the maximum number of threads do not exist. Although this is not typically noticeable, if your server sees hundreds of connections per second you should increase this value to so that new connections can use the cache. Thread_cache_size is an automatically detected variable in both MySQL 5.7 and MariaDB 10.2, but their methods to calculate the default vary significantly. MySQL uses a formula, with a maximum of 100: 8+ (max_connections / 100). MariaDB 10.2 uses the smaller value out of 256 or the max_connections size.

The thread_stack is the stack size for each thread. If the stack size is too small, it limits the complexity of SQL statements, the recursion depth of stored procedures and other memory-consuming actions. MySQL 5.7 defaults the stack size to 192KB on 32-bit platforms and 256KB on 64-bit systems. MariaDB 10.2 adjusted this value several times. MariaDB 10.2.0 used 290KB, 10.2.1 used 291KB and 10.2.5 used 292KB.

Conclusion

Hopefully, this helps you with the configurations options between MySQL and MariaDB. Use the comments for any questions.

Share this post

Comments (3)

  • Rick James Reply

    My opinions on some of the items:

    sync_binlog = 1 — Users are surprised when the Master’s binlog is shorter than the Slave thinks it is. This takes some explaining to them. Setting sync_binlog to 0 should be an explicit action by the DBA to say “I prefer speed over safety”. (Several other settings default that way.)

    myisam_recover_options — A common problem for a novice is when a MyISAM index is ‘corrupted’ due to a crash. Seems like ‘QUICK’ would solve that.

    key_buffer_size = 8M — Since MyISAM is going away, I prefer the smaller value. 8M is probably a good size for pre-8.0 MySQL. 8.0, with no MyISAM and with a Data Dictionary in InnoDB may be able to use 0? MariaDB, even though it continues to support MyISAM, is probably discouraging its usage. When MyISAM is used, it should be set to some percentage (20%) of available RAM; 128M is rather arbitrary.

    log_error — Going to stderr? Not only does that make it hard for the desperate user to find the errors, it may make it impossible.

    log_slow_% — When looking for what is hogging a server, turn on _admin_statements and _slave_statements. Meanwhile, but turn off log_queries_not_using_indexes as being unnecessary clutter. If a query is slow, it will show up in the slowlog; if it is a 5-row table not using an index, who cares.

    performance_schema — At some point in MySQL’s evolution, the P_S incurred an unconscionable amount of overhead. MariaDB, if you have gotten past that, please default to ON.

    default_tmp_storage_engine — Changes in MySQL 5.7, 8.0, 8.0.3 make this setting a moving target. I’ll wait for things to settle down to have an opinion.

    group_concat_max_len — 1K is all too often too small. But I am not sure 1M is advisable. I assume this is pre-allocated for each GROUP_CONCAT in a statement?

    old_passwords (and others) — It is irritating to deal with “0” versus “OFF”, and “1” versus “ON”. I understand it when a “2” comes along.

    open_files_limit — This setting is virtually useless. The OS’s limit is always in effect, and is almost always the one to bite users. That is, I don’t care that MySQL and MariaDB differ. One OS had the misguided setting of 1024, leading to lots of MySQL users puzzled over the problems it caused.

    sort_buffer_size — Without a good metric (in STATUS) to know whether this value is too big or too small, I can’t judge what value is wise.

    table*cache* — If you have more than 400 active tables, you have more serious problems.

    thread_cache_size — I thought that “0” was optimal for Windows? Anyway, the setting should depend on how much the connection count fluctuates, so a large value is rarely needed. More than 100 seems excessive.

    — Rick James
    (Background: I have responded to thousands of forum questions over the last decade, so I have a significant feel for what problems exist. Plus, I have used most versions of MySQL and its forks since 4.0.)

    October 9, 2017 at 3:40 pm
    • akrus Reply

      log_error — Going to stderr? Not only does that make it hard for the desperate user to find the errors, it may make it impossible.

      Systemd is now managing this, probably this was the reason (MariaDB now ships with unit files).

      October 9, 2017 at 3:55 pm
    • Holger Thiel Reply

      sync_binlog = 0
      As long there is no slave and you are using a HDD, you should set it to 0. It is then only relevant for the backup. The only problem is in case of a crash with a slave.

      myisam_recover_options = BACKUP,FORCE
      You simply cannot trust MyISAM.

      group_concat_max_len = 2048
      If use utf8 then use 2048 instead of 1024.

      table_open_cache
      On large server with many sessions and many tables you can have more open tables. Track “Opened_tables” as it is written in the documentation.

      October 11, 2017 at 9:56 am

Leave a Reply