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@gmail.com> 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@askmonty.org>wrote:
Hi!
> "AskMonty" == AskMonty KB <noreply@askmonty.org> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Mark Callaghan mdcallag@gmail.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp