The new ndbinfo interface in 7.1 is really useful to assist in tuning MySQL Cluster. Here is an example (more will follow):
I started with one test where I inserted two blobs (1KB + 1KB) in one table.
From 16 threads (colocated with one mysqld, two data nodes, separate computers) and one application driving the load I reached about 6960TPS, and the utilization of the redo buffers (controlled by the parameter RedoBuffer in config.ini) looked like:
mysql< select * from ndbinfo.logbuffers; +---------+----------+--------+----------+----------+--------+ | node_id | log_type | log_id | log_part | total | used | +---------+----------+--------+----------+----------+--------+ | 3 | REDO | 0 | 1 | 50331648 | 196608 | | 3 | REDO | 0 | 2 | 50331648 | 294912 | | 3 | REDO | 0 | 3 | 50331648 | 131072 | | 3 | REDO | 0 | 4 | 50331648 | 229376 | | 4 | REDO | 0 | 1 | 50331648 | 229376 | | 4 | REDO | 0 | 2 | 50331648 | 262144 | | 4 | REDO | 0 | 3 | 50331648 | 163840 | | 4 | REDO | 0 | 4 | 50331648 | 229376 | +---------+----------+--------+----------+----------+--------+ 8 rows in set (0.01 sec)
Which is basically nothing.
I then increased the load and inserted 2 x 5120B BLOBs (from 16 threads one MySQL server), and run with an insert speed of 4320TPS:
mysql< select * from ndbinfo.logbuffers; +---------+----------+--------+----------+----------+----------+ | node_id | log_type | log_id | log_part | total | used | +---------+----------+--------+----------+----------+----------+ | 3 | REDO | 0 | 1 | 50331648 | 11468800 | | 3 | REDO | 0 | 2 | 50331648 | 31522816 | | 3 | REDO | 0 | 3 | 50331648 | 42008576 | | 3 | REDO | 0 | 4 | 50331648 | 43057152 | | 4 | REDO | 0 | 1 | 50331648 | 14090240 | | 4 | REDO | 0 | 2 | 50331648 | 17432576 | | 4 | REDO | 0 | 3 | 50331648 | 10321920 | | 4 | REDO | 0 | 4 | 50331648 | 12615680 | +---------+----------+--------+----------+----------+----------+
Above you can see that the redo buffers are used (the load will be spread around, and it is hard to catch a moment where the load is even on all buffers), and now the application started to throw the error "Got temporary error 1221 'REDO buffers overloaded (increase RedoBuffer)' from NDBCLUSTER (1297)"
I can now follow the instruction to increase the REDO buffer, but would it help in this case?
No, no and no.
The disk is too slow to keep up and cannot write out to disk in the same rate as the application writes out.
‘iostat’ gives:
< iostat -kx 1 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util cciss/c0d0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 cciss/c0d1 0.00 27796.00 0.00 1454.00 0.00 115196.00 158.45 12.03 8.25 0.66 95.30 dm-0 0.00 0.00 0.00 29270.00 0.00 117080.00 8.00 274.79 9.33 0.03 95.20 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 dm-5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
And here you can see that the disks are quite utilized. This means that I have two options now if I want to be able to sustain the 4320TPS insert load:
- Increase the number of data nodes (computers) so instead of having two computers, I should have four so that I spread the load across more hardware
- Improve my disk subsystem (add better disks, e.g, to have 2-4 disk spindles to spread the load on), or by having the REDO log on device cciss/c0d1 and the the LCP on device cciss/c0d0.
The CPU, could that also been an bottleneck in this case? No, it was not the issue. The CMVMI thread (one of the data nodes threads) was spending 44.4% polling data from the other nodes, and it is reading in quite large packets so that is why it was the heaviest user of CPU of the data node threads.
5453 root 20 0 6594m 4.1g 6956 R 44.4 51.9 4:05.64 ndbmtd 5471 root 20 0 6594m 4.1g 6956 S 32.5 51.9 3:39.07 ndbmtd 5474 root 20 0 6594m 4.1g 6956 R 26.6 51.9 2:25.55 ndbmtd 5475 root 20 0 6594m 4.1g 6956 S 23.7 51.9 2:25.01 ndbmtd 5476 root 20 0 6594m 4.1g 6956 R 23.7 51.9 2:20.83 ndbmtd 5473 root 20 0 6594m 4.1g 6956 R 21.7 51.9 2:26.57 ndbmtd
