First a bit on
ndb_autoincrement_prefetch_szand then blobs!
One thing is contention on auto_increments, which can really slow down performance.
By default the
ndb_autoincrement_prefetch_sz=1. This means that the mysqld will cache one auto_increment number and then go down to the data nodes to fetch the next number. It is better to let the mysqld cache more numbers, to avoid the unnecessary round-trip.
From MySQL Cluster 7.0.13 you can set it up to 65536 (previously max was 256)
ndb_autoincrement_prefetch_sz=1024the mysqld will cache 1024 numbers before fetching the next range of numbers from the data nodes.
Som numbers for inserting batches of 16 records from 8 concurrent threads on one mysqld:
ndb_autoincrement_prefetch_sz=1: 1211.91TPSHere we got an 3x improvement on INSERT performance. Nice!
This test is by no means trying to max out the cluster, far from it, just to illustrate how important it is to set the
Another is on
BLOB/TEXTattributes - which in many cases are overused in applications (e.g, there is no reason to store an 'email' as a TEXT, or if the data is less than about 8000B).
If you can change them to VARBINARY/VARCHAR (as LinuxJedi suggests), do it:
BLOBs/TEXTsare significantly slower compared to
BLOBsare stored in a separate table, and need to be locked with at least a shared lock when accessed).
CREATE TABLE `t1_blob` (and inserted 200K records. sizeof(data1) = 1024B, sizeof(data2) = 1024B.
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
SELECT data1, data2 FROM t1 WHERE id=<rand>
Executed from 1 App - 8 Threads on one MySQLD and two data nodes resulted in:
data1 and data2 represented as BLOBs : 5844 TPS
data1 and data2 represented as VARBINARYs: 19206 TPS
Again, this is not maxing out the data nodes, just to show you the idea.
If you can replace
VARCHAR/VARBINARY(remember that the max record size in Cluster is 8052B) do it.