MySQL's InnoDB - avoid flushing log buffer for a single transaction?

Question

Is it possible to programmatically tell MySQL not to flush the log buffer immediately just for the current transaction, regardless of the innodb_flush_log_at_trx_commit setting?

Let's say you generally want ACID compliance so that your data is safe from any power/hardware faults. So you have innodb_flush_log_at_trx_commit = 1

But in your application, you have one particular INSERT query that needs to return quickly, and it's not as important for it to have the same level of integrity protection. Can you skip the flush (at the end of its transaction) just for that query/transaction? And can you do it programmatically, with an SQL query or something in PHP's PDO?

thomasrutter · Accepted Answer · 2012-11-23T03:04:16.687

0

I think the answer is that it can't be done - unless you are willing to switch to MyISAM just for the particular table in question.

edited Nov 23 '12 at 03:04

answered Jul 25 '11 at 05:06

thomasrutter

114,488
30
148
167

If anybody knows better, please add your answer. I can change my accepted answer. – thomasrutter Jul 25 '11 at 05:07

score -1 · Answer 2 · answered Jul 21 '11 at 05:02

-1

I'm not sure if this will do exactly what you want, but check out INSERT DELAYED.

answered Jul 21 '11 at 05:02

Michael Mior

28,107
9
89
113

Unfortunately InnoDB doesn't have INSERT DELAYED. I guess I have the option of using MyISAM for that one table, so thanks, but I was wanting to know if there was an InnoDB solution. – thomasrutter Jul 22 '11 at 02:39
Sorry, I should have noticed that you're using InnoDB. – Michael Mior Jul 23 '11 at 03:44

MySQL's InnoDB - avoid flushing log buffer for a single transaction?

2 Answers2