[Maria-discuss] ALGORITHM INPLACE for 10.0.24-MariaDB
Hi All, For the following statement... ALTER ONLINE TABLE dom_audit_event ENGINE=INNODB, ALGORITHM=INPLACE; I can still see that MariaDB is still using the value of tmpdir. We have this set to tmpfs on some of our servers and this causes some ALTER statements on big tables to fail. It was my understanding that if you specified INPLACE then the statement would fail if this was not possible. Am I missing something? Cheers, Rhys
Hi, Rhys.Campbell! On May 04, Rhys.Campbell@swisscom.com wrote:
Hi All,
For the following statement...
ALTER ONLINE TABLE dom_audit_event ENGINE=INNODB, ALGORITHM=INPLACE;
I can still see that MariaDB is still using the value of tmpdir. We have this set to tmpfs on some of our servers and this causes some ALTER statements on big tables to fail. It was my understanding that if you specified INPLACE then the statement would fail if this was not possible.
Yes, your understanding is correct. Furthermore, copy (not inplace) ALTER does not use tmpdir, it creates a temporary table in the datadir, and later renames it to the actual table name. If the temporary table were created in tmpdir, it could not be renamed to a different filesystem. So, that tmpdir usage you're seeing is not due to not-inplace alter. The alter isstill done inplace, and tmpdir is used for something else. Probaby for merge sort. Regards, Sergei Chief Architect MariaDB and security@mariadb.org
The log for changes during the online ALTER goes in the tmp dir. The log is applied at the end of the statement. Sent from my iPhone
On May 4, 2016, at 2:13 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Rhys.Campbell!
On May 04, Rhys.Campbell@swisscom.com wrote: Hi All,
For the following statement...
ALTER ONLINE TABLE dom_audit_event ENGINE=INNODB, ALGORITHM=INPLACE;
I can still see that MariaDB is still using the value of tmpdir. We have this set to tmpfs on some of our servers and this causes some ALTER statements on big tables to fail. It was my understanding that if you specified INPLACE then the statement would fail if this was not possible.
Yes, your understanding is correct.
Furthermore, copy (not inplace) ALTER does not use tmpdir, it creates a temporary table in the datadir, and later renames it to the actual table name. If the temporary table were created in tmpdir, it could not be renamed to a different filesystem.
So, that tmpdir usage you're seeing is not due to not-inplace alter. The alter isstill done inplace, and tmpdir is used for something else. Probaby for merge sort.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
_______________________________________________ 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
Thanks both. I've found the appropriate part of the manual.. "The INPLACE algorithm sometimes requires temporary files. These files are created in the temporary directory, specified in the tmpdir server system variable." https://mariadb.com/kb/en/mariadb/alter-table/ Is there any way for estimating the amount of tmpdir space you might need if altering a table this way? Rhys -----Original Message----- From: Justin Swanhart [mailto:greenlion@gmail.com] Sent: Wednesday, May 04, 2016 1:37 PM To: Sergei Golubchik <serg@mariadb.org> Cc: Campbell Rhys, PMK-ACS-QPM <Rhys.Campbell@swisscom.com>; maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] ALGORITHM INPLACE for 10.0.24-MariaDB The log for changes during the online ALTER goes in the tmp dir. The log is applied at the end of the statement. Sent from my iPhone
On May 4, 2016, at 2:13 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Rhys.Campbell!
On May 04, Rhys.Campbell@swisscom.com wrote: Hi All,
For the following statement...
ALTER ONLINE TABLE dom_audit_event ENGINE=INNODB, ALGORITHM=INPLACE;
I can still see that MariaDB is still using the value of tmpdir. We have this set to tmpfs on some of our servers and this causes some ALTER statements on big tables to fail. It was my understanding that if you specified INPLACE then the statement would fail if this was not possible.
Yes, your understanding is correct.
Furthermore, copy (not inplace) ALTER does not use tmpdir, it creates a temporary table in the datadir, and later renames it to the actual table name. If the temporary table were created in tmpdir, it could not be renamed to a different filesystem.
So, that tmpdir usage you're seeing is not due to not-inplace alter. The alter isstill done inplace, and tmpdir is used for something else. Probaby for merge sort.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
_______________________________________________ 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
participants (3)
-
Justin Swanhart -
Rhys.Campbell＠swisscom.com -
Sergei Golubchik