As larger and larger amount of memory become common (512GB is something you can fit into relatively commodity server this day) many customers select to build their application so all or most of their database (frequently Innodb) fits into memory.
If all tables fit in Innodb buffer pool the performance for reads will be quite good however writes will still suffer because Innodb will do a lot of random IO during fuzzy checkpoint operation which often will become bottleneck. This problem makes some customers not concerned with persistence run Innodb of ram drive
In fact with relatively simple changes Innodb could be made to perform much better for memory resident workloads and we should consider fixing these issues for XTRADB.
Preload It is possible to preload all innodb tables (ibdata, .ibd files) on the system start – this would avoid warmup problem and also make crash recovery fast even with very large log file – random IO is what usually limits recovery speed. Because files can be just read sequentially the read speed can be hundreds of megabytes per second even for commodity RAIDs.
Sequential Checkpointing Currently fuzzy checkpoint flushes pages which have not been flushed for longest amount of time which causes random IO. In resident checkpoint mode we should just flush everything (yes even clean pages) sequentially. This should allow to get sequential writes giving us 100MB+ of write speed – which means 256GB buffer pool can be flushed about once per 30 minutes. It should be possible to just size Innodb logs so they are not cycled through faster than flush cycle.
This may not be the most optimal solution if you design the system from scratch but it is something which can be done without changing Innodb core logic significantly or storing on disk storage format at all.
This is just an idea at this point which I’ve discussed with some customers and we’re not working on it yet, though if you think it is something which you think would help your performance challenges we would be happy to implement it for you.
