Имеется кластер MariaDB:
- master = 10.3.22 on Ubuntu 18.04
- slave = 10.3.14 on Ubuntu 16.04
Физически это две виртуалки на одном сервере.
Ресурсы процессора и объём ОЗУ одинаковые, загружены минимально.
Окружение тестовое, никакой нагрузки на базу нет.
Имеется таблица:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
MariaDB [test_sql]> show create table mdl_course_format_options\G
*************************** 1. row ***************************
Table: mdl_course_format_options
Create Table: CREATE TABLE `mdl_course_format_options` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`courseid` bigint(10) NOT NULL,
`format` varchar(21) NOT NULL DEFAULT '',
`sectionid` bigint(10) NOT NULL DEFAULT 0,
`name` varchar(100) NOT NULL DEFAULT '',
`value` longtext DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_courformopti_couforsec_uix` (`courseid`,`format`,`sectionid`,`name`),
KEY `mdl_courformopti_cou_ix` (`courseid`)
) ENGINE=InnoDB AUTO_INCREMENT=603368 DEFAULT CHARSET=utf8;
Количество записей и счётчик автоинкремента на мастере и слейве одинаковые, репликация работает нормально.
Имеется запрос:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT DISTINCT c.id, mpowned.name level, c.shortname, c.fullname, c.idnumber, c.timecreated, cat.name AS category,
CONCAT('{',GROUP_CONCAT(DISTINCT CONCAT('"', fo.name , '":"', fo.value, '"')),'}') as options
FROM mdl_course c
JOIN mdl_course_format_options mptreeco ON mptreeco.courseid = c.id and mptreeco.name='mp_ownerid'
JOIN mdl_mp mpowned ON mpowned.id=mptreeco.value
JOIN mdl_course_format_options catco ON catco.courseid = c.id and catco.name='global_categories'
JOIN mdl_global_categories cat ON cat.id=catco.value
JOIN mdl_course_format_options fo ON fo.courseid=c.id
WHERE c.visible=1
and mptreeco.value IN ('18','20','21','22','23','24','25','40','41','42','43','44','45','46','47','48','49','50','51','52','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69')
GROUP BY c.id, category, c.shortname, c.fullname, c.idnumber ORDER BY c.shortname ASC LIMIT 0, 10;
на мастере результат считается в несколько раз дольше, чем на слейве.
Чем длиннее список в "in (...)" - тем больше разница.
Например, на слейве 2 секунды, на мастере 4. На слейве 2 минуты, на мастере 7.
Explain показывает разные планы оптимизации.
- На мастере:
1.
2.
3.
4.
5.
6.
7.
8.
9.
+------+-------------+----------+--------+--------------------------------------------------------+--------------------------------+---------+-------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------+--------------------------------------------------------+--------------------------------+---------+-------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 23935 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | mptreeco | ref | mdl_courformopti_couforsec_uix,mdl_courformopti_cou_ix | mdl_courformopti_couforsec_uix | 8 | test_sql.c.id | 8 | Using index condition; Using where |
| 1 | SIMPLE | mpowned | eq_ref | PRIMARY | PRIMARY | 8 | test_sql.mptreeco.value | 1 | Using where |
| 1 | SIMPLE | catco | ref | mdl_courformopti_couforsec_uix,mdl_courformopti_cou_ix | mdl_courformopti_couforsec_uix | 8 | test_sql.c.id | 8 | Using index condition; Using where |
| 1 | SIMPLE | cat | eq_ref | PRIMARY | PRIMARY | 8 | test_sql.catco.value | 1 | Using where |
| 1 | SIMPLE | fo | ref | mdl_courformopti_couforsec_uix,mdl_courformopti_cou_ix | mdl_courformopti_couforsec_uix | 8 | test_sql.c.id | 8 | |
+------+-------------+----------+--------+--------------------------------------------------------+--------------------------------+---------+-------------------------+-------+----------------------------------------------+
- На слейве:
1.
2.
3.
4.
5.
6.
7.
8.
9.
+------+-------------+----------+--------+--------------------------------------------------------+-------------------------+---------+-------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------+--------------------------------------------------------+-------------------------+---------+-------------------------+-------+---------------------------------+
| 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 24911 | Using temporary; Using filesort |
| 1 | SIMPLE | mptreeco | ref | mdl_courformopti_couforsec_uix,mdl_courformopti_cou_ix | mdl_courformopti_cou_ix | 8 | test_sql.c.id | 8 | Using where |
| 1 | SIMPLE | mpowned | eq_ref | PRIMARY | PRIMARY | 8 | test_sql.mptreeco.value | 1 | Using where |
| 1 | SIMPLE | catco | ref | mdl_courformopti_couforsec_uix,mdl_courformopti_cou_ix | mdl_courformopti_cou_ix | 8 | test_sql.c.id | 8 | Using where |
| 1 | SIMPLE | cat | eq_ref | PRIMARY | PRIMARY | 8 | test_sql.catco.value | 1 | Using where |
| 1 | SIMPLE | fo | ref | mdl_courformopti_couforsec_uix,mdl_courformopti_cou_ix | mdl_courformopti_cou_ix | 8 | test_sql.c.id | 8 | |
+------+-------------+----------+--------+--------------------------------------------------------+-------------------------+---------+-------------------------+-------+---------------------------------+
Попробовал на мастере использовать "FORCE INDEX FOR JOIN(mdl_courformopti_cou_ix)" во всех трёх JOIN'ах по очереди - не помогло.
Кроме того, неясно, почему на slave отсутствует "using where".
Вопросы:
1) почему планы оптимизации на мастере и слейве отличаются?
2) как повысить скорость мастера до скорости слейва?
