Important Notice: Our web hosting provider recently started charging us for additional visits, which was unexpected. In response, we're seeking donations. Depending on the situation, we may explore different monetization options for our Community and Expert Contributors. It's crucial to provide more returns for their expertise and offer more Expert Validated Answers or AI Validated Answers. Learn more about our hosting issue here.

Is DELAY_KEY_WRITE honoured on Maria tables?

April 26, 2017honoured Maria tables

0

10 Posted

Is DELAY_KEY_WRITE honoured on Maria tables?

1 Answer

0

Posted

If you are using non-transactional Maria tables (CREATE TABLE… ENGINE=MARIA TRANSACTIONAL=0), which are similar to MyISAM, then DELAY_KEY_WRITE works as you expect. If you are using transactional Maria tables (the default), then DELAY_KEY_WRITE is always enabled. In MyISAM and non-transactional Maria tables (which have no logging), by default all the table’s key pages are flushed to the OS at the end of each statement, to guarantee some durability. DELAY_KEY_WRITE removes this flush, giving less durability. In transactional Maria tables, key pages are flushed by a background job, regularly, not necessarily at the end of each statement, and durability is guaranteed thanks to logging.