Note: You can download a PDF version of the CONNECT documentation (1.7.0003).

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

This page documents status variables related to the . See for a complete list of status variables that can be viewed with .

See also the .

Aria_pagecache_blocks_not_flushed

The storage engine used to be called Maria. This page gives the history and background of how and why this name was changed to Aria.

Backstory

When starting what became the MariaDB project, Monty and the initial developers only planned to work on a next generation storage engine replacement. This storage engine would be crash safe and eventually support transactions. Monty named the storage engine, and the project, after his daughter, Maria.

Work began in earnest on the Maria storage engine but the plans quickly expanded and morphed and soon the developers were not just working on a storage engine, but on a complete branch of the MySQL database. Since the project was already called Maria, it made sense to call the whole database server MariaDB.

The , , , and WMI table types use engine condition pushdown in order to restrict the number of rows returned by the RDBS source or the WMI component.

The CONDITION_PUSHDOWN argument used in old versions of CONNECT is no longer needed because CONNECT uses condition pushdown unconditionally.

_{This page is licensed: GPLv2}

The doublewrite buffer was implemented to recover from half-written pages. This can happen when there's a power failure while InnoDB is writing a page to disk. On reading that page, InnoDB can discover the corruption from the mismatch of the page checksum. However, in order to recover, an intact copy of the page would be needed.

The double write buffer provides such a copy.

Whenever InnoDB flushes a page to disk, it is first written to the double write buffer. Only when the buffer is safely flushed to disk will InnoDB write the page to the final destination. When recovering, InnoDB scans the double write buffer and for each valid page in the buffer checks if the page in the data file is valid too.

Doublewrite Buffer Settings

The Aria storage engine includes a feature to group commits to speed up concurrent threads doing many inserts into the same or different Aria tables.

By default, group commit for Aria is turned off. It is controlled by the aria_group_commit and aria_group_commit_interval system variables.

Information on setting server variables can be found on the Server System Variables page.

Terminology

• A commit is flush of logs followed by a sync.

• sent to disk means written to disk but not sync()ed,

• flushed mean sent to disk and synced().

• LSN means log serial number. It's refers to the position in the transaction log.

Non Group commit logic (aria_group_commit="none")

The thread which first started the commit is performing the actual flush of logs. Other threads set the new goal (LSN) of the next pass (if it is maximum) and wait for the pass end or just wait for the pass end.

The effect of this is that a flush (write of logs + sync) will save all data for all threads/transactions that have been waiting since the last flush.

If hard group commit is enabled (aria_group_commit="hard")

If hard commit and aria_group_commit_interval=0

The first thread sends all changed buffers to disk. This is repeated as long as there are new LSNs added. The process can not loop forever because we have a limited number of threads and they will wait for the data to be synced.

Pseudo code:

If hard commit and aria_group_commit_interval > 0

If less than rate microseconds has passed since the last sync, then after buffers have been sent to disk, wait until rate microseconds has passed since last sync, do sync and return. This ensures that if we call sync infrequently we don't do any waits.

If soft group commit is enabled (aria_group_commit="soft")

Note that soft group commit should only be used if you can afford to lose a few rows if your machine shuts down hard (as in the case of a power failure).

Works like in non group commit' but the thread doesn't do any real sync(). If aria_group_commit_interval is not zero, the sync() calls are performed by a service thread with the given rate when needed (new LSN appears). If aria_group_commit_interval is zero, there are no sync() calls.

Code

The code for this can be found in storage/maria/ma_loghandler.c::translog_flush().

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

Description

The Aria storage engine can automatically detect and deal with deadlocks (see the Wikipedia deadlocks article).

This feature is controlled by four configuration variables, two that control the search depth and two that control the timeout.

• deadlock_search_depth_long

How it Works

If Aria is ever unable to obtain a lock, we might have a deadlock. There are two primary ways for detecting if a deadlock has actually occurred. First is to search a wait-for graph (see the ) and the second is to just wait and let the deadlock exhibit itself. Aria Two-step Deadlock Detection does a combination of both.

First, if the lock request cannot be granted immediately, we do a short search of the wait-for graph with a small search depth as configured by the deadlock_search_depth_short variable. We have a depth limit because the graph can (theoretically) be arbitrarily big and we don't want to recursively search the graph arbitrarily deep. This initial, short search is very fast and most deadlocks are detected right away. If no deadlock cycles are found with the short search the system waits for the amount of time configured in deadlock_timeout_short to see if the lock conflicts are removed and the lock can be granted. Assuming this did not happen and the lock request still waits, the system then moves on to step two, which is a repeat of the process but this time searching deeper using the deadlock_search_depth_long. If no deadlock has been detected, it waits deadlock_timeout_long and times out.

When a deadlock is detected the system uses a weighting algorithm to determine which thread in the deadlock should be killed and then kills it.

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

The main characteristic of CONNECT is to enable accessing data scattered on a machine as if it was a centralized database. This, and the fact that locking is not used by connect (data files are open and closed for each query) makes CONNECT very useful for importing or exporting data into or from a MariaDB database and also for all types of Business Intelligence applications. However, it is not suited for transactional applications.

For instance, the index type used by CONNECT is closer to bitmap indexing than to B-trees. It is very fast for retrieving result but not when updating is done. In fact, even if only one indexed value is modified in a big table, the index is entirely remade (yet this being four to five times faster than for a b-tree index). But normally in Business Intelligence applications, files are not modified so often.

If you are using CONNECT to analyze files that can be modified by an external process, the indexes are of course not modified by it and become outdated. Use the OPTIMIZE TABLE command to update them before using the tables based on them.

This means also that CONNECT is not designed to be used by centralized servers, which are mostly used for transactions and often must run a long time without human intervening.

Performance

Performances vary a great deal depending on the table type. For instance, ODBC tables are only retrieved as fast as the other DBMS can do. If you have a lot of queries to execute, the best way to optimize your work can be sometime to translate the data from one type to another. Fortunately this is very simple with CONNECT. Fixed formats like FIX, BIN or VEC tables can be created from slower ones by commands such as:

FIX and BIN are often the better choice because the I/O functions are done on blocks of BLOCK_SIZE rows. VEC tables can be very efficient for tables having many columns only a few being used in each query. Furthermore, for tables of reasonable size, the MAPPED option can very often speed up many queries.

Create Table statement

Be aware of the two broad kinds of CONNECT tables:

Drop Table statement

For outward tables, the statement just removes the table definition but does not erase the table data. However, dropping an inward tables also erase the table data as well.

Alter Table statement

Be careful using the statement. Currently the data compatibility is not tested and the modified definition can become incompatible with the data. In particular, Alter modifies the table definition only but does not modify the table data. Consequently, the table type should not be modified this way, except to correct an incorrect definition. Also adding, dropping or modifying columns may be wrong because the default offset values (when not explicitly given by the FLAG option) may be wrong when recompiled with missing columns.

Safe use of ALTER is for indexing, as we have seen earlier, and to change options such as MAPPED or HUGE those do not impact the data format but just the way the data file is accessed. Modifying the BLOCK_SIZE option is all right with FIX, BIN, DBF, split VEC tables; however it is unsafe for VEC tables that are not split (only one data file) because at their creation the estimate size has been made a multiple of the block size. This can cause errors if this estimate is not a multiple of the new value of the block size.

In all cases, it is safer to drop and re-create the table (outward tables) or to make another one from the table that must be modified.

Update and Delete for File Tables

CONNECT can execute these commands using two different algorithms:

• It can do it in place, directly modifying rows (update) or moving rows (delete) within the table file. This is a fast way to do it in particular when indexing is used.

• It can do it using a temporary file to make the changes. This is required when updating variable record length tables and is more secure in all cases.

The choice between these algorithms depends on the session variable .

_{This page is licensed: GPLv2}

The use of the CONNECT engine requires the FILE privilege for "outward" tables. This should not be an important restriction. The use of CONNECT "outward" tables on a remote server seems of limited interest without knowing the files existing on it and must be protected anyway. On the other hand, using it on the local client machine is not an issue because it is always possible to create locally a user with the FILE privilege.

_{This page is licensed: GPLv2}

The CONNECT handler is a GA (stable) release. It was written starting both from an aborted project written for MySQL in 2004 and from the “DBCONNECT” program. It was tested on all the examples described in this document, and is distributed with a set of 53 test cases. Here is a not limited list of future developments:

1. Adding more table types.

2. Make more tests files (53 are already made)

3. Adding more data types, in particular unsigned ones (done for unsigned).

4. Supporting indexing on nullable and decimal columns.

5. Adding more optimize tools (block indexing, dynamic indexing, etc.) (done)

6. Supporting MRR (done)

7. Supporting partitioning (done)

8. Getting NOSQL data from the Net as answers from REST queries (done)

No programs are bug free, especially new ones. Please or documentation errors using the means provided by MariaDB.

_{This page is licensed: GPLv2}

The storage engine supports three different table storage formats.

These are FIXED, DYNAMIC and PAGE, and they can be set with the ROW FORMAT option in the statement. PAGE is the default format, while FIXED and DYNAMIC are essentially the same as the .

The statement can be used to see the storage format used by a table.

Fixed-length

Fixed-length (or static) tables contain records of a fixed-length. Each column is the same length for all records, regardless of the actual contents. It is the default format if a table has no BLOB, TEXT, VARCHAR or VARBINARY fields, and no ROW FORMAT is provided. You can also specify a fixed table with ROW_FORMAT=FIXED in the table definition.

CONNECT is not just a new “YASE” (Yet another Storage Engine) that provides another way to store data with additional features. It brings a new dimension to MariaDB, already one of the best products to deal with traditional database transactional applications, further into the world of business intelligence and data analysis, including NoSQL facilities. Indeed, BI is the set of techniques and tools for the transformation of raw data into meaningful and useful information. And where is this data?

"It's amazing in an age where relational databases reign supreme when it comes to managing data that so much information still exists outside RDBMS engines in the form of flat files and other such constructs. In most enterprises, data is passed back and forth between disparate systems in a fashion and speed that would rival the busiest expressways in the world, with much of this data existing in common, delimited files. Target systems intercept these source files and then typically proceed to load them via ETL (extract, transform, load) processes into databases that then utilize the information for business intelligence, transactional functions, or other standard operations. ETL tasks and data movement jobs can consume quite a bit of time and resources, especially if large volumes of data are present that require loading into a database. This being the case, many DBAs welcome alternative means of accessing and managing data that exists in file format."

Although CONNECT provides a rich set of table types, specific applications may need to access data organized in a way that is not handled by its existing foreign data wrappers (FDW). To handle these cases, CONNECT features an interface that enables developers to implement in C++ the required table wrapper and use it as if it were part of the standard CONNECT table type list. CONNECT can use these additional handlers providing the corresponding external module (dll or shared lib) be available.

To create such a table on an existing handler, use a Create Table statement as shown below.

The option module gives the name of the DLL or shared library implementing the OEM wrapper for the table type. This library must be located in the plugin directory like all other plugins or UDF’s.

This library must export a function GetMYTYPE

Directly using external (file) data has many advantages, such as to work on “fresh” data produced for instance by cash registers, telephone switches, or scientific apparatus. However, you may want in some case to import external data into your MariaDB database. This is extremely simple and flexible using the CONNECT handler. For instance, let us suppose you want to import the data of the xsample.xml XML file previously given in example into a table called biblio belonging to the connect database. All you have to do is to create it by:

This last statement creates the table and inserts the original XML data, translated to tabular format by the xsampall2 CONNECT table, into the MariaDB biblio table. Note that further transformation on the data could have been achieved by using a more elaborate Select statement in the Create statement, for instance using filters, alias or applying functions to the data. However, because the Create Table process copies table data, later modifications of the

Warning: Avoid using this table type in production applications. This file format is specific to CONNECT and may not be supported in future versions.

Tables of type VEC are binary files that in some cases can provide good performance on read-intensive query workloads. CONNECT organizes their data on disk as columns of values from the same attribute, as opposed to storing it as rows of tabular records. This organization means that when a query needs to access only a few columns of a particular table, only those columns need to be read from disk. Conversely, in a row-oriented table, all values in a table are typically read from disk, wasting I/O bandwidth.

CONNECT provides two integral VEC formats, in which each column's data is adjacent.

There are two broad categories of file-based CONNECT tables. Inward and Outward. They are described below.

Outward Tables

Overview

MariaDB features pluggable storage engines to allow per-table workload optimization.

A storage engine is a type of plugin for MariaDB:

• Different storage engines may be optimized for different workloads, such as transactional workloads, analytical workloads, or high throughput workloads.

• Different storage engines may be designed for different use cases, such as federated table access, table sharding, and table archiving in the cloud.

• Different tables on the same server may use different storage engines.

Examples

Identify the Default Storage Engine

Identify the server's global default storage engine by using to query the system variable:

Identify the session's default storage engine by using :

Set the Default Storage Engine

Global default storage engine:

Session default storage engine supersedes global default during this session:

Configure the Default Storage Engine

Identify Available Storage Engines

Choose Storage Engine for a New Table

Storage engine is specified at time of table creation using a ENGINE = parameter.

Resources

Engines for System Tables

Standard MariaDB storage engines are used for System Table storage:

FAQ

Can I use more than one storage engine on a server?

• Yes, different tables can use different storage engines on the same server.

• To create a table with a specific storage engine, specify the ENGINE table option to the statement.

Can I use more than one storage engine in a single query?

• Yes, a single query can reference tables that use multiple storage engines.

• In some cases, special configuration may be required. For example, ColumnStore requires cross engine joins to be configured.

What storage engine should I use for transactional or OLTP workloads?

• is the recommended storage engine for transactional or OLTP workloads.

What storage engine should I use for analytical or OLAP workloads?

• is the recommended storage engine for analytical or OLAP workloads.

What storage engine should I use if my application performs both transactional and analytical queries?

An application that performs both transactional and analytical queries is known as .

HTAP can be implemented with MariaDB by using for transactional queries and for analytical queries.

Reference

MariaDB Server Reference

• .

• , which shows available storage engines.

• , which shows storage engine by table.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The CSV Storage Engine can read and append to files stored in CSV (comma-separated-values) format.

The CSV storage engine and logging to tables

The CSV storage engine is the default storage engine when using logging of SQL queries to tables.

CSV Storage Engine files

When you create a table using the CSV storage engine, three files are created:

• <table_name>.frm

• <table_name>.CSV

• <table_name>.CSM

The .frm file is the table format file.

The .CSV file is a plain text file. Data you enter into the table is stored as plain text in comma-separated-values format.

The .CSM file stores metadata about the table such as the state and the number of rows in the table.

Limitations

• CSV tables do not support indexing.

• CSV tables cannot be partitioned.

• Columns in CSV tables must be declared as NOT NULL.

• No .

Examples

Forgetting to add NOT NULL:

Creating, inserting and selecting:

Viewing in a text editor:

See Also

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

CSV tables support the CHECK TABLE and REPAIR TABLE statements.

CHECK TABLE marks the table as corrupt if it finds a problem, while REPAIR TABLE restores rows until the first corrupted row, discarding the rest.

Examples

CREATE TABLE csv_test (
  x INT NOT NULL, y DATE NOT NULL, z CHAR(10) NOT NULL
  ) ENGINE=CSV;

INSERT INTO csv_test VALUES
    (1,CURDATE(),'one'),
    (2,CURDATE(),'two'),
    (3,CURDATE(),'three');

SELECT * FROM csv_test;
+---+------------+-------+
| x | y          | z     |
+---+------------+-------+
| 1 | 2013-07-08 | one   |
| 2 | 2013-07-08 | two   |
| 3 | 2013-07-08 | three |
+---+------------+-------+

Using an editor, the actual file will look as follows

Let's introduce some corruption with an unwanted quote in the 2nd row:

We can repair this, but all rows from the corrupt row onwards are lost:

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

Overview

When both innodb_flush_log_at_trx_commit=1 (the default) is set and the binary log is enabled, there is now one less sync to disk inside InnoDB during commit (2 syncs shared between a group of transactions instead of 3).

Durability of commits is not decreased — this is because even if the server crashes before the commit is written to disk by InnoDB, it are recovered from the binary log at next server startup (and it is guaranteed that sufficient information is synced to disk so that such a recovery is always possible).

Switching to Old Flushing Behavior

The old behavior, with 3 syncs to disk per (group) commit (and consequently lower performance), can be selected with the new option. There is normally no benefit to doing this, however there are a couple of edge cases to be aware of.

Non-durable Binary Log Settings

If is set and the is enabled, but is set, then commits are not guaranteed durable inside InnoDB after commit. This is because if is set and if the server crashes, then transactions that were not flushed to the binary log prior to the crash are missing from the binary log.

In this specific scenario, can be set to ensure that transactions are durable in InnoDB, even if they are not necessarily durable from the perspective of the binary log.

One should be aware that if is set, then a crash is nevertheless likely to cause transactions to be missing from the binary log. This will cause the binary log and InnoDB to be inconsistent with each other. This is also likely to cause any to become inconsistent, since transactions are replicated through the . Thus it is recommended to set . With the improvements introduced in , this setting has much less penalty in recent versions compared to older versions of MariaDB and MySQL.

Recent Transactions Missing from Backups

and only see transactions that have been flushed to the . With the improvements, there may be a small delay (defined by the system variable) between when a commit happens and when the commit are included in a backup.

Note that the backup will still be fully consistent with itself and the . This problem is normally not an issue in practice. A backup usually takes a long time to complete (relative to the 1 second or so that is normally set to), and a backup usually includes a lot of transactions that were committed during the backup. With this in mind, it is not generally noticeable if the backup does not include transactions that were committed during the last 1 second or so of the backup process. It is just mentioned here for completeness.

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

Locks are acquired by a transaction to prevent concurrent transactions from modifying, or even reading, some rows or ranges of rows. This is done to make sure that concurrent write operations never collide.

InnoDB supports a number of lock modes.

Shared and Exclusive Locks

The two standard row-level locks are share locks(S) and exclusive locks(X).

A shared lock is obtained to read a row, and allows other transactions to read the locked row, but not to write to the locked row. Other transactions may also acquire their own shared locks.

An exclusive lock is obtained to write to a row, and stops other transactions from locking the same row. It's specific behavior depends on the ; the default (REPEATABLE READ), allows other transactions to read from the exclusively locked row.

Intention Locks

InnoDB also permits table locking, and to allow locking at both table and row level to co-exist gracefully, a series of locks called intention locks exist.

An intention shared lock(IS) indicates that a transaction intends to set a shared lock.

An intention exclusive lock(IX) indicates that a transaction intends to set an exclusive lock.

Whether a lock is granted or not can be summarised as follows:

• An X lock is not granted if any other lock (X, S, IX, IS) is held.

• An S lock is not granted if an X or IX lock is held. It is granted if an S or IS lock is held.

• An IX lock is not granted if in X or S lock is held. It is granted if an IX or IS lock is held.

• An IS lock is not granted if an X lock is held. It is granted if an S, IX or IS lock is held.

AUTO_INCREMENT Locks

Locks are also required for auto-increments - see .

Gap Locks

With the default , REPEATABLE READ, and, until , the default setting of the variable, a method called gap locking is used. When InnoDB sets a shared or exclusive lock on a record, it's actually on the index record. Records will have an internal InnoDB index even if they don't have a unique index defined. At the same time, a lock is held on the gap before the index record, so that another transaction cannot insert a new index record in the gap between the record and the preceding record.

The gap can be a single index value, multiple index values, or not exist at all depending on the contents of the index.

If a statement uses all the columns of a unique index to search for unique row, gap locking is not used.

Similar to the shared and exclusive intention locks described above, there can be a number of types of gap locks. These include the shared gap lock, exclusive gap lock, intention shared gap lock and intention exclusive gap lock.

Gap locks are disabled if the system variable is set (until ), or the is set to READ COMMITTED.

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

The InnoDB Monitor refers to particular kinds of monitors included in MariaDB and since the early versions of MySQL.

There are four types: the standard InnoDB monitor, the InnoDB Lock Monitor, InnoDB Tablespace Monitor and the InnoDB Table Monitor.

Standard InnoDB Monitor

The standard InnoDB Monitor returns extensive InnoDB information, particularly lock, semaphore, I/O and buffer activity:

To enable the standard InnoDB Monitor, from , set the innodb_status_output system variable to 1. Before , running the following statement was the method used:

To disable the standard InnoDB monitor, either set the system variable to zero, or, before , drop the table

The CREATE TABLE and DROP TABLE method of enabling and disabling the InnoDB Monitor has been deprecated, and may be removed in a future version of MariaDB.

For a description of the output, see .

InnoDB Lock Monitor

The InnoDB Lock Monitor displays additional lock information.

To enable the InnoDB Lock Monitor, the standard InnoDB monitor must be enabled. Then, from , set the system variable to 1. Before , running the following statement was the method used:

To disable the standard InnoDB monitor, either set the system variable to zero, or, before , drop the table

The CREATE TABLE and DROP TABLE method of enabling and disabling the InnoDB Lock Monitor has been deprecated, and may be removed in a future version of MariaDB.

InnoDB Tablespace Monitor

The InnoDB Tablespace Monitor is deprecated, and may be removed in a future version of MariaDB.

Enabling the Tablespace Monitor outputs a list of file segments in the shared tablespace to the error log, and validates the tablespace allocation data structures.

To enable the Tablespace Monitor, run the following statement:

To disable it, drop the table:

InnoDB Table Monitor

The InnoDB Table Monitor is deprecated, and may be removed in a future version of MariaDB.

Enabling the Table Monitor outputs the contents of the InnoDB internal data dictionary to the error log every fifteen seconds.

To enable the Table Monitor, run the following statement:

To disable it, drop the table:

SHOW ENGINE INNODB STATUS

The statement can be used to obtain the standard InnoDB Monitor output when required, rather than sending it to the error log. It will also display the InnoDB Lock Monitor information if the system variable is set to 1.

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

Overview

Starting with MariaDB Enterprise Server 10.5 and MariaDB Community Server 10.5, InnoDB uses the InnoDB Background Thread Pool to perform internal operations in the background. In previous versions, the internal operations were performed by dedicated threads. By using the InnoDB Background Thread Pool instead of many dedicated threads, InnoDB can reduce context switching and use system resources more effectively.

The InnoDB Background Thread Pool performs internal operations in multiple categories: tasks, timers, and asynchronous I/O.

Tasks are used to perform internal operations that are triggered by some event. In ES 10.5 and later and CS 10.5 and later, the following threads have been replaced by tasks with the InnoDB Background Thread Pool:

• The InnoDB Buffer Pool Resize Thread

• The InnoDB Buffer Pool Dump Thread

• The InnoDB Full-Text Search (FTS) Optimization Thread

Timers are used to perform internal operations that are triggered periodically. In ES 10.5 and later and CS 10.5 and later, the following threads have been replaced by timers with the InnoDB Background Thread Pool:

• The InnoDB Master Thread

• The InnoDB Defragmentation Thread

• The InnoDB Monitor Thread

• The InnoDB Error Monitor Thread

Asynchronous I/O is used to read from and write to disk asynchronously. In ES 10.5 and later and CS 10.5 and later, the following threads have been replaced by asynchronous I/O with the InnoDB Background Thread Pool:

• The

Feature Summary

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The ARCHIVE storage engine is a storage engine that uses gzip to compress rows. It is mainly used for storing large amounts of data, without indexes, with only a very small footprint.

A table using the ARCHIVE storage engine is stored in two files on disk. There's a table definition file with an extension of .frm, and a data file with the extension .ARZ. At times during optimization, a .ARN file will appear.

New rows are inserted into a compression buffer and are flushed to disk when needed. SELECTs cause a flush. Sometimes, rows created by multi-row inserts are not visible until the statement is complete.

ARCHIVE allows a maximum of one key. The key must be on an column, and can be a PRIMARY KEY or a non-unique key. However, it has a limitation: it is not possible to insert a value which is lower than the next AUTO_INCREMENT value.

Note: You can download a PDF version of the CONNECT documentation (1.7.0003):

A PROXY table is a table that accesses and reads the data of another table or view. To create a table based on the boys FIX table:

Simply, PROXY being the default type when TABNAME is specified:

Because the boys table can be directly used, what can be the use of a proxy table? Well, its main use is to be internally used by other table types such as

Sometimes there is a requirement that when some data is deleted, it is really gone. This might be the case when one stores user's personal information or some other sensitive data. Normally though, when a row is deleted, the space is only marked as free on the page. It may eventually be overwritten, but there is no guarantee when that will happen. A copy of the deleted rows may also be present in the log files.

Support for data scrubbing: Background threads periodically scan tablespaces and logs and remove all data that should be deleted. The number of background threads for tablespace scans is set by . Log scrubbing happens in a separate thread.

To configure scrubbing one can use the following variables:

Setting a Table's File Format

A table's tablespace is tagged with the lowest InnoDB file format that supports the table's . So, even if the Barracuda file format is enabled, tables that use the COMPACT or REDUNDANT row formats are tagged with the Antelope file format in the table.

Overview

Starting with MariaDB Enterprise Server 10.5 and MariaDB Community Server 10.5, the InnoDB I/O Threads were replaced by the asynchronous I/O functionality in the .

A high-level overview of the main reasons for choosing a particular storage engine:

Topic List

General Purpose

• is a good general transaction storage engine, and the best choice in most cases. It is the default storage engine.

• , MariaDB's more modern improvement on , has a small footprint and allows for easy copying between systems.

• has a small footprint and allows for easy copying between systems. MyISAM is MySQL's oldest storage engine. There is usually little reason to use it except for legacy purposes. Aria is MariaDB's more modern improvement.

• is no longer available. It was a performance-enhanced fork of InnoDB and was MariaDB's default engine until

Scaling, Partitioning

When you want to split your database load on several servers or optimize for scaling. We also suggest looking at , a synchronous multi-master cluster.

• uses partitioning to provide data sharding through multiple servers.

• utilizes a massively parallel distributed data architecture and is designed for big data scaling to process petabytes of data.

• The storage engine is a collection of identical tables that can be used as one. "Identical" means that all tables have identical column and index information.

Compression / Archive

• enables greater compression than InnoDB, as well as less write amplification giving better endurance of flash storage and improving overall throughput.

• The storage engine is, unsurprisingly, best used for archiving.

Connecting to Other Data Sources

When you want to use data not stored in a MariaDB database.

• The storage engine can read and append to files stored in CSV (comma-separated-values) format. However, since MariaDB 10.0, CONNECT is a better choice and is more flexibly able to read and write such files.

Search Optimized

Search engines optimized for search.

• is used as a proxy to run statements on a remote Sphinx database server (mainly useful for advanced fulltext searches).

• provides fast CJK-ready full text searching using column store.

Cache, Read-only

• does not write data on-disk (all rows are lost on crash) and is best-used for read-only caches of data from other tables, or for temporary work areas. With the default and other storage engines having good caching, there is less need for this engine than in the past.

Other Specialized Storage Engines

• is a read-only storage engine that stores its data in Amazon S3.

• allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment, creating virtual, ephemeral tables automatically when you need them.

• The storage engine accepts data but does not store it and always returns an empty result. This can be useful in environments, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master.

Alphabetical List

• The storage engine is, unsurprisingly, best used for archiving.

• , MariaDB's more modern improvement on MyISAM, has a small footprint and allows for easy copy between systems.

• The storage engine accepts data but does not store it and always returns an empty result. This can be useful in environments, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master.

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

The BLACKHOLE storage engine accepts data but does not store it and always returns an empty result.

A table using the BLACKHOLE storage engine consists of a single .frm table format file, but no associated data or index files.

This storage engine can be useful, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master. The master can run a BLACKHOLE storage engine, with the data replicated to the slave for processing.

Installing the Plugin

Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.

The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing or :

The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an :

Uninstalling the Plugin

You can uninstall the plugin dynamically by executing or :

If you installed the plugin by providing the or the options in a relevant server in an , then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.

Using the BLACKHOLE Storage Engine

Using with DML

, , and statements all work with the BLACKHOLE storage engine. However, no data changes are actually applied.

Using with Replication

If the binary log is enabled, all SQL statements are logged as usual, and replicated to any slave servers. However, since rows are not stored, it is important to use statement-based rather than the row or mixed format, as and statements are neither logged nor replicated. See .

Using with Triggers

Some work with the BLACKHOLE storage engine.

BEFORE for statements are still activated.

for and statements are not activated.

with the FOR EACH ROW clause do not apply, since the tables have no rows.

Using with Foreign Keys

Foreign keys are not supported. If you convert an table to BLACKHOLE, then the foreign keys will disappear. If you convert the same table back to InnoDB, then you will have to recreate them.

Using with Virtual Columns

If you convert an table which contains to BLACKHOLE, then it produces an error.

Using with AUTO_INCREMENT

Because a BLACKHOLE table does not store data, it will not maintain the value. If you are replicating to a table that can handle AUTO_INCREMENT columns, and are not explicitly setting the primary key auto-increment value in the query, or using the statement, inserts will fail on the slave due to duplicate keys.

Limits

The maximum key size is:

• 3500 bytes (>= , , , and )

• 1000 bytes (<= , , , and ).

Examples

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

The CONNECT storage engine enables MariaDB to access external local or remote data (MED). This is done by defining tables based on different data types, in particular files in various formats, data extracted from other DBMS or products (such as Excel or MongoDB) via ODBC or JDBC, or data retrieved from the environment (for example DIR, WMI, and MAC tables)

This storage engine supports table partitioning, MariaDB virtual columns and permits defining special columns such as ROWID, FILEID, and SERVID.

The storage engine must be installed before it can be used.

Installing the Plugin's Package

The CONNECT storage engine's shared library is included in MariaDB packages as the ha_connect.so or ha_connect.so shared library on systems where it can be built.

Installing on Linux

The CONNECT storage engine is included in on Linux.

Installing with a Package Manager

The CONNECT storage engine can also be installed via a package manager on Linux. In order to do so, your system needs to be configured to install from one of the MariaDB repositories.

You can configure your package manager to install it from MariaDB Corporation's MariaDB Package Repository by using the .

You can also configure your package manager to install it from MariaDB Foundation's MariaDB Repository by using the .

Installing with yum/dnf

On RHEL, CentOS, Fedora, and other similar Linux distributions, it is highly recommended to install the relevant from MariaDB's repository using or . Starting with RHEL 8 and Fedora 22, yum has been replaced by dnf, which is the next major version of yum. However, yum commands still work on many systems that use dnf:

Installing with apt-get

On Debian, Ubuntu, and other similar Linux distributions, it is highly recommended to install the relevant from MariaDB's repository using :

Installing with zypper

On SLES, OpenSUSE, and other similar Linux distributions, it is highly recommended to install the relevant from MariaDB's repository using :

Installing the Plugin

Once the shared library is in place, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.

The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing or :

The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an :

Uninstalling the Plugin

You can uninstall the plugin dynamically by executing or :

If you installed the plugin by providing the or the options in a relevant server in an , then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.

Installing Dependencies

The CONNECT storage engine has some external dependencies.

Installing unixODBC

The CONNECT storage engine requires an ODBC library. On Unix-like systems, that usually means installing . On some systems, this is installed as the unixODBC package:

On other systems, this is installed as the libodbc1 package:

If you do not have the ODBC library installed, then you may get an error about a missing library when you attempt to install the plugin:

See Also

_{This page is licensed: GPLv2}

Because so many ODBC and JDBC drivers exist and only the main ones have been heavily tested, these table types cannot be ranked as stable. Use them with care in production applications.

These types can be used to access tables belonging to the current or another database server. Six types are currently provided:

ODBC: To be used to access tables from a database management system providing an ODBC connector. ODBC is a standard of Microsoft and is currently available on Windows. On Linux, it can also be used provided a specific application emulating ODBC is installed. Currently only unixODBC is supported.

JDBC: To be used to access tables from a database management system providing a JDBC connector. JDBC is an Oracle standard implemented in Java and principally meant to be used by Java applications. Using it directly from C or C++ application seems to be almost impossible due to an Oracle bug still not fixed. However, this can be achieved using a Java wrapper class used as an interface between C++ and JDBC. On another hand, JDBC is available on all platforms and operating systems.

: To access MongoDB collections as tables via their MongoDB C Driver. Because this requires both MongoDB and the C Driver to be installed and operational, this table type is not currently available in binary distributions but only when compiling MariaDB from source.

: This type is the preferred way to access tables belonging to another MySQL or MariaDB server. It uses the MySQL API to access the external table. Even though this can be obtained using the FEDERATED(X) plugin, this specific type is used internally by CONNECT because it also makes it possible to access tables belonging to the current server.

: Internally used by some table types to access other tables from one table.

External Table Specification

The four main external table types – odbc, jdbc, mongo and mysql – are specified giving the following information:

1. The data source. This is specified in the connection option.

2. The remote table or view to access. This can be specified within the connection string or using specific CONNECT options.

3. The column definitions. This can be also left to CONNECT to find them using the discovery MariaDB feature.

4. The optional Quoted option. Can be set to 1 to quote the identifiers in the query sent to the remote server. This is required if columns or table names can contain blanks.

The way this works is by establishing a connection to the external data source and by sending it an SQL statement (or its equivalent using API functions for MONGO) enabling it to execute the original query. To enhance performance, it is necessary to have the remote data source do the maximum processing. This is needed in particular to reduce the amount of data returned by the data source.

This is why, for SELECT queries, CONNECT uses the MariaDB feature to retrieve the maximum of the where clause of the original query that can be added to the query sent to the data source. This is automatic and does not require anything to be done by the user.

However, more can be done. In addition to accessing a remote table, CONNECT offers the possibility to specify what the remote server must do. This is done by specifying it as a view in the srcdef option:

Doing so, the group by clause are done by the remote server considerably reducing the amount of data sent back on the connection.

This may even be increased by adding to the srcdef part of the “compatible” part of the query where clauses like this are done for table-based tables. Note that for MariaDB, this table has two columns, country and customers. Supposing the original query is:

How can we make the where clause be added to the sent srcdef? There are many problems:

1. Where to include the additional information.

2. What about the use of alias.

3. How to know what are a where clause or a having clause.

The first problem is solved by preparing the srcdef view to receive clauses. The above example srcdef becomes:

The %s in the srcdef are place holders for eventual compatible parts of the original query where clause. If the select query does not specify a where clause, or a gives an unacceptable where clause, place holders are filled by dummy clauses (1=1).

The other problems must be solved by adding to the create table a list of columns that must be translated because they are aliases or/and aliases on aggregate functions that must become a having clause. For example, in this case:

This is specified by the alias option, to be used in the option list. It is made of a semi-colon separated list of items containing:

1. The local column name (alias in the remote server)

2. An equal sign.

3. An eventual ‘*’ indicating this is column correspond to an aggregate function.

4. The remote column name.

With this information, CONNECT are able to make the query sent to the remote data source:

Note: Some data sources, including MySQL and MariaDB, accept aliases in the having clause. In that case, the alias option could have been specified as:

Another option exists, phpos, enabling to specify what place holders are present and in what order. To be specified as “W”, “WH”, “H”, or “HW”. It is rarely used because by default CONNECT can set it from the srcdef content. The only cases it is needed is when the srcdef contains only a having place holder or when the having place holder occurs before the where place holder, which can occur on queries containing joins. CONNECT cannot handle more than one place holder of each type.

SRCDEF is not available for MONGO tables, but other ways of achieving this exist and are described in the MONGO table type chapter.

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

Although the JSON UDFs can be nicely included in the CONNECT library module, there are cases when you may need to have them in a separate library.

This is when CONNECT is compiled embedded, or if you want to test or use these UDFs with other MariaDB versions not including them.

To make it, you need to have access to the most recent MariaDB source code. Then, make a project containing these files:

1. jsonudf.cpp

2. json.cpp

3. value.cpp

4. osutil.c

5. plugutil.cpp

6. maputil.cpp

7. jsonutil.cpp

jsonutil.cpp is not distributed with the source code, you will have to make it from the following:

You can create the file by copy/paste from the above.

Set all the additional include directories to the MariaDB include directories used in plugin compiling plus the reference of the storage/connect directories, and compile like any other UDF giving any name to the made library module (I used jsonudf.dll on Windows).

Then you can create the functions using this name as the soname parameter.

There are some restrictions when using the UDFs this way:

• The variable cannot be accessed. The group size is set and retrieved using the and functions (previously 100).

• In case of error, warnings are replaced by messages sent to stderr.

• No trace.

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

The InnoDB storage engine in MariaDB Enterprise Server utilizes the Buffer Pool as a crucial in-memory cache. This Buffer Pool stores recently accessed data pages, enabling faster retrieval for subsequent requests. Recognizing patterns of access, InnoDB also employs predictive prefetching, caching nearby pages when sequential access is detected. To manage memory efficiently, a least recently used (LRU) algorithm is used to evict older, less frequently accessed pages.

To optimize server restarts, the Buffer Pool's contents can be preserved across shutdowns. At shutdown, the page numbers of all pages residing in the Buffer Pool are recorded. Upon the next startup, InnoDB reads this dump of page numbers and reloads the corresponding data pages from their respective data files, effectively avoiding a "cold" cache scenario.

The size of each individual page within the Buffer Pool is determined by the setting of the innodb_page_size system variable.

How the Buffer Pool Works

The buffer pool attempts to keep frequently-used blocks in the buffer, and so essentially works as two sublists, a new sublist of recently-used information, and an old sublist of older information. By default, 37% of the list is reserved for the old list.

When new information is accessed that doesn't appear in the list, it is placed at the top of the old list, the oldest item in the old list is removed, and everything else bumps back one position in the list.

When information is accessed that appears in the old list, it is moved to the top the new list, and everything above moves back one position.

innodb_buffer_pool_size

The most important is . This size should contain most of the active data set of your server so that SQL request can work directly with information in the buffer pool cache. Starting at several gigabytes of memory is a good starting point if you have that RAM available. Once warmed up to its normal load there should be very few compared to . Look how these values change over a minute. If the change in is less than 1% of the change in then you have a good amount of usage. If you are getting the status variable increasing then you don't have enough buffer pool (or your flushing isn't occurring frequently enough).

The larger the size, the longer it takes to initialize. On a 64-bit server with a 10GB memory pool, this can take five seconds or longer.

The buffer pool can be set dynamically. See .

Buffer Pool Changes

MariaDB server deprecates and ignores the . Now, the buffer pool size is changed in arbitrary 1-megabyte increments, all the way up to , which must be specified at startup.

If innodb_buffer_pool_size_max is 0 or not specified, it defaults to the value.

The variable specifies the minimum size the buffer pool can be shrunk to by a memory pressure event. When a memory pressure event occurs, MariaDB server attempts to shrink innodb_buffer_pool_size halfway between its current value and the innodb_buffer_pool_size_auto_min value. If innodb_buffer_pool_size_auto_min is not specified or 0, its default value is adjusted to innodb_buffer_pool_size — in other words, memory pressure events are disregarded by default.

The minimum innodb_buffer_pool_size is 320 pages (256*5/4). With the default value of innodb_page_size=16k, this corresponds to 5 MiB. However, since innodb_buffer_pool_size includes the memory allocated for the block descriptors, the minimum is effectively innodb_buffer_pool_size=6m.

The variable is removed. Issuing SET GLOBAL innodb_buffer_pool_size blocks until the buffer pool has been resized or the operation was aborted by a KILL or SHUTDOWN command, a client disconnect, or an interrupt.

innodb_old_blocks_pct and innodb_old_blocks_time

The default 37% reserved for the old list can be adjusted by changing the value of . It can accept anything between 5% and 95%.

The variable specifies the delay before a block can be moved from the old to the new sublist. 0 means no delay, while the default has been set to 1000.

Before changing either of these values from their defaults, make sure you understand the impact and how your system currently uses the buffer. Their main reason for existence is to reduce the impact of full table scans, which are usually infrequent, but large, and previously could clear everything from the buffer. Setting a non-zero delay could help in situations where full table scans are performed in quick succession.

Temporarily changing these values can also be useful to avoid the negative impact of a full table scan, as explained in .

Dumping and Restoring the Buffer Pool

When the server starts, the buffer pool is empty. As it starts to access data, the buffer pool will slowly be populated. As more data are accessed, the most frequently accessed data are put into the buffer pool, and old data may be evicted. This means that a certain period of time is necessary before the buffer pool is really useful. This period of time is called the warmup.

InnoDB can dump the buffer pool before the server shuts down, and restore it when it starts again. If this feature is used, no warmup is necessary. Use the and system variables to enable or disable the buffer pool dump at shutdown and the restore at startup respectively.

It is also possible to dump the InnoDB buffer pool at any moment while the server is running, and it is possible to restore the last buffer pool dump at any moment. To do this, the special and system variables can be set to ON. When selected, their value is always OFF.

A buffer pool restore, both at startup or at any other moment, can be aborted by setting to ON.

The file which contains the buffer pool dump is specified via the system variable.

See Also

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

If you are using a version of MariaDB that does not support REST, this is how the REST feature can be added as a library called by an OEM table.

Before making the REST OEM module, the Microsoft Casablanca package must be installed as for compiling MariaDB from source.

Even if this module is to be used with a binary distribution, you need some CONNECT source files in order to successfully make it. It is made with four files existing in the version 1.06.0010 of CONNECT: tabrest.cpp, restget.cpp, tabrest.h and mini-global.h. It also needs the CONNECT header files that are included in tabrest.cpp and the ones they can include. This can be obtained by going to a recent download site of a version of MariaDB that includes the REST feature, downloading the MariaDB source file tar.gz and extracting from it the CONNECT sources files in a directory that are added to the additional source directories if it is not the directory containing the above files.

On Windows, use a recent version of Visual Studio. Make a new empty DLL project and add the source files tabrest.cpp and restget.cpp. Visual studio should automatically generate all necessary connections to the cpprest SDK. Just edit the properties of the project to add the additional include directory (the one where the CONNECT source was downloaded) et the link to the ha_connect.lib of the binary version of MariaDB (in the same directory than ha_connect.dll in your binary distribution). Add the preprocessor definition XML_SUPPORT. Also set in the linker input page of the project property the Module definition File to the rest.def file (with its full path) also existing in the CONNECT source files. If you are making a debug configuration, make sure that in the C/C++ Code generation page the Runtime library line specifies Multi-threaded Debug DLL (/MDd) or your server will crash when using the feature.

This is not really simple but it is nothing compared with Linux! Someone having made an OEM module for its own application have written:

For whatever reason, g++ / ld on Linux are both extremely picky about what they will and won't consider a "library" for linking purposes. In order to get them to recognize and therefore find ha_connect.so as a "valid" linkable library, ha_connect.so must exist in a directory whose path is in /etc/ld.so.conf or /etc/ld.so.conf.d/ha_connect.conf AND its filename must begin with "lib".

On Fedora, you can make a link to ha_connect.so by:

This provides a library whose name begins with “lib”. It was made in /usr/lib64/ because it was the directory of the libcpprest.so Casablanca library. This solved the need of a file in /etc/ld.so.conf.d as this was already done for the cpprest library. Note that the -s parameter is a must, without it all sort of nasty errors are met when using the feature.

Then compile and install the OEM module with:

The oemrest.mak file:

The SD and CD variables are the directories of the CONNECT source files and the one containing the libcpprest.so lib. They can be edited to match those on your machine OD is the directory that was made to contain the object files.

A very important flag is -fno-rtti. Without it you would be in big trouble.

The resulting module, for instance rest.so or rest.dll, must be placed in the plugin directory of the MariaDB server. Then, you are able to use NoSQL tables simply replacing in the CREATE TABLE statement the TABLE_TYPE option =JSON or XML by TABLE_TYPE=OEM SUBTYPE=REST MODULE=’rest.(so|dll)’. Actually, the module name, here supposedly ‘rest’, can be anything you like.

The file type is JSON by default. If not, it must be specified like this:

To be added to the create table statement. For instance:

Note: this last example returns an XML file whose format was not recognized by old CONNECT versions. It is here the reason of the option ‘Rownode=weatherdata’.

If you have trouble making the module, you can post an issue on .

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

AUTO_INCREMENT Lock Modes

The innodb_autoinc_lock_mode system variable determines the lock mode when generating AUTO_INCREMENT values for InnoDB tables. These modes allow InnoDB to make significant performance optimizations in certain circumstances.

The innodb_autoinc_lock_mode system variable may be removed in a future release. See MDEV-19577 for more information.

Traditional Lock Mode

When is set to 0, uses the traditional lock mode.

In this mode, holds a table-level lock for all statements until the statement completes.

Consecutive Lock Mode

When is set to 1, uses the consecutive lock mode.

In this mode, holds a table-level lock for all bulk statements (such as or ) until the end of the statement. For simple statements, no table-level lock is held. Instead, a lightweight mutex is used which scales significantly better. This is the default setting.

Interleaved Lock Mode

When is set to 2, uses the interleaved lock mode.

In this mode, does not hold any table-level locks at all. This is the fastest and most scalable mode, but is not safe for replication.

Setting AUTO_INCREMENT Values

The value for an table can be set for a table by executing the statement and specifying the table option:

However, in and before, stores the table's counter in memory. In these versions, when the server restarts, the counter is re-initialized to the highest value found in the table. This means that the above operation can be undone if the server is restarted before any rows are written to the table.

In and later, the counter is persistent, so this restriction is no longer present. Persistent, however, does not mean transactional. Gaps may still occur in some cases, such as if a statement fails, or if a user executes or .

For example:

If the server is restarted at this point, then the counter will revert to 101, which is the persistent value set as part of the failed .

See Also

• - an alternative to auto_increment available from

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

From , InnoDB uses asynchronous I/O to read from and write to disk asynchronously. This forms part of the InnoDB Background Thread Pool.

Stages

Each asynchronous IO operation goes through multiple stages:

1. SUBMITTED – The IO operation is initiated.

• For asynchronous writes, this typically occurs in the buffer pool flushing code.

• For asynchronous reads, this may happen during buffer pool loading at startup or in prefetching logic.

1. COMPLETED_IN_OS – The operating system notifies InnoDB that the I/O operation is complete.

• If using libaio or io_uring, a dedicated thread handles this notification.

• The completed IO operation is then submitted to InnoDB’s internal thread pool (tpool).

1. EXECUTING_COMPLETION_TASK – A tpool thread processes the completion task for the IO operation.

2. COMPLETED – The IO operation is fully handled.

Resource Constraints and Queuing Mechanisms

Waiting for IO Slots

The total number of pending asynchronous IO operations is limited by:

where number_of_IO_threads refers to either or .

Each IO operation is associated with an IO slot, which contains necessary metadata such as the file handle, operation type, offset, length, and any OS error codes. Initially, all total_count slots are free, but as pending IO requests accumulate, slots get occupied. If all slots are in use, additional IO requests must wait for a free slot.

Queuing Mechanism

The number of completion tasks (EXECUTING_COMPLETION_TASK stage) that can run in parallel is also limited by or . If too many IO operations complete simultaneously, they cannot all be processed in parallel and must be queued, respecting the thread limit.

Variables

From , a number of status variables were added to give insight into the above operations:

• – Number of read IO operations currently in progress (from SUBMITTED to COMPLETED).

• – Number of read IO operations currently in the EXECUTING_COMPLETION_TASK state.

• – Total number of read completion tasks that have finished execution.

• – Current size of the queue (see ).

Similar variables exist for write operations:

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

Most of the tables processed by CONNECT are just plain DOS or UNIX data files, logically regarded as tables thanks to the description given when creating the table. This description comes from the statement. Depending on the application, these tables can already exist as data files, used as is by CONNECT, or can have been physically made by CONNECT as the result of a CREATE TABLE ... SELECT ... and/or INSERT statement(s).

The file path/name is given by the FILE_NAME option. If it is a relative path/name, it are relative to the database directory, the one containing the table .FRM

Starting with , JSON, XML and possibly CSV data files can be retrieved as results from REST queries when creating or querying such tables. This is done internally by CONNECT using the CURL program generally available on all systems (if not just install it).

This can also be done using the Microsoft Casablanca (cpprestsdk) package. To enable it, first, install the package as explained in . Then make the GetRest library (dll or so) as explained in .

Note: If both are available, cpprestsdk is used preferably because it is faster. This can be changed by specifying ‘curl=1’ in the option list.

Note: If you want to use this feature with an older distributed version of MariaDB not featuring REST, it is possible to add it as an OEM module as explained in

To enable the REST feature with binary distributions of MariaDB, the function calling the cpprestsdk package is not included in CONNECT, thus allowing CONNECT normal operation when the cpprestsdk package is not installed. Therefore, it must be compiled separately as a library (so or dll) that are loaded by CONNECT when needed.

This library will contain only one file shown here:

This file exists in the source of CONNECT as restget.cpp. If you have no access to the source, use your favorite editor to make it by copy/pasting from the above.

Then, on Linux, compile the GetRest.so library:

Benchmarks attached to show that the change buffer sometimes reduces performance, and in the best case seem to bring a few per cent improvement to throughput. However, such improvement could come with a price: If the buffered changes are never merged (, motivated by the reduction of random crashes and the removal of an innodb_force_recovery option that can inflict further corruption), then the InnoDB system tablespace can grow out of control ().

INSERT

The has the following limitations.

Limitations on Schema

• InnoDB tables can have a maximum of 1,017 columns. This includes .

Overview

When a writes data, it always inserts them in the table indexes or data (in the buffer pool or in physical files). No private copies are created. The old versions of data being modified by active transactions are stored in the undo log. The original data can then be restored, or viewed by a consistent read.

The Aria storage engine is compiled in by default from and it is required to be 'in use' when MariaDB is started.

All system tables are Aria.

Additionally, internal on-disk tables are in the Aria table format instead of the MyISAM table format. This should speed up some GROUP BY and DISTINCT queries because Aria has better caching than MyISAM.

Note: The Aria storage engine was previously called Maria (see The Aria Name for details on the rename) and in previous versions of MariaDB the engine was still called Maria.

The following table options to Aria tables in CREATE TABLE and ALTER TABLE:

• TRANSACTIONAL= 0 | 1 : If the TRANSACTIONAL table option is set for an Aria table, then the table are crash-safe. This is implemented by logging any changes to the table to Aria's transaction log, and syncing those writes at the end of the statement. This will marginally slow down writes and updates. However, the benefit is that if the server dies before the statement ends, all non-durable changes will roll back to the state at the beginning of the statement. This also needs up to 6 bytes more for each row and key to store the transaction id (to allow concurrent insert's and selects).

  • TRANSACTIONAL=1 is not supported for partitioned tables.

  • An Aria table's default value for the TRANSACTIONAL table option depends on the table's value for the ROW_FORMAT table option. See below for more details.

  • If the TRANSACTIONAL table option is set for an Aria table, the table does not actually support transactions. See for more information. In this context, transactional just means crash-safe.

• PAGE_CHECKSUM= 0 | 1 : If index and data should use page checksums for extra safety.

• TABLE_CHECKSUM= 0 | 1 : Same as CHECKSUM in MySQL 5.1

• ROW_FORMAT=PAGE | FIXED | DYNAMIC : The table's .

  • The default value is PAGE.

  • To emulate MyISAM, set ROW_FORMAT=FIXED or ROW_FORMAT=DYNAMIC

The TRANSACTIONAL and ROW_FORMAT table options interact as follows:

• If TRANSACTIONAL=1 is set, then the only supported row format is PAGE. If ROW_FORMAT is set to some other value, then Aria issues a warning, but still forces the row format to be PAGE.

• If TRANSACTIONAL=0 is set, then the table are not be crash-safe, and any row format is supported.

Some other improvements are:

• now ignores values in NULL fields. This makes CHECKSUM TABLE faster and fixes some cases where same table definition could give different checksum values depending on . The disadvantage is that the value is now different compared to other MySQL installations. The new checksum calculation is fixed for all table engines that uses the default way to calculate and MyISAM which does the calculation internally. Note: Old MyISAM tables with internal checksum returns the same checksum as before. To fix them to calculate according to new rules you have to do an . You can use the old ways to calculate checksums by using the option --old to mariadbdmysqld or set the system variable '@@old' to 1 when you do CHECKSUM TABLE ... EXTENDED;

Startup Options for Aria

For a full list, see .

In normal operations, the only variables you have to consider are:

• • This is where all index and data pages are cached. The bigger this is, the faster Aria will work.

• • The default value 8192, should be ok for most cases. The only problem with a higher value is that it takes longer to find a packed key in the block as one has to search roughly 8192/2 to find each key. We plan to fix this by adding a dictionary at the end of the page to be able to do a binary search within the block before starting a scan. Until this is done and key lookups takes too long time even if you are not hitting disk, then you should consider making this smaller.

Aria Log Files

aria_log_control file is a very short log file (52 bytes) that contains the current state of all Aria tables related to logging and checkpoints. In particular, it contains the following information:

• The uuid is a unique identifier per system. All Aria files created will have a copy of this in their .MAI headers. This is mainly used to check if someone has copied an Aria file between MariaDB servers.

• last_checkpoint_lsn and last_log_number are information about the current aria_log files.

• trid is the highest transaction number seen so far. Used by recovery.

aria_log.* files contain the log of all operations that change Aria files (including create table, drop table, insert etc..) This is a 'normal' WAL (Write Ahead Log), similar to the InnoDB log file, except that aria_logs contain both redo and undo. Old aria_log files are automatically deleted when they are not needed anymore (Neither the last checkpoint or any running transaction need to refer to the old data anymore).

Missing valid id

The error Missing valid id at start of file. File is not a valid aria control file means that something overwrote at least the first 4 bytes in the file. This can happen due to a problem with the file system (hardware or software), or a bug in which a thread inside MariaDB wrote on the wrong file descriptor (in which case you should , attaching a copy of the control file to assist).

In the case of a corrupted log file, with the server shut down, one should be able to fix that by deleting all aria_log files. If the control_file is corrupted, then one has to delete the aria_control_file and all aria_log.* files. The effect of this is that on table open of an Aria table, the server will think that it has been moved from another system and do an automatic check and repair of it. If there was no issues, the table are opened and can be used as normal. See also .

See Also

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

CONNECT can handle very many table formats; it is indeed one of its main features. The Type option specifies the type and format of the table. The Type options available values and their descriptions are listed in the following table:

Catalog Tables

For all table types marked with a '*' in the table above, CONNECT is able to analyze the data source to retrieve the column definition. This can be used to define a “catalog” table that display the column description of the source, or to create a table without specifying the column definition that are automatically constructed by CONNECT when creating the table.

When marked with a ‘$’ the file can be the result returned by a REST query.

_{This page is licensed: GPLv2}

Indexing is one of the main ways to optimize queries. Key columns, in particular when they are used to join tables, should be indexed. But what should be done for columns that have only few distinct values? If they are randomly placed in the table they should not be indexed because reading many rows in random order can be slower than reading the entire table sequentially. However, if the values are sorted or clustered, indexing can be acceptable because CONNECT indexes store the values in the order they appear into the table and this will make retrieving them almost as fast as reading them sequentially.

CONNECT provides four indexing types:

1. Standard Indexing

2. Block Indexing

3. Remote Indexing

4. Dynamic Indexing

Standard Indexing

CONNECT standard indexes are created and used as the ones of other storage engines although they have a specific internal format. The CONNECT handler supports the use of standard indexes for most of the file based table types.

You can define them in the statement, or either using the CREATE INDEX statement or the statement. In all cases, the index files are automatically made. They can be dropped either using the statement or the statement, and this erases the index files.

Indexes are automatically reconstructed when the table is created, modified by INSERT, UPDATE or DELETE commands, or when the SEPINDEX option is changed. If you have a lot of changes to do on a table at one moment, you can use table locking to prevent indexes to be reconstructed after each statement. The indexes are reconstructed when unlocking the table. For instance:

If a table was modified by an external application that does not handle indexing, the indexes must be reconstructed to prevent returning false or incomplete results. To do this, use the command.

For outward tables, index files are not erased when dropping the table. This is the same as for the data file and preserves the possibility of several users using the same data file via different tables.

Unlike other storage engines, CONNECT constructs the indexes as files that are named by default from the data file name, not from the table name, and located in the data file directory. Depending on the SEPINDEX table option, indexes are saved in a unique file or in separate files (if SEPINDEX is true). For instance, if indexes are in separate files, the primary index of the table_dept.dat_ of type DOS is a file named dept_PRIMARY.dnx. This makes possible to define several tables on the same data file, with eventual different options such as mapped or not mapped, and to share the index files as well.

If the index file should have a different name, for instance because several tables are created on the same data file with different indexes, specify the base index file name with the XFILE_NAME option.

Note1: Indexed columns must be declared NOT NULL; CONNECT doesn't support indexes containing null values.

Note 2: MRR is used by standard indexing if it is enabled.

Note 3: Prefix indexing is not supported. If specified, the CONNECT engine ignores the prefix and builds a whole index.

Handling index errors

The way CONNECT handles indexing is very specific. All table modifications are done regardless of indexing. Only after a table has been modified, or when anOPTIMIZE TABLE command is sent are the indexes made. If an error occurs, the corresponding index is not made. However, CONNECT being a non-transactional engine, it is unable to roll back the changes made to the table. The main causes of indexing errors are:

• Trying to index a nullable column. In this case, you can alter the table to declare the column as not nullable or, if the column is nullable indeed, make it not indexed.

• Entering duplicate values in a column indexed by a unique index. In this case, if the index was wrongly declared as unique, alter is declaration to reflect this. If the column should really contain unique values, you must manually remove or update the duplicate values.

In both cases, after correcting the error, remake the indexes with the command.

Index file mapping

To accelerate the indexing process, CONNECT makes an index structure in memory from the index file. This can be done by reading the index file or using it as if it was in memory by “file mapping”. On enabled versions, file mapping is used according to the boolean system variable. Set it to 0 (file read) or 1 (file mapping).

Block Indexing

To accelerate input/output, CONNECT uses when possible a read/write mode by blocks of n rows, n being the value given in the BLOCK _ SIZE option of the Create Table, or a default value depending on the table type. This is automatic for fixed files (, , or ), but must be specified for variable files ( , or ).

For blocked tables, further optimization can be achieved if the data values for some columns are “clustered” meaning that they are not evenly scattered in the table but grouped in some consecutive rows. Block indexing permits to skip blocks in which no rows fulfill a conditional predicate without having even to read the block. This is true in particular for sorted columns.

You indicate this when creating the table by using the DISTRIB =d column option. The enum value d can be scattered, clustered, or sorted. In general only one column can be sorted. Block indexing is used only for clustered and sorted columns.

Difference between standard indexing and block indexing

• Block indexing is internally handled by CONNECT while reading sequentially a table data. This means in particular that when standard indexing is used on a table, block indexing is not used.

• In a query, only one standard index can be used. However, block indexing can combine the restrictions coming from a where clause implying several clustered/sorted columns.

• The block index files are faster to make and much smaller than standard index files.

Notes for this Release:

• On all operations that create or modify a table, CONNECT automatically calculates or recalculates and saves the mini/maxi or bitmap values for each block, enabling it to skip block containing no acceptable values. In the case where the optimize file does not correspond anymore to the table, because it has been accidentally destroyed, or because some column definitions have been altered, you can use the OPTIMIZE TABLE command to reconstruct the optimization file.

• Sorted column special processing is currently restricted to ascending sort. Column sorted in descending order must be flagged as clustered. Improper sorting is not checked in Update or Insert operations but is flagged when optimizing the table.

• Block indexing can be done in two ways. Keeping the min/max values existing for each block, or keeping a bitmap allowing knowing what column distinct values are met in each blocks. This second ways often gives a better optimization, except for sorted columns for which both are equivalent. The bitmap approach can be done only on columns having not too many distinct values. This is estimated by the MAX _ DIST option value associated to the column when creating the table. Bitmap block indexing are used if this number is not greater than the MAXBMP setting for the database.

Remote Indexing

Remote indexing is specific to the table type. It is equivalent to what the storage does. A MYSQL table does not support indexes per se. Because access to the table is handled remotely, it is the remote table that supports the indexes. What the MYSQL table does is just to add a WHERE clause to the command sent to the remote server allowing the remote server to use indexing when applicable. Note however that because CONNECT adds when possible all or part of the where clause of the original query, this happens often even if the remote indexed column is not declared locally indexed. The only, but very important, case a column should be locally declared indexed is when it is used to join tables. Otherwise, the required where clause would not be added to the sent SELECT query.

See for more.

Dynamic Indexing

An indexed created as “dynamic” is a standard index which, in some cases, can be reconstructed for a specific query. This happens in particular for some queries where two tables are joined by an indexed key column. If the “from” table is big and the “to” big table reduced in size because of a where clause, it can be worthwhile to reconstruct the index on this reduced table.

Because of the time added by reconstructing the index, this is valuable only if the time gained by reducing the index size if more than this reconstruction time. This is why this should not be done if the “from” table is small because there will not be enough row joining to compensate for the additional time. Otherwise, the gain of using a dynamic index is:

• Indexing time is a little faster if the index is smaller.

• The join process will return only the rows fulfilling the where clause.

• Because the table is read sequentially when reconstructing the index there no need for MRR.

• Constructing the index can be faster if the table is reduced by block indexing.

This last point is particularly important. It means that after the index is reconstructed, the join is done on a temporary memory table.

Unfortunately, storage engines being called independently by MariaDB for each table, CONNECT has no global information to decide when it is good to use dynamic indexing. This is why you should use it only on cases where you see that some important join queries take a very long time and only on columns used for joining the table. How to declare an index to be dynamic is by using the Boolean DYNAM index option. For instance, the query:

Such a query joining the diag table to the patients table may last a very long time if the tables are big. To declare the primary key on the pnb column of the patients table to be dynamic:

Note 1: The comment is not mandatory here but useful to see that the index is dynamic if you use the command.

Note 2: There is currently no way to just change the DYNAM option without dropping and adding the index. This is unfortunate because it takes time.

Virtual Indexing

It applies only to the virtual tables of type and must be made on a column specifying SPECIAL=ROWID or SPECIAL=ROWNUM.

_{This page is licensed: GPLv2}

VIR Type

A VIR table is a virtual table having only Special or Virtual columns. Its only property is its “size”, or cardinality, meaning the number of virtual rows it contains. It is created using the syntax:

The optional BLOCK_SIZE option gives the size of the table, defaulting to 1 if not specified. When its columns are not specified, it is almost equivalent to a table “seq_1_to_Size”.

Displaying constants or expressions

Many DBMS use a no-column one-line table to do this, often call “dual”. MySQL and MariaDB use syntax where no table is specified. With CONNECT, you can achieve the same purpose with a virtual table, with the noticeable advantage of being able to display several lines:

This will return:

What happened here? First of all, unlike Oracle “dual” tableS that have no columns, a MariaDB table must have at least one column. By default, CONNECT creates VIR tables with one special column. This can be seen with the SHOW CREATE TABLE statement:

This special column is called “n” and its value is the row number starting from 1. It is purely a virtual table and no data file exists corresponding to it and to its index. It is possible to specify the columns of a VIR table but they must be CONNECT special columns or virtual columns. For instance:

This table shows the sum and the sum of the square of the n first integers:

Note that the size of the table can be made very big as there no physical data. However, the result should be limited in the queries. For instance:

Such a query could last very long if the rowid column were not indexed. Note that by default, CONNECT declares the “n” column as a primary key. Actually, VIR tables can be indexed but only on the ROWID (or ROWNUM) columns of the table. This is a virtual index for which no data is stored.

Generating a Table filled with constant values

An interesting use of virtual tables, which often cannot be achieved with a table of any other type, is to generate a table containing constant values. This is easily done with a virtual table. Let us define the table FILLER as:

Here we choose a size larger than the biggest table we want to generate. Later if we need a table pre- filled with default and/or null values, we can do for example:

This will generate a table having 10000 rows that can be updated later when needed. Note that a table could have been used here instead of FILLING .

VIR tables vs. SEQUENCE tables

With just its default column, a VIR table is almost equivalent to a table. The syntax used is the main difference, for instance:

can be obtained with a VIR table (of size >= 15) by:

Therefore, the main difference is to be able to define the columns of VIR tables. Unfortunately, there are currently many limitations to virtual columns that hopefully should be removed in the future.

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

Overview

The INI type is one of the configuration or initialization files often found on Windows machines. For instance, let us suppose you have the following contact file contact.ini:

This is an example showing how an OEM table can be implemented.

The header File my_global.h:

Note: This is a fake my_global.h that just contains what is useful for the jmgoem.cppsource file.

The source File jmgoem.cpp

Connect can work on table files that are compressed in one or several zip files.

The specific options used when creating tables based on zip files are:

When a transaction updates a row in an InnoDB table, InnoDB's MVCC implementation keeps old versions of the row in the . The old versions are kept at least until all transactions older than the transaction that updated the row are no longer open. At that point, the old versions can be deleted. InnoDB has purge process that is used to delete these old versions.

InnoDB Purge Threads

In MariaDB Enterprise Server, the InnoDB storage engine uses Purge Threads to perform garbage collection in the background. The Purge Threads are related to multi-version concurrency control (MVCC).

The Purge Threads perform garbage collection of various items: