You might know if you have long running transactions you’re risking having a lot of “garbage” accumulated in undo segment size which can cause performance degradation as well as increased disk space usage. Long transactions can also be bad for other reasons such as taking row level locks which will prevent other transactions for execution, this however only applies to read transactions while problem with undo space growth exists for read only transactions as well.

This is how long transaction looks:

---TRANSACTION 17402D749, ACTIVE 15867 sec, process no 19349, OS thread id 1630148928
MySQL thread id 188790, query id 14796224615 host.domain.com 127.0.0.1 root

It was running over 4 hours so Innodb could be preventing purge from happening for long time and there could be a lot of garbage in undo segment. If you’re running Percona Server it is easy to check:

mysql> select * from …

I again work with the system which needs high insertion rate for data which generally fits in memory. Last time I worked with similar system it used MyISAM and the system was built using multiple tables. Using multiple key caches was the good solution at that time and we could get over 200K of inserts/sec.

This time I worked with Innodb tables... it was a different system with different table structure, not to mention different hardware so It can't be compared directly, still it is nice to see you can get the numbers as high with Innodb too.

I will spare you all experiments we went through and just share final numbers. On 8 core Opteron Box we were able to achieve 275K inserts/sec at which time we started to see load to get IO bound because of log writes and flushing dirty buffers. I'm confident you can get to 400K+ inserts/sec on faster hardware and disks (say better RAID or …

While trying to add an index to a fairly large table today, on a server1 I’d not worked on previously, I got the following error after some time (and while I was away from the computer):

mysql> ALTER TABLE t
    ->   ADD KEY `index_a` (`a`),
    ->   ADD KEY `index_b` (`b`),
    ->   ADD KEY `index_c` (`c`);
ERROR 1114 (HY000): The table 't' is full

The error log did not bring particular enlightenment (as usual, InnoDB is extremely verbose with the logs, without saying anything useful):

110105 16:22:30  InnoDB: Error: Write to file (merge) failed at offset 4 1387266048.
InnoDB: 1048576 bytes should have been written, only 888832 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at …

Inspired by Baron's earlier post, here is one I hear quite frequently -

"If you enable innodb_file_per_table, each table is it's own .ibd file.  You can then relocate the heavy hit tables to a different location and create symlinks to the original location."

There are a few things wrong with this advice:

1. InnoDB does not support these symlinks.  If you run an ALTER TABLE command, what you will find is that a new temporary table is created (in the original location!), the symlink is destroyed, and the temporary table is renamed.  Your "optimization" is lost.
2. Striping (with RAID) is usually a far better optimization.  Striping a table across multiple disks effectively balances the  'heavy hit' access across many more disks.  With 1 disk/table you are …

I had earlier written a post on tuning the MySQL server configuration which was more geared towards the MyISAM storage engine. While that is not because I didn't intend on ignoring InnoDB but because I had planned a whole post on tuning InnoDB related configuration. So this post is the post that I had planned, I have discussed the major configuration parameters in here that should help you out most of the times.

The first addition is something that users of stored routines have been waiting for since MySQL 5.0. No, it is not SIGNAL and its close associate RESIGNAL, which have been publicized enough. I am talking about the stored routine parameters, for which now there is a dedicated table in the information_schema.
Let's see an example, with a simple …

I have been looking forward to the general availability (GA) release of MySQL 5.5 since is was publically announced in September that we would see this in 2010. While I already have a production client with 5.5.7rc, the badge of general availability is a great way to promote why environments should consider moving to using MySQL 5.5. Here is my quick short list of why I’d promote moving to MySQL 5.5.

1. Improved integration

The first significant improvement is that InnoDB is now again firmly a default included storage engine. The InnoDB plugin 1.1.x is now the builtin version of the engine, not a plugin version. Also the 1.1.x version has continued improvements over the 1.0.x version available as an included but not enabled plugin in current MySQL 5.1.x versions. Removing the complexity for end users over the choice of InnoDB and the necessary configuration changes is a great simplification. The introduction in the InnoDB plugin …

Percona XtraBackup 1.5-Beta is now available for download.

This release adds additional functionality to Percona XtraBackup 1.4, the current general availability version of XtraBackup.

This is a beta release.

Functionality Added or Changed

• Support for MySQL 5.5 databases has been implemented. (Yasufumi Kinoshita)
• XtraBackup can now be built from the MySQL 5.1.52, MySQL 5.5.7, or Percona Server 5.1.53-12 code bases (fixes bug #683507). (Alexey Kopytov)
• The program is now distributed as three separate binaries: 
  • xtrabackup - for use with Percona Server with the built-in InnoDB plugin
  • xtrabackup_51 - for use with MySQL 5.0 & 5.1 with built-in InnoDB
  • xtrabackup_55 - for use with MySQL 5.5 (this binary is not provided for the …

In case you haven’t heard yet, I’ve merged in the latest InnoDB from MySQL 5.5.7 into Drizzle. The innobase plugin is now based on InnoDB 1.1.3.

This gets a lot of bug fixes and improvements from 1.1.2 (and on 1.1.1). Enjoy!