MySQL Forums
Forum List  »  Performance

Why does MySQL's performance decrease when queries are executed in parallel?
Posted by: dmitry kotelnikov
Date: June 28, 2012 04:49AM

**The Question**: Why MySQL performance goes down for queries joining nearly empty tables when executed in parallel?

Below is more detailed explanation of the issue I'm facing. I have two tables in MySQL

CREATE TABLE first (
num int(10) NOT NULL,
UNIQUE KEY key_num (num)
) ENGINE=InnoDB

CREATE TABLE second (
num int(10) NOT NULL,
num2 int(10) NOT NULL,
UNIQUE KEY key_num (num, num2)
) ENGINE=InnoDB

The first one contains about a thousand records. The second one is empty or contains a very few records. It also contains double index which somehow relates to the issue: the problem goes away for single index. Now I'm trying to make a lot of identical queries to those tables in parallel. Each query looks like this:

SELECT first.num
FROM first
LEFT JOIN second AS second_1 ON second_1.num = -1 # non-existent key
LEFT JOIN second AS second_2 ON second_2.num = -2 # non-existent key
LEFT JOIN second AS second_3 ON second_3.num = -3 # non-existent key
LEFT JOIN second AS second_4 ON second_4.num = -4 # non-existent key
LEFT JOIN second AS second_5 ON second_5.num = -5 # non-existent key
LEFT JOIN second AS second_6 ON second_6.num = -6 # non-existent key
WHERE second_1.num IS NULL
AND second_2.num IS NULL
AND second_3.num IS NULL
AND second_4.num IS NULL
AND second_5.num IS NULL
AND second_6.num IS NULL

The issue I'm getting is that instead of having a nearly linear raise in performance on 8 core machine I actually have a drop. Namely having one process, the typical number of requests per second I have is about 160. Having two processes instead of expected increase up to 200-300 queries per second I actually have a drop down to 130. For 8 processes I have only 80 queries per seconds.

And for compare queries like this executed in concurrent with increasing performance:

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 NULL
AND second_3.num IS NULL
AND second_4.num IS NULL
AND second_5.num IS NULL
AND second_6.num IS NULL

################################################################################
Testing results:
################################################################################

* table `first` have 1000 rows
* table `second` have 6 rows: `[1,1],[2,2],..[6,6]`

For query:
SELECT first.num
FROM first
LEFT JOIN second AS second_1 ON second_1.num = -1 # non-existent key
LEFT JOIN second AS second_2 ON second_2.num = -2 # non-existent key
LEFT JOIN second AS second_3 ON second_3.num = -3 # non-existent key
LEFT JOIN second AS second_4 ON second_4.num = -4 # non-existent key
LEFT JOIN second AS second_5 ON second_5.num = -5 # non-existent key
LEFT JOIN second AS second_6 ON second_6.num = -6 # non-existent key
WHERE second_1.num IS NULL
AND second_2.num IS NULL
AND second_3.num IS NULL
AND second_4.num IS NULL
AND second_5.num IS NULL
AND second_6.num IS NULL

Results:
concurrency: 1, speed: 162.910 / sec
concurrency: 2, speed: 137.818 / sec
concurrency: 3, speed: 130.728 / sec
concurrency: 4, speed: 107.387 / sec
concurrency: 6, speed: 90.513 / sec
concurrency: 8, speed: 80.445 / sec
concurrency: 10, speed: 80.381 / sec
concurrency: 20, speed: 84.069 / sec

################################################################################

For query:
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 NULL
AND second_3.num IS NULL
AND second_4.num IS NULL
AND second_5.num IS NULL
AND second_6.num IS NULL

Results:
concurrency: 1, speed: 875.973 / sec
concurrency: 2, speed: 944.986 / sec
concurrency: 3, speed: 1256.072 / sec
concurrency: 4, speed: 1401.657 / sec
concurrency: 6, speed: 1354.351 / sec
concurrency: 8, speed: 1110.100 / sec
concurrency: 10, speed: 1145.251 / sec
concurrency: 20, speed: 1142.514 / sec

Does anyone have an idea why performance decreased for query with non-existent keys?

More information i posted here: http://stackoverflow.com/questions/11160522/why-does-mysqls-performance-decrease-when-queries-are-executed-in-parallel

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.