Description:
During testing of 5.0.17 binary with system_1 test under high load (number of threads > 20) I observed very low concurrency when almost all threads were in Locked state.
Scheme of system_1 test consists of two tables(MyISAM and InnoDB), several SPs, triggers for each table which executed on insert, update, delete operations. For details please look on t/initdb.test file in system_1 test suite. Test itself consists of several simple tests that call SPs or modify data in two tables above.
Execution sequence of the test:
- start server
- initialize db scheme(tables, SPs, triggers, load data)
- start N threads and pass _one_ random test from set of tests to each thread
(this test then will be executed with mysqltest)
- wait for X sec
- exit
If we would start test with N=10 and X=180 all threads will finish in time. But for N=30|40 and X=180 test will last not ~180 sec but rather 30-45mins.
Also different symptoms of the problem above were observed on different platforms.(mysqladmin proc was used to monitor state of threads on server side). On Solaris(SPARC) some threads were in Locked state from begin of the test till the moment when all other threads finished with execution of their statements. On Linux(x86_64) most of the threads were in state Locked for some time and only some threads were executed statements.
Despite of different symptoms it looks like there are some flaws in locking mechanism
(related to SPs/triggers?) which descrease concurency on such workloads
Description above was added to note that issue exists and further investiagation is required.
How to repeat:
Will be added later
Description: During testing of 5.0.17 binary with system_1 test under high load (number of threads > 20) I observed very low concurrency when almost all threads were in Locked state. Scheme of system_1 test consists of two tables(MyISAM and InnoDB), several SPs, triggers for each table which executed on insert, update, delete operations. For details please look on t/initdb.test file in system_1 test suite. Test itself consists of several simple tests that call SPs or modify data in two tables above. Execution sequence of the test: - start server - initialize db scheme(tables, SPs, triggers, load data) - start N threads and pass _one_ random test from set of tests to each thread (this test then will be executed with mysqltest) - wait for X sec - exit If we would start test with N=10 and X=180 all threads will finish in time. But for N=30|40 and X=180 test will last not ~180 sec but rather 30-45mins. Also different symptoms of the problem above were observed on different platforms.(mysqladmin proc was used to monitor state of threads on server side). On Solaris(SPARC) some threads were in Locked state from begin of the test till the moment when all other threads finished with execution of their statements. On Linux(x86_64) most of the threads were in state Locked for some time and only some threads were executed statements. Despite of different symptoms it looks like there are some flaws in locking mechanism (related to SPs/triggers?) which descrease concurency on such workloads Description above was added to note that issue exists and further investiagation is required. How to repeat: Will be added later