[Commits] 135d926d3: MDEV-6714 mysqldump slow with tables in big databases

vicentiu at mariadb.org vicentiu at mariadb.org
Thu Mar 19 15:19:28 EET 2015


revision-id: 135d926d347443672626f1e655709fd5bf6dec80
parent(s): 197afb413fcc9f06b5e5e6ef41ce980d108b354f
committer: Vicențiu Ciorbaru
branch nick: server
timestamp: 2015-03-19 15:16:22 +0200
message:

MDEV-6714 mysqldump slow with tables in big databases

mysqldump now attempts to make use of the INFORMATION_SCHEMA tables.
If the table name is not found with a case sensitive search, it
fallbacks to a case insensitive search.

---
 client/mysqldump.c | 90 ++++++++++++++++++++++++++++++++++++++++++------------
 1 file changed, 71 insertions(+), 19 deletions(-)

diff --git a/client/mysqldump.c b/client/mysqldump.c
index 2da4ce6..c265217 100644
--- a/client/mysqldump.c
+++ b/client/mysqldump.c
@@ -3341,8 +3341,10 @@ 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",
-              quote_for_like(table_name, name_buff));
+              "SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS "
+              "WHERE EVENT_OBJECT_SCHEMA = DATABASE() AND "
+              "EVENT_OBJECT_TABLE = \"%s\"",
+              table_name);
 
   if (mysql_query_with_error_report(mysql, &show_triggers_rs, query_buff))
     goto done;
@@ -4695,29 +4697,37 @@ static my_bool dump_all_views_in_db(char *database)
 
 
 /*
-  get_actual_table_name -- executes a SHOW TABLES LIKE '%s' to get the actual
-  table name from the server for the table name given on the command line.
-  we do this because the table name given on the command line may be a
-  different case (e.g.  T1 vs t1)
-
-  RETURN
-    pointer to the table name
-    0 if error
+  See get_actual_table_name. Used to retrieve the correct table name
+  from the database schema.
 */
-
-static char *get_actual_table_name(const char *old_table_name, MEM_ROOT *root)
+static char *get_actual_table_name_helper(const char *old_table_name,
+                                          my_bool case_sensitive,
+                                          MEM_ROOT *root)
 {
   char *name= 0;
   MYSQL_RES  *table_res;
   MYSQL_ROW  row;
   char query[50 + 2*NAME_LEN];
   char show_name_buff[FN_REFLEN];
-  DBUG_ENTER("get_actual_table_name");
+  DBUG_ENTER("get_actual_table_name_helper");
 
   /* 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",
-              quote_for_like(old_table_name, show_name_buff));
+
+  if (case_sensitive)
+  {
+    DBUG_PRINT("info", ("case sensitive search"));
+    my_snprintf(query, sizeof(query),
+                "SELECT table_name FROM INFORMATION_SCHEMA.TABLES "
+                "WHERE table_schema = DATABASE() AND table_name = \"%s\"",
+                old_table_name);
+  }
+  else
+  {
+    DBUG_PRINT("info", ("case insensitive search"));
+    my_snprintf(query, sizeof(query), "SHOW TABLES LIKE %s",
+                quote_for_like(old_table_name, show_name_buff));
+  }
 
   if (mysql_query_with_error_report(mysql, 0, query))
     return NullS;
@@ -4742,6 +4752,46 @@ static char *get_actual_table_name(const char *old_table_name, MEM_ROOT *root)
   DBUG_RETURN(name);
 }
 
+/*
+  get_actual_table_name -- executes a SELECT .. FROM I_S.tables to check
+  if the table name given on the command line matches the one in the database.
+  If the table is not found, it falls back to a slower SHOW TABLES LIKE '%s' to
+  get the actual table name from the server.
+
+  We do this because the table name given on the command line may be a
+  different case (e.g.  T1 vs t1), but checking this takes a long time
+  when there are many tables present.
+
+  RETURN
+    pointer to the table name
+    0 if error
+*/
+
+static char *get_actual_table_name(const char *old_table_name, MEM_ROOT *root)
+{
+  char *name= 0;
+  MYSQL_RES  *table_res;
+  MYSQL_ROW  row;
+  int lower_case_table_names;
+  const char *show_var_query = "SHOW VARIABLES LIKE 'lower_case_table_names'";
+  DBUG_ENTER("get_actual_table_name");
+
+  if (mysql_query_with_error_report(mysql, &table_res, show_var_query))
+    return NullS;
+
+  if ((row= mysql_fetch_row(table_res)))
+  {
+    lower_case_table_names= atoi(row[1]);
+    mysql_free_result(table_res);
+  }
+
+  name= get_actual_table_name_helper(old_table_name, TRUE, root);
+  if (!name && !lower_case_table_names)
+    name= get_actual_table_name_helper(old_table_name, FALSE, root);
+  DBUG_RETURN(name);
+}
+
+
 
 static int dump_selected_tables(char *db, char **table_names, int tables)
 {
@@ -5369,8 +5419,10 @@ char check_if_ignore_table(const char *table_name, char *table_type)
 
   /* Check memory for quote_for_like() */
   DBUG_ASSERT(2*sizeof(table_name) < sizeof(show_name_buff));
-  my_snprintf(buff, sizeof(buff), "show table status like %s",
-              quote_for_like(table_name, show_name_buff));
+  my_snprintf(buff, sizeof(buff),
+              "SELECT table_type FROM INFORMATION_SCHEMA.TABLES "
+              "WHERE table_name = \"%s\"",
+              table_name);
   if (mysql_query_with_error_report(mysql, &res, buff))
   {
     if (mysql_errno(mysql) != ER_PARSE_ERROR)
@@ -5388,7 +5440,7 @@ char check_if_ignore_table(const char *table_name, char *table_type)
     mysql_free_result(res);
     DBUG_RETURN(result);                         /* assume table is ok */
   }
-  if (!(row[1]))
+  if (!(row[0]))
     strmake(table_type, "VIEW", NAME_LEN-1);
   else
   {
@@ -5398,7 +5450,7 @@ char check_if_ignore_table(const char *table_name, char *table_type)
       these types, but we do want to use delayed inserts in the dump if
       the table type is _NOT_ one of these types
     */
-    strmake(table_type, row[1], NAME_LEN-1);
+    strmake(table_type, row[0], NAME_LEN-1);
     if (opt_delayed)
     {
       if (strcmp(table_type,"MyISAM") &&


More information about the commits mailing list