Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

1st setup was a DB slave that was used by customers. That was sufficiently fast. 2nd setup (now) is replication of a DB slave that is just used by me. The current setup is slower on query execution (even though I don't have to share CPU and memory) and the theory is that this is because the DBMS is not "warm" by use but f.x. has to reload the index every time I use it. This leads to weaker performance. The conclusion would be to run scheduled queries that keep the DB "warm".

I would like to know three things which are directly connected:

  1. What is the primary reason for this effect? Is it because after every small write access to the slave which gets replicated onto my server, indexes are loaded into the memory first before a query is executed? My guts tell me there something more complex going on.

  2. How would a query have to look like that optimally uses the DB (scheduled) to keep it warm and ready? I am aware of this post: MySQL warm procedure - but I am not so much interested in a generic query but in understanding the logic behind it.

  3. What determines how frequently such a keep-warm-query (or set of) should be run?


SHOW VARIABLES LIKE "%version%";

innodb_version          5.6.13-rel61.0
protocol_version        10
slave_type_conversions  -
version                 5.6.13-rc61.0
version_comment         Percona Server (GPL), Release 61.0
version_compile_machine x86_64
version_compile_os      Linux
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

Database "warm up" is a real thing... but then again, people making excuses for under-performing systems is also a real thing. An important take-away here is that once a server is warmed up, it doesn't generally spontaneously cool back down again.

Warm-up is needed because the best performance of a database is achieved when its various caches are populated with the most relevant data, since reading from memory is generally substantially faster than reading from disk.

InnoDB has the InnoDB buffer pool. MyISAM has the key cache and the OS cache. Then there's also the MySQL query cache. MySQL has the open table cache. Some or all of these structures contribute to the ideal performance of a server. When a server is handling traffic that is characteristic of its workload, it tends to have the most frequently used -- and therefore presumably the most relevant data -- already cached.

Take the load off of an active MySQL server and you will not see it start closing tables and evicting data from its caches. If you leave it running, then come back to it tomorrow, you should find that it's still as warm as you left it.

The need for a warm-up is not typically something that is a relevant consideration needed unless the server process is restarted. Percona Server has a feature that can pre-warm a server's InnoDB buffer pool on restart, by storing the pointers to the pages that were in-memory before shutdown and reloading those pages again into the pool on startup.

However, you can also undo a well-warmed system to some extent by taking a full backup from the client-side of the server, such as with mysqldump and most graphical tools, but not backup utilities that understand native tablespaces, like innobackupex (if my understanding of how that utility works is correct), since a large portion of the data being backed up may be data that is older or irrelevant to the primary workload but may still cause the eviction of other data from the various buffers and caches as it is loaded in, on its way back out for the backup.

The logic in the question you referenced is that reading from the tables and their indexes brings them into memory -- warms things up -- before the "real" queries jump in to start doing their work.

Generally speaking, though... a single query, run once, will have warmed up whatever structures were needed to execute that particular query. If the same query, executed again immediately, is still slow, then warm-up is not the issue. Indexes are not reloaded every time you use them, unless there is competition for space in the buffer and cache structures.

share|improve this answer
    
but let's say the replication is updated on an hourly basis. Wouldn't the index render invalid every hour then and be discarded from RAM? –  Raffael Oct 28 '13 at 9:01
    
Indexes in MySQL are updated as the data in the rows with indexed columns are changed, not asynchronously, so, no, the index wouldn't become invalid over time, it would be continually modified to reflect the underlying data as the data was changed. As soon as you're done executing a query that updates a row, the index reflects the new row content. This would be done in memory and if nothing else is competing for that memory, it will remain there. –  Michael - sqlbot Oct 31 '13 at 18:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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