Aria Max Table Size for internal on-disk temporary tables - Source

Revision: 17881
User: Thomas Riccardi
Date: 2013-06-11 18:27

Using MariaDB 5.5.31 (deb file for ubuntu 12.04 amd_64: 5.5.31+maria-1~precise).

When a query requires an internal temporary tables, and this tables becomes too large to be kept in ram, the table is moved to disk using Aria engine.
If the on-disk .MAD file reaches 4GB, an error occurs:
{{{
[ERROR] mysqld: The table '/tmp/#sql_74e_0' is full
}}}

How can we change this limit?

In https://kb.askmonty.org/en/aria-max-table-size/ it is recommended to change MAX_ROW, but it only applies for explicitly created temporary tables.

Looking at the code I found this in maria_create():
{{{
data_file_length= ((((ulonglong) 1 << ((BLOCK_RECORD_POINTER_SIZE-1) *
                                               8))/2 -1) * maria_block_size);
}}}

Which amounts to 4GB with default values (8192 for maria_block_size).
I created a new DB with aria-block-size = 32768 (the max value), and it still failed at 4GB.
Even if it worked, it would mean the max size is only 16GB, and setting a large block size may have performance penalties.

So what is the proper fix to increase this limit?

With MyISAM as on-disk temporary tables engine the limit seems to be much higher (at least 14GB from experiments).

Answer

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Aria Max Table Size for internal on-disk temporary tables - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB