Re: Two instances better - or mix MyISAM and InnoDB?

[Rick James]

> information about buffering when mixing table types

It is hard to provide details on this.

MyISAM needs key_buffer_size set for its _index_ cache, plus it needs spare room in RAM for the OS to cache _data_ blocks.

InnoDB needs innodb_buffer_pool_size for caching both data and indexes.

Swapping severely hurts MySQL.

So, you need to carve up available RAM between the two Engines. This lets you use both engines, but degrades (somewhat) the performance of each.

One instance of mysql, using both Engines --> carving up RAM.
Two instances of mysql, each using one Engine --> carving up RAM.
Not much difference.

> Performance is important with our wide, heavily indexed table.

More indexes --> slower INSERTs. Don't add indexes unless you know of SELECTs that will need them.

These contradict each other, which is it?:
> monthly updates
> heavy use of real-time user-driven transactions

How would you do "two instances"?? How would the data get into both instances?