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 hash join:

# 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}

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 Segmented Key Cache Performance 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}

key_buffer_size is a MyISAM 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, aria-pagecache-buffer-size.

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 key_read_requests value, which is the total value of requests to read an index, and the key_reads 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}

table_open_cache 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 MariaDB 10.1.7. Before that, the default was 400.

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

SELECT @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
|                400 |
+--------------------+

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 (Opened_tables and Uptime status variables):

SHOW global status LIKE 'opened_tables';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Opened_tables | 354858 |
+---------------+--------+

If the number of opened tables is increasing rapidly, you should look at increasing the table_open_cache value. Try to find a value that sees a slow, or possibly even no, increase in the number of opened tables.

Make sure that your operating system can cope with the number of open file descriptors required by the table_open_cache setting. If table_open_cache is set too high, MariaDB may start to refuse connections as the operating system runs out of file descriptors. Also note that the MyISAM (and Aria?) storage engines need two file descriptors per open table.

It's possible that the open_table_cache can even be reduced.

If your number of open_tables has not yet reached the table_open_cache_size, and the server has been up a while, you can look at decreasing the value.

SHOW global status LIKE 'open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 354   |
+---------------+-------+

The open table cache can be emptied with FLUSH TABLES or with the flush-tables or refresh mariadb-admin commands.

Automatic Creation of New Table Open Cache Instances

MariaDB Server can create multiple instances of the table open cache. It initially creates just a single instance. However, whenever it detects contention on the existing instances, it will automatically create a new instance. When the number of instances has been increased due to contention, it does not decrease again.

When MariaDB Server creates a new instance, it prints a message like the following to the error log:

[Note] Detected table cache mutex contention at instance 1: 25% waits. Additional 
  table cache instance activated. Number of instances after activation: 2.

The maximum number of instances is defined by the table_open_cache_instances system variable. The default value of the table_open_cache_instances system variable is 8, which is expected to handle up to 100 CPU cores. If your system is larger than this, then you may benefit from increasing the value of this system variable.

Depending on the ratio of actual available file handles, and table_open_cache size, the max. instance count may be auto adjusted to a lower value on server startup.

The implementation and behavior of this feature is different than the same feature in MySQL 5.6.

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

This page documents system and status variables related to the OQGRAPH storage engine. See Server Status Variables and Server System Variables for complete list of all system and status variables.

System Variables

oqgraph_allow_create_integer_latch

• Description: Created when the OQGRAPH storage engine is installed, if set to 1 (0 is default), permits the latch field to be an integer (see OQGRAPH Overview). This deprecated feature was removed in MariaDB 11.5.

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default Value: 0

• Removed: MariaDB 11.5.

Status Variables

Oqgraph_boost_version

• Description: OQGRAPH boost version.

• Scope: Global, Session

• Data Type: string

Oqgraph_compat_mode

• Description: Whether or not legacy tables with integer latches are supported.

• Scope: Global, Session

• Data Type: string

Oqgraph_verbose_debug

• Description: Whether or not verbose debugging is enabled. If it is, performance may be adversely impacted

• Scope: Global, Session

• Data Type: string

_{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}

About Segmented Key Cache

A segmented key cache is a collection of structures for regular MyISAM 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.

The idea and the original code of the segmented key cache was provided by Fredrik Nylander from Stardoll.com. The code was extensively reworked, improved, and eventually merged into MariaDB by Igor Babaev from Monty Program (now MariaDB Corporation).

You can find some benchmark results comparing various settings on the Segmented Key Cache Performance page.

Segmented Key Cache Syntax

New global variable: key_cache_segments. This variable sets the number of segments in a key cache. Valid values for this variable are whole numbers between 0 and 64. If the number of segments is set to a number greater than 64 the number of segments will be truncated to 64 and a warning will be issued. A value of 0 means the key cache is a regular (i.e. non-segmented) key cache. This is the default. If key_cache_segments is1 (or higher) then the new key cache segmentation code is used. In practice there is no practical use of a single-segment segmented key cache except for testing purposes, and settingkey_cache_segments = 1 should be slower than any other option and should not be used in production.

Other global variables used when working with regular key caches also apply to segmented key caches: key_buffer_size,key_cache_age_threshold, key_cache_block_size, and key_cache_division_limit.

Segmented Key Cache Statistics

Statistics about the key cache can be found by looking at the KEY_CACHES table in the INFORMATION_SCHEMA database. Columns in this table are:

See Also

• Segmented Key Cache Performance

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

This page documents status variables related to the Semisynchronous Replication Plugin (which has been merged into the main server from MariaDB 10.3.3). See Server Status Variables for a complete list of status variables that can be viewed with SHOW STATUS.

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

Rpl_semi_sync_master_clients

• Description: Number of semisynchronous slaves.

• Data Type: numeric

Rpl_semi_sync_master_get_ack

• Description: Number of acknowledgements received by the master from slaves.

• Data Type: numeric

Rpl_semi_sync_master_net_avg_wait_time

• Description: Average time the master waited for a slave to reply, in microseconds.

• Data Type: numeric

Rpl_semi_sync_master_net_wait_time

• Description: Total time the master waited for slave replies, in microseconds.

• Data Type: numeric

Rpl_semi_sync_master_net_waits

• Description: Total number of times the master waited for slave replies.

• Data Type: numeric

Rpl_semi_sync_master_no_times

• Description: Number of times the master turned off semisynchronous replication. The global value can be flushed by FLUSH STATUS.

• Data Type: numeric

Rpl_semi_sync_master_no_tx

• Description: Number of commits that have not been successfully acknowledged by a slave. The global value can be flushed by FLUSH STATUS.

• Data Type: numeric

Rpl_semi_sync_master_request_ack

• Description: Number of acknowledgement requests sent to slaves.

• Data Type: numeric

Rpl_semi_sync_master_status

• Description: Whether or not semisynchronous replication is currently operating on the master. The value will be ON if both the plugin has been enabled and a commit acknowledgment has occurred. It will be OFF if either the plugin has not been enabled, or the master is replicating asynchronously due to a commit acknowledgment timeout.

• Data Type: boolean

Rpl_semi_sync_master_timefunc_failures

• Description: Number of times the master failed when calling a time function, such as gettimeofday(). The global value can be flushed by FLUSH STATUS.

• Data Type: numeric

Rpl_semi_sync_master_tx_avg_wait_time

• Description: Average time the master waited for each transaction, in microseconds.

• Data Type: numeric

Rpl_semi_sync_master_tx_wait_time

• Description: Total time the master waited for transactions, in microseconds.

• Data Type: numeric

Rpl_semi_sync_master_tx_waits

• Description: Total number of times the master waited for transactions.

• Data Type: numeric

Rpl_semi_sync_master_wait_pos_backtraverse

• Description: Total number of times the master waited for an event that had binary coordinates lower than previous events waited for. Occurs if the order in which transactions start waiting for a reply is different from the order in which their binary log events were written. The global value can be flushed by FLUSH STATUS.

• Data Type: numeric

Rpl_semi_sync_master_wait_sessions

• Description: Number of sessions that are currently waiting for slave replies.

• Data Type: numeric

Rpl_semi_sync_master_yes_tx

• Description: Number of commits that have been successfully acknowledged by a slave. The global value can be flushed by FLUSH STATUS.

• Data Type: numeric

Rpl_semi_sync_slave_status

• Description: Whether or not semisynchronous replication is currently operating on the slave. ON if both semi-sync has been enabled for the replica (i.e. by setting the variable rpl_semi_sync_slave_enabled to TRUE) and the slave I/O thread is running.

• Data Type: boolean

Rpl_semi_sync_slave_send_ack

• Description: Number of acknowledgements the slave sent to the master.

• Data Type: numeric

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

See Server Status Variables for a complete list of status variables that can be viewed with SHOW STATUS.

Much of the InnoDB information here can also be seen with a SHOW ENGINE INNODB STATUS statement.

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

Innodb_adaptive_hash_cells

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5, this status variable is present in XtraDB.

  • In MariaDB 10.1 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.0.0

Innodb_adaptive_hash_hash_searches

• Description: Hash searches as shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • Before the variable was introduced in MariaDB 10.5.0, use the adaptive_hash_searches counter in the information_schema.INNODB_METRICS table instead.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.5.0

Innodb_adaptive_hash_heap_buffers

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5, this status variable is present in XtraDB.

  • In MariaDB 10.1 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.0.0

Innodb_adaptive_hash_non_hash_searches

• Description: Non-hash searches as shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output. From MariaDB 10.6.2, not updated if innodb_adaptive_hash_index is not enabled (the default).

  • In MariaDB 10.1, MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present. Use the adaptive_hash_searches_btree counter in the information_schema.INNODB_METRICS table instead.

  • From MariaDB 10.5, this status variable is present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5, MariaDB 10.5.0

Innodb_async_reads_pending

• Description: Number of async read I/O operations currently in progress (from SUBMITTED to COMPLETED). See InnoDB Asynchronous I/O.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_reads_queue_size

• Description: Current size of the async I/O read queue. See InnoDB Asynchronous I/O Queuing Mechanism.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_reads_tasks_running

• Description: Number of async read I/O operations currently in the EXECUTING_COMPLETION_TASK state. See InnoDB Asynchronous I/O.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_reads_total_count

• Description: Total number of async read completion tasks that have finished execution. See InnoDB Asynchronous I/O.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_reads_total_enqueues

• Description: Total number of async read operations that were queued. Includes those still waiting and making up innodb_async_reads_queue_size. See InnoDB Asynchronous I/O.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_reads_wait_slot_sec

• Description: Total wait time for a free I/O slot (see Waiting for IO Slots). See InnoDB Asynchronous I/O Waiting for IO Slots.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_writes_pending

• Description: Number of async write I/O operations currently in progress (from SUBMITTED to COMPLETED). See InnoDB Asynchronous I/O.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_writes_queue_size

• Description: Current size of the async I/O write queue. See InnoDB Asynchronous I/O Queuing Mechanism.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_writes_tasks_running

• Description: Number of async write I/O operations currently in the EXECUTING_COMPLETION_TASK state. See InnoDB Asynchronous I/O.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_writes_total_count

• Description: Total number of async write completion tasks that have finished execution. See InnoDB Asynchronous I/O.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_writes_total_enqueues

• Description: Total number of async write operations that were queued. Includes those still waiting and making up innodb_async_writes_queue_size. See InnoDB Asynchronous I/O.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_async_writes_wait_slot_sec

• Description: See InnoDB Asynchronous I/O.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 11.5

Innodb_available_undo_logs

• Description: Total number available InnoDB undo logs. Differs from the innodb_undo_logs system variable, which specifies the number of active undo logs.

• Scope: Global

• Data Type: numeric

Innodb_background_log_sync

• Description: As shown in the BACKGROUND THREAD section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5, this status variable is present in XtraDB.

  • In MariaDB 10.1, MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB only), MariaDB 10.5.0

Innodb_buffer_pool_bytes_data

• Description: Number of bytes contained in the InnoDB buffer pool, both dirty (modified) and clean (unmodified). See also Innodb_buffer_pool_pages_data, which can contain pages of different sizes in the case of compression.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_bytes_dirty

• Description: Number of dirty (modified) bytes contained in the InnoDB buffer pool. See also Innodb_buffer_pool_pages_dirty, which can contain pages of different sizes in the case of compression.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_dump_status

• Description: A text description of the progress or final status of the last Innodb buffer pool dump.

• Scope: Global

• Data Type: string

• Introduced: MariaDB 10.0.0

Innodb_buffer_pool_load_incomplete

• Description: Whether or not the loaded buffer pool is incomplete, for example after a shutdown or abort during innodb buffer pool load from file caused an incomplete save.

• Scope: Global

• Data Type: boolean

• Introduced: MariaDB 10.3.5

Innodb_buffer_pool_load_status

• Description: A text description of the progress or final status of the last Innodb buffer pool load.

• Scope: Global

• Data Type: string

• Introduced: MariaDB 10.0.0

Innodb_buffer_pool_pages_data

• Description: Number of InnoDB buffer pool pages which contain data, both dirty (modified) and clean (unmodified). See also Innodb_buffer_pool_bytes_data.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_pages_dirty

• Description: Number of InnoDB buffer pool pages which contain dirty (modified) data. See also innodb_buffer_pool_bytes_dirty.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_pages_flushed

• Description: Number of InnoDB buffer pool pages which have been flushed.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_pages_LRU_flushed

• Description: Flush list as shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_buffer_pool_pages_LRU_freed

• Description: Monitor the number of pages that were freed by a buffer pool LRU eviction scan, without flushing.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.6.0

Innodb_buffer_pool_pages_free

• Description: Number of free InnoDB buffer pool pages.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_pages_made_not_young

• Description: Pages not young as shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_buffer_pool_pages_made_young

• Description: Pages made young as shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_buffer_pool_pages_misc

• Description: Number of InnoDB buffer pool pages set aside for internal use.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_pages_old

• Description: Old database page, as shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present for XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_buffer_pool_pages_total

• Description: Total number of InnoDB buffer pool pages.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_read_ahead

• Description: Number of pages read into the InnoDB buffer pool by the read-ahead background thread.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_read_ahead_evicted

• Description: Number of pages read into the InnoDB buffer pool by the read-ahead background thread that were evicted without having been accessed by queries.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_read_ahead_rnd

• Description: Number of random read-aheads.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_read_requests

• Description: Number of requests to read from the InnoDB buffer pool .

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_reads

• Description: Number of reads that could not be satisfied by the InnoDB buffer pool and had to be read from disk.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_resize_status

• Description: Progress of the dynamic InnoDB buffer pool resizing operation. See Setting Innodb Buffer Pool Size Dynamically.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.2.2

Innodb_buffer_pool_wait_free

• Description: Number of times InnoDB waited for a free page before reading or creating a page. Normally, writes to the InnoDB buffer pool happen in the background. When no clean pages are available, dirty pages are flushed first in order to free some up. This counts the numbers of wait for this operation to finish. If this value is not small, look at increasing innodb_buffer_pool_size.

• Scope: Global

• Data Type: numeric

Innodb_buffer_pool_write_requests

• Description: Number of requests to write to the InnoDB buffer pool .

• Scope: Global

• Data Type: numeric

Innodb_buffered_aio_submitted

• Description:

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.5.0

Innodb_bulk_operations

• Description: Number of bulk insert operations to InnoDB tables.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.6.19, MariaDB 10.11.9, MariaDB 11.1.6, MariaDB 11.2.5, MariaDB 11.4.3

Innodb_checkpoint_age

• Description: The amount of data written to the InnoDB redo log since the last checkpoint, as shown in the LOG section of the SHOW ENGINE INNODB STATUS output. (This is equivalent to subtracting "Last checkpoint at" from "Log sequence number", cf the RedoLog details). Despite the name ("age"), the value is not a duration, but really an amount of data (in bytes) !

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_checkpoint_max_age

• Description: Max checkpoint age, as shown in the LOG section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_checkpoint_target_age

• Description: Checkpoint age target, as shown in the LOG section of the SHOW ENGINE INNODB STATUS output. XtraDB only. Removed in MariaDB 10.0 and replaced with MySQL 5.6's flushing implementation.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.0

Innodb_current_row_locks

• Description: Number of current row locks on InnoDB tables as shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output. Renamed from InnoDB_row_lock_numbers in XtraDB 5.5.8-20.1.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

Innodb_data_fsyncs

• Description: Number of InnoDB fsync (sync-to-disk) calls. fsync call frequency can be influenced by the innodb_flush_method configuration option.

• Scope: Global

• Data Type: numeric

Innodb_data_pending_fsyncs

• Description: Number of pending InnoDB fsync (sync-to-disk) calls. fsync call frequency can be influenced by the innodb_flush_method configuration option.

• Scope: Global

• Data Type: numeric

Innodb_data_pending_reads

• Description: Number of pending InnoDB reads.

• Scope: Global

• Data Type: numeric

Innodb_data_pending_writes

• Description: Number of pending InnoDB writes.

• Scope: Global

• Data Type: numeric

Innodb_data_read

• Description: Number of InnoDB bytes read since server startup (not to be confused with Innodb_data_reads).

• Scope: Global

• Data Type: numeric

Innodb_data_reads

• Description: Number of InnoDB read operations (not to be confused with Innodb_data_read).

• Scope: Global

• Data Type: numeric

Innodb_data_writes

• Description: Number of InnoDB write operations.

• Scope: Global

• Data Type: numeric

Innodb_data_written

• Description: Number of InnoDB bytes written since server startup. From MariaDB 10.5.1, no longer includes writes to the redo log file ib_logfile0, which continue to be counted by Innodb_os_log_written. An error in counting was introduced in MariaDB 10.5.7 until MariaDB 10.5.20, MariaDB 10.6.13, MariaDB 10.8.8, MariaDB 10.9.6, MariaDB 10.10.4 and MariaDB 10.11.3 (MDEV-31124) in which writes via the doublewrite buffer started to be counted incorrectly, without multiplying them by innodb_page_size. A workaround for the error could be the following formulae: real_data_written = Innodb_data_written + (innodb_page_size - 1) * Innodb_dblwr_pages_writteninnodb_written = real_data_written + Innodb_os_log_written

• Scope: Global

• Data Type: numeric

Innodb_dblwr_pages_written

• Description: Number of pages written to the InnoDB doublewrite buffer.

• Scope: Global

• Data Type: numeric

Innodb_dblwr_writes

• Description: Number of writes to the InnoDB doublewrite buffer.

• Scope: Global

• Data Type: numeric

Innodb_deadlocks

• Description: Total number of InnoDB deadlocks. Deadlocks occur when at least two transactions are waiting for the other to finish, creating a circular dependency. InnoDB usually detects these quickly, returning an error.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_defragment_compression_failures

• Description: Number of defragment re-compression failures. See Defragmenting InnoDB Tablespaces.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.1

Innodb_defragment_count

• Description: Number of defragment operations. See Defragmenting InnoDB Tablespaces.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.1

Innodb_defragment_failures

• Description: Number of defragment failures. See Defragmenting InnoDB Tablespaces.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.1

Innodb_dict_tables

• Description: Number of entries in the XtraDB data dictionary cache. This Percona XtraDB variable was removed in MariaDB 10/XtraDB 5.6 as it was replaced with MySQL 5.6's table_definition_cache implementation.

• Scope: Global

• Data Type: numeric

• Introduced: XtraDB 5.0.77-b13

• Removed: MariaDB 10.0

Innodb_encryption_n_merge_blocks_decrypted

• Description:

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.28, MariaDB 10.2.9, MariaDB 10.3.2

Innodb_encryption_n_merge_blocks_encrypted

• Description:

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.28, MariaDB 10.2.9, MariaDB 10.3.2

Innodb_encryption_n_rowlog_blocks_decrypted

• Description:

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.28, MariaDB 10.2.9, MariaDB 10.3.2

Innodb_encryption_n_rowlog_blocks_encrypted

• Description:

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.28, MariaDB 10.2.9, MariaDB 10.3.2

Innodb_encryption_n_temp_blocks_decrypted

• Description:

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.2.26, MariaDB 10.3.17, MariaDB 10.4.7

Innodb_encryption_n_temp_blocks_encrypted

• Description:

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.2.26, MariaDB 10.3.17, MariaDB 10.4.7

Innodb_encryption_num_key_requests

• Description: Was not present in MariaDB 10.5.2.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.2.4

Innodb_encryption_rotation_estimated_iops

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.1.3

Innodb_encryption_rotation_pages_flushed

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.1.3

Innodb_encryption_rotation_pages_modified

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.1.3

Innodb_encryption_rotation_pages_read_from_cache

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.1.3

Innodb_encryption_rotation_pages_read_from_disk

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.1.3

Innodb_have_atomic_builtins

• Description: Whether the server has been built with atomic instructions, provided by the CPU ensuring that critical low-level operations can't be interrupted. XtraDB only.

• Scope: Global

• Data Type: boolean

Innodb_have_bzip2

• Description: Whether the server has the bzip2 compression method available. See InnoDB/XtraDB Page Compression.

• Scope: Global

• Data Type: boolean

• Introduced: MariaDB 10.1.0

Innodb_have_lz4

• Description: Whether the server has the lz4 compression method available. See InnoDB/XtraDB Page Compression.

• Scope: Global

• Data Type: boolean

• Introduced: MariaDB 10.1.0

Innodb_have_lzma

• Description: Whether the server has the lzma compression method available. See InnoDB/XtraDB Page Compression.

• Scope: Global

• Data Type: boolean

• Introduced: MariaDB 10.1.0

Innodb_have_lzo

• Description: Whether the server has the lzo compression method available. See InnoDB/XtraDB Page Compression.

• Scope: Global

• Data Type: boolean

• Introduced: MariaDB 10.1.0

Innodb_have_punch_hole

• Description:

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.2.4

Innodb_have_snappy

• Description: Whether the server has the snappy compression method available. See InnoDB/XtraDB Page Compression.

• Scope: Global

• Data Type: boolean

• Introduced: MariaDB 10.1.3

Innodb_history_list_length

• Description: History list length as shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output. XtraDB only until introduced in MariaDB 10.5.0.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_discarded_delete_marks

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_discarded_deletes

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_discarded_inserts

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_free_list

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_merged_delete_marks

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_merged_deletes

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_merged_inserts

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_merges

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_segment_size

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_ibuf_size

• Description: As shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_instant_alter_column

• Description: See Instant ADD COLUMN for InnoDB.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.3.2

Innodb_log_waits

• Description: Number of times InnoDB was forced to wait for log writes to be flushed due to the log buffer being too small.

• Scope: Global

• Data Type: numeric

Innodb_log_write_requests

• Description: Number of requests to write to the InnoDB redo log.

• Scope: Global

• Data Type: numeric

Innodb_log_writes

• Description: Number of writes to the InnoDB redo log.

• Scope: Global

• Data Type: numeric

Innodb_lsn_current

• Description: Log sequence number as shown in the LOG section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_lsn_flushed

• Description: Flushed up to log sequence number as shown in the LOG section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_lsn_last_checkpoint

• Description: Log sequence number last checkpoint as shown in the LOG section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_master_thread_1_second_loops

• Description: As shown in the BACKGROUND THREAD section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5, this status variable is present in XtraDB.

  • In MariaDB 10.1 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.0

Innodb_master_thread_10_second_loops

• Description: As shown in the BACKGROUND THREAD section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5, this status variable is present in XtraDB.

  • In MariaDB 10.1 and later, this status variable is not present

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.0

Innodb_master_thread_active_loops

• Description: As shown in the BACKGROUND THREAD section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.0.9 (XtraDB-only), MariaDB 10.5.0:

Innodb_master_thread_background_loops

• Description: As shown in the BACKGROUND THREAD section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5, this status variable is present in XtraDB.

  • In MariaDB 10.1 and later, this status variable is not present

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.0

Innodb_master_thread_idle_loops

• Description: As shown in the BACKGROUND THREAD section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.0.9 (XtraDB-only), MariaDB 10.5.0:

Innodb_master_thread_main_flush_loops

• Description: As shown in the BACKGROUND THREAD section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5, this status variable is present in XtraDB.

  • In MariaDB 10.1 and later, this status variable is not present

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.0

Innodb_master_thread_sleeps

• Description: As shown in the BACKGROUND THREAD section of the SHOW ENGINE INNODB STATUS output. XtraDB only.

  • In MariaDB 5.5, this status variable is present in XtraDB.

  • In MariaDB 10.1, MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present. Use the innodb_master_thread_sleeps counter in the information_schema.INNODB_METRICS table instead.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.0

Innodb_max_trx_id

• Description: As shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_mem_adaptive_hash

• Description: As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_mem_dictionary

• Description: As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this status variable is not present.

  • In MariaDB 10.5, this status variable was reintroduced.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_mem_total

• Description: As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

Innodb_mutex_os_waits

• Description: Mutex OS waits as shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

Innodb_mutex_spin_rounds

• Description: Mutex spin rounds as shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

Innodb_mutex_spin_waits

• Description: Mutex spin waits as shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

Innodb_num_index_pages_written

• Description:

• Scope:

• Data Type: numeric

• Introduced: MariaDB 10.1.0

Innodb_num_non_index_pages_written

• Description:

• Scope:

• Data Type: numeric

• Introduced: MariaDB 10.1.0

Innodb_num_open_files

• Description: Number of open files held by InnoDB. InnoDB only.

• Scope: Global

• Data Type: numeric

Innodb_num_page_compressed_trim_op

• Description: Number of trim operations performed.

• Scope: Global

• Data Type: numeric

Innodb_num_page_compressed_trim_op_saved

• Description: Number of trim operations not done because of an earlier trim.

• Scope: Global

• Data Type: numeric

Innodb_num_pages_decrypted

• Description: Number of pages page decrypted. See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

Innodb_num_pages_encrypted

• Description: Number of pages page encrypted. See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

Innodb_num_pages_page_compressed

• Description: Number of pages that are page compressed.

• Scope: Global

• Data Type: numeric

Innodb_num_pages_page_compression_error

• Description: Number of compression errors.

• Scope: Global

• Data Type: numeric

Innodb_num_pages_page_decompressed

• Description: Number of pages compressed with page compression that are decompressed.

• Scope: Global

• Data Type: numeric

Innodb_num_pages_page_encryption_error

• Description: Number of page encryption errors. See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.3

• Removed: MariaDB 10.1.4

Innodb_oldest_view_low_limit_trx_id

• Description: As shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.2

Innodb_onlineddl_pct_progress

• Description: Shows the progress of in-place alter table. It might be not so accurate because in-place alter is highly dependent on disk and buffer pool status. See Monitoring progress and temporal memory usage of Online DDL in InnoDB.

• Scope: Global

• Data Type: numeric

Innodb_onlineddl_rowlog_pct_used

• Description: Shows row log buffer usage in 5-digit integer (10000 means 100.00%). See Monitoring progress and temporal memory usage of Online DDL in InnoDB.

• Scope: Global

• Data Type: numeric

Innodb_onlineddl_rowlog_rows

• Description: Number of rows stored in the row log buffer. See Monitoring progress and temporal memory usage of Online DDL in InnoDB.

• Scope: Global

• Data Type: numeric

Innodb_os_log_fsyncs

• Description: Number of InnoDB log fsync (sync-to-disk) requests.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.8

Innodb_os_log_pending_fsyncs

• Description: Number of pending InnoDB log fsync (sync-to-disk) requests.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.8

Innodb_os_log_pending_writes

• Description: Number of pending InnoDB log writes.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.8

Innodb_os_log_written

• Description: Number of bytes written to the InnoDB log.

• Scope: Global

• Data Type: numeric

Innodb_page_compression_saved

• Description: Number of bytes saved by page compression.

• Scope:

• Data Type:

Innodb_page_compression_trim_sect512

• Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 512 byte block-size.

• Scope:

• Data Type: numeric

• Introduced: MariaDB 10.1.0, MariaDB 10.0.15 Fusion-io

• Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect1024

• Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 1K block-size.

• Scope:

• Data Type: numeric

• Introduced: MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io

• Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect2048

• Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 2K block-size.

• Scope:

• Data Type: numeric

• Introduced: MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io

• Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect4096

• Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 4K block-size.

• Scope:

• Data Type: numeric

• Introduced: MariaDB 10.1.0, MariaDB 10.0.15 Fusion-io

• Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect8192

• Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 8K block-size.

• Scope:

• Data Type: numeric

• Introduced:MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io

• Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect16384

• Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 16K block-size.

• Scope:

• Data Type: numeric

• Introduced: MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io

• Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect32768

• Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 32K block-size.

• Scope:

• Data Type: numeric

• Introduced: MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io

• Removed: MariaDB 10.2.4

Innodb_page_size

• Description: Page size used by InnoDB. Defaults to 16KB, can be compiled with a different value.

• Scope: Global

• Data Type: numeric

Innodb_pages_created

• Description: Number of InnoDB pages created.

• Scope: Global

• Data Type: numeric

Innodb_pages_read

• Description: Number of InnoDB pages read.

• Scope: Global

• Data Type: numeric

Innodb_pages0_read

• Description: Counter for keeping track of reads of the first page of InnoDB data files, because the original implementation of data-at-rest-encryption for InnoDB introduced new code paths for reading the pages. Removed in MariaDB 10.4 as the extra reads of the first page were removed, and the encryption subsystem will be initialized whenever we first read the first page of each data file, in fil_node_open_file().

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.2.4, MariaDB 10.1.21

• Removed: MariaDB 10.4

Innodb_pages_written

• Description: Number of InnoDB pages written.

• Scope: Global

• Data Type: numeric

Innodb_purge_trx_id

• Description: Purge transaction id as shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.2

Innodb_purge_undo_no

• Description: As shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.2

Innodb_read_views_memory

• Description: As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output. Shows the total of memory in bytes allocated for the InnoDB read view.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5.32

• Removed: MariaDB 10.2

Innodb_row_lock_current_waits

• Description: Number of pending row lock waits on InnoDB tables.

• Scope: Global

• Data Type: numeric

Innodb_row_lock_numbers

• Description: Number of current row locks on InnoDB tables as shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output. Renamed to InnoDB_current_row_locks in XtraDB 5.5.10-20.1.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5 / XtraDB 5.5.8-20

• Removed: MariaDB 5.5 / XtraDB 5.5.10-20.1

Innodb_row_lock_time

• Description: Total time in milliseconds spent getting InnoDB row locks.

• Scope: Global

• Data Type: numeric

Innodb_row_lock_time_avg

• Description: Average time in milliseconds spent getting an InnoDB row lock.

• Scope: Global

• Data Type: numeric

Innodb_row_lock_time_max

• Description: Maximum time in milliseconds spent getting an InnoDB row lock.

• Scope: Global

• Data Type: numeric

Innodb_row_lock_waits

• Description: Number of times InnoDB had to wait before getting a row lock.

• Scope: Global

• Data Type: numeric

Innodb_rows_deleted

• Description: Number of rows deleted from InnoDB tables that where not system tables. Almost equivalent to Handler_delete which does include system tables.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10

Innodb_rows_inserted

• Description: Number of rows inserted into InnoDB tables that where not system tables. No direct equivalent in Handler status variables.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10

Innodb_rows_read

• Description: Number of rows read from InnoDB tables that where not system tables. Almost equivalent to the sum of Handler_read* status variables which do include system tables.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10

Innodb_rows_updated

• Description: Number of rows updated in InnoDB tables that where not system tables. Almost equivalent to Handler_update which does include system tables.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10

Innodb_s_lock_os_waits

• Description: As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.2

Innodb_s_lock_spin_rounds

• Description: As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.2

Innodb_s_lock_spin_waits

• Description: As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.2

Innodb_scrub_background_page_reorganizations

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.3

• Removed: MariaDB 10.5.2

Innodb_scrub_background_page_split_failures_missing_index

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.3

• Removed: MariaDB 10.5.2

Innodb_scrub_background_page_split_failures_out_of_filespace

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.1.3

• Removed: MariaDB 10.5.2

Innodb_scrub_background_page_split_failures_underflow

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.5.2

Innodb_scrub_background_page_split_failures_unknown

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.5.2

Innodb_scrub_background_page_splits

• Description: See Table and Tablespace Encryption.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.5.2

Innodb_scrub_log

• Description:

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.2.4

• Removed: MariaDB 10.5.2

Innodb_secondary_index_triggered_cluster_reads

• Description: Used to track the effectiveness of the Prefix Index Queries Optimization (MDEV-6929). Removed in MariaDB 10.10 as the optimization is now always enabled.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10

Innodb_secondary_index_triggered_cluster_reads_avoided

• Description: Used to track the effectiveness of the Prefix Index Queries Optimization (MDEV-6929). Removed in MariaDB 10.10 as the optimization is now always enabled.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10

Innodb_system_rows_deleted

• Description: Number of rows deleted on system tables.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10

Innodb_system_rows_inserted

• Description: Number of rows inserted on system tables.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10

Innodb_system_rows_read

• Description: Number of rows read on system tables.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10.0

Innodb_system_rows_updated

• Description: Number of rows updated on system tables.

• Scope: Global

• Data Type: numeric

• Removed: MariaDB 10.10.0

Innodb_truncated_status_writes

• Description: Number of times output from SHOW ENGINE INNODB STATUS has been truncated.

• Scope: Global

• Data Type: numeric

Innodb_undo_truncations

• Description: Number of undo tablespace truncation operations.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 10.3.10

Innodb_x_lock_os_waits

• Description: As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.2

Innodb_x_lock_spin_rounds

• Description: As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.2

Innodb_x_lock_spin_waits

• Description: As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.

  • In MariaDB 5.5 and MariaDB 10.1, this status variable is present in XtraDB.

  • In MariaDB 10.2 and later, this status variable is not present.

• Scope: Global

• Data Type: numeric

• Introduced: MariaDB 5.5

• Removed: MariaDB 10.2

_{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 MariaDB 10.5, 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

SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name                    | Value                                              |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 161103 16:26:54. |
+----------------------------------+----------------------------------------------------+

Progress is also logged in the error log.

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

This page documents status variables related to the Sphinx storage engine. See Server Status Variables for a complete list of status variables that can be viewed with SHOW STATUS.

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

Sphinx_error

• Description: See SHOW ENGINE SPHINX STATUS.

• Scope: Global, Session

• Data Type: numeric

Sphinx_time

• Description: See SHOW ENGINE SPHINX STATUS.

• Scope: Global, Session

• Data Type: numeric

Sphinx_total

• Description: See SHOW ENGINE SPHINX STATUS.

• Scope: Global, Session

• Data Type: numeric

Sphinx_total_found

• Description: See SHOW ENGINE SPHINX STATUS.

• Scope: Global, Session

• Data Type: numeric

Sphinx_word_count

• Description: See SHOW ENGINE SPHINX STATUS.

• Scope: Global, Session

• Data Type: numeric

Sphinx_words

• Description: See SHOW ENGINE SPHINX STATUS.

• Scope: Global, Session

• Data Type: numeric

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

The following status variables are associated with the Spider storage engine. See Server Status Variables for a complete list of status variables that can be viewed with SHOW STATUS.

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

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 SUM and COUNT, spider forwards aggregate queries to these tables directly and aggregate the results.

• Scope: Global, Session

• Data Type: numeric

Spider_direct_delete

• Description:

• Scope: Global, Session

• Data Type: numeric

• Introduced: MariaDB 10.3.3

Spider_direct_order_limit

• Description:

• Scope: Global, Session

• Data Type: numeric

Spider_direct_update

• Description:

• Scope: Global, Session

• Data Type: numeric

• Introduced: MariaDB 10.3.3

Spider_mon_table_cache_version

• Description: The version of the spider monitoring table cache, always less than or equal to Spider_mon_table_cache_version_req. If the inequality is strict, then the cache is refreshed when the spider udf spider_ping_table is called. When the cache is refreshed, the value of Spider_mon_table_cache_version is brought up to be the same value as Spider_mon_table_cache_version_req.

• Scope: Global, Session

• Initial value: 0

• Data Type: numeric

Spider_mon_table_cache_version_req

• Description: The required version of the spider monitoring table cache. A call to the spider udf spider_flush_table_mon_cache will cause its value to be incremented by one, thus ensuring a refresh of the cache will happen when needed.

• Scope: Global, Session

• Initial value: 1

• Data Type: numeric

Spider_parallel_search

• Description:

• Scope: Global, Session

• Data Type: numeric

• Introduced: MariaDB 10.3.3

_{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 mysql.plugins table.

    • ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.

    • FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.

    • FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.

  • See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.

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

• Data Type: enumerated

• Default Value: ON

• Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT

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 data directory.

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

• Scope: Global

• Dynamic: No

• Data Type: string

• Default Value: sql_errors.log

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

• Dynamic: Yes

• Data Type: numeric

• Default Value: 1

sql_error_log_rotate

• Description: Setting to #1forces log rotation.

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

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default Value: OFF

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

• Data Type: numeric

• Default Value: 9

• Range: 1 to 999

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

• Data Type: numeric

• Default Value: 1000000

• Range: 100 to 9223372036854775807

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

• Data Type: boolean

• Default Value: ON

• Introduced: MariaDB 10.11.5

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

• Data Type: boolean

• Default Value: OFF

• Introduced: MariaDB 10.6.17, MariaDB 10.11.7, MariaDB 11.0.5, MariaDB 11.1.4, MariaDB 11.2.3

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

The status variables listed on this page relate to encrypting data during transfer with the Transport Layer Security (TLS) protocol. Often, the term Secure Socket Layer (SSL) is used interchangeably with TLS, although strictly speaking, the SSL protocol is a predecessor to TLS and is no longer considered secure.

For compatibility reasons, the TLS status variables in MariaDB still use the Ssl_ prefix, but MariaDB only supports its more secure successors. For more information on SSL/TLS in MariaDB, see Secure Connections Overview.

Some of these status values are not under the control of the server, but are reporting back settings of the underlying SSL library used by the server.

Variables