Clever Geek Handbook

MySQL transaction support with mixed tables

It looks like I will need a transaction with MySQL and I don't know how I manage transactions in Mysql with mixed InnoDB / MyISAM tables. It all seems like a huge mess.

You might ask why I should ever mix tables together ... anwer is PERFORMANCE. As many developers have noticed, InnoDB tables generally have poor performance but in turn give higher isolation levels, etc.

Does anyone have any advice on this issue?

0


source to share


4 answers


I think you are overestimating the performance difference between MyISAM and InnoDB. MyISAM is faster in storage situations (like full reporting of table scans, etc.), but InnoDB can be faster in many cases in all cases with normal OLTP queries.



InnoDB is harder to configure as it has more buttons, but a properly configured InnoDB system can often have higher throughput than MyISAM due to better blocking and better I / O schemes.

+6


source


Given that you cannot have transactions on MyISAM tables, I'm not sure what the actual problem is. Any data you need transactions for must be in an InnoDB table, and you manage transactions using whatever access library you use or with manual SQL commands.



+2


source


There are certain advantages to using a single engine.

A server configured for one engine will not be configured for another - both require you to allocate a significant amount of RAM for its exclusive use, so you cannot give them an optimal amount.

Say you have 8GB of RAM on your (obviously 64bit, but still relatively small) database server, you can assign about 3/4 of that to your innodb cache file. Alternatively, if you are using MyISAM, you might need about half of it to be your key_buffer. You cannot get around both.

Choose an engine and use it exclusively. There are ways to work around performance problems - most of them are not easy (i.e. they require redesigning the data structure or your application).

0


source


The short answer is the lack of transaction support in MyISAM. If you start a transaction, add or change data in some InnoDB tables, add or change data in a MyISAM table, and then have to roll back, the MyISAM change cannot be removed. To support such mixed engines, your application needs to know that changes to any data are stored. MyISAM happens "outside" the transaction.

If you need transactions for some processes, then isolate the data that needs to be transactional and put all that data in InnoDB.

0


source






More articles:


All Articles