maria-discuss team mailing list archive

[MARK CALLAGHAN <mdcallag@xxxxxxxxx>]

On many occasions I have worked with DBAs who really, really wanted a limit
on the max on-disk size for implicit and explicit temp tables. They prefer
to fail a long running query over filling up a disk and halting the
database. Or don't have a limit but make the query fail when a temp table
write gets ENOSPC.


On Wed, Jun 5, 2013 at 5:57 AM, Michael Widenius <monty@xxxxxxxxxxxx> wrote:

>
> Hi!
>
> >>>>> "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).
>
> Regards,
> Monty
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>



-- 
Mark Callaghan
mdcallag@xxxxxxxxx