2

I understand that the InnoDB engine relies heavily on primary keys for its storage mechanisms (index layouts, etc), and that it is consequently a bad idea to use a non-sequential primary key (say a random 15 digit integer), because it will cause frequent (not to say systematic) rebuilds of the primary key's BTree, thus slowing exponentially insertions on the table.

I was considering setting up a MySQL Cluster to host my application databases, which need to support a write-intensive load (around 40% writes on about 2M operations a day). Given that NDB records are using primary key hashes to distribute records between the cluster's nodes, I was wondering if this limitation also apply to this engine.

My first guess would be that in the contrary, the randomness would help distribute evenly the data, but I can't find precise information about that. So, does anyone have an insight on this matter ?

Elwinar
  • 9,103
  • 32
  • 40
  • Not the answer you want but, are you sure your random keys are unique? – ToBe Jul 25 '14 at 07:55
  • My keys are 15-digit random integers. So a set of 10^15 possibles values. They are generated at a rate of about 1M / week, so I think I can safely assume that they will be unique. – Elwinar Jul 25 '14 at 08:00
  • At least capture a (however rare) event of the DB aborting due to unique key violations on insert. – ToBe Jul 25 '14 at 08:02
  • I already do that, that's not the point of my question. But thanks for the concern. – Elwinar Jul 25 '14 at 08:06
  • This article: **[slow-insert-into-innodb-table-with-random-primary-key-columns-value](http://stackoverflow.com/questions/7896534/slow-insert-into-innodb-table-with-random-primary-key-columns-value)**, also **[How important a primary key can be for MySQL performance?](http://www.dbasquare.com/2012/04/04/how-important-a-primary-key-can-be-for-mysql-performance/)**, also **[MySQL Insert performance and random primary key values](http://blog.nirav.name/2014/04/mysql-insert-performance-and-random.html)** may be useful. – Ryan Vincent Jul 25 '14 at 09:21
  • I already found these articles while searching for informations. The problem is that all of these (and so many others) focus only on InnoDB, which is not the engine I'm wondering about. – Elwinar Jul 25 '14 at 10:22
  • I wanted to help - would it be useful to explain in your question, what you didn't want and what should be looked for specifically? It isn't clear what you mean by 'MySql clusters and their keys'. The more information you provide and what you want - helps. – Ryan Vincent Jul 28 '14 at 18:44

0 Answers0