MySQL Forums :: Performance :: Why does MySQL's performance decrease when queries are executed in parallel?


Advanced Search

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


Subject Views Written By Posted
Why does MySQL's performance decrease when queries are executed in parallel? 2864 dmitry kotelnikov 06/28/2012 04:49AM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 1293 Aftab Khan 06/28/2012 09:30AM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 933 dmitry kotelnikov 06/28/2012 12:23PM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 1089 Aftab Khan 06/28/2012 02:27PM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 917 Rick James 06/29/2012 10:07AM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 983 dmitry kotelnikov 06/29/2012 10:27AM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 912 dmitry kotelnikov 06/29/2012 10:53AM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 725 Rick James 06/30/2012 09:00AM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 759 dmitry kotelnikov 07/04/2012 03:29AM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 915 Rick James 07/05/2012 06:30AM
Re: Why does MySQL's performance decrease when queries are executed in parallel? 969 dmitry kotelnikov 07/05/2012 10:56AM


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.