I have a MariaDB server with several databases.

On my MariaDB server, some queries (with joins between tables of different databases) are slower after the daily mysqdump (one file sql by database).

After the daily mysqldump, the first run for one query will be 1.5 minutes and the second run 3.2 secondes (this time is coherent with current run on the day). I understand the second run is faster but why the first run after the daily mysqldump is slower ? And how improve it ?

An extrait of my server.cnf : key_buffer_size = 384M max_allowed_packet = 16M table_open_cache = 2000 sort_buffer_size = 8M join_buffer_size = 8M read_buffer_size = 8M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 64M query_cache_limit= 128M query_cache_size= 256M query_cache_strip_comments=1 thread_concurrency = 8 tmp_table_size = 32M max_heap_table_size = 32M innodb_buffer_pool_size = 3G innodb_buffer_pool_instances = 3 innodb_log_file_size = 448M innodb_log_buffer_size = 64M innodb_flush_log_at_trx_commit = 1 innodb_print_all_deadlocks = 1 innodb_lock_wait_timeout = 210

Thanks for you help.

  • have you tried timings without the query cache? SELECT SQL_NO_CACHE .... – danblack Oct 5 '18 at 17:12
  • Yah. I tried with SQL_NO_CACHE on each run. same result. To be sure it was not a problem with the query_cache, I tried with the query_cache disabled. Same resulsts. – user2463696 Oct 10 '18 at 13:38

First query could be slow as the innodb buffer has been filled up with the queries required for the mysqldump and all the data needs to be fetched from disk.

You could run SET GLOBAL innodb_buffer_pool_dump_now=1 before the dump and SET GLOBAL innodb_buffer_pool_load_now=1 after the dump to ensure the buffer pool is what it was prior.

  • I tried it. In developpement environnement, I had some good results. So I am trying in one production environnement. I will do a feedback tomorrow. Thanks for your advice danblack ! – user2463696 Oct 10 '18 at 13:41
  • I had good results in production too. I will do it in all our productions environnements. thank for it. – user2463696 Oct 11 '18 at 9:17
  • Good. innodb_buffer_pool_dump_pct defaults to 25 pct in later versions so there may be tradeoffs with more or less of the pool. The other alternative is to dump from a replication slave. – danblack Oct 13 '18 at 8:15
  • I noticed it. I put this parameter on my server.cnf and I will change it if necessary according to performances. – user2463696 Oct 15 '18 at 7:38
  • thanks very useful info. Do I understand correctly when xtrabackup is used, it doesn't fill the innodb bufferspace with uneeded queries? – Ivanov Oct 24 '18 at 10:10

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.