Just in case you don't know resizing Innodb log file is not just simple as changing the value of innodb_log_file_size in the MySQL configuration file. But its even more simpler. Follow the steps in this post and you will be done with the resizing.

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.

First of all: mysqldump is not converting tables. It is something else. Here is the story:

One of my clients had a case when they were migrating to a new mysql server: they used mysqldump to export data from the old server (all InnoDB) and imported it to the new server. When finished, all the tables became MyISAM on the new server. So they asked me this question:
“Why mysqldump is converting my tables from InnoDB to MyISAM?”

First of all we made sure that the tables are InnoDB on the old server. It was true.
Second we run “show engines” on the new server:

+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| …

My branch that updates the innobase plugin in Drizzle to be based on innodb_plugin 1.0.9 has been merged. For the next milestone, we’ll probably have 1.0.11 as well.

How’s the progress getting 1.1 and 1.2 in? Pretty good actually. We’ll have it for either this milestone or the next one.

and merging newer innodb into HailDB? It’s going well too, expect more news “soon”.

Before I start a story about the data recovery case I worked on yesterday, here's a quick tip - having a database backup does not mean you can restore from it. Always verify your backup can be used to restore the database! If not automatically, do this manually, at least once a month. No, seriously - in most of the recovery cases I worked on, customers did have some sort of backup, but it just wasn't working, complete and what not. Someone set it up and never bothered to check if it still works after a while.

Anyway, this post is not really about the backups but rather about few interesting things I learned during last recovery case.

First, some facts about the system and how data was lost:

• MySQL had a dedicated partition on XFS file system
• Server was running innodb_file_per_table
• There was a …

The following is an edited transcription from the #mysql on the Freenode IRC Chat. It is here so that it can be referenced by the IRC bot on that channel.

G> So it's not necessarily a bad idea to use say an autoinc as the primary index of a table?

A> It's a very good idea to have an autoinc primary index. See the Manual.

T> A: I tend to prefer the natural primary key if one exists, unless ORM is an issue.

I> For Innodb autoinc is usually preferrable.



Continue reading "Natural keys vs. auto_increment"

innotop is a fine tool that every MySQL DBA should be familiar with. Although it takes a while to get used to, it's worth it. It's less than intuitive for most people, unless you grew up in Linux.
The other problematic part except the learning curve is the installation. It took me a while last time I had to install it, and this time re-installing it I've decided to write down the steps needed.

Most of the trouble I went through the first time around were with CPAN, and finding out that those Perl modules were available in RPM form saved me lots of time. Avoid CPAN if you can for this purpose.
Also, these steps should work on RedHat Linux as well, of course, but I didn't test it.

1. Download the compatibility version of the shared MySQL libraries. The …

MySQL Community Edition continues to be available under the open source GPL. It includes all existing storage engines (e.g. InnoDB, MyISAM, Memory, Archive, etc), all existing Connectors (e.g. ODBC, JDBC, .NET, etc), and all existing functionality (e.g. replication, partitioning). There are no changes for the community.

Download the latest "GA" versions at:

http://www.mysql.com/downloads/

Download the "under development" versions at:

http://dev.mysql.com/downloads

InnoDB storage engine for MySQL continues to be freely available under GPL, as part of the MySQL Community Edition.

For more information, check out the MySQL Community …

Mark Callaghan over at Facebook wrote a note recently about InnoDB disk IO counters in SHOW STATUS, with some extra things that he wanted to track. I posted a quick comment over there, but I thought this deserved it’s own write up.

MySQL 5.5’s PERFORMANCE_SCHEMA has had a fair bit written about it in terms of tracking synchronization point contention (mutexes etc.), but it currently tracks two orders within the wait class - these are /wait/synch and /wait/io.

Actually, allow me to detour first, it’s not clear from the documentation, though it is clear in the worklog. Each …

The UK Oracle User Group (UKOUG) is an independent non-profit organisation of users of Oracle products in the UK, which organizes over 80 events per year all across the UK and Ireland. They have more that 15.000 members and were established over 25 years ago. Currently, they are in the final stretch of the preparations for their annual Technology & E-Business Conference, which will take place from November, 29th - December, 1st in Birmingham (UK).

For the first time, the conference will host a dedicated stream of presentations related to MySQL, which is scheduled for Wednesday (Dec. 1st). Targeted at database administrators, developers as well as users new to MySQL, there will be presentations from well-known speakers and participants of the MySQL community as well …