Mysql – MariaDB 10.0 slow work thesqldump

mariadbMySQL

i have 1.800(innodb) DBs in my MariaDB 10.0, 'mysqldump' work very slowly!

If i run 'mysqldump' starts the process:

"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 (
'table_test'
)
)
)
GROUP BY LOGFILE_GROUP_NAME, FILE_NAME,
ENGINE ORDER BY LOGFILE_GROUP_NAME" in information_schema

How fix it? How disable this process in 'mysqldump'?

Thank you!

Best Answer

Edit:

The issue is confirmed in MariaDB 10.0.8 and 5.5.36, and confirmed not to exist in MySQL 5.5.36 apparently due to implementation differences in the query optimizer.

The text below is therefore adjusted according to the recommendations by Elena Stepanova of MariaDB.

I have reproduced this behaviour in MariaDB 10.0.8 on Debian wheezy. I have not been able to reproduce it in MySQL 5.5.36.

If you execute mysqldump -uroot, or any user with full privileges, this generates a query that causes MariaDB's mysqld to scan the database superdirectory and all subdirectories and all the table metadata for all tables in these subdirectories.

If you execute mysqldump with specific user privileges, and those have been granted for access to only one or two databases, mysqldump is as quick as you would expect it to be.

There are two work-arounds.

Work-around A is still using a super-privileged user with access to all databases, but speeds up the process by disabling the semijoin feature.

Work-around B creates a backup user that is granted access to each database in turn, narrowing the privilege set.

Which method works best for you depends on how comfortable you are with using a super-privileged user to access all databases, how many databases you have, how many users you have that have access to multiple databases, how many databases have dependencies between them, and how your database is being accessed while you make your database dumps.

Work-around A is probably the quickest, work-around B slightly slower.

Work-around A - super-privileged user, disabled semijoin

Please note that disabling semijoin can affect the performance of other queries. While it can be set in my.cnf, you probably shouldn't.

  1. Disable semijoin temporarily:

    SET GLOBAL optimizer_switch='semijoin=off';

  2. Dump the database, e.g. as the "root" user:

    mysqldump db -uroot -prootpassword | gzip > DB.sql.gz

  3. Re-enable semijoin:

    SET GLOBAL optimizer_switch='semijoin=on';

Work-around B - separate backup user

  1. Grant a backup user full privileges to the database:

    GRANT USAGE ON `db`.* TO 'backup'@'%' IDENTIFIED BY 'backuppassword';
GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'backup'@'%';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `db`.* TO 'backup'@'%';
FLUSH PRIVILEGES;

  2. Dump the database as that user:

    mysqldump db -ubackup -pbackuppassword | gzip > DB.sql.gz

  3. Revoke the privileges:

    REVOKE ALL PRIVILEGES ON `db`.* FROM 'backup'@'%';
REVOKE ALL PRIVILEGES ON `mysql`.* FROM 'backup'@'%';
FLUSH PRIVILEGES;

If each database is owned by a separate user, and you know these users' passwords, you could of course use these instead of the backup user and its password, and thereby skip steps 1 and 3 entirely.

BTW: I also reported this to the MariaDB development team, if you have anything useful to contribute, feel free to follow up on that here:

https://mariadb.atlassian.net/browse/MDEV-5723

Related Topic