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 <jer...@zawodny.com> 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 <mdcal...@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 <mo...@askmonty.org>wrote: >> >>> >>> Hi! >>> >>> >>>>> "AskMonty" == AskMonty KB <nore...@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 >> mdcal...@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 >> >> > -- Mark Callaghan mdcal...@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
