Query taking much longer on production server than test server

Question

We have a couple servers, one test server, and another production server.

The table schemas, indexes, and data are identical on both.

Here is the table schema:

create table service_environment_dependency_index (
    service_id int unsigned default 0 not null,
    environment_id smallint unsigned default 0 not null,
    depends_on_service_id int unsigned default 0 not null,
    depends_on_environment_id smallint unsigned default 0 not null,
    primary key (service_id, environment_id, depends_on_service_id, depends_on_environment_id)
) charset = latin1;

create index depends_on_service_fields
    on service_environment_dependency_index(depends_on_service_id, depends_on_environment_id);

create index service_fields
    on service_environment_dependency_index(service_id, environment_id);

And here is the query in question:

INSERT IGNORE INTO service_environment_dependency_index (
    service_id, 
    environment_id, 
    depends_on_service_id,
    depends_on_environment_id
) (
    SELECT one.service_id
        , one.environment_id
        , two.depends_on_service_id
        , two.depends_on_environment_id
    FROM service_environment_dependency_index one
        INNER JOIN service_environment_dependency_index two 
            ON one.depends_on_service_id = two.service_id 
                AND one.depends_on_environment_id = two.environment_id
);

I know that query should take a bit, and it does on the test server, with some queries (accessing 750k rows) taking ~4 min.

The issue is that on the production server, the same query, with the same table schema, and same data, takes ~2 hours.

What possible causes could there be for an identical query taking 30x as long?

I ran SHOW VARIABLES; on both servers, and they both are pretty close to identical with the production server having some larger pool values: (Here are some of the different variables ** as well as some possibly related other variable values)

Both servers are running 10.4.8-MariaDB-log. Both servers give the following EXPLAIN for the query posted:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	two	index	PRIMARY,ids	PRIMARY	12	NULL	1056208	Using index; Using temporary
1	SIMPLE	one	ref	depends_on	depends_on	6	two.service_id,two.environment_id	8	Using index

Both tables are InnoDB.

Answer 1

Let me simplify the question so that my head won't spin like an owl's while trying to analyze the problem:

CREATE TABLE tbl (
    s ...,
    dep ...,
    PRIMARY KEY(s, dep)
    INDEX(dep)
    INDEX(s)
    ) ENGINE = InnoDB;

INSERT IGNORE INTO tbl
    SELECT one.s, two.dep
        FROM tbl AS one
        JOIN tbl AS two  ON one.dep = two.s;

EXPLAIN shows it is starting with table two, then using INDEX(dep) to reach into one.

Goals:

1. faster on production

2. It might be better to start with one so that the results of the SELECT would be in PRIMARY KEY in order to make the INSERT line up better with the target table.

3. INDEX(s) is virtually useless since it matches the start of the PK.

I think that simply dropping INDEX (s) (aka, your service_environment_dependency_index) would address all three goals.

Notes:

• If the table is ENGINE=MyISAM, I will need to rethink the analysis. This is because indexes, especially the PK, are handled differently between the engines.

• When a secondary index is used to look up a row, an extra lookup is required -- first in the secondary index's BTree, then in the data's BTree (based, in this case, on the 6-byte fabricated key). However, in this example, INDEX(dep) is "covering" (at least in InnoDB) since it implicitly includes s. The presence of Using index confirms this. That is, the extra lookup does not occur; all the work is done in the index.

• If the Production system is very busy, blocks in the buffer_pool could be bumped out by other activity, thereby interfering with the speed of a query. Perhaps on your 'test' server nothing else was going on, hence nothing being bumped out of cache.

• Turn on the slowlog - it should help you find that (and other) queries that are hurting performance.