2

From what i know, this is not possible but i'm surprised its not highly requested feature. I'd love to be able to have one db using InnoDB in ACID compliant mode and another db on the same server used for non-critical data that uses InnoDB with more relaxed settings related to disk writes.

Is there a way to achieve this setup?

1 Answer 1

2

YOUR QUESTION

The reason there is no way to achieve this has to do with the InnoDB Architecture

InnoDB Architecture

There are three(3) things in this which makes different flushing behavior per database impossible

These three(3) things work together. The log buffer flushes changes from the buffer pool into system tablespace files but there is no way to control how much or how little to flush from the log buffer and buffer pool (in more than one direction).

Look at a more sophisticated RDBMS : Oracle RAC. Scaling out Oracle RAC simply creates a log buffer per RAC Node to distribute writes to one or more servers that have the data files. You could probably unleash CHECKPOINT commands to flush the Log Buffer at a RAC Node. If you could, this would still not be wise (and your scenario impossible) because you cannot control which blocks are written to which RAC Node. Why? Even in a distributed architecture like Oracle RAC, you still have a single Data Dictionary. This is much more so the case with InnoDB when it comes to controlling changes and flushing of dirty pages.

Flushing databases differently puts the InnoDB Storage Engine at risk of losing all data integrity altogether.

ALTERNATIVE SOLUTION #1

Create another MySQL instance on port 3307 (or a different number other than 3306) on the same server. That second instance would have its own Log Buffer and its own Checkpointing. Using a separate my.cnf for the second instance, configure innodb_flush_log_at_trx_commit at your discretion since it will be independent of the first instance's flushing behavior.

ALTERNATIVE SOLUTION #2

Take the source code and implement multiples log buffers (each with a dedicated buffer pool), including a mechanism for tweeking the flushing of commits per log buffer (You'll be a millionaire in 2 years.

Give it a Try !!!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.