Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight:

Scalability issues due to tables without primary keys

This scalability issue is caused by the usage of tables without primary keys. This issue typically shows itself as contention on the InnoDB dict_sys mutex. Now the dict_sys mutex controls access to the data dictionary. This mutex is used at various places. I will only mention a few of them:

• During operations such as opening and closing table handles, or
• When accessing I_S tables, or
• During undo of a freshly inserted row, or
• During other data dictionary modification operations such as CREATE TABLE, or
• Within the “Persistent Stats” subsystem, among other things.

Of course this list is not exhaustive but should …

I configure all our master databases to use row-based binary logging where I work. In my opinion it is a much safer option than statement-based replication. The advantages and disadvantages of both types of MySQL replication are detailed in the online documentation here. You can't view the events a slave is applying directly with 'show processlist' but by issuing 'show open tables where in use' you can detect what table is receiving the attention of the SQL thread. If you need more information the mysqlbinlog command must be used to decode the slaves relay logs or masters binary logs.

Our developers often change a lot of rows with a single update statement. This usually results in some reasonable replication lag on downstream slaves. Occasionally the lag continues to grow and eventually nagios …

From time to time I see articles in defense of natural primary keys against surrogate keys.I don't take an immovable stand on either side, as I have seen good cases for both. In general, I like the idea of a natural primary key, when I see one that it is really natural. Quite often, though, a natural primary key has proved itself faulty. And most of the times, when such faults happened, it was because of limited understanding of the data. I like to describe this kind of misinformation as data analysis culture clash.When choosing a natural primary key, one should consider which element, or group of elements, are unique in a given set. Not only that, they must be immutable, at least within that set.For example, in a group of people, we may assume that a combination of name, surname, date and place of birth is a good natural primary key. Well, no. It isn't, for several reasons. Read on for a few real cases.If we rule out the above combination, …

InnoDB uses an index-organized data storage technique, wherein the primary key acts as the clustered index and this clustered index holds the data. Its for this reason that understanding the basics of InnoDB primary key is very important, and hence the need for these notes.

Some people don't probably know, but there is a difference between how indexes work in MyISAM and how they work in InnoDB, particularly when talking from the point of view of performance enhancement. Now since, InnoDB is starting to be widely used, it is important we understand how indexing works in InnoDB. Hence, the reason for this post!

There’s a really important difference between a unique index (MySQL’s answer to a “unique constraint”) and a primary key in MySQL. Please take a look at this:

CREATE TABLE `t` (
  `a` int,
  `b` int,
  `c` int,
  UNIQUE KEY `a` (`a`,`b`)
)

The combination of columns a, b should uniquely identify any tuple in the table, right?

select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
+------+------+------+

Wrong. Our arch-enemy NULL messes things up again:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does …

A friend asked for this, so I thought it’d be helpful: All tables with no primary key:

use INFORMATION_SCHEMA; select CONCAT(t.table_name,".",t.table_schema) as tbl, from TABLES AS t LEFT JOIN KEY_COLUMN_USAGE AS c ON (t.TABLE_NAME=c.TABLE_NAME AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA AND constraint_name='PRIMARY') WHERE t.table_schema!="information_schema" AND constraint_name IS NULL;

All tables and their primary keys, if exist:

use INFORMATION_SCHEMA; select CONCAT(t.table_name,".",t.table_schema) as tbl, c.column_name,c.constraint_name from TABLES AS t LEFT JOIN [...]

InnoDB is a transaction-safe, ACID compliant MySQL storage engine. It has commit, rollback, and crash recovery capabilities, and offers row level locking. The engine's overview page explains, “InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.”

read more