When I clone an existing MySQL server, it takes for the same query executed against the same data and producing the same results significantly more time to execute on the new server. It seems the query is running forever (over 5 mins before I kill it as opposed to 15-20 second on the origin server) in the very beginning right after the new server was created then, as some time passes by, the query performance improves and finally it takes several hours before the query in question produces the same execution time as on the origin server.
I experienced this when I clone an AWS EC2 instance running a MySQL server and also when I add a new read replica to an AWS Aurora MySQL cluster.
What is happening in the background that makes the new server perform much worse? Is there a way to monitor / get feedback on these processes?
Best Answer
There are two things you can look into:
Query cache will cache query results (depending on QCache configuration). When the same query is run again, results are returned from the cache instead of MySQL fetching the data directly from the tables. If the Query Cache is full and a new query is executed that needs caching, then the least used Query results are flushed from the Cache to make space for the new query results and so on.
Buffer Pool: InnoDB maintains a storage area called the buffer pool for caching table data and indexes in memory. So if your tables use InnoDB storage engine (or XtraDB for that matter), then the query execution times will improve over a period of time as the Buffer Pool becomes "warm".
Having said that, if your queries are taking too long to run in the first instance, then they may be a candidate for tuning. If you can post results of:
SHOW CREATE table <table name>for the tables involved in the querythen that will provide a ground for looking into possible improvements to the query and/or indexes that may need to be created/modified.