0

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)

enter image description here

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.

7
  • can include the EXPLAIN SELECT ... portion of queries from test and prod? Which mariadb version(s) (are test and prod the same?)? – danblack Jun 8 at 6:24
  • @danblack requested info added to post. Thanks. – Benjam Jun 8 at 15:34
  • How much RAM on production? – Rick James Jun 8 at 16:20
  • Are there any other columns in the table? – Rick James Jun 8 at 16:38
  • Is it InnoDB? or MyISAM? (Please provide SHOW CREATE TABLE service_environment_dependency_index) – Rick James Jun 8 at 16:42
0

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.

3
  • I just confirmed that both tables are InnoDB. Your third note seems most likely, just because test is much less active than prod. Is there something we can do to increase performance on prod? – Benjam Jun 10 at 19:12
  • @Benjam - Perhaps. The usual problem is a big query that could be made more efficient. Bullet 4 - the slowlog. More: mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog – Rick James Jun 10 at 20:28
  • I just looked at the memory for both, and prod has ~256G, and test has ~8G, so that's not it. – Benjam Jun 11 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.