fast IO and ndb

i read Marks blog entry about fast IO for PBXT, InnoDB (1.0.6 plugin) and MyISAM with some interest.

then there was the durable, not durable and really not durable blog in which Mark and LinuxJedi discussed cluster, and Mark wondered how much IO one could get from 1 data-node with ndb.

so I decided to try.
the setup is similar.
ndb cluster using disk-tables, with a tablespace stored in /dev/shm.

Data occupied 7.3G, which is slightly less than (some of) the others, this is as we don't support having columns with indexes stored on disk. I.e any column that has an index will be stored only in memory instead.

ndb does however not support "handler" statements, so I used oltp-point-select-all-cols instead.

sysbench --test=oltp --mysql-host=foobar --mysql-user=root \
--mysql-password=pw \
--mysql-db=test \
--oltp-table-size=40000000 \
--max-time=60 --max-requests=0 \
--mysql-table-engine=ndb \
--db-ps-mode=disable --mysql-engine-trx=yes \
--oltp-read-only --oltp-skip-trx --oltp-test-mode=simple \
--oltp-point-select-all-cols \
--oltp-dist-type=uniform \
--oltp-range-size=1000 \
--num-threads=1 --seed-rng=1 run

results are not super great...
so i did a ndbapi program (which is roughly the equivalent of handler-statements)



these number look a bit better. but datanode (ndbmtd) was almost idle when running this...

so i made another experiment. Instead of retrieving 1 row at a time (set@r = rand() % 40000000; select * from sbtest where id = @r) i changed to retrive 16 rows at a time (set @r1 = rand(); set @r2 = rand(); select * from sbtest where id in (@r1...@r16).



i believe these results are relevant given that mark's aim was to test fast IO,
and i think that this rewrite wont affect other SE as much as it does with ndb.
and those numbers was quite ok.

as an extra bonus, i also tried with using our memory tables (alter table sbtest storage memory), also with ndbapi and 16 rows at a time.



these tests was executed on a 16-core machine Intel(R) Xeon(R) CPU E7420@2.13GHz
and in my config.ini I had

DiskPageBufferMemory = 1GB
DiskIOThreadPool=8
FileSystemPath=/dev/shm

note: I first create tables in myisam, then converted them to ndb disk-tables by issuing "alter table sbtest storage disk tablespace TS engine = ndb" and all tests was using ndb-cluster-connection-pool=4

all results:

SQL      2756   4998   7133   9130  10720  12222  13305  14190  14626  15287  15547  15955  16143  16334  16507  16757
ndbapi   4177   7581  10319  13162  15245  17064  18874  20652  20850  24131  25976  24910  29832  30666  32625  34841
b=16     1520  11374  35454  53396  55601  63248  71819  78468 103324  97330  97572 111099 125564 126790 133873 141588
mm b=16 73004 128296 172207 207361 246907 270783 293753 312435 327006 345085 346924 374837 360747 372192 376887 394862