Re: Why does MySQL's performance decrease when queries are executed in parallel?
Aftab Khan, thank u for your answer. I made a mistake in second query it should looks like below. But it doesn't matter. I can't understand why first query produce performance decrease in concurrent.
mysql>
mysql> explain SELECT first.num
-> FROM first
-> LEFT JOIN second AS second_1 ON second_1.num = 1 # existent key
-> LEFT JOIN second AS second_2 ON second_2.num = 2 # existent key
-> LEFT JOIN second AS second_3 ON second_3.num = 3 # existent key
-> LEFT JOIN second AS second_4 ON second_4.num = 4 # existent key
-> LEFT JOIN second AS second_5 ON second_5.num = 5 # existent key
-> LEFT JOIN second AS second_6 ON second_6.num = 6 # existent key
-> WHERE second_1.num IS NOT NULL
-> AND second_2.num IS NOT NULL
-> AND second_3.num IS NOT NULL
-> AND second_4.num IS NOT NULL
-> AND second_5.num IS NOT NULL
-> AND second_6.num IS NOT NULL;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+--------------------------------+
| 1 | SIMPLE | second_1 | ref | key_num | key_num | 4 | const | 1 | Using where; Using index |
| 1 | SIMPLE | second_2 | ref | key_num | key_num | 4 | const | 1 | Using where; Using index |
| 1 | SIMPLE | second_3 | ref | key_num | key_num | 4 | const | 1 | Using where; Using index |
| 1 | SIMPLE | second_4 | ref | key_num | key_num | 4 | const | 1 | Using where; Using index |
| 1 | SIMPLE | second_5 | ref | key_num | key_num | 4 | const | 1 | Using where; Using index |
| 1 | SIMPLE | second_6 | ref | key_num | key_num | 4 | const | 1 | Using where; Using index |
| 1 | SIMPLE | first | index | NULL | key_num | 4 | NULL | 1311 | Using index; Using join buffer |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+--------------------------------+
7 rows in set (0.00 sec)
And results:
################################################################################
second_table_row_count = 6
2012-06-29T20:50:42 [INFO] starting benchmark: concurrency: 1, time: 3
2012-06-29T20:50:47 [INFO] done benchmark: score 1471, elapsed 3.011 sec = 488.494 / sec
2012-06-29T20:50:47 [INFO] starting benchmark: concurrency: 2, time: 3
2012-06-29T20:50:52 [INFO] done benchmark: score 2892, elapsed 3.014 sec = 959.506 / sec
2012-06-29T20:50:52 [INFO] starting benchmark: concurrency: 3, time: 3
2012-06-29T20:50:57 [INFO] done benchmark: score 4293, elapsed 3.010 sec = 1426.044 / sec
2012-06-29T20:50:57 [INFO] starting benchmark: concurrency: 4, time: 3
2012-06-29T20:51:02 [INFO] done benchmark: score 5469, elapsed 3.006 sec = 1819.310 / sec
2012-06-29T20:51:02 [INFO] starting benchmark: concurrency: 6, time: 3
2012-06-29T20:51:07 [INFO] done benchmark: score 7799, elapsed 2.842 sec = 2744.011 / sec
2012-06-29T20:51:07 [INFO] starting benchmark: concurrency: 8, time: 3
2012-06-29T20:51:12 [INFO] done benchmark: score 10078, elapsed 3.010 sec = 3347.932 / sec
2012-06-29T20:51:12 [INFO] starting benchmark: concurrency: 10, time: 3
2012-06-29T20:51:17 [INFO] done benchmark: score 10074, elapsed 3.012 sec = 3344.845 / sec
2012-06-29T20:51:17 [INFO] starting benchmark: concurrency: 20, time: 3
2012-06-29T20:51:23 [INFO] done benchmark: score 9455, elapsed 3.033 sec = 3117.034 / sec