The system my team develops has many huge tables in MySQL databases. Since our clients need faster responses from our application, we are testing alternatives to the MyISAM engine, our default engine.
We are testing the TokuDB engine. After few attempts, we stumbled on timeouts issues, fixed by changes in tokudb_lock_timeout configuration variable. Although the timeout problem was fixed, queries of type
INSERT ... <insert_table> SELECT ... <select_tables> started to queue, get stuck by query locks, even though the
insert_table is different for every query in the queue. However, queries of type
SELECT ... <select_tables> do not get stuck. We’ve tried some fine tuning in the configuration file, but the problem continues.
Here is a reproduction guide: https://gist.github.com/susanoobit/33e49cdfa80e66fd7392e755acf716b7
Our configuration file (…/percona-server.conf.d/mysqld.cnf): https://gist.github.com/susanoobit/e8610687247aaeab48c5911e8f161958
Our test server has 62G RAM, 27G SWAP, CPU Intel® Xeon® CPU E5-2620 v3 @ 2.40GHz.
Searching for a solution, we’ve found that TokuDB has exclusive read locks according to this document: https://github.com/percona/tokudb-engine/wiki/Transactions-and-Concurrency#lock-types.
Is the exclusive read locks the actual problem?
Thanks in advance.