we run percona on 2x Xeon E5-2670 v2 @ 2.50GHz, 256 GB DDR4 RAM, RAID0: 4xSSD 800GB each, ESXI.
Total size of mysql_db: 100 GB.
But we using time critical applications and need to have always query time not more then: 0.01 - 0.05 s
but now sometimes we are getting very often table locks for 2-5 seconds due to myisam (our applications can’t work properly with innodb as can’t deal with restarting of deadlocks transactions).
Even we getting locks on table with 700-1000 rows only when parallel execution of query with JOINs.
and sounds very weird as qps only 700-800
Main table which used has only 500 rows.
and main selects/updates related to it.
But we have many tables with larger data, like 10000-25000 rows, with less read/write but all most queries have JOINs to main table.
1.Does overall performance of mysql limited only to CPU/RAM/DISK ?
because we have totally 40 cores and total load is not more then 10, more then half RAM is free and disk I/O is almost used by Percona server only.
2. If 1st question is NO, then will we have performance benefit of NDB cluster installation on single host?
As I correctly understand principles of NBD it has all data stored in memory, right ? and NBD engine doesnt have deadlock issues while all data gonna be synchronized lately and stored on disk ?
Or I’m confusing something and we not gonna have at all benefits in performance ?
Will it help if I create few nodes on same ESXI (as again point is resources of host not used even on half) ?
Many thx for advises.