Although MyISAM has been the default storage engine for MySQL but its soon going to change with the release of MySQL server 5.5. Not only that, more and more people are shifting over to the Innodb storage engine and the reasons for that is the tremendous benefits, not only in terms of performance, concurrency, ACID-transactions, foreign key constraints, but also because of the way it helps out the DBA with hot-backups support, automatic crash recovery and avoiding data inconsistencies which can prove to be a pain with MyISAM. In this article I try to hammer out the reasons why you should move on to using Innodb instead of MyISAM.
The following are my slides that I presented at ODTUG Kaleidoscope 2010. This presentation talks about the MySQL defaults including a non-transactional state, silent data truncations, date management, and transaction isolation options. These are all critical for data integrity and consistency. I also cover in-depth topics including SQL_MODE, character sets and collations.
A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”
Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):
- sysbench –test=oltp –db-driver=mysql –oltp-table-size=1000000 –mysql-engine-trx=yes –oltp-test-mode=complex …
So you're using explicit transactions. Everything appears to work
(every query gives the expected result) until you get to
Then you get an exception thrown from COMMIT. What happened?
Usually this would be because the server has been shut down, or you've lost the connection.
The problem is, that you can't assume that the commit failed, but you also can't assume it succeeded.
A robust application must make NO ASSUMPTION about whether a failed commit did, indeed, commit the transaction or not. It can safely assume that either all or none of it was committed, but can't easily tell which.
So the only way to really know is to have your application somehow remember that the transaction MIGHT have failed, and check later.
- Ignore it and deal with any inconsistencies manually, or
decide that you don't care :)
Well, here we are … the first post to the InnoDB blog. Now there is a blog dedicated solely to InnoDB products and technology. The Innobase team will be posting here regularly on all manner of topics regarding the InnoDB storage engine. We plan to provide timely updates and important technical information about InnoDB-related products including the built-in InnoDB distributed by MySQL, the InnoDB Plugin and InnoDB Hot Backup. We invite you to visit regularly and post your comments.
We’ve borrowed the name “Transactions on” from the computer-science journal Transactions on Database Systems, published by the ACM society for computing professionals. Like that journal, this blog will cover a wide range of database topics, specifically as they relate to InnoDB.
Users of InnoDB know a transaction is an atomic all-or-nothing set of changes made to a collection of data. But …[Read more]
First off, let me wish you all a Happy 2009, and my apologies for being a bit slack with blogging in the last two months of 2008. It’s been a bunch of busy weeks, but I’m fully expecting to remedy that in this year.
Second, let me welcome you to this series on refactoring MySQL applications. I will cover best practices and useful tips, as well as show working examples of potential loopholes and solutions.
So, you are a DBA or a developer, and you’re faced with a problem. Your years-old application (perhaps you inherited it from a former co-worker) is now starting to keel over, and your choice of responses is limited.
Let’s start by saying that there are other ways to reach performance, but in some cases refactoring may be the only way that is possible to pursue, for one reason or another. Let’s take the refactoring way, focusing on SQL rather than applications, as there’s a lot that can be done on this side. …[Read more]
For anyone who lives and dies by MySQL and InnoDB, things are finally starting to heat up and get interesting. I’ve been banging the “MySQL/InnoDB scales poorly” drums for years now, and despite having paid Enterprise licenses, I haven’t been able to get anywhere. I was pretty excited when Sun …[Read more]
My editor Andy Oram recently sent me an ACM article on BASE, a technique for improving scalability by being willing to give up some other properties of traditional transactional systems.
It’s a really good read. In many ways it is the same religion everyone who’s successfully scaled a system Really Really Big has advocated. But this is different: it’s a very clear article, with a great writing style that really cuts out the fat and teaches the principles without being specific to any environment or sounding egotistical.
He mentions a lot of current thinking in the field, including the CAP principle, which Robert Hodges of Continuent first turned me onto a couple months ago. …[Read more]
Contrary to what I said earlier, Falcon has decided to deliberately disable statement-based replication using the same capabilities mechanism that InnoDB uses.
The reason is that isolation between concurrent transactions cannot be guaranteed, meaning that two concurrent transactions are not guaranteed to be serializable (the result of a concurrent transaction that has committed can "leak" into an ongoing transaction). Since they are not serializable, it means they cannot be written to the binary log in an order that produce the same result on the slave as on the master.
However, when using row-based replication they are serializable, because whatever values are written to the tables are also written to the binary log, so if data "leaks" into an ongoing transaction, this is what is written to the binary log as …[Read more]
I'd been doing some stress testing of my mysql application today,
and I was hitting some weird cases. Several transactions were
deadlocking - this was expected - but the number of records that
got inserted into my table was more than the number that I
expected after subtracting errors.
My test was fairly simple:
- Fork 15 processes
- Insert and update 100 records in each process, running each INSERT/UPDATE pair inside one transaction
- ROLLBACK on error
Either the INSERT or the UPDATE was expected to fail due to
deadlock, and the whole transaction should have rolled back
leaving no record in the table.
Before I go on, I should mention that I was using InnoDB, which does support transactions.
What I expected was that the total number of records in the table + the total number of INSERT/UPDATE aborts due to deadlock should be equal to 1500 (15*100). What …