Warming up a cold MySQL instance is moving all data or the data with the highest probability of getting used to main memory. And, yes, by main memory, I mean InnoDB Buffer Pool. The Buffer Pool is configured as a large chunk of main memory, usually. Getting all the data from disk to main memory will take time. Rather than leaving it for the future, can the buffer pool be loaded in advance with the data most expected to be used at the earliest?
Mysql – What are the different ways to Warmup InnoDB Buffer Pool in MySQL 5.6
buffer-poolinnodbMySQLmysql-5.6startup
Related Solutions
You have a read/write ratio of 26:1 (based on 10869877 divided by 406010), which is 96% reads.
I think you might be suffering from what I call READ AHEAD FUTILITY. Sounds like I made that up. Well, you are right. I did. What do I mean ? According to the MySQL Documentation on the status variable Innodb_buffer_pool_read_ahead_evicted
The number of pages read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries.
Tons of SELECTs may read too many pages of data for the purposes of doing read ahead. Those pages may never be accessed and are simply evicted from the Buffer Pool. It's like blowing up a gigantic balloon without tying a knot. The air will eventually come out. This is true even for a full Buffer Pool. Pages are not allowed to lie dormant. They must be removed to make room for other moving parts of the InnoDB Architecture
For example, look at the diagram. Were you aware that up to 25% of the Buffer Pool is used as a scratch pad for non-unique index changes? It forms a conduit that migrates those changes into the system tablespace. This can crash InnoDB if there are heavy INSERTs and UPDATEs into tables that have many nonunique indexes. You would drop those indexes, do your INSERTs, and create the indexes again to circumvent this. I wrote about this before
Jul 30, 2014
: InnoDB import performanceMay 12, 2014
: Buffer pool above 90% utilized causes MySQL to crash
Looking back at your chart, your miscellaneous is non existent. What does that indicate?
According to MySQL Documentation on Innodb_buffer_pool_pages_misc
The number of pages that are busy because they have been allocated for administrative overhead such as row locks or the adaptive hash index. This value can also be calculated as Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free – Innodb_buffer_pool_pages_data.
Apparently, you are not doing frequent searches for the same data. Your SELECTs must be doing
- large index range scans
- lots of index range scans
- lots of table scan
- I discussed this in MySQL status variable Handler_read_rnd_next is growing a lot
SUGGESTIONS
- You probably need to tune your queries so they do not request so much data.
- You should also avoid doing mysqldumps in the middle of the business day and this tends to load every data page and its grandmother into the Buffer Pool, only to have those pages quietly evicted. If you have to run mysqldumps, setup a MySQL Slave and o your dump from the Slave.
- You could experiment with disabling the change buffering (set innodb_change_buffering to 0).
UPDATE 2014-08-11 16:45 EDT
Please look back at the comment section. Derek Downey gave you a link to my 3 year old post. Based on the 2012 update in my post, you need to set innodb_buffer_pool_instances = 2. This will help curb mysqld's swap behavior.
From your SHOW ENGINE INNODB STATUS\G
, you have too few read and write threads
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
You only have 4 read and 4 write I/O threads. Bump up your threads
innodb_read_io_threads = 16
innodb_write_io_threads = 16
If you had more that 2 cores, I would use 32 or 64. You can try 16 for now.
InnoDB is always at odds with the Query Cache. Please disable it.
query_cache_size = 0
You need a bigger log buffer for better InnoDB write performance
innodb_log_buffer_size = 256M
These are all the changes
[mysqld]
innodb_buffer_pool_instances = 2
innodb_log_buffer_size = 256M
innodb_read_io_threads = 16
innodb_write_io_threads = 16
query_cache_size = 0
Uncommitted data is what the name suggest: uncommitted. This means the transaction that was running failed when MySQL crashed. Therefore the data should not be available because it could cause inconsistent state. Any ACID compliant database should roll back these transactions.
Ib_log files do contain uncommitted changes to the tablespace but chances are these are already checkpointed and overwritten many times.
There is a possibility to get back (some) uncommitted data from the ibdata and the .ibd files. For this you can use something like innodb_ruby to read the pages on disk and the history for each row from undo space. Be aware this will be a tedious work though.
If MySQL was started it will do the cleanup and rollback from the undo space which means pages in .ibd files will get back their original (last committed) state and your undo history will be purged. After this all the uncommitted data is lost for good.
ps.: If you really have transactions running for 10 days fix the application first. Of course this depends on the use case but majority of transactions shouldn't run longer than a couple of seconds.
Related Question
- MySQL – huge Dictionary memory allocated
- Mysql – Querying MySQL InnoDB to find if the Record is in its Buffer Pool
- Mysql – How MySQL use the InnoDB Buffer
- Mysql – Would the MySQL database benefit from increasing the InnoDB buffer pool size
- Mysql – In InnoDB, how does fuzzy checkpointing’s recovery consistency work
- Innodb – Partitioning and the InnoDB Buffer Pool
Best Answer
Assuming, you have a 7200 rpm HDD, if you perform random reads and expect the data to be loaded whenever it is required by the application, it will slow down the whole process. Random reads are way slower than sequential reads in case of HDD. So, rather the records must be loaded in sequential access.
It may take a while under a regular workload to store your entire working set data into the buffer pool, after restarting MySQL for instance. You can go with one of these two options:
Reference - From Dual's article on InnoDB Warmup
Marcelo Altmann's article on MySQL Warmup