bencher - a benchmarking utility for MySQL Cluster

Tweet bencher is a test program that allows you to benchmark requests on MySQL Cluster. I have used this utility a lot of customers, because it lets me:

• specify a simple query that I want to benchmark on the command line
  
• implement more complex use cases.
  
• implement NDBAPI requests

and I don't have to reinvent the wheel every time. It is all there: connectivity, multi-threading support, timers, and some basic statistics, and it compiles on most platforms. I just have to focus on the queries I want to optimize or benchmark.

The simple use case is to specify the SQL query you want to benchmark, the number of threads, and how many times. You can also customize this very easily to benchmark more elaborate SQL requsts, and NDBAPI requests.

bencher outputs per thread statistics and total throughput:

./src/bencher -s /tmp/mysql.sock.3306 -t 2 -l 10000 -q "select * from t1 limit 1"

------- Starting Benchmark ----------

Thread 1 - 638 qps (average qps measured after 5 secs)
Thread 0 - 631 qps (average qps measured after 5 secs)
Thread 1 - 680 qps (average qps measured after 10 secs)
Thread 0 - 679 qps (average qps measured after 10 secs)

------- Benchmark Finished ----------

Thread 0 - max: 83091 us, min 668 us, less than 5 ms: 9761 of 10000, avg: 1485 us, total time: 14949 ms, qps: 668.91
Thread 1 - max: 43743 us, min 578 us, less than 5 ms: 9770 of 10000, avg: 1475 us, total time: 14767 ms, qps: 677.16

Total throughput = 1346.08 qps
Average exec time per thread = 14.86 secs


You can also specify a "querytime-threshold", to see how many transaction have executed under a certain time (default is 5 ms). From the above you can see that for this particular query 9760/10000 requests finished within 5 ms.

Triggers, table locks and NDB

Tweet At a customer yesterday, I confirmed what Jonas suspected and what is probably related to bug 42474. Scroll down and look for the output of SHOW PROCESSLIST.
It seems that TRIGGERs causes Table Locks to be taken when used with Ndb cluster tables!! I have created a bug report .

If we do an update on a table that has an update trigger the trigger will upon execution lock the entire table that is affected by the trigger.

This was verified by having several threads updating random records in a table. When one update gets to execute, the trigger will block the other updates from happening. This was shown using SHOW FULL PROCESSLIST¸ which shows a bunch of statements being in the state Locked. Also, the Table_locks_waited (SHOW GLOBAL STATUS) was also constantly increasing.

This shouldn't happen as Cluster uses Row-level locking. Innodb handles this by downgrading the table lock to a row-level lock.

Removing the TRIGGER "fixed" the problem (i.e, proving it is broken).

So if you experience terrible scalability with Cluster and Triggers, then this may explain why..