Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

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

Tags:

performance

mysql

perl

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::Abstract::Plugin::InsertMulti;

my $children_dbh;

foreach my $second_table_row_count (0, 1, 1000) {
    print '#' x 80, "\nsecond_table_row_count = $second_table_row_count\n";
    create_and_fill_tables(1000, $second_table_row_count);
    foreach my $concurrency (1, 2, 3, 4, 6, 8, 10, 20) {
        my $bm = Parallel::Benchmark->new(
            'benchmark' => sub {
                _run_sql();
                return 1;
            },
            'concurrency' => $concurrency,
            'time' => 3,
        );
        my $result = $bm->run();
    }
}

sub create_and_fill_tables {
    my ($first_table_row_count, $second_table_row_count) = @_;
    my $dbh = dbi_connect();
    {
        $dbh->do(q{DROP TABLE IF EXISTS first});
        $dbh->do(q{
            CREATE TABLE first (
                num int(10) NOT NULL,
                UNIQUE KEY key_num (num)
            ) ENGINE=InnoDB
        });
        if ($first_table_row_count) {
            my ($stmt, @bind) = SQL::Abstract->new()->insert_multi(
                'first',
                ['num'],
                [map {[$_]} 1 .. $first_table_row_count],
            );
            $dbh->do($stmt, undef, @bind);
        }
    }
    {
        $dbh->do(q{DROP TABLE IF EXISTS second});
        $dbh->do(q{
            CREATE TABLE second (
                num int(10) NOT NULL,
                num2 int(10) NOT NULL,
                UNIQUE KEY key_num (num, num2)
            ) ENGINE=InnoDB
        });
        if ($second_table_row_count) {
            my ($stmt, @bind) = SQL::Abstract->new()->insert_multi(
                'second',
                ['num'],
                [map {[$_]} 1 .. $second_table_row_count],
            );
            $dbh->do($stmt, undef, @bind);
        }
    }
}

sub _run_sql {
    $children_dbh ||= dbi_connect();
    $children_dbh->selectall_arrayref(q{
        SELECT first.num
        FROM first
        LEFT JOIN second AS second_1 ON second_1.num = -1
        LEFT JOIN second AS second_2 ON second_2.num = -2
        LEFT JOIN second AS second_3 ON second_3.num = -3
        LEFT JOIN second AS second_4 ON second_4.num = -4
        LEFT JOIN second AS second_5 ON second_5.num = -5
        LEFT JOIN second AS second_6 ON second_6.num = -6
        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
    });
}

sub dbi_connect {
    return DBI->connect(
        'dbi:mysql:'
            . 'database=tmp'
            . ';host=localhost'
            . ';port=3306',
        'root',
        '',
    );
}

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

Testing results, cpu and disk usage measurements are here:

* 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

System usage after for last 60 minutes of running query in 6 processes:
    $ iostat -cdkx 60

    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              74.82    0.00    0.08    0.00    0.08   25.02

    Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
    sda1              0.00     0.00    0.00    0.12     0.00     0.80    13.71     0.00    1.43   1.43   0.02
    sdf10             0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdf4              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   30.00  15.00   0.05
    sdm               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf8              0.00     0.00    0.00    0.37     0.00     1.24     6.77     0.00    5.00   3.18   0.12
    sdf6              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdf9              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00    0.00   0.00   0.00
    sdf               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf3              0.00     0.00    0.00    0.08     0.00     1.33    32.00     0.00    4.00   4.00   0.03
    sdf2              0.00     0.00    0.00    0.17     0.00     1.37    16.50     0.00    3.00   3.00   0.05
    sdf15             0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf14             0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf1              0.00     0.00    0.00    0.05     0.00     0.40    16.00     0.00    0.00   0.00   0.00
    sdf13             0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdf5              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   50.00  25.00   0.08
    sdm2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdm1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf12             0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdf11             0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdf7              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    md0               0.00     0.00    0.00    0.97     0.00    13.95    28.86     0.00    0.00   0.00   0.00

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

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

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

System usage after for last 60 minutes of running query in 6 processes:
    $ iostat -cdkx 60

    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              74.40    0.00    0.53    0.00    0.06   25.01

    Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
    sda1              0.00     0.00    0.00    0.02     0.00     0.13    16.00     0.00    0.00   0.00   0.00
    sdf10             0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdf4              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdm               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf8              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdf6              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00    0.00   0.00   0.00
    sdf9              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdf               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf3              0.00     0.00    0.00    0.13     0.00     2.67    40.00     0.00    3.75   2.50   0.03
    sdf2              0.00     0.00    0.00    0.23     0.00     2.72    23.29     0.00    2.14   1.43   0.03
    sdf15             0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf14             0.00     0.00    0.00    0.98     0.00     0.54     1.10     0.00    2.71   2.71   0.27
    sdf1              0.00     0.00    0.00    0.08     0.00     1.47    35.20     0.00    8.00   6.00   0.05
    sdf13             0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf5              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    sdm2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdm1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf12             0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    sdf11             0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00    0.00   0.00   0.00
    sdf7              0.00     0.00    0.00    0.03     0.00     1.07    64.00     0.00   10.00   5.00   0.02
    md0               0.00     0.00    0.00    1.70     0.00    15.92    18.74     0.00    0.00   0.00   0.00

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

And this server has lots of free memory. Example of top:
    top - 19:02:59 up  4:23,  4 users,  load average: 4.43, 3.03, 2.01
    Tasks: 218 total,   1 running, 217 sleeping,   0 stopped,   0 zombie
    Cpu(s): 72.8%us,  0.7%sy,  0.0%ni, 26.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.1%st
    Mem:  71701416k total, 22183980k used, 49517436k free,      284k buffers
    Swap:        0k total,        0k used,        0k free,  1282768k cached

      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     2506 mysql     20   0 51.7g  17g 5920 S  590 25.8 213:15.12 mysqld
     9348 topadver  20   0 72256  11m 1428 S    2  0.0   0:01.45 perl
     9349 topadver  20   0 72256  11m 1428 S    2  0.0   0:01.44 perl
     9350 topadver  20   0 72256  11m 1428 S    2  0.0   0:01.45 perl
     9351 topadver  20   0 72256  11m 1428 S    1  0.0   0:01.44 perl
     9352 topadver  20   0 72256  11m 1428 S    1  0.0   0:01.44 perl
     9353 topadver  20   0 72256  11m 1428 S    1  0.0   0:01.44 perl
     9346 topadver  20   0 19340 1504 1064 R    0  0.0   0:01.89 top

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

like image 520
awayka Avatar asked Jun 22 '12 16:06

awayka

People also ask

Does MySQL execute queries in parallel?

MySQL is not Oracle (despite being owned by Oracle). It does not support parallel queries. You can, however, get them using shard-query which is a third-party database engine that supports them.

How many concurrent queries can MySQL handle?

Problem. By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance.

1 Answers

Well written question, that shows some research.

Out of curiosity, I tried MySQL 5.6 to see what the tooling there has to say about these queries.

First, note that the queries are different:

  • changing the value from "1" to "-1" for the existent / non existent key case is one thing
  • changing "second_1.num IS NOT NULL" to "second_1.num IS NULL" in the WHERE clause is another.

Using EXPLAIN gives different plans:

EXPLAIN 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
;
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  first   index   NULL    key_num 4       NULL    1000    Using index
1       SIMPLE  second_1        ref     key_num key_num 4       const   1       Using where; Not exists; Using index
1       SIMPLE  second_2        ref     key_num key_num 4       const   1       Using where; Not exists; Using index
1       SIMPLE  second_3        ref     key_num key_num 4       const   1       Using where; Not exists; Using index
1       SIMPLE  second_4        ref     key_num key_num 4       const   1       Using where; Not exists; Using index
1       SIMPLE  second_5        ref     key_num key_num 4       const   1       Using where; Not exists; Using index
1       SIMPLE  second_6        ref     key_num key_num 4       const   1       Using where; Not exists; Using index

as opposed to

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 index
1       SIMPLE  second_2        ref     key_num key_num 4       const   1       Using index
1       SIMPLE  second_3        ref     key_num key_num 4       const   1       Using index
1       SIMPLE  second_4        ref     key_num key_num 4       const   1       Using index
1       SIMPLE  second_5        ref     key_num key_num 4       const   1       Using index
1       SIMPLE  second_6        ref     key_num key_num 4       const   1       Using index
1       SIMPLE  first   index   NULL    key_num 4       NULL    1000    Using index; Using join buffer (Block Nested Loop)

Using the JSON format, we have:

EXPLAIN FORMAT=JSON 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
;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "first",
          "access_type": "index",
          "key": "key_num",
          "key_length": "4",
          "rows": 1000,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "second_1",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "not_exists": true,
          "using_index": true,
          "attached_condition": "<if>(found_match(second_1), isnull(`test`.`second_1`.`num`), true)"
        }
      },
      {
        "table": {
          "table_name": "second_2",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "not_exists": true,
          "using_index": true,
          "attached_condition": "<if>(found_match(second_2), isnull(`test`.`second_2`.`num`), true)"
        }
      },
      {
        "table": {
          "table_name": "second_3",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "not_exists": true,
          "using_index": true,
          "attached_condition": "<if>(found_match(second_3), isnull(`test`.`second_3`.`num`), true)"
        }
      },
      {
        "table": {
          "table_name": "second_4",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "not_exists": true,
          "using_index": true,
          "attached_condition": "<if>(found_match(second_4), isnull(`test`.`second_4`.`num`), true)"
        }
      },
      {
        "table": {
          "table_name": "second_5",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "not_exists": true,
          "using_index": true,
          "attached_condition": "<if>(found_match(second_5), isnull(`test`.`second_5`.`num`), true)"
        }
      },
      {
        "table": {
          "table_name": "second_6",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "not_exists": true,
          "using_index": true,
          "attached_condition": "<if>(found_match(second_6), isnull(`test`.`second_6`.`num`), true)"
        }
      }
    ]
  }
}

as opposed to

EXPLAIN FORMAT=JSON 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
;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "second_1",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "second_2",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "second_3",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "second_4",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "second_5",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "second_6",
          "access_type": "ref",
          "possible_keys": [
            "key_num"
          ],
          "key": "key_num",
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows": 1,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "first",
          "access_type": "index",
          "key": "key_num",
          "key_length": "4",
          "rows": 1000,
          "filtered": 100,
          "using_index": true,
          "using_join_buffer": "Block Nested Loop"
        }
      }
    ]
  }
}

Looking at the table io instrumented by the performance schema at runtime, we have:

truncate table performance_schema.objects_summary_global_by_type;
select * from performance_schema.objects_summary_global_by_type
where OBJECT_NAME in ("first", "second");
OBJECT_TYPE OBJECT_SCHEMA   OBJECT_NAME COUNT_STAR  SUM_TIMER_WAIT  MIN_TIMER_WAIT  AVG_TIMER_WAIT  MAX_TIMER_WAIT
TABLE   test    first   0   0   0   0   0
TABLE   test    second  0   0   0   0   0
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
;
(...)
select * from performance_schema.objects_summary_global_by_type
where OBJECT_NAME in ("first", "second");
OBJECT_TYPE OBJECT_SCHEMA   OBJECT_NAME COUNT_STAR  SUM_TIMER_WAIT  MIN_TIMER_WAIT  AVG_TIMER_WAIT  MAX_TIMER_WAIT
TABLE   test    first   1003    5705014442  1026171 5687889 87356557
TABLE   test    second  6012    271786533972    537266  45207298    1123939292

as opposed to:

select * from performance_schema.objects_summary_global_by_type
where OBJECT_NAME in ("first", "second");
OBJECT_TYPE OBJECT_SCHEMA   OBJECT_NAME COUNT_STAR  SUM_TIMER_WAIT  MIN_TIMER_WAIT  AVG_TIMER_WAIT  MAX_TIMER_WAIT
TABLE   test    first   1003    5211074603  969338  5195454 61066176
TABLE   test    second  24  458656783   510085  19110361    66229860

The query that scales does almost no table IO in table second. The query that does not scale does 6K table IO in table second, or 6 times the size of table first.

This is because the query plans are different, in turn because the queries are different (IS NOT NULL versus IS NULL).

I think that answers the performance related question.

Note that both queries returned 1000 rows in my tests, which may not be what you want. Before tuning a query to make it faster, make sure it works as expected.

like image 192
Marc Alff Avatar answered Oct 08 '22 17:10

Marc Alff
Sign in to Comment

Related questions

Good rating/reputation systems? [closed]
How to store 800 billion GPS markers in database [closed]
Socket.io for checking updates from database
How to mass insert or update in a single query (not a for loop of queries) using Laravel 4.2
How to migrate MySQL data directory in docker container?
How to do a incremental backup in Mysql [duplicate]
Configuration storage setup [file vs. database]
Finding k-nearest neighbors for a given vector?
Executing multiple native queries in one go
What ways are there to store information about an anonymous/guest user in a database?
Can you index subqueries?
IllegalArgumentException: Type cannot be null
Convert SQL Server tables to MySQL using ZF2 and Doctrine2
Cache/Re-Use a Subquery in MySQL
Is there any difference between DATE_SUB() and using arithmetic operators for datetime calculation?
What is the unit in returned by MySQL GLength method?
MySQL query with dependent subquery takes too long
MySQL InnoDB locks on joined rows
Algorithm using soundex() or metaphone() to create Mad Gab style phrases
MySQL. Creating an index for "OR" queries

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!