[Maria-developers] MDEV-6714: A couple of questions
Hi Sergei, I've achieved the desired performance speedup related to MDEV-6714. You can check the preliminary patch on the commits mailing-list. I'll clean up the code and then submit a final version for review. I do however have two concerns: * By using SHOW TABLES LIKE %s, we performed a case insensitive search to find the table's correct name case. This command takes a long time with a lot of tables. Using an equality operator in I_D speeds it up, but it no longer matches if we have a different case. Possible solution: Try to search for the table with the provided string via INFORMATION_SCHEMA using the equals operator. If we can not find the table, fall back to a case insensitive search that takes longer. * The I_S.triggers and I_S.tables are available since 5.5 and 5.1 respectively. I suppose it is safe to assume we will have these tables available when running mysqldump. I can code all the changes to fall back to the previous query if the one that uses I_S tables fails. Regards, Vicentiu
I think: 1) case sensivity of tables and databases should always respect the setting of lower_case_table_names variable ( http://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html). Compromissing here in order to achive speed in completely unacceptable IMO. and 2) triiggers are always case sensitive on case sensitive file systems and non-sensitive on non-case-sensitive file systems. There is no setting for this (and the lower_case_table_names variable does not control it either). 3) "I suppose it is safe to assume we will have these tables available when running mysqldump". As safe any backup of any object. A user can only 'mysqldump' objects he has privileges to access. -- Peter -- Webyog On Mon, Mar 16, 2015 at 9:21 PM, Vicențiu Ciorbaru <vicentiu@mariadb.org> wrote:
Hi Sergei,
I've achieved the desired performance speedup related to MDEV-6714. You can check the preliminary patch on the commits mailing-list. I'll clean up the code and then submit a final version for review. I do however have two concerns:
* By using SHOW TABLES LIKE %s, we performed a case insensitive search to find the table's correct name case. This command takes a long time with a lot of tables. Using an equality operator in I_D speeds it up, but it no longer matches if we have a different case.
Possible solution: Try to search for the table with the provided string via INFORMATION_SCHEMA using the equals operator. If we can not find the table, fall back to a case insensitive search that takes longer.
* The I_S.triggers and I_S.tables are available since 5.5 and 5.1 respectively. I suppose it is safe to assume we will have these tables available when running mysqldump. I can code all the changes to fall back to the previous query if the one that uses I_S tables fails.
Regards, Vicentiu
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Hi, Peter! On Mar 16, Peter Laursen wrote:
I think:
1) case sensivity of tables and databases should always respect the setting of lower_case_table_names variable ( http://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html). Compromissing here in order to achive speed in completely unacceptable IMO.
Unfortunately (and I think it's a bug in the information_schema.tables) mysqldump for many years accepted table names ignoring the case sensitivity. That is, one could always specify the table name for mysqldump in the incorrect letter case. Even on Linux. I would agree that mysqldump should have respected lower_case_table_names. But it doesn't, and I don't want to risk changing it :( Regards, Sergei
Hi, Vicențiu! On Mar 16, Vicențiu Ciorbaru wrote:
I've achieved the desired performance speedup related to MDEV-6714. You can check the preliminary patch on the commits mailing-list. I'll clean up the code and then submit a final version for review. I do however have two concerns:
* By using SHOW TABLES LIKE %s, we performed a case insensitive search to find the table's correct name case. This command takes a long time with a lot of tables. Using an equality operator in I_D speeds it up, but it no longer matches if we have a different case.
Possible solution: Try to search for the table with the provided string via INFORMATION_SCHEMA using the equals operator. If we can not find the table, fall back to a case insensitive search that takes longer.
Okay, good idea. A drawback is that if the table doesn't exist, you'll need a slow search to find it out. But I don't see a solution for this. At least, you could check lower_case_table_names. And if it's not 0, you don't need to do a case insensitive search.
* The I_S.triggers and I_S.tables are available since 5.5 and 5.1 respectively. I suppose it is safe to assume we will have these tables available when running mysqldump. I can code all the changes to fall back to the previous query if the one that uses I_S tables fails.
Right, fallback is fine. Regards, Sergei
participants (3)
-
Peter Laursen -
Sergei Golubchik -
Vicențiu Ciorbaru