1

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.

2
  • Same hardware? Same engine? Did you run the query twice (to avoid cache diffs)? Let's see the 6-sec query and SHOW CREATE TABLE. There may be some subtle changes that lead to the diff.
    – Rick James
    Feb 2 at 22:50
  • Yes, same hardware and engine. However it's started happening on random tables when we do a dump. In all cases the records have tens of thousands of rows (it doesn't happen on small tables)
    – MrCarrot
    Feb 4 at 19:14
1

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
8
  • Thanks - I was concerned as to why the queries from mysqldump would suddenly be logging to the slow log - implying that they are being carried out more slowly than prior to our upgrade. In any case, skip-log-queries does indeed seem to work as an option for mysqldump
    – MrCarrot
    Feb 2 at 21:24
  • Sorry, turns out that skip-log-queries doesn't prevent mysqldump from writing to the slow log afterall
    – MrCarrot
    Feb 4 at 19:12
  • @MrCarrot did you try out my additional suggestion when reloading the dump to do the following : mysql --init-command="SET long_query_time = 10000000;" < dump-file.sql ??? Feb 4 at 21:16
  • Similar to your suggestion, I'm actually doing set global slow_query_log='OFF' just before we run mysqldump, and then turning slow_query_log back on afterwards. I was just concerned as to why these dumps were suddenly generating slow queries, when they weren't before.
    – MrCarrot
    Feb 4 at 22:23
  • What is the result of SELECT @@global.long_query_time; ??? Feb 4 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.