← Back to team overview

maria-developers team mailing list archive

Re: 53b73a7: MDEV-6714 mysqldump slow with tables in big databases

 

Hi, Vicentiu!

On Mar 16, vicentiu@xxxxxxxxxxx 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