We have a MySQL Master Instance & Read (failover only) on AWS. MySQL 5.6 Aurora running Magento. Performance is great, and better than standard MySQL (we have high traffic site so see the benefits).
We suffer some issues with when Magento Indexing happens even under pretty low loads.
Truncate functions happen on some key table one of them being: TRUNCATE TABLE
When the above happens other queries build up / bottleneck in the process. The state changes to: "checking permissions" and they hold until the truncate is complete
Its fairly fast, but what it does is slows/blocks the website for a second or 2 when the site is under load.
No foreign key constraints on the table; catalog_product_index_group_price
We have query_cache_limit set to 4mb (which has helped queries perform better).
Looking in Performance Schema we are trying to see what could possible be holing this up with no luck.
Having read a lot of about 5.6-5.7. We have read statements like:
"Fixed as of the upcoming 5.7.18, 8.0.1 release, and here's the changelog entry:
A TRUNCATE TABLE operation held the dict_sys mutex while scanning for and removing pages from the buffer pool, causing concurrent DDL operations to stall. The mutex is now released during the scan and acquired again when the scan is completed. "
Does anyone have any ideas or insight into the above. Especially being able to verify this behaviour. We are also going to log ticket in with AWS too.