maria-discuss team mailing list archive

[MARK CALLAGHAN <mdcallag@xxxxxxxxx>]

Not sure this was the right way to do it, but it prevented many full-disk
problems.

http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3652

Port tmp_table_max_file_size option from the Google patch to limit max size of
on-disk temp tables.  This adds the my.cnf option and session variable
"tmp_table_max_file_size" to limit the max size of an on-disk (MyISAM)
temp table.
The error ER_TMP_TABLE_MAX_FILE_SIZE_EXCEEDED is raised when the limit
is exceeded.


On Tue, Jun 11, 2013 at 11:15 AM, Jeremy Zawodny <Jeremy@xxxxxxxxxxx> wrote:

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


-- 
Mark Callaghan
mdcallag@xxxxxxxxx