Mysql – thesqldump select queries appearing in slow query log after upgrade

mariadbmariadb-10.5MySQLmysqldumpslow-log

We recently upgraded from MariaDB 5.5 to MariaDB 10.5

Since doing so, we have a couple of select queries appearing in the slow query log when we backup databases using mysqldump.

One of the tables is an InnoDB table with 125,000 records. The other is a MyISAM table with 220,000 records.

I guess this might not seem like any great concern, however for many years we never had anything in the slow query log related to mysqldump. So I am wondering if we should be worried? Did the behaviour of mysqldump change between versions 5.5 and 10.5, or could there be some other local setting that we perhaps had optimsied previously, that now isn't?

Our slow query time is set to 5 seconds, which is what it was previously. The offending queries are taking just under 6 seconds.

Thanks

EDIT

After further investigation, it turns out we are piping mysqldump through gzip, to compress the resulting dump files. Removing gzip from the equation results in each dump completing in 3 seconds or less.

The fact still remains that our setup remained unchanged other than the upgrade from MariaDB 5 to 10, and this didn't seem to be an issue previously.

In any case, whilst piping to gzip results in a nice "one liner", I have changed it to dump the files as plain text, then gzip them afterwards. I feel better knowing that the dump queries complete much faster, and aren't held up by gzip.

Best Answer

By default, MariaDB's mysqldump will log queries (--log-queries enabled by default) to the slow log when executing the reload of a dump. The MariaDB doc says:

When restoring the dump, the server will, if logging is turned on, log the queries to the general and slow query log. Defaults to on; use --skip-log-queries to disable. Added in MariaDB 10.1.1.

This was introduced back in MariaDB 10.1.1. You could try skip-log-queries as an option for mysqldump.

This may or may not affect the dump process itself.

If you are seeing SELECTs from the mysqldump in the slow log, the table being dumped might be waiting for a lock to release (in the case of a MyISAM table) or an table with a lot of transactional activity (in the case of InnoDB) when generating the INSERT INTO for thousands of rows.

SUGGESTION

You could initialize the reload session to ignore the slow query log by hiking the long_query_time for the local session:

mysql --init-command="SET long_query_time = 10000000;" < dump-file.sql