**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