That is, retrieving any row would only require a maximum of 6-8 page reads (potentially from disk).
I’m telling you all this, because while most replication and clustering mechanisms essentially work it.
Query Cache 영역은 Lock이 필요할 때 Global Lock을 사용되며 Lock이 걸린 상태에서 다른 Thread들은 대기를 하는 수 밖에 없다.
이러한 문제로 인해 My SQL Query Cache 크기 (query_cache_size)를 크게 늘리기 어렵다.
When a query is executed, any required page that’s not yet in the buffer pool is requested to be loaded in the background.
Naturally, this does slow down queries, which is why we preferably want all necessary pages to already be in memory. In Inno DB, all data/indexes are structured in B trees, so an INSERT has to be merged into a page and possibly causes pages to be split and other items shuffled so as to “re-balance” the tree.
On behalf of our clients, we look beyond the marketing, and see what the technical architecture actually delivers. It comprises a set of Amazon modifications on top of stock Oracle My SQL 5.6 and 5.7, implementing a different replication mechanism and some other changes/additions.
We will address specific topics in individual posts, this time checking out what the Aurora architecture means for write and caching behaviour (and thus performance). While we have some information (for instance from the “deep dive” by AWS VP Anurag Gupta), the source code of the Aurora modifications are not published, so unfortunately it is not immediately clear how things are implemented.
So, imagine an individual instance in an Aurora setup as an EC2 (Amazon Elastic Computing) instance with My SQL using an SSD EBS (Amazon Elastic Block Storage) volume, where the Inno DB I/O threads interface more directly the the EBS API.
The actual architecture might be slightly different still (more on that in a later post), but this rough description helps set up a basic idea of what a node might look like.
Of course, it is possible to recover to any point-in-time from an Inno DB snapshot Inno DB transaction logs – this would require adding timestamps to the Inno DB transaction log format.
While it is noted that the Inno DB transaction log is also backed up to S3, it doesn’t appear to be used directly (so, only for recovery purposes then).
After all, any changed page needs to be communicated to the other instances, so essentially all pages are always flushed (no dirty pages).