← Back to team overview

maria-discuss team mailing list archive

Re: New Question: Aria Max Table Size for internal on-disk temporary tables



>>>>> "AskMonty" == AskMonty KB <noreply@xxxxxxxxxxxx> writes:

AskMonty> Hello,
AskMonty> A new question has been asked in "Aria" by trsystran:
AskMonty> --------------------------------
AskMonty> Using MariaDB 5.5.31 (deb file for ubuntu 12.04 amd_64: 5.5.31+maria-1~precise).

AskMonty> 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.
AskMonty> If the on-disk .MAD file reaches 4GB, an error occurs:
AskMonty> {{{
AskMonty> [ERROR] mysqld: The table '/tmp/#sql_74e_0' is full
AskMonty> }}}

AskMonty> How can we change this limit?

AskMonty> 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.

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

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

AskMonty> So what is the proper fix to increase this limit?

This is a bug. Internal temporary tables should always be able to be
'big enough for any kind of query'.  I will fix this ASAP.

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


Follow ups