Hi, Vicentiu! On Mar 16, vicentiu@mariadb.org wrote:
revision-id: 53b73a7940039809a02184a69502142d767b0741 parent(s): 197afb413fcc9f06b5e5e6ef41ce980d108b354f committer: Vicențiu Ciorbaru branch nick: server timestamp: 2015-03-16 21:59:26 +0200 message:
MDEV-6714 mysqldump slow with tables in big databases
First draft commit. Performance speedup achieved.
--- client/mysqldump.c | 15 ++++++++++----- 1 file changed, 10 insertions(+), 5 deletions(-)
diff --git a/client/mysqldump.c b/client/mysqldump.c index 2da4ce6..6138e94 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -3341,7 +3341,8 @@ static int dump_triggers_for_table(char *table_name, char *db_name) /* Get list of triggers. */
my_snprintf(query_buff, sizeof(query_buff), - "SHOW TRIGGERS LIKE %s", + "SELECT TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS " + "where TRIGGER_SCHEMA = DATABASE() and EVENT_OBJECT_TABLE = %s",
This is wrong, should be EVENT_OBJECT_SCHEMA, not TRIGGER_SCHEMA. Why? That's how I_S optimizations work - for file-based objects it might either use readdir to get all files and then select those that it needs. Or it can open the requested file directly, if it knows the database (= directory name) and the object name (= file name). It kind of works like an index over (db_name, object_name). And what fields are "indexed" is specified in the ST_SCHEMA_TABLE structure for the I_S table in question. For I_S.TRIGGERS it's {"TRIGGERS", triggers_fields_info, 0, get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0, OPEN_TRIGGER_ONLY|OPTIMIZE_I_S_TABLE}, where triggers_fields_info is the list of fields and 5,6 are "indexed" fields. Looking at triggers_fields_info you'll see that 5,6 fields are EVENT_OBJECT_SCHEMA and EVENT_OBJECT_TABLE.
quote_for_like(table_name, name_buff));
if (mysql_query_with_error_report(mysql, &show_triggers_rs, query_buff)) @@ -4716,8 +4717,12 @@ static char *get_actual_table_name(const char *old_table_name, MEM_ROOT *root)
/* Check memory for quote_for_like() */ DBUG_ASSERT(2*sizeof(old_table_name) < sizeof(show_name_buff)); - my_snprintf(query, sizeof(query), "SHOW TABLES LIKE %s", + my_snprintf(query, sizeof(query), + "select table_name from information_schema.tables " + "where table_schema = DATABASE() " + "and table_name = %s", quote_for_like(old_table_name, show_name_buff));
This is wrong, here and everywhere. quote_for_like() quotes, exactly, for LIKE. It is a wrong kind of quoting for equality operator.
+ verbose_msg(query);
Regards, Sergei