PlanetMysql.ru — информация о СУБД MySQL

After this mornings twitteriffic panic where people thought Oracle had removed innodb from the mysql community version, its better to clear some things out.

As a friend explained, mysql and innodb are free as in free speech and free beer (the dream). Hence Oracle model version is only talking about ENTERPRISE versions of mysql NOT the popular COMMUNITY version. All of you who used to go to downloads.mysql.com to get your mysql + innodb fix still can do so for FREE.

Oracle have changed their products page, but haven’t deemed important to explain the changes to the MySQL community. Oversight or intentional, the news that innodb won’t come with MySQL for free anymore with the community version is NOT TRUE.

Because Sharing is Caring

So this morning I was welcomed to work with a “Have you heard the news?” … Good lord I just woke up for God sakes.

Just like the storm back in April 2008 when Sun bought MySQL, another (possibly the perfect storm) happened in April 2009 when Oracle bought Sun, today the latter storm took us be “surprise” to a certain extent when Oracle released their new Editions model showing innodb removed from the classic edition. People are shouting all kinds of things at Oracle and there is a lot of confusion but I think it’s better to wait and see! We are a strong community and we won’t let this storm take us down ….. we are stronger than the Andrea Gail!

If you check out twitter be careful what you believe and don’t panic yet :) no need to migrate to postgresql or whatever.

http://twitter.com/mysql explains the version most are used to, in the download section is still and will remain there (at least that is what they are saying).

Keep you posted!

Because Sharing is Caring

Monty Program along with various guests and our friends at the recently founded SkySQL recently wrapped up a meeting in Istanbul Turkey. Sadly it rained most days but we had a good time and got a lot done.

Besides learning how to say “Şerefe” which is “Cheers” in Turkish, we discussed the future of MariaDB, our companies goals, the MariaDB knowledge base and many other topics. Since I am the web guy, I lead the presentation / talk on the KB and thought I would share some highpoints of it with you.

If you don’t know, the KB is located at http://kb.askmonty.org. The goal is to provide one location for MariaDB and MySQL documentation along with a place to ask questions (and have developers actually answer them). To start we have both information licensed under the GPL about MySQL, content from the “SQL-99 Complete, Really” book (still in the progress of adding the whole thing) and original articles by Daniel, Monty and others.

We want anyone to be able to create new articles, clean up existing content and categorize them but for the moment only editors can. This is a limitation in our permissions system that I will be addressing shortly. I also am in the planning stages of allowing all content to be translated into multiple language. If you would like to volunteer, keep reading this blog and I will let you know when it is ready.

The KB is written in python (Django) running against MariaDB. I wrote it over the course of several months using many Django libraries. Let me know any comments or questions you might have.

Mea Culpa, Mea Culpa, Mea Maxima Culpa!
My fault, my fault, it’s all my fault! Why? Well because new versions = new bugs :) = wait till they are fixed, then use it!

As an ubuntu fan I just had to install 10.10 GA … and as a MySQL DBA I can’t run apt-get install mysql …. not even in my nightmares can I do that. Why? Well because if you are a car mechanic you don’t take your car for repairs to another mechanic and not ask what was done on it do you? You do it yourself right? That is what I did, I installed mysql 5.1.50, the same binaries I had downloaded and used on my 10.04 machine and loaded a dump. Woops, mysql crashed big time. I figured, lets install 5.1.51, load and … boom, again crashed.

The same dump, is loading fine on my 10.04 ubuntu server running both 5.1.50 and 5.1.51.


101018 12:48:26 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.


key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=6
max_threads=151
threads_connected=6
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337737 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.


thd: 0xa364a88
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xb4162388 thread_stack 0x30000
/mysql/sandboxes/5.1.51/bin/mysqld(my_print_stacktrace+0x21)[0x84e5271]
/mysql/sandboxes/5.1.51/bin/mysqld(handle_segfault+0x381)[0x8206e71]
[0xb7785400]
/mysql/sandboxes/5.1.51/bin/mysqld(_Z23well_formed_copy_ncharsP15charset_info_stPcjS0_PKcjjPS3_S4_S4_+0x44)[0x8200a64]
/mysql/sandboxes/5.1.51/bin/mysqld(_ZN15Field_varstring5storeEPKcjP15charset_info_st+0x64)[0x81e0324]
/mysql/sandboxes/5.1.51/bin/mysqld(_ZN11Item_string13save_in_fieldEP5Fieldb+0x50)[0x8155f50]
/mysql/sandboxes/5.1.51/bin/mysqld(_Z36fill_record_n_invoke_before_triggersP3THDPP5FieldR4ListI4ItemEbP19Table_triggers_list14trg_event_type+0x4d)[0x825100d]
/mysql/sandboxes/5.1.51/bin/mysqld(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0x1089)[0x828da79]
/mysql/sandboxes/5.1.51/bin/mysqld(_Z21mysql_execute_commandP3THD+0x1b37)[0x8218927]
/mysql/sandboxes/5.1.51/bin/mysqld(_Z11mysql_parseP3THDPcjPPKc+0x3c4)[0x821e8c4]
/mysql/sandboxes/5.1.51/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x694)[0x821ef64]
/mysql/sandboxes/5.1.51/bin/mysqld(_Z10do_commandP3THD+0xe0)[0x8220480]
/mysql/sandboxes/5.1.51/bin/mysqld(handle_one_connection+0x253)[0x82109f3]
/lib/libpthread.so.0(+0x5cc9)[0xb7760cc9]
/lib/libc.so.6(clone+0x5e)[0xb76596ae]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...


I have done several small things like creating databases, users, tables, stored proceudres etc, but on loading big chunks of data the above happens.
Anybody had the same problem on 10.10 by any chance? I don’t have time to test stuff out to be honest, but would appreciate your feedback on this.

Because Sharing is Caring

I swear, my intention was to go for a break. A year taking pictures, sharing them over the web, writing texts, running, kayaking, just being social. Honestly, ask my family and friends!

But this was not to be, in spite of what I said when I announced my resignation just days ago. Instead, I am joining SkySQL Ab, the startup that aims to become a new centre for the MySQL universe. My role will span Marketing and Engineering, and is like the title “EVP Products” inspired by Zack Urlocker’s role at MySQL AB.

I would have preferred the company to have been called KajSQL, but have come to terms with the company having an extra phonetic “s” in the beginning. Quite a while ago, having eaten my favourite fish “Börjes fisk” in my country house in Nagu, Finland, my now-former colleague Giuseppe Maxia jokingly even suggested a logotype for KajSQL, then labelled “The database for community lovers”.

Why this change of minds? For five reasons:

Reason 1: The team. Behind SkySQL Ab is a set of top people: founders, executives, and experts who all were colleagues during MySQL AB times. Our Chairman is MySQL AB angel investor Ralf Wahlsten, who found MySQL AB’s first Chairman, and defined the MySQL Core Values with MySQL AB founders Michael “Monty” Widenius and David Axmark. Monty and David are investors and stakeholders, but have no operational or management roles. Our CEO is Ulf Sandberg, formerly SVP Services at MySQL AB. The co-founders include Mick Carney, formerly European Field Sales Manager for MySQL at Sun Microsystems, Patrik Backman, who worked with me on the SAP partnership and continued as Director of Engineering, and Max Mether, who set up training at MySQL AB with me, and already prior to that at Polycon Ab. Early colleagues include MySQL AB time stars such as Boel Larsen in HR, as well as Dean Ellis and Alexander “Salle” Keremidarski in Support.

Reason 2: The timing. SkySQL Ab is happening now, not when I’ve got my pictures sorted out. I want to support the team, and keep colleagues together, who work well together. I would hate to see an exodus of talent from the MySQL ecosystem.

Reason 3: The role. I can be back, making a difference, making decisions again. It isn’t as if I wouldn’t have given it a try at Oracle. I promised myself to make an attempt at influencing Oracle from the inside, explaining what the MySQL Community is, how the MySQL ecosystem works, and how it can be adapted to Oracle. Sure, like Oracle, SkySQL strives for profit, but the Oracle focus on the bottom line seemed to me to overshadow everything else, perhaps not respecting Open Source and community dynamics to the degree I would consider adequate. As listening to a Vice President from Munich wasn’t high on the list in Redwood Shores, I clearly prefer having real influence in a startup than a mere title but no influence at Oracle.

Reason 4: The credibility. Like I said in the previous reason, I tried to exert influence from inside Oracle but felt I failed. What I cannot live with is conveying information I don’t fully believe in myself, lending my ten years of MySQL presence to give Oracle credibility I feel it may not deserve. At SkySQL, I expect to be able to stand behind my statements for a tad longer than what happened with the ones I made in April. Referring to my resignation blog entry, The Register hints at my statements during the April 2010 keynote at the O’Reilly MySQL Conference:

The loss of another MySQL veteran in Arnö on the back of other Sun exits will counter attempts by Oracle to try and reassure users that their open-source database’s future is safe on the corporate mothership.

Last year [I do think this means April 2010; my own note] Arnö had tried to convince MySQLers that nothing would change for the worse under Oracle, that their database was safe, and that concerns over the database were unfounded.

Among the facts he highlighted was a “huge talent pool” of MySQL experts inside Oracle — a talent pool that’s now one member smaller. He also said that Oracle planned to be proactive in its dealings with the community and would emphasize maturing the database.

Reason 5: The opportunity. MySQL as a database hasn’t seen it’s apex yet, and SkySQL can contribute to the well-being of the MySQL ecosystem. The initial SkySQL Ab team collectively has over one hundred years of experience working for MySQL AB. I think this team has an excellent chance of grabbing the opportunity to build a profitable, sustainable commercial business providing MySQL related offerings, while preserving open source values.

In summary, to do the right thing, I felt I didn’t have much of a choice! I continue to feel responsibility for the MySQL ecosystem and I don’t want to let anyone important down.

So yes, I’ll still pursue my private goals. But on top of that, I plan to have lots of fun working at SkySQL Ab, and do my best to make the company a success comparable to MySQL AB!

Warning, the following is quite ugly, but does the job :)

A while back I needed to create an archive slave database from a half a terabyte myisam master and had space restrictions. I could not dump the db, load it, then drop keys (archive doesn’t support keys apart from a primary key on one column as of 5.1), alter engine etc (would take even longer than it took either way). So an ugly single liner came to mind and worked nicely too.

mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname --master-data=1 | sed 's/ENGINE=MyISAM/ENGINE=archive/g' | grep -v '^ UNIQUE KEY' | grep -v '^ KEY' | perl -p0777i -e 's/,\n^\)/\n\)/mg' | mysql -uuser -ppassword -h127.0.0.1 -P3307 dbname

So what is it doing?
Broken down:
mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname --master-data=1 –> extract the database with the master position
sed 's/ENGINE=MyISAM/ENGINE=archive/g' –> change myisam with archive so as to spare an alter table later
grep -v '^ UNIQUE KEY' –> remove unique keys
grep -v '^ KEY' –> remove other indexes
perl -p0777i -e 's/,\n^\)/\n\)/mg' –> after removing the indexes, any commas before the closing bracket need to be removed
mysql -uuser -ppassword -h127.0.0.1 -P3307 dbname –> inserting it directly into the slave

Let me know if you have any other ideas, again, the above is definitely not the neatest way, but it does work.

Because Sharing Is Caring

For more than nine years, I worked for MySQL AB and its successors, Sun Microsystems and Oracle. I handed in my resignation late June, two days before Sun’s German legal entity ceased to exist. Germany isn’t a country where you quit HP one day and join Oracle the next, so I had a long summer with plenty of so-called Garden Leave. Last Thursday was my last day, and I’m now outside MySQL AB, outside Sun Microsystems, outside Oracle.

Like all the many former colleagues who resigned before me, I did so with mixed feelings. Leaving the colleagues, finding freedom, I think you follow. The topmost feeling I have, the one I want to highlight right now, is gratitude. I’m very grateful for what MySQL AB has meant for my career, my personal development, my life experiences, my social life. Let me mention a small subset of the people that made my life at MySQL AB, and later Sun Microsystems, a truly memorable and enjoyable one.

Back in early 2000, when MySQL AB founders Michael “Monty” Widenius and David Axmark were just beginning to see the traction for MySQL, I got a proposal from Ralf Wahlsten, an old friend of Monty’s and mine: Since you’ve done training and consulting, and Monty hasn’t, why don’t you create a training program for MySQL? I’m sure Monty will help you and promote it. I followed the advice. So, in my company Polycon Ab where I was an entrepreneur for fourteen years, I started working with MySQL a good ten years ago. Ralf connected the dots! And extracted the MySQL Core Values from Monty and David, and found our first Chairman John Wattin, and became an Angel Investor in MySQL AB.

In February 2001, after a good half year of working with MySQL, it became obvious that I was experiencing something which was going to be big. My last doubts were removed when, at the outset of a boys’ trip to Rio de Janeiro, I understood Mårten Mickos (whom I had known since the early 1980s and respected for his leadership and judgement) had signed on as CEO. I asked Monty (whom I had known since the late 1970s) whether he was interested in me selling out the MySQL training operations of Polycon and formally joining MySQL AB. He was. Monty was kind enough to have me, and welcomed me with open arms. And created MySQL the product and the MySQL community.

In May 2001, I formally joined MySQL AB, together with my Polycon colleagues Bertrand Matthelié, Max Mether, and Sylvia Arte, soon to be joined by Olivier Beutels. There was a good dozen of employees before us. My initial title was VP Training, and Mårten invited me to join the management team, together with Monty, David and others. In the years to come, I was to get a number of other roles, VP Consulting, VP Services, VP Engineering and CIO, before becoming VP Community Relations in 2005. Mårten gave me all these opportunities, trusted me, and supported me as his reportee until he left Sun. And made MySQL AB into a company, grew it from a dozen people to 500 people, created a success story, and was a role model for how to do business with Open Source.

During the many years until the Sun acquisition, I had the privilege to work with some members of the MySQL AB Board of Directors. I learned a lot from you. John Wattin, our first Chairman, successfully guided us through financing rounds and growth pain. I fondly remember John referring to me as a “fireman”, given that I swapped roles so many times through the ride. I also had the pleasure to work with Fredrik Oweson of Scope Capital, Kevin Harvey of Benchmark (our second and final Chairman), Danny Rimer of Index Ventures, Bernard Liautaud (then of Business Objects), and Tim O’Reilly. Later on, at Sun, the board connections were replaced by contacts with Sun executives, where I most of all appreciated working with David Douglas, Rich Green and Alain Andreoli, and where all of us MySQLers got some unforgettable help from Rich Lang and Julie Ross.

MySQL AB opened the doors for learning to know many brilliant minds. Co-chairing a GPLv3 Committee with HP senior counsel Scott K. Peterson, I experienced Software Freedom Law Center’s Eben Moglen first-hand. Through a combination of intelligence and diplomacy, he tamed a conference-call-ful of the seniormost US corporate counsels, who all bought into Eben’s plans for the next generation of free software licenses. In 2005, I supported Florian Müller’s successful efforts to (at least for a while) save the EU from the software patents. I think Eben, Florian and I all agree swpats are obsolete legal tools, used to protect incumbent players against having to innovate. Sadly, Eben and Florian have since had some disagreements. Speaking of brilliant minds, the SAP negotiations in 2002 and 2003, and MySQL AB’s subsequent relationship with SAP AG, introduced us to people like Shai Agassi and Rudi Munz. Other memorable events was introducing MySQL Conference guest speakers, such as Guy Kawasaki and Mark Shuttleworth. I left the stage for Mark and his Ubuntu presentation on a MySQL conference by cheering him with “Поехали!” (Poyechale, Off we go!), a retired cosmonaut as he is.

Perhaps most rewarding was learning to know and appreciate the colleagues from nearly 30 countries. Yuri Gagarin’s exclamation when he left into space was something I learned to know from Alexander Barkov and other Russian and Ukrainian colleagues, whom I’ve had the pleasure to work with since 2002. I learned so much about sales and customer relations from Larry Stefonic, Kerry Ancheta, Joe Pen, Mark Rubin, Mark Burton, Mick Carney, Magnus Stenberg, Richard Mason, Philip Antoniades and Ivan Zoratti. I learned about Services and Support from Ulf Sandberg, Dean Ellis, Tom Basil, Alexander “Salle” Keremidarski, and Sinisa Milivojevic. I had the privilege to work with top engineers like Serg Golubchik, Kostja Osipov, Jan Kneschke, Igor Babaev, Georg Richter, Georgi “Joro” Kodinov, Heikki Tuuri, Kent Boortz and Brian Aker. I enjoyed working with my Community Team members, such as Lenz Grimmer, Jay Pipes, Duleepa Dups Wijayawardhana, and Colin Charles, and with management team colleagues, such as Zack Urlocker, Dennis Wolf, Clint Smith, Jeff Wiss, Tomas Ulin, Hans von Bell, Maurizio Gianola, Jeffrey Pugh, and Boel Larsen. And my sanity was saved through being excellently supported by some of my longest-time reportees, Patrik Backman and Giuseppe Maxia. And now having gone out on a limb by mentioning names and thereby most certainly having omitted at least a dozen people absolutely worth mentioning, I would like to thankfully highlight Edwin Desouza for labeling me as diplomatic, although this blog post is bound to fail on that account.

What next? As I resigned from having worked for and with MySQL for nearly ten years, I decided to give myself some time to spend on matters not directly related to IT. First, I enjoy expressing myself in writing, mostly in Swedish, German, and English. Second, I appreciate the beautiful things in life, and for me, aesthetics go hand in hand with photography. Third, I think there are opportunities to combine these into some experiments in the social web. Let’s see how long the break will take, before I return to more conventional duties, such as developing a startup, evangelising technology or devoting myself to Venture Capital.

What I want to do today, though, is simply to express my gratitude. Thank you, everyone behind MySQL AB!

There’s a new version of the dbbenchmark tool available. Now we’re creating the MySQL connection pool thread count based on automatic reporting of core quantity. There is still the same method to set the thread count manually if you are interested in finding your system’s thread limits. Download the MySQL benchmarking script now and add your server performance to the community database of results!

MySQL has a well-earned reputation for being easy-to-use and delivering performance and scalability. In previous versions, MyISAM was the default storage engine. In our experience, most users never changed the default settings. With MySQL 5.5, InnoDB becomes the default storage engine. Again, we expect most users will not change the default settings. But, because of InnoDB, the default settings deliver the benefits users expect from their RDBMS — ACID Transactions, Referential Integrity, and Crash Recovery. Lets explore how using InnoDB tables improves your life as a MySQL user, DBA, or developer.

Background

In the first years of MySQL growth, early web-based applications didn’t push the limits of concurrency and availability. In 2010, hard drive and memory capacity and the performance/price ratio have all gone through the roof. Users pushing the performance boundaries of MySQL care a lot about reliability and crash recovery. MySQL databases are big, busy, robust, distributed, and important.

InnoDB hits the sweet spot of these top user priorities. The trend of storage engine usage has shifted in favor of the more efficient InnoDB. With MySQL 5.5, the time is right to make InnoDB the default storage engine.

The Big Change

Starting from MySQL 5.5.5, the default storage engine for new tables is InnoDB. This change applies to newly created tables that don’t specify a storage engine with a clause such as ENGINE=MyISAM. (Given this change of default behavior, MySQL 5.5 might be a logical point to evaluate whether your tables that do use MyISAM could benefit from switching to InnoDB.)

The mysql and information_schema databases, that implement some of the MySQL internals, still use MyISAM. In particular, you cannot switch the grant tables to use InnoDB.

Benefits of InnoDB Tables

If you use MyISAM tables but aren’t tied to them for technical reasons, you’ll find many things more convenient when you use InnoDB tables in MySQL 5.5:

• If your server crashes because of a hardware or software issue, regardless of what was happening in the database at the time, you don’t need to do anything special after restarting the database. InnoDB automatically finalizes any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.
• The InnoDB buffer pool caches table and index data as the data is accessed. Frequently used data is processed directly from memory. This cache applies to so many types of information and speeds up processing so much that dedicated database servers assign most of their physical memory to it.
• If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and it gets kicked out automatically.
• If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.
• When you do a little advance planning to decide on primary key columns for each table, lots of operations involving those columns are automatically optimized. It is very fast to reference the primary key columns in WHERE clauses, ORDER BY clauses, GROUP BY clauses, and join operations.
• Inserts, updates, deletes are optimized by an automatic mechanism called change buffering. InnoDB not only allows concurrent read and write access to the same table, it caches changed data to streamline disk I/O.
• Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.

Best Practices for InnoDB Tables

If you have been using InnoDB for a long time, you already know about features like transactions and foreign keys. If not, read about them in the MySQL Reference Manual. To make a long story short:

• Specify a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there isn’t an obvious primary key.
• Embrace the idea of joins, where data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same datatype in each table. The foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.
• Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).
• Bracket sets of related changes, “logical units of work”, with START TRANSACTION and COMMIT statements. While you don’t want to commit too often, you also don’t want to issue huge batches of INSERT, UPDATE, or DELETE statements that run for hours without committing.
• Stop using LOCK TABLE statements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacrificing reliability or high performance.
• Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)
• Evaluate whether your data and access patterns benefit from the new InnoDB table compression feature (ROW_FORMAT=COMPRESSED on the CREATE TABLE statement. You can compress InnoDB tables without sacrificing read/write capability.
• Run your server with the option --sql_mode=NO_ENGINE_SUBSTITUTION to prevent tables being created with a different storage engine if there is an issue with the one specified in the ENGINE= clause of CREATE TABLE.

Recent Improvements for InnoDB Tables (from the Plugin Era)

If you have experience with InnoDB, but not the recent incarnation known as the InnoDB Plugin, read about those new features in the MySQL Reference Manual too. To make a long story short:

• You can compress tables and associated indexes.
• You can create and drop indexes with much less performance or availability impact than before.
• Truncating a table is very fast, and can free up disk space for the operating system to reuse, rather than freeing up space within the system tablespace that only InnoDB could reuse.
• The storage layout for table data is more efficient for BLOBs and long text fields.
• You can monitor the internal workings of the storage engine by querying INFORMATION_SCHEMA tables.
• You can monitor the performance details of the storage engine by querying PERFORMANCE_SCHEMA tables.
• There are many many performance improvements.

Performance Improvements for InnoDB Tables

• Crash recovery, the automatic process that makes all data consistent when the database is restarted, is fast and reliable. (Now much much faster than long-time InnoDB users are used to.) The bigger the database, the more dramatic the speedup.
• Most new performance features are automatic, or at most require setting a value for a configuration option. Read details here. Advanced users can review the InnoDB configuration options.
• Review the InnoDB-specific tuning techniques.

Testing and Benchmarking

It’s never too early to prepare for InnoDB as the default storage engine. You can preview whether your database server or application works correctly with MySQL 5.1 or even earlier. To set up InnoDB as the default storage engine, either specify on the command line --default-storage-engine=InnoDB, or add to your my.cnf file default-storage-engine=innodb in the [mysqld] section, then restart the server.

Since changing the default storage engine only affects new tables as they are created, run all your application installation and setup steps to confirm that everything installs properly. Then exercise all the application features to make sure all the data loading, editing, and querying features work. If a table relies on some MyISAM-specific feature, you’ll receive an error; add the ENGINE=MyISAM clause to the CREATE TABLE statement to avoid the error. (For example, tables that rely on full-text search must be MyISAM tables rather than InnoDB ones.)

If you didn’t make a deliberate decision about the storage engine, and you just want to preview how certain tables work when they’re created under InnoDB, issue the command ALTER TABLE table_name ENGINE=InnoDB; for each table. Or, to run test queries and other statements without disturbing the original table, make a copy like so:

CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;

Since there are so many performance enhancements in the InnoDB that’s part of MySQL 5.5, to get a true idea of the performance with a full application under a realistic workload, you’ll want to install the real MySQL 5.5 and run benchmarks. (Those who use the InnoDB Plugin with MySQL 5.1 have already seen some of these improvements, but to keep things simple, this article focuses on 5.5, where the latest and greatest InnoDB is fully integrated.)

Test the full application lifecycle, from installation, through heavy usage, and server restart. (For bonus points, kill the server process while the database is busy to simulate a power failure, and verify that the data is recovered successfully when you restart the server.)

Test any replication configurations, especially if you use different MySQL versions and options on the master and the slaves, because some of the InnoDB options have different defaults than in previous releases.

Verifying

To know what the status of InnoDB is, whether you’re doing what-if testing with an older MySQL or comprehensive testing with the real MySQL 5.5:

• Issue the command SHOW VARIABLES LIKE 'have_innodb'; to confirm that InnoDB is available at all. If the result is NO, you have a mysqld binary that was compiled without InnoDB support and you need to get a different one. If the result is DISABLED, go back through your startup options and configuration file and get rid of any skip-innodb option.
• Issue the command SHOW ENGINES; to see all the different MySQL storage engines. You want to see DEFAULT in the InnoDB line.

MySQL has a well-earned reputation for being easy-to-use and delivering performance and scalability. In previous versions, MyISAM was the default storage engine. In our experience, most users never changed the default settings. With MySQL 5.5, InnoDB becomes the default storage engine. Again, we expect most users will not change the default settings. But, because of InnoDB, the default settings deliver the benefits users expect from their RDBMS — ACID Transactions, Referential Integrity, and Crash Recovery. Lets explore how using InnoDB tables improves your life as a MySQL user, DBA, or developer.

Background

In the first years of MySQL growth, early web-based applications didn’t push the limits of concurrency and availability. In 2010, hard drive and memory capacity and the performance/price ratio have all gone through the roof. Users pushing the performance boundaries of MySQL care a lot about reliability and crash recovery. MySQL databases are big, busy, robust, distributed, and important.

InnoDB hits the sweet spot of these top user priorities. The trend of storage engine usage has shifted in favor of the more efficient InnoDB. With MySQL 5.5, the time is right to make InnoDB the default storage engine.

The Big Change

Starting from MySQL 5.5.5, the default storage engine for new tables is InnoDB. This change applies to newly created tables that don’t specify a storage engine with a clause such as ENGINE=MyISAM. (Given this change of default behavior, MySQL 5.5 might be a logical point to evaluate whether your tables that do use MyISAM could benefit from switching to InnoDB.)

The mysql and information_schema databases, that implement some of the MySQL internals, still use MyISAM. In particular, you cannot switch the grant tables to use InnoDB.

Benefits of InnoDB Tables

If you use MyISAM tables but aren’t tied to them for technical reasons, you’ll find many things more convenient when you use InnoDB tables in MySQL 5.5:

• If your server crashes because of a hardware or software issue, regardless of what was happening in the database at the time, you don’t need to do anything special after restarting the database. InnoDB automatically finalizes any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.
• The InnoDB buffer pool caches table and index data as the data is accessed. Frequently used data is processed directly from memory. This cache applies to so many types of information and speeds up processing so much that dedicated database servers assign most of their physical memory to it.
• If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and it gets kicked out automatically.
• If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.
• When you do a little advance planning to decide on primary key columns for each table, lots of operations involving those columns are automatically optimized. It is very fast to reference the primary key columns in WHERE clauses, ORDER BY clauses, GROUP BY clauses, and join operations.
• Inserts, updates, deletes are optimized by an automatic mechanism called change buffering. InnoDB not only allows concurrent read and write access to the same table, it caches changed data to streamline disk I/O.
• Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.

Best Practices for InnoDB Tables

If you have been using InnoDB for a long time, you already know about features like transactions and foreign keys. If not, read about them in the MySQL Reference Manual. To make a long story short:

• Specify a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there isn’t an obvious primary key.
• Embrace the idea of joins, where data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same datatype in each table. The foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.
• Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).
• Bracket sets of related changes, “logical units of work”, with START TRANSACTION and COMMIT statements. While you don’t want to commit too often, you also don’t want to issue huge batches of INSERT, UPDATE, or DELETE statements that run for hours without committing.
• Stop using LOCK TABLE statements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacrificing reliability or high performance.
• Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)
• Evaluate whether your data and access patterns benefit from the new InnoDB table compression feature (ROW_FORMAT=COMPRESSED on the CREATE TABLE statement. You can compress InnoDB tables without sacrificing read/write capability.
• Run your server with the option --sql_mode=NO_ENGINE_SUBSTITUTION to prevent tables being created with a different storage engine if there is an issue with the one specified in the ENGINE= clause of CREATE TABLE.

Recent Improvements for InnoDB Tables (from the Plugin Era)

If you have experience with InnoDB, but not the recent incarnation known as the InnoDB Plugin, read about those new features in the MySQL Reference Manual too. To make a long story short:

• You can compress tables and associated indexes.
• You can create and drop indexes with much less performance or availability impact than before.
• Truncating a table is very fast, and can free up disk space for the operating system to reuse, rather than freeing up space within the system tablespace that only InnoDB could reuse.
• The storage layout for table data is more efficient for BLOBs and long text fields.
• You can monitor the internal workings of the storage engine by querying INFORMATION_SCHEMA tables.
• You can monitor the performance details of the storage engine by querying PERFORMANCE_SCHEMA tables.
• There are many many performance improvements.

Performance Improvements for InnoDB Tables

• Crash recovery, the automatic process that makes all data consistent when the database is restarted, is fast and reliable. (Now much much faster than long-time InnoDB users are used to.) The bigger the database, the more dramatic the speedup.
• Most new performance features are automatic, or at most require setting a value for a configuration option. Read details here. Advanced users can review the InnoDB configuration options.
• Review the InnoDB-specific tuning techniques.

Testing and Benchmarking

It’s never too early to prepare for InnoDB as the default storage engine. You can preview whether your database server or application works correctly with MySQL 5.1 or even earlier. To set up InnoDB as the default storage engine, either specify on the command line --default-storage-engine=InnoDB, or add to your my.cnf file default-storage-engine=innodb in the [mysqld] section, then restart the server.

Since changing the default storage engine only affects new tables as they are created, run all your application installation and setup steps to confirm that everything installs properly. Then exercise all the application features to make sure all the data loading, editing, and querying features work. If a table relies on some MyISAM-specific feature, you’ll receive an error; add the ENGINE=MyISAM clause to the CREATE TABLE statement to avoid the error. (For example, tables that rely on full-text search must be MyISAM tables rather than InnoDB ones.)

If you didn’t make a deliberate decision about the storage engine, and you just want to preview how certain tables work when they’re created under InnoDB, issue the command ALTER TABLE table_name ENGINE=InnoDB; for each table. Or, to run test queries and other statements without disturbing the original table, make a copy like so:

CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;

Since there are so many performance enhancements in the InnoDB that’s part of MySQL 5.5, to get a true idea of the performance with a full application under a realistic workload, you’ll want to install the real MySQL 5.5 and run benchmarks. (Those who use the InnoDB Plugin with MySQL 5.1 have already seen some of these improvements, but to keep things simple, this article focuses on 5.5, where the latest and greatest InnoDB is fully integrated.)

Test the full application lifecycle, from installation, through heavy usage, and server restart. (For bonus points, kill the server process while the database is busy to simulate a power failure, and verify that the data is recovered successfully when you restart the server.)

Test any replication configurations, especially if you use different MySQL versions and options on the master and the slaves, because some of the InnoDB options have different defaults than in previous releases.

Verifying

To know what the status of InnoDB is, whether you’re doing what-if testing with an older MySQL or comprehensive testing with the real MySQL 5.5:

• Issue the command SHOW VARIABLES LIKE 'have_innodb'; to confirm that InnoDB is available at all. If the result is NO, you have a mysqld binary that was compiled without InnoDB support and you need to get a different one. If the result is DISABLED, go back through your startup options and configuration file and get rid of any skip-innodb option.
• Issue the command SHOW ENGINES; to see all the different MySQL storage engines. You want to see DEFAULT in the InnoDB line.