This is expected, because the contention is supposed to increase as we increase the concurrency of operations that contend on the dict_sys mutex. These become more apparent when we move to a concurrency of 64 threads. Furthermore, as we increase the concurrency downward spikes start appearing. Some key things to note from the graphs are that the throughput of the INSERTs to the tables without explicit primary keys never goes above 87% of the throughput of the INSERTs to the tables with primary keys defined. Note that the benchmarks were run with three variations in the number of concurrent threads used by sysbench: 16, 32 and 64.īelow are how the graphs look like for each of these benchmarks. lua - oltp- tables- count= 64 - oltp- table- size= 1000000 - oltp- secondary - oltp- dist- type= uniform - mysql- table- engine= innodb - mysql- user= root - mysql- host= 127.0.0.1 - mysql- port= 3306 - mysql- db= test - max- time= 300 - num- threads= 16 - max- requests= 0 - report- interval= 1 run Sysbench - test=/ root/ sysbench/ sysbench/ tests/ db/ insert. Let’s also take a look at the relevant source code.įirstly, below is the function that does the row-id allocation which is defined in the file storage/innobase/row/ So INSERTs to tables with implicit primary keys is a InnoDB system-wide contention point. But what if none of that is actually causing the contention on the dict_sys mutex? Are you aware when generating “row-id” values, for tables without explicit primary keys, or without non-nullable unique keys, dict_sys mutex is acquired. You look for execution of CREATE TABLE, DROP TABLE, TRUNCATE TABLE, etc. Of course this list is not exhaustive but should give you a good picture of how heavily it is used.īut the thing is when you are mainly debugging contention related to a data dictionary control structure, you start to look off at something that is directly related to data dictionary modifications. Within the “Persistent Stats” subsystem, among other things.During other data dictionary modification operations such as CREATE TABLE, or.During undo of a freshly inserted row, or.During operations such as opening and closing table handles, or.Now the dict_sys mutex controls access to the data dictionary. This issue typically shows itself as contention on the InnoDB dict_sys mutex. This scalability issue is caused by the usage of tables without primary keys. Hence there was another one I wanted to highlight: Scalability issues due to tables without primary keys
Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues.
0 kommentar(er)
