anquegi anquegi - 6 months ago 64
MySQL Question

Different performance mysql and mariadb

I'm migrating and database from mysql to mariadb. the ruby on rails app is doing the following query using cancan so I cannot change the query:

SELECT DISTINCT COUNT(DISTINCT `buildings`.`id`)
FROM `buildings`
INNER JOIN `structure_subtypes`
ON `structure_subtypes`.`id` = `buildings`.`structure_subtype_id`
WHERE `buildings`.`id` IN (33553, 33554, 33555, 33556, 33557,
33558, 33559, 33560, 33561, 33562, 33563, 33564, 33565,
33566, 33567, 33568, 33569, 33570, 33571, 33572, 33573,
33574, 33575, 33576,+ ,..., 439515, 439521, 439600, 439602,
439604, 440016, 440017, 440019, 440275, 440315, 440379,
440403, 440405, 412987, 439889, 439969, 439980, 440216,
440218, 440341, 440485, 440486, 440488, 440491, 440494,
440501, 441458, 441498, 441519, 441536, 443387, 443389,
443464, 433752, 440109, 440110, 440464, 443938, 440513,
440514, 443391, 443394, 443353, 443364, 443401, 443486,
175036, 175037
)
AND `buildings`.`client_id` IN (175, 47 , 162, 152, 170,
104, 90, 127, 101, 51, 163, 81, 164, 165, 166, 172, 137,
174, 106, 108, 161, 158, 169, 97, 123, 136, 102, 157,
167, 135, 105, 171, 180, 120, 119, 118, 121, 110, 59,
57, 178, 140, 138, 176, 141, 168, 126, 96, 117, 103, 133,
173, 131, 179, 80, 100, 95, 116, 142, 147, 159, 160, 154,
115, 153, 156, 91, 125, 144, 150, 93, 155, 149, 151, 146
)
AND `structure_subtypes`.`structure_type_id` IN (11, 12,
13, 14, 15
)
AND (buildings.created_at >= '2016-12-31 23:00:00')
AND (buildings.created_at <= '2017-12-31 22:59:59');


The problem seems to be so many items in the IN section

MySQL "IN" operator performance on (large?) number of values

but the problem is de difference in performance between MYSQL and mariadb

for MYSQL it takes less than 10 seconds

$ mysql -h 127.0.0.1 -u root -pPASSWORD database

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [database]> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

MySQL [database]> Bye

$ mysql -h 127.0.0.1 -u root -pPASSWORD database < select_query.sql

RESULTS

ouput explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE buildings range PRIMARY,fk__buildings_client_id,index_buildings_on_workflow_state,fk__buildings_structure_subtype_id,index_buildings_on_workflow_state_and_created_at,index_buildings_on_client_id_and_created_at
index_buildings_on_workflow_state_and_created_at 258 NULL 1001 Using index condition; Using where; Using temporary; Using filesort
1 SIMPLE structure_subtypes eq_ref PRIMARY,index_structure_subtypes_on_structure_type_code,fk__structure_subtypes_structure_type_id PRIMARY 4 antifraud.buildings.structure_subtype_id 1 Using where; Distinct


output describe table

MySQL [antifraud]> describe buildings;
+--------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| client_id | int(11) | YES | MUL | NULL | |
| observations | varchar(2000) | YES | | NULL | |
| created_at | datetime | NO | MUL | NULL | |
| updated_at | datetime | NO | | NULL | |
| workflow_state | varchar(255) | YES | MUL | NULL | |
| structure_subtype_id | int(11) | YES | MUL | NULL | |
| svs | tinyint(1) | YES | | NULL | |
| parent_id | int(11) | YES | MUL | NULL | |
| reference | varchar(255) | YES | | NULL | |
| soc_notify | tinyint(1) | YES | | NULL | |
| origin | int(11) | YES | | NULL | |
| category | int(11) | YES | | NULL | |
| ip_filtering | int(11) | YES | | NULL | |
| priority | int(11) | YES | | NULL | |
| creator_id | int(11) | YES | MUL | NULL | |
| external_id | varchar(255) | YES | | NULL | |
| duration_time | float | YES | | NULL | |
| reopening_at | datetime | YES | MUL | NULL | |
| closed_at | datetime | YES | | NULL | |
| cbs_detection_id_legacy | varchar(255) | YES | | NULL | |
| cbs_callback_legacy | varchar(255) | YES | | NULL | |
| load_percentage | float | YES | | NULL | |
| items_in_special_domains | tinyint(1) | NO | | 0 | |
+--------------------------+---------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)


..

MySQL [antifraud]> show index from buildings;
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buildings | 0 | PRIMARY | 1 | id | A | 72567 | NULL | NULL | | BTREE | | |
| buildings | 1 | fk__buildings_client_id | 1 | client_id | A | 82 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state | 1 | workflow_state | A | 8 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_structure_subtype_id | 1 | structure_subtype_id | A | 78 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_parent_id | 1 | parent_id | A | 4535 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_creator_id | 1 | creator_id | A | 168 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state_and_created_at | 1 | workflow_state | A | 8 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state_and_created_at | 2 | created_at | A | 72567 | NULL | NULL | | BTREE | | |
| buildings | 1 | index_buildings_on_created_at | 1 | created_at | A | 72567 | NULL | NULL | | BTREE | | |
| buildings | 1 | index_buildings_on_reopening_at | 1 | reopening_at | A | 806 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_client_id_and_created_at | 1 | client_id | A | 80 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_client_id_and_created_at | 2 | created_at | A | 72567 | NULL | NULL | | BTREE | | |
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
12 rows in set (0.00 sec)

MySQL [antifraud]> Bye


for MariaDB it takes 1 minute 10 seconds

$ mysql -h 127.0.0.1 -u root -pPASSWORD database



Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.26-MariaDB-1~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [database]> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [database]> Bye

$ mysql -h 127.0.0.1 -u root -pPASSWORD database < select_query.sql

ouput explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE buildings range PRIMARY,fk__buildings_client_id,index_buildings_on_workflow_state,fk__buildings_structure_subtype_id,index_buildings_on_workflow_state_and_created_at,index_buildings_on_client_id_and_created_at index_buildings_on_workflow_state_and_created_at 258 NULL 1001 Using index condition; Using where; Using temporary; Using filesort
1 SIMPLE structure_subtypes eq_ref PRIMARY,index_structure_subtypes_on_structure_type_code,fk__structure_subtypes_structure_type_id PRIMARY 4 antifraud.buildings.structure_subtype_id 1 Using where; Distinct


..

output describe table

MariaDB [antifraud]> describe buildings;
+--------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| client_id | int(11) | YES | MUL | NULL | |
| observations | varchar(2000) | YES | | NULL | |
| created_at | datetime | NO | MUL | NULL | |
| updated_at | datetime | NO | | NULL | |
| workflow_state | varchar(255) | YES | MUL | NULL | |
| structure_subtype_id | int(11) | YES | MUL | NULL | |
| svs | tinyint(1) | YES | | NULL | |
| parent_id | int(11) | YES | MUL | NULL | |
| reference | varchar(255) | YES | | NULL | |
| soc_notify | tinyint(1) | YES | | NULL | |
| origin | int(11) | YES | | NULL | |
| category | int(11) | YES | | NULL | |
| ip_filtering | int(11) | YES | | NULL | |
| priority | int(11) | YES | | NULL | |
| creator_id | int(11) | YES | MUL | NULL | |
| external_id | varchar(255) | YES | | NULL | |
| duration_time | float | YES | | NULL | |
| reopening_at | datetime | YES | MUL | NULL | |
| closed_at | datetime | YES | | NULL | |
| cbs_detection_id_legacy | varchar(255) | YES | | NULL | |
| cbs_callback_legacy | varchar(255) | YES | | NULL | |
| load_percentage | float | YES | | NULL | |
| items_in_special_domains | tinyint(1) | NO | | 0 | |
+--------------------------+---------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)

MariaDB [antifraud]> show index from buildings;
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buildings | 0 | PRIMARY | 1 | id | A | 71923 | NULL | NULL | | BTREE | | |
| buildings | 1 | fk__buildings_client_id | 1 | client_id | A | 82 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state | 1 | workflow_state | A | 8 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_structure_subtype_id | 1 | structure_subtype_id | A | 80 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_parent_id | 1 | parent_id | A | 4495 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_creator_id | 1 | creator_id | A | 170 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state_and_created_at | 1 | workflow_state | A | 8 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state_and_created_at | 2 | created_at | A | 71923 | NULL | NULL | | BTREE | | |
| buildings | 1 | index_buildings_on_created_at | 1 | created_at | A | 71923 | NULL | NULL | | BTREE | | |
| buildings | 1 | index_buildings_on_reopening_at | 1 | reopening_at | A | 826 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_client_id_and_created_at | 1 | client_id | A | 80 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_client_id_and_created_at | 2 | created_at | A | 71923 | NULL | NULL | | BTREE | | |
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
12 rows in set (0.00 sec)


I'm playing with the variables in show variables but I do not know how to proceed

Answer Source

Try from buildings use index (primary) inner join ...

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download