0

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?

  • it's rather the opposite... There is a process in the background, but it is making your instance run much faster after a while, and that is caching. I don't know the specifics of EC2, but there are quite a few variables to check how good the cache is doing within MySQL. You should compare those values between your instances, for example with SHOW STATUS LIKE 'Qcache%'; – KookieMonster May 15 '17 at 10:06
  • Aurora's QC is probably better, not worse, than MySQL's. – Rick James May 15 '17 at 15:44
  • As I commented on the first answer, the query I use for testing is executed with SQL_NO_CACHE so any cache usage is irrelevant in this context. – marekful May 17 '17 at 0:17
2

There are two things you can look into:

  • Query cache
  • Buffer pool

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 query
  • the query that is slow

then that will provide a ground for looking into possible improvements to the query and/or indexes that may need to be created/modified.

  • thanks for your answer. I forgot to mention that the SELECT query I'm using for these tests are issued with SQL_NO_CACHE exactly for the reason so execution time can be measured w/o cache. The test query is a complex query with JOINs and aggregates and IFs. It is not the point of this investigation to determine how optimal the table definition is or how the query itself could be rewritten. – marekful May 17 '17 at 0:01
  • All I am interested in is why the same query against the same data with the same results requires significantly different execution time when it's executed at time T where T is the time when the database server was created, and then at time T + 30 minutes, then T + 1 hour, etc. – marekful May 17 '17 at 0:01
  • FYI: The database sees no traffic whatsoever during these tests and no configuration or anything else is being changed. – marekful May 17 '17 at 0:06
  • 1
    AFAIK regardless of SQL_NO_CACHE (that only affects qcache) buffer pool still caches table data and query performance will improve as the buffer pool warms up. – thatsaru May 17 '17 at 21:46

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.