[Maria-discuss] New Question: Aria Max Table Size for internal on-disk temporary tables
Hello, A new question has been asked in "Aria" by trsystran: -------------------------------- Using MariaDB 5.5.31 (deb file for ubuntu 12.04 amd_64: 5.5.31+maria-1~precise). 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. If the on-disk .MAD file reaches 4GB, an error occurs: {{{ [ERROR] mysqld: The table '/tmp/#sql_74e_0' is full }}} How can we change this limit? 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. Looking at the code I found this in maria_create(): {{{ data_file_length= ((((ulonglong) 1 << ((BLOCK_RECORD_POINTER_SIZE-1) * 8))/2 -1) * maria_block_size); }}} Which amounts to 4GB with default values (8192 for maria_block_size). I created a new DB with aria-block-size = 32768 (the max value), and it still failed at 4GB. Even if it worked, it would mean the max size is only 16GB, and setting a large block size may have performance penalties. So what is the proper fix to increase this limit? With MyISAM as on-disk temporary tables engine the limit seems to be much higher (at least 14GB from experiments). -------------------------------- To view or answer this question please visit: http://kb.askmonty.org/en/aria-max-table-size-for-internal-on-disk-temporary...
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
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
Hi!
"MARK" == MARK CALLAGHAN <mdcallag@gmail.com> writes:
MARK> On many occasions I have worked with DBAs who really, really wanted a limit MARK> on the max on-disk size for implicit and explicit temp tables. They prefer MARK> to fail a long running query over filling up a disk and halting the MARK> database. Or don't have a limit but make the query fail when a temp table MARK> write gets ENOSPC. I thought that I had fixed that for internal temporary files but apparently I haven't done that. I will create a Jira task for doing that for Aria (which is used for internal temporary tables in MariaDB). Explicit temporary tables are a bit hard as this depends on the storage engine. I am not sure how InnoDB reacts to a disk full for a temporary table. Jan, do you happen to know? At least, it should not wait for disk space to be freed, like MyISAM or Aria. Regards, Monty
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
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/36... 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@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 <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
-- Mark Callaghan mdcallag@gmail.com
Hi!
"MARK" == MARK CALLAGHAN <mdcallag@gmail.com> writes:
MARK> Not sure this was the right way to do it, but it prevented many full-disk MARK> problems. MARK> http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/36... MARK> Port tmp_table_max_file_size option from the Google patch to limit max size of MARK> on-disk temp tables. This adds the my.cnf option and session variable MARK> "tmp_table_max_file_size" to limit the max size of an on-disk (MyISAM) MARK> temp table. MARK> The error ER_TMP_TABLE_MAX_FILE_SIZE_EXCEEDED is raised when the limit MARK> is exceeded. This code sets te same size for implicate and internal tmp tables. Mark, do you think we should have both variables availble? (It's similar amount of work to add two variables instead of one). I looked at the above patch (thanks for the link) and most of if looks good, but there is a few ways we can improve it when we apply it. - Reuse 'record file full' error. - No need to add THD argument to some functions; One can access THD trough the TABLE object. - We need to add same limits to the Aria engine (no big deal) - I prefer to have MAX_UNSIGNED_LONGLONG as the default value for the variable instead of 0. (no reason to make 0 a special case). - I prefer to send the information of the max file length through the ...CREATE_INFO, like with all other parameters. Svoj, any chance you can do this in 5.5 the incoming week? What I would also like to fix, which is a bit harder so we have to do this a later: - If we get disk full when writing to an internal temporary aria file, we should mark the file as 'crashed' and next access to the file should give a 'could-not-write-as-disk-was-full' error. This is a bit complex as the write is done in the background by another process, but in practice this should be easy to add and detect. The main thing to do for temporary files: - Add file specific flag to my_pwrite() for ma_pagecache.c that it should not wait for file full. - The file will automaticly be marked as crashed. We should however detect this case and ensure that we get a proper error message ("ABORTED_AS_FILE_SYSTEM_WAS_FULL" ?) for this case. Still, the later is not more than 3-4 hours of work... Regards, Monty
On Sat, Jun 15, 2013 at 7:27 AM, Michael Widenius <monty@askmonty.org>wrote:
MARK> http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/36...
MARK> Port tmp_table_max_file_size option from the Google patch to limit max size of MARK> on-disk temp tables. This adds the my.cnf option and session variable MARK> "tmp_table_max_file_size" to limit the max size of an on-disk (MyISAM) MARK> temp table. MARK> The error ER_TMP_TABLE_MAX_FILE_SIZE_EXCEEDED is raised when the limit MARK> is exceeded.
This code sets te same size for implicate and internal tmp tables.
Mark, do you think we should have both variables availble? (It's similar amount of work to add two variables instead of one).
I looked at the above patch (thanks for the link) and most of if looks good, but there is a few ways we can improve it when we apply it.
I am sure my patch can be improved. It changes code that I don't know much about. I think it is better to use separate variables for implicit vs explicit temp tables. But the behavior that is most needed is 1) better behavior when the filesystem is full during temp table writes and 2) maybe a global limit on the amount of disk space that can be used by temp tables. Per-session limits might not prevent concurrent sessions from causing a problem. -- Mark Callaghan mdcallag@gmail.com
participants (4)
-
AskMonty KB -
Jeremy Zawodny -
MARK CALLAGHAN -
Michael Widenius