InnoDB scalability issues due to tables without primary keys

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 give you a good picture of how heavily it is used.

But the thing is when you are mainly debugging contention related to a data dictionary control structure, you start to look off at something that is directly related to data dictionary modifications. You look for execution of CREATE TABLE, DROP TABLE, TRUNCATE TABLE, etc. But what if none of that is actually causing the contention on the dict_sys mutex? Are you aware when generating “row-id” values, for tables without explicit primary keys, or without non-nullable unique keys, dict_sys mutex is acquired. So INSERTs to tables with implicit primary keys is a InnoDB system-wide contention point.

Let’s also take a look at the relevant source code.

Firstly, below is the function that does the row-id allocation which is defined in the file storage/innobase/row/

Secondly, below is the function that actually generates the row-id which is defined in the file storage/innobase/include/dict0boot.ic

Finally, I would like to share results of a few benchmarks that I conducted in order to show you how this affects performance.

Benchmarking affects of non-presence of primary keys

First off all, let me share information about the host that was used in the benchmarks. I will also share the MySQL version and InnoDB configuration used.


The host was a “hi1.4xlarge” Amazon EC2 instance. The instance comes with 16 vCPUs and 60.5GB of memory. The instance storage consists of 2×1024 SSD-backed storage volumes, and the instance is connected to a 10 Gigabit ethernet network. So the IO performance is very decent. I created a RAID 0 array from the 2 instance storage volumes and created XFS filesystem on the resultant software RAID 0 volume. This configuration would allows us to get the best possible IO performance out of the instance.


The MySQL version used was 5.5.34 MySQL Community Server, and the InnoDB configuration looked as follows:

I conducted two different types of benchmarks, and both of them were done by using sysbench.

First one involved benchmarking the performance of single-row INSERTs for tables with and without explicit primary keys. That’s what I would be showing first.

Single-row INSERTs

The tables were generated as follows for the benchmark involving tables with primary keys:

This resulted in the following table being created:

While the tables without primary keys were generated as follows:

This resulted in the tables being created with the following structure:

The actual benchmark for the table with primary keys was run as follows:

While the actual benchmark for the table without primary keys was run as follows:

Note that the benchmarks were run with three variations in the number of concurrent threads used by sysbench: 16, 32 and 64.
Below are how the graphs look like for each of these benchmarks.

Writes per second 16 threads
Writes per second 32 threads
Writes per second 64 threads

Some key things to note from the graphs are that the throughput of the INSERTs to the tables without explicit primary keys never goes above 87% of the throughput of the INSERTs to the tables with primary keys defined. Furthermore, as we increase the concurrency downward spikes start appearing. These become more apparent when we move to a concurrency of 64 threads. This is expected, because the contention is supposed to increase as we increase the concurrency of operations that contend on the dict_sys mutex.

Now let’s take a look at how this impacts the bulk load performance.

Bulk Loads

The bulk loads to the tables with primary keys were performed as follows:

While the bulk loads to the tables without primary keys were performed as follows:

Note that the benchmarks were again run with three variations in the number of concurrent threads used by sysbench: 16, 32 and 64.
Below is what the picture is portrayed by the graph.

Parallel Bulk Loading of Tables

Here again, you can see how the bulk load time increases as we increase the number of concurrent threads. This against points to the increase in contention on the dict_sys mutex. With 16 threads the bulk load time for tables without primary keys is 107% more than the bulk load time for the tables with primary keys. This increases to 116% with 32 threads and finally 124% with 64 threads.


Tables without primary keys cause a wide range of contention because they rely on acquiring dict_sys mutex to generate row-id values. This mutex is used at critical places within InnoDB. Hence the affect of large amount of INSERTs to tables without primary keys is not only isolated to that table alone but can be seen very widely. There are a number of times I have seen tables without primary keys being used in many different scenarios that include simple INSERTs to these tables as well as multi-row INSERTs as a result of, for example, INSERT … SELECT into a table that is being temporarily created. The advice is always to have primary keys present in your tables. Hopefully I have been able to highlight the true impact non-presence of primary keys can have.

Share this post

Comments (15)

  • Justin Swanhart

    Unfortunately, partitioning often means having to go without a primary key because all columns in a unique index must be part of the partitioning key.

    Thus, it would be great if Percona or Oracle (or Facebook, or whomever) would make the id generation per-table instead of per-instance.

    October 18, 2013 at 1:37 am
  • Justin Swanhart

    Also, the bulk input test – was the input sorted? I’d be curious to see how InnoDB with the primary key compares to no primary key when InnoDB has to sort the data (assuming your load was sorted).

    October 18, 2013 at 3:30 pm
  • Bill Karwin

    Justin, the rule is slightly different from what you stated. All primary/unique constraints must include the partitioning key. But not all columns in the primary/unique constraint must be part of the partitioning key.

    October 18, 2013 at 4:43 pm
  • Daniël van Eeden

    If only MySQL could generate warnings if someone forgets a primary keys… (please click affects me too if it does affect you)

    Without PRIMARY KEY the performance for InnoDB not only suffers, but also slave performance suffers. With PXC not having PK’s will result in bad performance or might break things (old versions).

    October 19, 2013 at 7:23 am
  • Perrin Harkins

    Any suggestions on ways to add a primary key to a large existing table without a lot of down time? I normally use pt-online-schema-change for this sort of thing, but that won’t work on a table without a primary key.

    October 20, 2013 at 10:39 am
  • Ovais Tariq

    Justin, regarding your question about “bulk inserts”, the bulk inserts when InnoDB has a primary key would insert the data in sorted order. The inserts were done in that case in a table with an AUTO INCREMENT column and the value for that column was not specified when doing the bulk insert.

    October 21, 2013 at 7:47 am
  • Ovais Tariq


    What you have suggested is a good feature request. However, if you think from the standpoint of what is valid and what is not in terms of RDMS and SQL, then its perfectly fine for tables to not have primary keys. For example as Justin mentioned, you may not have PK in partitioned tables. The bug is actually in how the internal PK is generated, I would not expect InnoDB to need an instance level mutex to generate internal PK. And that is something that should be fixed.

    On the other hand, not having PK can hurt InnoDB performance in certain other ways, and also impacts performance of slaves with RBR. Again that is something that needs to be handled on the part of how RBR applies row-events. There are a lot of performance improvements in MySQL/PS 5.6 in terms of RBR when it comes to tables without primary keys. Similarly you should see improvement in PXC 5.6 with respect to tables without primary keys.

    October 21, 2013 at 7:53 am
  • Ovais Tariq


    pt-online-schema change would not he helpful when the table does not have a PK or a UK that could be used, because then pt-osc would not be able to safely capture and apply row modifications to the new table while it has not yet swapped the old table with the new table.

    In such cases you would have to rely on traditional approaches such as adding the key on the slave and then promoting it to be the master. But then you may hit this bug:

    October 21, 2013 at 7:56 am
  • Frederic Descamps

    Without Primary Key (and without unique key), the hidden InnoDB primary key (6-bytes) will be global to all your InnoDB tables without primary keys… that’s were the contention in case of concurrency happens.

    October 21, 2013 at 10:54 am
  • Rick James

    In your benchmarks, were you comparing the 6-byte hidden key to a 4-byte INT or to an 8-byte BIGINT? The difference in size of the data stored on disk _might_ explain the performance difference.

    October 21, 2013 at 6:16 pm
  • Jeremy Cole

    Hi Ovais,

    FYI, I covered this a few months ago here:

    Thanks for doing these benchmarks, but I too would like to see a comparison of INT vs. BIGINT for the key, and perhaps even CHAR(6) BINARY, to provide better “control” data points.

    October 22, 2013 at 1:16 pm
  • Ovais Tariq


    I will do a follow up blog post after testing with BIGINT PK columns.

    October 23, 2013 at 2:50 pm
  • svar

    Good surprised it’s not that bad, note that with the tested DDL, without the PK InnoDB have to maintain 3 indexes and with the PK only 2. Results with the same number of indexes would be more fair on impact of bad schema design .The mutex is not under contention, small dict without much memory copy i guess mutex have been just made for this. As Jeremy pointed out it can cause dict operations to be stalled but no longer than disk sync latency. Any idea on what DML take dict_mutex in concurency and would require fast latency. I guess InnoDB disk temporary tables ?

    September 16, 2014 at 5:46 pm
  • HarryKalahan

    Hello Ovais,

    Could you provide your lua scripts to try to reproduce your tests? I find this interesting as we are using a monitoring application like Zabbix without primary keys in its mysql’s history tables and we would like to test the performance.

    Thanks in advance. Best regards!

    December 14, 2014 at 2:43 pm
  • Jay Janssen

    @Harry — all of those lua scripts are part of the sysbench 0.5 package — you can build it yourself from launchpad, or download packages from our percona repos for it now.

    December 15, 2014 at 9:00 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.