[MDEV-5723] mysqldump -uroot unusable for multi-database operations, checks all databases and tables when dumping a single DB Created: 2014-02-24  Updated: 2014-03-05  Resolved: 2014-03-05

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.36, 10.0.8
Fix Version/s: 5.5.37, 10.0.9

Type: Bug Priority: Major
Reporter: Jan Ingvoldstad Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: information_schema, mysqldump, optimizer
Environment:

Debian 7.4 (Wheezy), Linux mysql06 3.2.0-4-amd64 #1 SMP Debian 3.2.54-2 x86_64 GNU/Linux
deb http://mariadb.mirror.nucleus.be//repo/10.0/debian wheezy main
deb-src http://mariadb.mirror.nucleus.be//repo/10.0/debian wheezy main


 Description   

When using mysqldump -uroot, this initiates a very, very resource intensive recursive check of every single database and table on the system.

On a system with some 12k databases and 540k .frm files, this takes a long, long time.

(The following output has substituted "censored" for the real username.)

Using mysqldump -ucensored takes next to no time at all, and does not last for long enough that I can even see the query running.

The issue does not appear to be present in MySQL 5.5.36 server as delivered by Oracle, but it occurs also when using mysqldump from that version, and therefore seems like it is in the server code, not the client code.

Someone else have asked this as a question on serverfault.com also, so I'm not the only one experiencing the issue:

http://serverfault.com/questions/574646/mariadb-10-0-slow-work-mysqldump

Here is the relevant excerpt from SHOW FULL PROCESSLIST for my use case:

| 292 | root | localhost | NULL | Query   |  265 | Opening tables | SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('censored'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME |    0.000 |

And here is some sample output from strace, showing what's going on:

[pid 37289] getcwd("/var/lib/mysql", 4096) = 15
 
[pid 37289] lstat("/var/lib/mysql/CENSORED", {st_mode=S_IFDIR|0700, st_size=8192, ...}) = 0
 
[pid 37289] read(1449, "\376\1\n\f\22\0V\0\1\0\37\10\0\0\371\0010\3\0\0\0\0\0\0\0\0\0\2!\0\t\0"..., 64) = 64
 
[pid 37289] read(1449, "\0\20\2539\2215\232.\21\343\217\221\270\312:d\362\200\217\5\0\0\1\1\0\0\n\0\0\0\4\0"..., 2015) = 2015
 
[pid 37289] close(1449)                 = 0
 
[pid 37289] time(NULL)                  = 1393234684
 
[pid 37289] access("./CENSORED/flabber_updates.TRG", F_OK) = -1 ENOENT (No such file or directory)
 
[pid 37289] stat("./CENSORED/flabber_updates.frm", {st_mode=S_IFREG|0660, st_size=2079, ...}) = 0
 
[pid 37289] open("./CENSORED/flabber_user_notes.frm", O_RDONLY) = 1449
 
[pid 37289] getcwd("/var/lib/mysql", 4096) = 15
 
[pid 37289] lstat("/var/lib/mysql/CENSORED", {st_mode=S_IFDIR|0700, st_size=8192, ...}) = 0
 
[pid 37289] read(1449, "\376\1\n\f\22\0V\0\1\0t\n\0\0\313\5\201\1\0\0\0\0\0\0\0\0\0\2_\0\t\0"..., 64) = 64
 
[pid 37289] read(1449, "\0\20\253B\364\376\232.\21\343\217\221\270\312:d\362\200\262\7\0\0\3\3\0\0&\0\0\0\4\0"..., 2612) = 2612
 
[pid 37289] close(1449)                 = 0
 
[pid 37289] time(NULL)                  = 1393234684
 
[pid 37289] access("./CENSORED/flabber_user_notes.TRG", F_OK) = -1 ENOENT (No such file or directory)
 
[pid 37289] stat("./CENSORED/flabber_user_notes.frm", {st_mode=S_IFREG|0660, st_size=2676, ...}) = 0
 
[pid 37289] open("./CENSORED/flabber_user_profiles.frm", O_RDONLY) = 1449



 Comments   
Comment by Elena Stepanova [ 2014-02-28 ]

Hi Jan,

Thanks for the report.

MySQL runs the same INFORMATION_SCHEMA query, but the query is almost instant on MySQL, while on MariaDB it takes considerable time.
The trick with a non-root user works because the user does not have access to the database(s) where all those partitioned tables sit.

As a workaround, please try 

# from mysql client:
 
SET GLOBAL optimizer_switch='semijoin=off';
 
 
 
# execute your mysqldump(s) as usual, under root
 
 
 
# from mysql client:
 
SET GLOBAL optimizer_switch='semijoin=on';

It should be easier than creating a backup user for each schema that you want to dump.

Alternatively, you can switch off semijoin in your server .cnf file, but you can get performance regression on some "normal" (non-INFORMATION_SCHEMA) queries.

Comment by Elena Stepanova [ 2014-02-28 ]

psergey

Here is a test case to reproduce the issue (the query is taken from mysqldump as is, no changes):

--source include/have_partition.inc
 
 
 
CREATE DATABASE db;
 
USE db;
 
 
 
let $num = 500;
 
 
 
--disable_query_log
 
--echo # Creating tables...
 
while ($num)
 
{
 
	eval CREATE TABLE t$num (i INT) ENGINE=MyISAM PARTITION BY HASH(i) PARTITIONS 16;
 
	dec $num;
 
}
 
--enable_query_log
 
 
 
FLUSH TABLES;
 
 
 
SELECT NOW();
 
SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME;
 
SELECT NOW();
 
 
 
DROP DATABASE db;

Before/after timestamps:

on MariaDB 5.5:
2014-02-28 03:36:18
2014-02-28 03:36:29

on MariaDB 10.0:
2014-02-28 03:37:06
2014-02-28 03:37:23

on MariaDB 10.0, semijoin=off:
2014-02-28 03:37:56
2014-02-28 03:37:56

on MySQL 5.6 (default switch):
2014-02-28 02:43:25
2014-02-28 02:43:25

EXPLAIN on 10.0 with semijoin=on:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	FILES	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using where; Using temporary; Using filesort
 
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	195	func	1	100.00	
2	MATERIALIZED	FILES	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using where
 
2	MATERIALIZED	PARTITIONS	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using where; Open_full_table; Scanned all databases; Using join buffer (flat, BNL join)
 
Warnings:
 
Note	1003	select `information_schema`.`FILES`.`LOGFILE_GROUP_NAME` AS `LOGFILE_GROUP_NAME`,`information_schema`.`FILES`.`FILE_NAME` AS `FILE_NAME`,`information_schema`.`FILES`.`TOTAL_EXTENTS` AS `TOTAL_EXTENTS`,`information_schema`.`FILES`.`INITIAL_SIZE` AS `INITIAL_SIZE`,`information_schema`.`FILES`.`ENGINE` AS `ENGINE`,`information_schema`.`FILES`.`EXTRA` AS `EXTRA` from `INFORMATION_SCHEMA`.`FILES` semi join (`INFORMATION_SCHEMA`.`PARTITIONS` join `INFORMATION_SCHEMA`.`FILES`) where ((`information_schema`.`PARTITIONS`.`TABLESPACE_NAME` = `information_schema`.`FILES`.`TABLESPACE_NAME`) and (`information_schema`.`FILES`.`FILE_TYPE` = 'UNDO LOG') and (`information_schema`.`FILES`.`FILE_NAME` is not null) and (`information_schema`.`FILES`.`FILE_TYPE` = 'DATAFILE') and (`information_schema`.`PARTITIONS`.`TABLE_SCHEMA` = 'non-existing')) group by `information_schema`.`FILES`.`LOGFILE_GROUP_NAME`,`information_schema`.`FILES`.`FILE_NAME`,`information_schema`.`FILES`.`ENGINE` order by `information_schema`.`FILES`.`LOGFILE_GROUP_NAME`

EXPLAIN on 10.0 with semijoin=off:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	FILES	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using where; Using temporary; Using filesort
 
2	MATERIALIZED	FILES	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using where
 
3	MATERIALIZED	PARTITIONS	ALL	NULL	TABLE_SCHEMA	NULL	NULL	NULL	NULL	Using where; Open_full_table; Scanned 1 database
 
Warnings:
 
Note	1003	select `information_schema`.`FILES`.`LOGFILE_GROUP_NAME` AS `LOGFILE_GROUP_NAME`,`information_schema`.`FILES`.`FILE_NAME` AS `FILE_NAME`,`information_schema`.`FILES`.`TOTAL_EXTENTS` AS `TOTAL_EXTENTS`,`information_schema`.`FILES`.`INITIAL_SIZE` AS `INITIAL_SIZE`,`information_schema`.`FILES`.`ENGINE` AS `ENGINE`,`information_schema`.`FILES`.`EXTRA` AS `EXTRA` from `INFORMATION_SCHEMA`.`FILES` where ((`information_schema`.`FILES`.`FILE_TYPE` = 'UNDO LOG') and (`information_schema`.`FILES`.`FILE_NAME` is not null) and <expr_cache><`information_schema`.`FILES`.`LOGFILE_GROUP_NAME`>(<in_optimizer>(`information_schema`.`FILES`.`LOGFILE_GROUP_NAME`,`information_schema`.`FILES`.`LOGFILE_GROUP_NAME` in ( <materialize> (select `information_schema`.`FILES`.`LOGFILE_GROUP_NAME` from `INFORMATION_SCHEMA`.`FILES` where ((`information_schema`.`FILES`.`FILE_TYPE` = 'DATAFILE') and <expr_cache><`information_schema`.`FILES`.`TABLESPACE_NAME`>(<in_optimizer>(`information_schema`.`FILES`.`TABLESPACE_NAME`,`information_schema`.`FILES`.`TABLESPACE_NAME` in ( <materialize> (select `information_schema`.`PARTITIONS`.`TABLESPACE_NAME` from `INFORMATION_SCHEMA`.`PARTITIONS` where (`information_schema`.`PARTITIONS`.`TABLE_SCHEMA` = 'non-existing') ), <primary_index_lookup>(`information_schema`.`FILES`.`TABLESPACE_NAME` in <temporary table> on distinct_key where ((`information_schema`.`FILES`.`TABLESPACE_NAME` = `<subquery3>`.`TABLESPACE_NAME`))))))) ), <primary_index_lookup>(`information_schema`.`FILES`.`LOGFILE_GROUP_NAME` in <temporary table> on distinct_key where ((`information_schema`.`FILES`.`LOGFILE_GROUP_NAME` = `<subquery2>`.`LOGFILE_GROUP_NAME`))))))) group by `information_schema`.`FILES`.`LOGFILE_GROUP_NAME`,`information_schema`.`FILES`.`FILE_NAME`,`information_schema`.`FILES`.`ENGINE` order by `information_schema`.`FILES`.`LOGFILE_GROUP_NAME`

Comment by Sergei Petrunia [ 2014-02-28 ]

If I change the query so that inner select calls FILES table "F2", then the EXPLAINs are:

default @@optimizer_switch:

+--+------------+-----------+------+-------------+------------+-------+----+----+---------------------------------------------------------------------------------------+
 
|id|select_type |table      |type  |possible_keys|key         |key_len|ref |rows|Extra                                                                                  |
 
+--+------------+-----------+------+-------------+------------+-------+----+----+---------------------------------------------------------------------------------------+
 
|1 |PRIMARY     |FILES      |ALL   |NULL         |NULL        |NULL   |NULL|NULL|Using where; Using temporary; Using filesort                                           |
 
|1 |PRIMARY     |<subquery2>|eq_ref|distinct_key |distinct_key|195    |func|1   |                                                                                       |
 
|2 |MATERIALIZED|F2         |ALL   |NULL         |NULL        |NULL   |NULL|NULL|Using where                                                                            |
 
|2 |MATERIALIZED|PARTITIONS |ALL   |NULL         |NULL        |NULL   |NULL|NULL|Using where; Open_full_table; Scanned all databases; Using join buffer (flat, BNL join)|
 
+--+------------+-----------+------+-------------+------------+-------+----+----+---------------------------------------------------------------------------------------+

optimizer_switch='semijoin=off':

+--+------------+----------+----+-------------+------------+-------+----+----+------------------------------------------------+
 
|id|select_type |table     |type|possible_keys|key         |key_len|ref |rows|Extra                                           |
 
+--+------------+----------+----+-------------+------------+-------+----+----+------------------------------------------------+
 
|1 |PRIMARY     |FILES     |ALL |NULL         |NULL        |NULL   |NULL|NULL|Using where; Using temporary; Using filesort    |
 
|2 |MATERIALIZED|F2        |ALL |NULL         |NULL        |NULL   |NULL|NULL|Using where                                     |
 
|3 |MATERIALIZED|PARTITIONS|ALL |NULL         |TABLE_SCHEMA|NULL   |NULL|NULL|Using where; Open_full_table; Scanned 1 database|
 
+--+------------+----------+----+-------------+------------+-------+----+----+------------------------------------------------+

Comment by Sergei Petrunia [ 2014-02-28 ]

The query plans are different. The first apparent difference is "Scanned 1 database" vs "Scanned all databases" for table PARTITIONS.

I don't see a reason why the first plan should have "Scanned all databases". The subquery looks like this:

(SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('db')))

and there is no reason for the optimizer to be unable to take advantage of TABLE_SCHEMA IN ('db').

Comment by Sergei Petrunia [ 2014-02-28 ]

== Investigation ==

1. The optimizer attaches TABLE_SCHEMA='db' to table PARTITIONS (everything ok so far)

(gdb) p join->join_tab[1].bush_children.start[1].table->alias.Ptr
$265 = 0x7fffcb856d30 "PARTITIONS"
(gdb) p dbug_print_item(join->join_tab[1].bush_children.start[1].select_cond)
$266 = 0x1951880 "((`information_schema`.`PARTITIONS`.`TABLESPACE_NAME` = `F2`.`TABLESPACE_NAME`) and (`information_schema`.`PARTITIONS`.`TABLE_SCHEMA` = 'db'))"

But then Join Buffering code removes it:

(gdb) wher
#0 st_join_table::set_select_cond (this=0x7fffcb8ac568, to=0x7fffcb8aca90, line=497) at /home/psergey/dev2/10.0/sql/sql_select.h:493
#1 0x00000000006c9d3c in st_join_table::set_cond (this=0x7fffcb8ac568, new_cond=0x7fffcb8aca90) at /home/psergey/dev2/10.0/sql/sql_select.h:497
#2 0x00000000006a3a61 in st_join_table::remove_redundant_bnl_scan_conds (this=0x7fffcb8ac568) at /home/psergey/dev2/10.0/sql/sql_select.cc:10651
#3 0x00000000006a47e3 in make_join_readinfo (join=0x7fffcb8990b0, options=4, no_jbuf_after=3) at /home/psergey/dev2/10.0/sql/sql_select.cc:10912
#4 0x000000000068c008 in JOIN::optimize_inner (this=0x7fffcb8990b0) at /home/psergey/dev2/10.0/sql/sql_select.cc:1764
#5 0x00000000006895da in JOIN::optimize (this=0x7fffcb8990b0) at /home/psergey/dev2/10.0/sql/sql_select.cc:1013
#6 0x0000000000691413 in mysql_select (thd=0x7fffd832e070, rref_pointer_array=0x7fffd83326a0, tables=0x7fffcb84ce28, wild_num=0, fields=..., conds=0x7fffcb84d908, og_num=4, order=0x7fffcb858098, group=0x7fffcb857c78, having=0x0, proc_param=0x0, select_options=2684619524, result=0x7fffcb85fd88, unit=0x7fffd8331d48, select_lex=0x7fffd8332428) at /home/psergey/dev2/10.0/sql/sql_select.cc:3278
#7 0x00000000006c3ce0 in mysql_explain_union (thd=0x7fffd832e070, unit=0x7fffd8331d48, result=0x7fffcb85fd88) at /home/psergey/dev2/10.0/sql/sql_select.cc:23646
#8 0x000000000065b307 in execute_sqlcom_select (thd=0x7fffd832e070, all_tables=0x7fffcb84ce28) at /home/psergey/dev2/10.0/sql/sql_parse.cc:5264
#9 0x0000000000653573 in mysql_execute_command (thd=0x7fffd832e070) at /home/psergey/dev2/10.0/sql/sql_parse.cc:2587
#10 0x000000000065dda5 in mysql_parse (thd=0x7fffd832e070, rawbuf=0x7fffcb84c088 "explain SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_"..., length=788, parser_state=0x7ffff7f384b0) at /home/psergey/dev2/10.0/sql/sql_parse.cc:6447
#11 0x0000000000650637 in dispatch_command (command=COM_QUERY, thd=0x7fffd832e070, packet=0x7fffd263c071 "", packet_length=788) at /home/psergey/dev2/10.0/sql/sql_parse.cc:1308
#12 0x000000000064f985 in do_command (thd=0x7fffd832e070) at /home/psergey/dev2/10.0/sql/sql_parse.cc:1005
#13 0x000000000077121d in do_handle_one_connection (thd_arg=0x7fffd832e070) at /home/psergey/dev2/10.0/sql/sql_connect.cc:1379
#14 0x0000000000770f70 in handle_one_connection (arg=0x7fffd832e070) at /home/psergey/dev2/10.0/sql/sql_connect.cc:1293
#15 0x0000000000ae3bb7 in pfs_spawn_thread (arg=0x7fffd83a4990) at /home/psergey/dev2/10.0/storage/perfschema/pfs.cc:1853
#16 0x00007ffff7166e9a in start_thread (arg=0x7ffff7f39700) at pthread_create.c:308
#17 0x00007ffff66813fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
#18 0x0000000000000000 in ?? ()
(gdb)

Old:
(gdb) p dbug_print_item(tmp_select_cond)
$285 = 0x1951880 "((`information_schema`.`PARTITIONS`.`TABLESPACE_NAME` = `F2`.`TABLESPACE_NAME`) and (`information_schema`.`PARTITIONS`.`TABLE_SCHEMA` = 'db'))"

New:
(gdb) p dbug_print_item(new_cond)
$283 = 0x1951880 "(`information_schema`.`PARTITIONS`.`TABLESPACE_NAME` = `F2`.`TABLESPACE_NAME`)"

This is because TABLE_SCHEMA='db' is in cache_select->cond:
(gdb) p tab->table->alias.Ptr
$293 = 0x7fffcb856d30 "PARTITIONS"
(gdb) p dbug_print_item(tab->cache_select->cond)
$291 = 0x1951880 "(`information_schema`.`PARTITIONS`.`TABLE_SCHEMA` = 'db')"

The problem is that I_S optimization is done on tab->select_cond :
(gdb) wher
#0 get_all_tables (thd=0x7fffd832e070, tables=0x7fffcb856d40, cond=0x7fffcb8aca90) at /home/psergey/dev2/10.0/sql/sql_show.cc:4724
#1 0x00000000006e9391 in do_fill_table (thd=0x7fffd832e070, table_list=0x7fffcb856d40, join_table=0x7fffcb8ac568) at /home/psergey/dev2/10.0/sql/sql_show.cc:8066
#2 0x00000000006e970a in get_schema_tables_result (join=0x7fffcb8990b0, executed_place=PROCESSED_BY_JOIN_EXEC) at /home/psergey/dev2/10.0/sql/sql_show.cc:8164
#3 0x000000000068e903 in JOIN::exec_inner (this=0x7fffcb8990b0) at /home/psergey/dev2/10.0/sql/sql_select.cc:2520
#4 0x000000000068ded0 in JOIN::exec (this=0x7fffcb8990b0) at /home/psergey/dev2/10.0/sql/sql_select.cc:2355
#5 0x00000000006914a3 in mysql_select (thd=0x7fffd832e070, rref_pointer_array=0x7fffd83326a0, tables=0x7fffcb84ce28, wild_num=0, fields=..., conds=0x7fffcb84d908, og_num=4, order=0x7fffcb858098, group=0x7fffcb857c78, having=0x0, proc_param=0x0, select_options=2684619524, result=0x7fffcb85fd88, unit=0x7fffd8331d48, select_lex=0x7fffd8332428) at /home/psergey/dev2/10.0/sql/sql_select.cc:3292
#6 0x00000000006c3ce0 in mysql_explain_union (thd=0x7fffd832e070, unit=0x7fffd8331d48, result=0x7fffcb85fd88) at /home/psergey/dev2/10.0/sql/sql_select.cc:23646

(gdb) p dbug_print_item(cond)
$300 = 0x1951880 "(`information_schema`.`PARTITIONS`.`TABLESPACE_NAME` = `F2`.`TABLESPACE_NAME`)"

The bad call is here in do_fill_table():

bool res= table_list->schema_table->fill_table(
thd, table_list, join_table->select_cond);

Comment by Sergei Petrunia [ 2014-02-28 ]

Possible solutions:

  • Call fill_table() twice
  • Pass fill_table() both conditions.
Comment by Sergei Petrunia [ 2014-03-03 ]

The problem that is described above (let's call it "Scanned all databases"-problem) is an unintended side effect of this fix in MariaDB 10.0:

timour@askmonty.org-20131018084525-0fal318cbopywlkp,
MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins.

If we look at MariaDB 5.5, it doesn't have the "scanned all databases"-problem:

+------+--------------+-------------+--------+---------------+--------------+---------+------+------+--------------------------------------------------------------------------------------+                                                      
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra                                                                                |
 
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+--------------------------------------------------------------------------------------+
 
|    1 | PRIMARY      | FILES       | ALL    | NULL          | NULL         | NULL    | NULL | NULL | Using where; Using temporary; Using filesort                                         |
 
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 195     | func |    1 |                                                                                      |
 
|    2 | MATERIALIZED | FILES       | ALL    | NULL          | NULL         | NULL    | NULL | NULL | Using where                                                                          |
 
|    2 | MATERIALIZED | PARTITIONS  | ALL    | NULL          | TABLE_SCHEMA | NULL    | NULL | NULL | Using where; Open_full_table; Scanned 1 database; Using join buffer (flat, BNL join) |
 
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+--------------------------------------------------------------------------------------+

However, the query is still slower than in MySQL-5.6.

Comment by Sergei Petrunia [ 2014-03-03 ]

MySQL-5.6 produces this EXPLAIN:

+--+------------+-----------+------+-------------+----------+-------+------------------------+----+------------------------------------------------------------------------------------------+
 
|id|select_type |table      |type  |possible_keys|key       |key_len|ref                     |rows|Extra                                                                                     |
 
+--+------------+-----------+------+-------------+----------+-------+------------------------+----+------------------------------------------------------------------------------------------+
 
|1 |SIMPLE      |files      |ALL   |NULL         |NULL      |NULL   |NULL                    |2   |Using where; Using temporary; Using filesort                                              |
 
|1 |SIMPLE      |<subquery2>|eq_ref|<auto_key>   |<auto_key>|195    |files.LOGFILE_GROUP_NAME|1   |NULL                                                                                      |
 
|2 |MATERIALIZED|F2         |ALL   |NULL         |NULL      |NULL   |NULL                    |2   |Using where                                                                               |
 
|2 |MATERIALIZED|PARTITIONS |ALL   |NULL         |NULL      |NULL   |NULL                    |NULL|Using where; Skip_open_table; Scanned all databases; Using join buffer (Block Nested Loop)|
 
+--+------------+-----------+------+-------------+----------+-------+------------------------+----+------------------------------------------------------------------------------------------+

when I debug, I see that it doesn't even attempt to scan the PARTITIONS table. Besides that, I don't understand how they managed to get "Skip_open_table".

Comment by Sergei Petrunia [ 2014-03-03 ]

Ok, this is not a MySQL 5.6 optimization, I've found it that it's their bug: http://bugs.mysql.com/bug.php?id=71914 .

Most users of MySQL-5.6 and mysqldump will not be affected, because their database doesn't have anything that matches INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' or SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE'. These are only used by NDB storage engine.

Comment by Sergei Petrunia [ 2014-03-04 ]

Committed a patch that makes MariaDB 10.0 be as fast as MariaDB 5.5. It will not scan unneeded databases anymore.

Comment by Sergei Petrunia [ 2014-03-04 ]

Did investigation of optimizer behavior:

1. Old optimizer (before semi-join subquery optimizations)
The query ran instantly on older versions, because they only supported top-to-bottom execution. The top-level select is "SELECT ... FROM INFORMATION_SCHEMA.FILES", which typically produces no rows and the subquery is never invoked (I_S tables are populated in JOIN::exec(), see get_schema_tables_result() call)

2. Semi-join optimizer, MariaDB
Semi-join optimizer merges the subquery into top-level query. The decision to use SJ-Materialization is essentially a random choice: I_S tables provide bogus data to the optimizer (each I_S table has 2 rows, table->scan_time() =2).
The problem is that JOIN::exec()/get_schema_tables_result() will attempt to populate all tables before executing the join. Populating I_S.PARTITIONS table will cause all tables in the database of interest to be opened.

3. Semi-join optimizer, MySQL 5.6
They have MySQL BUG# 71914 . They never populate I_S.PARTITIONS which causes them to run fast.

Comment by Sergei Petrunia [ 2014-03-04 ]

Fixing I_S tables to provide sane data to the optimizer is a big project.

For now, we will:

  • Fix MariaDB 10.0 to only open tables/databases of interest (already committed)
  • To get a bit of extra performance, fix mysqldump in 5.5/10.0 to run the query with 'semijoin=off' to speed it up.
Comment by Sergei Petrunia [ 2014-03-05 ]

Fixed as described in the previous comment.

frettled, thanks alot for taking time to report this issue. We really appreciate such input.

Comment by Jan Ingvoldstad [ 2014-03-05 ]

And thank you very much, elenst and psergey, for the detailed analysis and, as far as I can tell from following the comments, exactly the correct fix for the problem!

Generated at Thu Feb 08 07:06:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.