У меня есть запрос, который запускается примерно через 20 секунд на сервере MySQL 5.1, но занимает почти 15 минут на сервере MariaDB 5.5. Обычные подозреваемые, такие как key_buffer_size и tmp_table_size и max_heap_table_size, равны (128M). Большинство настроек равны, насколько я могу видеть (query_cache и т.д.)
Запрос:
SELECT products.id,
concat(publications.company_name,' [',publications.quote,'] ', products.name) as n,
products.impressions,
products.contacts,
is_channel,
sl.i,
count(*)
FROM products
LEFT JOIN publications ON products.publications_id = publications.id
LEFT OUTER JOIN (
SELECT adspace.id AS i,
slots.products_id FROM adspace
LEFT JOIN slots ON adspace.slots_id = slots.id
AND adspace.end > '2016-01-25 10:28:49'
WHERE adspace.active = 1) AS sl
ON sl.products_id = products.id
WHERE 1 = 1
AND publications.active=1
GROUP BY products.id
ORDER BY n ASC;
Единственное различие заключается в объяснении fase:
Старый сервер (MySQL 5.1)
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| 1 | PRIMARY | products | ALL | NULL | NULL | NULL | NULL | 6568 | Using temporary; Using filesort |
| 1 | PRIMARY | publications | eq_ref | PRIMARY | PRIMARY | 4 | db.products.publications_id | 1 | Using where |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 94478 | |
| 2 | DERIVED | adspace | ALL | NULL | NULL | NULL | NULL | 101454 | Using where |
| 2 | DERIVED | slots | eq_ref | PRIMARY | PRIMARY | 4 | db.adspace.slots_id | 1 | |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
Новый сервер (MariaDB 5.5)
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| 1 | SIMPLE | products | ALL | test_idx | NULL | NULL | NULL | 6557 | Using temporary; Using filesort |
| 1 | SIMPLE | publications | eq_ref | PRIMARY | PRIMARY | 4 | db.products.publications_id | 1 | Using where |
| 1 | SIMPLE | adspace | ALL | NULL | NULL | NULL | NULL | 100938 | Using where |
| 1 | SIMPLE | slots | eq_ref | PRIMARY | PRIMARY | 4 | db.adspace.slots_id | 1 | Using where |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
Индекс был добавлен в таблицу продуктов на новом сервере, чтобы ускорить процесс, но безрезультатно.
Переменные двигателя:
Старый сервер:
mysql> show variables like '%engine%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| engine_condition_pushdown | ON |
| storage_engine | MyISAM |
+---------------------------+--------+
mysql> show variables like '%buffer_pool%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
Новый сервер:
MariaDB [db]> show variables like '%engine%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | InnoDB |
| engine_condition_pushdown | OFF |
| storage_engine | InnoDB |
+---------------------------+--------+
MariaDB [db]> show variables like '%buffer_pool%';
+---------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------+-----------+
| innodb_blocking_buffer_pool_restore | OFF |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_populate | OFF |
| innodb_buffer_pool_restore_at_startup | 0 |
| innodb_buffer_pool_shm_checksum | ON |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 134217728 |
+---------------------------------------+-----------+
Все таблицы, используемые в запросе, - MyISAM (как старый, так и новый сервер)
Профилирование показало, что старый запрос тратит около 16 секунд на "копирование в таблицу tmp" и новый сервер около 800 секунд в этой фазе.
У нового сервера все диски SSD для хранения и старые серверы имеют нормальные диски.
Изменить. У меня также есть сервер MySQL 5.5, и запрос занимает всего около 10 секунд. Также со всеми теми же настройками, насколько я могу видеть.
Я попытался обобщить его в таблице:
Location: Customer Own Customer
MySQL Type: MySQL MySQL MariaDB
Mysql Version: 5.1.56-community-log 5.5.39-1-log (Debian) 5.5.44-MariaDB-log
HDD: Normal Normal SSD
Type: Virtual Real Virtual
Query time: ~15s ~10s ~15min
DB engine: MyISAM InnoDB InnoDB
Table Engine: MyISAM MyISAM MyISAM
Я не хочу переписывать запрос (хотя он может использовать некоторую работу), но я хочу найти разницу между двумя машинами, моя догадка - это настройка, которая не идеальна в MariaDB, но я не могу найти он.
