maria-discuss team mailing list archive

[Jeremy Zawodny <Jeremy@xxxxxxxxxxx>]

Same here.  It's often good to sacrifice a single query than lose the whole
server.

Jeremy


On Wed, Jun 5, 2013 at 3:44 PM, MARK CALLAGHAN <mdcallag@xxxxxxxxx> wrote:

> 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
>
> _______________________________________________
> 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
>
>