My earlier post mysql versus postgresql brought me lots of negative comments - that i did not compare the transactional database of pgsql with the transactional engine (innodb) of mysql. The main reason why i did not do that was because i had found InnoDB to be very slow as compared to MyISAM.

But after all those comments i ran the benchmarks again using the same scripts and the same technology

Kaj just announced that as of 5.1.25, MySQL Cluster will no longer be included in the normal MySQL packages. Instead, a new branch is being created for MySQL Cluster releases, where the first release is informally called “6.2.15″ but the releases are really named “mysql-5.1.23-ndb-6.2.15″. This new branch is based on the MySQL Cluster Carrier Grade Edition of MySQL.

Overall, this seems like a good idea — the needs for releases, schedules, pressures, etc., are at odds between MySQL Cluster and MySQL’s core. I am, however, baffled by the decision of how to release the new product: coupled with MySQL as a single monolithic package with compiled-in storage engine. After all, 5.1 has long been touted to have this amazing new …

Innotop is a very useful tool to monitor innodb information in real time. This tool is written by Baron Schwartz who is also an author of “High Performance MySQL, Second edition” book. [Side note: I highly recommend getting this book when it comes out (in June, 08?). Other authors include: Peter Zaitsev, Jeremy Zawodny, Arjen Lentz, Vadim Tkachenko and Derek J. Balling.] Quick summary of what innotop can monitor (from: http://innotop.sourceforge.net/): InnoDB transactions and internals, queries and processes, deadlocks, foreign key errors, replication status, system variables and status and much more.

Following are the instructions on how to install innotop on CentOS x64/Fedora/RHEL (Redhat enterprise). Most probably same instructions can be used on all flavors of Linux. If not, leave me a comment and I will research a solution for you. Let us start with downloading innotop. I used version 1.6.0 which is the latest at the time of …

Properties:

Description:

This variable changes the way InnoDB open files and flush data to disk and is should be considered as very important for InnoDB performance. By default, InnoDB uses …

Properties:

Description:

This variable if enabled, makes InnoDB to create a separate .ibd file for storing indexes and data. Setting this variable makes it easy to manage disks with huge tables. Having …

Brian Moon suggest that community provided example my.cnf files would be a great thing to have on MySQLforge in this recent post: http://doughboy.wordpress.com/2008/05/06/example-mycnf-files/

I pulled out the "innodb heavy" config sample file and modified it with the standard settings that I typically start with when setting up a new InnoDB master. I've also modified the comments in the file a bit and have added some of my own too. I removed the sample slave configuration parameters (master-host, etc) because you should be using 'CHANGE MASTER TO'.

He suggested tagging such files with a 'mycnf' tag and very kindly tagged mine after I posted it :)

Feel free to share yours too and please feel free to make any comments about my configuration choices.

You can find it here (along with any other mycnf …

Zack Urlocker says MySQL 5.1 has zero bugs. He may have been misquoted, or quoted out of context, but there it is. I’ll quote enough of it that you can’t take it out of context twice:

Mickos also said MySQL 5.1 has upgraded its reliability and ease of use over 2005’s v5.0.

“Now we can admit it, but this version is much improved over 5.0, which we weren’t totally happy with,” Mickos confided.

He reported that more than 1,300 bugs (997 in 2007, 386 so far in 2008) have been fixed in v5.1, and that, according to standard DBT2 benchmarks, the performance of v5.1 is 10 to 15 percent better than the previous version.

“This version now has zero bugs,” Urlocker told eWEEK.

You can check for yourself at the MySQL bug …

Ever since Peter over at Percona wrote about MySQL and swap, I’ve been meaning to write this post. But after I saw Dathan Pattishall’s post on the subject, I knew I’d better actually do it.

There’s a nasty problem with Linux 2.6 even when you have a ton of RAM. No matter what you do, including setting /proc/sys/vm/swappiness = 0, your OS is going to prefer swapping stuff out rather than freeing up system cache. On a single-use machine, where the application is better at utilizing RAM than the system is, this is incredibly stupid. Our MySQL boxes are a perfect example – they run only MySQL and we want InnoDB to have a lot of RAM (32-64GB …

The video of one of my three sessions, "Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community", presented at MySQL Conference & Expo 2008 has been uploaded by Sheeri. I am very thankful to her for doing all the hard work and making it available.

There are a few slides that were edited out of video because of reasons beyond my control. However, you should still be able to enjoy most of the video.

There is one point related to this video that I would like to make: Based on my particular experience I was leading to believe that Solaris 10 Kernel had the same …

This article provides a comparison between the MyISAM and InnoDB storage engines for MySQL. InnoDB is commonly considered to perform worse than MyISAM, but this article aims to dispel this myth by describing the differences between these engines and what makes InnoDB a good fit for many database needs.

In addition, a look at when it is better to use MyISAM and a case study of the drupal.org site provide insight for determining which engine is best for a given situation.

read more