CODEWITHSUNDEEP
mysqlbashmyisam
Pudpuduk
Pudpuduk

Reputation: 1619

First query after mysql reboot is slow

I am not as experienced with the backend of mysql and am having the following situation.

Each night I have a script that turns off our mysql database and rsyncs the mysql folder to another. Afterwards mysql is turned on.

Later, mysql is accessed by either a bash script or by a web application.

After each restart, the first few queries that come in either through the bash script or webapp are really slow(stuff that takes 10 seconds takes 3 minutes). Once the slow queries finish or timeout, the queries run at normal speed. Also, when I login to mysql through the command line, all is fine afterwards and queries run at expected speeds.

I believe that the slowdown is because normally MySQL reads all the table information when I login(assuming no -A option is used) or after the first queries already access the necessary meta-table information.

My question is: How can I speed this process up through a bash script so I avoid the slow queries/manual login? Do I just do a dummy select on the necessary tables, or is there a smarter way getting the table information?

Environment:

Thanks!

Upvotes: 2

Views: 1322

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I would speculate that such a long lag in query time is due to the page cache being emptied when the server is rebooted. Once a query has run, tables are loaded into the page cache and everything runs faster.

The first suggestion is to reload the page cache for the larger tables . . . you can do this just by doing a select * from <table> in your script.

This raises a question, though: Why are the queries are doing full table scans? If they are, you might have an opportunity to speed up the queries by using indexes.

Upvotes: 2

Kermit
Kermit

Reputation: 34063

Your query cache may be too large or be fragmented.

Take a look at this this guide that helps with tuning your database.

Upvotes: 1

ricick
ricick

Reputation: 5766

Sounds like your database is losing its cached queries. Maybe try using stored procedures for queries you need to be fast on first run.

Upvotes: 1

Related Questions