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 200. Having two processes instead of expected increase up to 300 - 400 queries per second I actually have a drop down to 150. For 10 processes I have only 70 queries per seconds. The Perl code I'm using for testing is shown below:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Parallel::Benchmark;
use SQL::Abstract;
use SQL