MariaDB 5.3 and beyond have a number of features that are targeted at big queries and so are disabled by default.

This page describes recommended settings for IO-bound queries that shovel through lots of records.

First, turn on Batched Key Access:

# Turn on disk-ordered reads
optimizer_switch='mrr=on'
optimizer_switch='mrr_cost_based=off'

# Turn on Batched Key Access (BKA)
join_cache_level = 6

Give BKA buffer space to operate on. Ideally, it should have enough space to fit all the data examined by the query.

# Size limit for the whole join
join_buffer_space_limit = 300M

# Limit for each individual table
join_buffer_size = 100M

Turn on index_merge/sort-intersection:

optimizer_switch='index_merge_sort_intersection=on'

If your queries examine big fraction of the tables (somewhere more than ~ 30%), turn on :

# Turn on both Hash Join and Batched Key Access
join_cache_level = 8

_{This page is licensed: CC BY-SA / Gnu FDL}

Systems that get too busy can return the too_many_connections error.

When the number of threads_connected exceeds the max_connections server variable, it's time to make a change. Viewing the threads_connected status variable shows only the current number of connections, but it's more useful to see what the value has peaked at, and this is shown by the max_used_connections status variable.

This error may be a symptom of slow queries and other bottlenecks, but if the system is running smoothly this can be addressed by increasing the value of max_connections.

_{This page is licensed: CC BY-SA / Gnu FDL}

This page documents system and status variables related to the . See and for complete list of all system and status variables.

System Variables

oqgraph_allow_create_integer_latch

is a variable which determines the size of the index buffers held in memory, which affects the speed of index reads. Note that Aria tables by default make use of an alternative setting, .

A good rule of thumb for servers consisting particularly of MyISAM tables is for about 25% or more of the available server memory to be dedicated to the key buffer.

A good way to determine whether to adjust the value is to compare the value, which is the total value of requests to read an index, and the values, the total number of requests that had to be read from disk.

The ratio of key_reads to key_read_requests should be as low as possible, 1:100 is the highest acceptable, 1:1000 is better, and 1:10 is terrible.

The effective maximum size might be lower than what is set, depending on the server's available physical RAM and the per-process limit determined by the operating system.

If you don't make use of MyISAM tables at all, you can set this to a very low value, such as 64K.

_{This page is licensed: CC BY-SA / Gnu FDL}

Place holder for sample my.cnf files, customized for different memory size and storage engines. In addition, we'd like to hear from you what works for you, so the knowledge can be crowd-sourced and shared.

_{This page is licensed: CC BY-SA / Gnu FDL}

Resizing the buffer pool is performed in chunks determined by the size of the innodb_buffer_pool_chunk_size variable.

The resize operation waits until all active transactions and operations are completed, and new transactions and operations that need to access the buffer pool must wait until the resize is complete (although when decreasing the size, access is permitted when defragmenting and withdrawing pages).

Nested transactions may fail if started after the buffer pool resize has begun.

The new buffer pool size must be a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances (note that innodb_buffer_pool_instances is ignored from , and removed in MariaDB 10.6, as the buffer pool is no longer split into multiple instances). If you attempt to set a different figure, the value is automatically adjusted to a multiple of at least the attempted size. Note that adjusting the innodb_buffer_pool_chunk_size setting can result in a change in the buffer pool size.

The number of chunks as calculated by innodb_buffer_pool_size / innodb_buffer_pool_chunk_size should not exceed 1000 in order to avoid performance issues.

A background thread performs the resizing operation. The Innodb_buffer_pool_resize_status status variable shows the progress of the resizing operation, for example:

SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name                    | Value                            |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+

or

Progress is also logged in the .

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB contains many new options and optimizations which, for compatibility or other reasons, are not enabled in the default install. Enabling them helps you gain extra performance from the same hardware when upgrading from MySQL to MariaDB. This article contains information on options and settings which you should enable, or at least look in to, when making the switch.

aria-pagecache-buffer-size=##

If you are using a log of on-disk temporary tables, increase the above to as much as you can afford. See Aria Storage Engine for more details.

key-cache-segments=8

If you use/have a lot of MyISAM files, increase the above to 4 or 8. See Segmented Key Cache and for more information.

thread-handling=pool-of-threads

Threadpool is a great way to increase performance in situations where queries are relatively short and the load is CPU bound (e.g. OLTP workloads). To enable it, add the above to your my.cnf file. See Threadpool in 5.5 for more information.

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of system variables that have been added in the MariaDB 12.0 series.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of system variables that have been added in the series.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of system variables that have been added in the series.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of status variables that were added in the series.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

For system variables added since the previous long-term release, MariaDB 11.4, see:

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of status variables that were added in the MariaDB 11.4 series.

For status variables added since the previous long-term release, MariaDB 10.11, see:

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

The following status variables are associated with the . See for a complete list of status variables that can be viewed with .

See also the .

Spider_direct_aggregate

• Description: Number of times direct aggregate has happened in spider. That is, in a partitioned spider table, instead of scanning individual rows in remote tables corresponding to partitions for aggregation functions like

This is a list of that have been added in the series.

This is a list of that have been added in the series.

This is a list of that have been added in the series.

For system variables that have been removed or deprecated, see .

This is a list of that have been added in the series.

This is a list of that were added in the series.

See Also

This is a list of that were added in the series.

This is a list of system variables that have been added in the MariaDB 10.6 series. The list does not include variables that are not part of the default release.

For system variables that have been removed or deprecated, see .

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of status variables that were added in the series.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of system variables that have been added in the series.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of status variables that were added in the series.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

About Segmented Key Cache

A segmented key cache is a collection of structures for regular key caches called key cache segments. Segmented key caches mitigate one of the major problems of the simple key cache: thread contention for key cache lock (mutex). With regular key caches, every call of a key cache interface function must acquire this lock. So threads compete for this lock even in the case when they have acquired shared locks for the file and the pages they want to read from are in the key cache buffers.

When working with a segmented key cache any key cache interface function that needs only one page has to acquire the key cache lock only for the segment the page is assigned to. This makes the chances for threads not having to compete for the same key cache lock better.

Any page from a file can be placed into a buffer of only one segment. The number of the segment is calculated from the file number and the position of the page in the file, and it's always the same for the page. Pages are evenly distributed among segments.

This page documents status variables related to the (which has been merged into the main server from ). See for a complete list of status variables that can be viewed with .

See also the .

Rpl_semi_sync_master_clients

This is a list of that have been added in the series.

This is a list of that were added in the series.

The list excludes variables related to non-default storage engines and plugins that can be added to :

This is a list of that have been added in the series.

This is a list of system variables that have been added in the MariaDB 10.11 series.

For system variables added since the previous long-term release, , see:

For system variables that have been removed or deprecated, see .

_{This page is licensed: CC BY-SA / Gnu FDL}

This page documents system variables and options related to the SQL_Error_Log Plugin. See Server System Variables for a complete list of system variables and instructions on setting them.

See also the Full list of MariaDB options, system and status variables.

Options

sql_error_log

• Description: Controls how the server should treat the plugin when the server starts up.

  • Valid values are:

    • OFF - Disables the plugin without removing it from the table.

System Variables

sql_error_log_filename

• Description: The name (and optionally path) of the logfile containing the errors. Rotation will use a naming convention such as sql_error_log_filename.001. If no path is specified, the log file will be written to the .

• Command line: --sql-error-log-filename=value

• Scope: Global

sql_error_log_rate

• Description: The logging sampling rate. Setting to 10, for example, means that one in ten errors will be logged. If set to zero, logging is disabled. The default, 1, logs every error.

• Command line: --sql-error-log-rate=#

• Scope: Global

sql_error_log_rotate

• Description: Setting to #1forces log rotation.

• Command line: --sql-error-log-rate[={0|1}]

• Scope: Global

• Dynamic: Yes

sql_error_log_rotations

• Description: Number of rotations before the log is removed. When rotated, the current log file is stored and a new, empty, log is created. Any rotations older than this setting are removed.

• Command line: --sql-error-log-rotations=#

• Scope: Global

• Dynamic: Yes

sql_error_log_size_limit

• Description: The log file size limit in bytes. After reaching this size, the log file is rotated.

• Command line: --sql-error-log-size-limit=#

• Scope: Global

• Dynamic: No

sql_error_log_warnings

• Description: If set, log warnings in addition to errors.

• Command line: --sql-error-log-warnings={0,1}

• Scope: Global

• Dynamic: Yes

sql_error_log_with_db_and_thread_info

• Description: If enabled, it prints the database name and the thread ID in the log in addition to already existing columns.

• Command line: --sql-error-log-with-db-and-thread-info=value

• Scope: Global

• Dynamic: No

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of system variables that have been added in the MariaDB 11.4 series.

For system variables added since the previous long-term release, , see:

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of system variables that have been added in the series. The list does not include variables that are not part of the default release.

For system variables that have been removed or deprecated, see .

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of that were added in the series.

This is a list of that have been added in the series. The list does not include variables that are not part of the default release.

This is a list of system variables that have been added in the series. The list does not include variables that are not part of the default release.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of that were added in the series.

This is a list of that have been added in the series. The list does not include variables that are not part of the default release.

This is a list of that have been added in the series.

This is a list of status variables that were added in the series.

The list excludes status related to the following storage engines included in :

• Mroonga Status Variables

• Spider Status Variables

See also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of that have been added in the series.

This is a list of system variables that were added in the series.

The list excludes the following variables, related to storage engines and plugins included in :

• Spider System Variables

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a list of that were added in the series.

The list excludes status related to the following storage engines included in :

can be a useful variable to adjust to improve performance.

Each concurrent session accessing the same table does so independently. This improves performance, although it comes at a cost of extra memory usage.

table_open_cache indicates the maximum number of tables the server can keep open in any one table cache instance. Ideally, you'd like this set so as to re-open a table as infrequently as possible.

However, note that this is not a hard limit. When the server needs to open a table, it evicts the least recently used closed table from the cache, and adds the new table. If all tables are used, the server adds the new table and does not evict any table. As soon as a table is not used anymore, it will be evicted from the list even if no table needs to be open, until the number of open tables will be equal to table_open_cache

table_open_cache has defaulted to 2000 since . Before that, the default was 400.

You can view the current setting in the my.cnf file, or by running:

To evaluate whether you could do with a higher table_open_cache, look at the number of opened tables, in conjunction with the server uptime ( and status variables):