MySQL Forums
Forum List  »  Performance

Re: Why does MySQL's performance decrease when queries are executed in parallel?
Posted by: dmitry kotelnikov
Date: June 29, 2012 10:53AM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.