Почему этот запрос выводит из строя mysql?

Question

[psiklop]

Версия mysql = 8
Не то, чтобы я опытный в mysql, но для меня полтергейст.

Итак занимаюсь, чем умею на практике долблю в одиночку mysql, таблица довольно большая на 10 миллионов строк.
Делаю такой запрос:

SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE
time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR;

Срабатывает норм, в 5-10 секунд укладывается.

Далее добавляю это:

SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE
time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n <=4;

Тоже нормально.

А теперь просто трындец, пишу так и mysql виснет (не помогает systemctl restart mysql)
И не помогает reboot серверу (только с физической кнопки)
Пробовал на двух разных серверах.

SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE
time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n=4;

Видимо я что-то делаю не так и не понимаю, но логически мне кажется ничего особенного.

Comments

[Everything_is_bad]

А теперь просто трындец, пишу так и mysql виснет (не помогает systemctl restart mysql)
И не помогает reboot серверу (только с физической кнопки)

не верю

[psiklop]

Everything_is_bad, как доказать ?

[psiklop]

Everything_is_bad, зуб даю я специально таблицу скопировал на домашний сервер и результат точь в точь такой же

[Akina]

Планы всех запросов и DDL таблицы в студию. А также планы запросов, где подзапрос преобразован в CTE. И точно версию MySQL.

[psiklop]

Akina, я разве видео могу записать

[psiklop]

Akina, https://rutube.ru/video/17a27a19a2a599869073715846...

[Rsa97]

psiklop, А на кой нужно видео? Вы не в состоянии EXPLAIN запроса сделать и текст (не скриншот) результата скопировать? Или у вас SHOW CREATE TABLE не работает?

[psiklop]

Rsa97, explain не делал не разу. Команду таблицы могу прислать, могу хоть всю с данными.

table

CREATE TABLE `sms` (
`_id` int unsigned NOT NULL AUTO_INCREMENT,
`time` datetime NOT NULL,
`sid` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`id` varchar(34) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone` bigint DEFAULT NULL,
`status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`source` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`main` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`utm_source` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`utm_medium` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`utm_inside` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`alfa` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`short` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`price` decimal(8,5) NOT NULL,
`ip` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT '',
PRIMARY KEY (`_id`,`time`),
KEY `time` (`time`),
KEY `vid` (`id`),
KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=20866306 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(`time`)
(PARTITION d221 VALUES LESS THAN ('2022-03-01') ENGINE = InnoDB,
PARTITION d222 VALUES LESS THAN ('2022-06-01') ENGINE = InnoDB,
PARTITION d223 VALUES LESS THAN ('2022-09-01') ENGINE = InnoDB,
PARTITION d224 VALUES LESS THAN ('2023-01-01') ENGINE = InnoDB,
PARTITION d231 VALUES LESS THAN ('2023-03-01') ENGINE = InnoDB,
PARTITION d232 VALUES LESS THAN ('2023-06-01') ENGINE = InnoDB,
PARTITION d233 VALUES LESS THAN ('2023-09-01') ENGINE = InnoDB,
PARTITION d234 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
PARTITION d241 VALUES LESS THAN ('2024-03-01') ENGINE = InnoDB,
PARTITION d242 VALUES LESS THAN ('2024-06-01') ENGINE = InnoDB,
PARTITION d243 VALUES LESS THAN ('2024-09-01') ENGINE = InnoDB,
PARTITION d244 VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB,
PARTITION d251 VALUES LESS THAN ('2025-03-01') ENGINE = InnoDB,
PARTITION d252 VALUES LESS THAN ('2025-06-01') ENGINE = InnoDB,
PARTITION d253 VALUES LESS THAN ('2025-09-01') ENGINE = InnoDB,
PARTITION d254 VALUES LESS THAN ('2026-01-01') ENGINE = InnoDB,
PARTITION d261 VALUES LESS THAN ('2026-03-01') ENGINE = InnoDB,
PARTITION d262 VALUES LESS THAN ('2026-06-01') ENGINE = InnoDB,
PARTITION d263 VALUES LESS THAN ('2026-09-01') ENGINE = InnoDB,
PARTITION d264 VALUES LESS THAN ('2027-01-01') ENGINE = InnoDB,
PARTITION d271 VALUES LESS THAN ('2027-03-01') ENGINE = InnoDB,
PARTITION d272 VALUES LESS THAN ('2027-06-01') ENGINE = InnoDB,
PARTITION d273 VALUES LESS THAN ('2027-09-01') ENGINE = InnoDB,
PARTITION d274 VALUES LESS THAN ('2028-01-01') ENGINE = InnoDB,
PARTITION dmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;

[psiklop]

Rsa97,
mysql> EXPLAIN SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE
n=4;
+----+-------------+------------+----------------+------+---------------+-------------+---------+-------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+----------------+------+---------------+-------------+---------+-------+---------+----------+-----------------------------+
| 1 | PRIMARY | | NULL | ref | | | 8 | const | 10 | 100.00 | NULL |
| 2 | DERIVED | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7408789 | 50.00 | Using where; Using filesort |
+----+-------------+------------+----------------+------+---------------+-------------+---------+-------+---------+----------+-----------------------------+
2 rows in set, 2 warnings (0,00 sec)

[Rsa97]

psiklop, Ещё EXPLAIN двух других вариантов запросов, которые работают быстро.

[psiklop]

Rsa97, вот первый
mysql> EXPLAIN SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR;
+----+-------------+-------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7408789 | 50.00 | Using where; Using filesort |
+----+-------------+-------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 2 warnings (0,00 sec)

[psiklop]

и Второй
mysql> EXPLAIN SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE
n<=4;
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 3704394 | 33.33 | Using where |
| 2 | DERIVED | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7408789 | 50.00 | Using where; Using filesort |
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
2 rows in set, 2 warnings (0,00 sec)

[Антон Антон]

Этот запрос должен найти четвертую смс каждого номера? или что? Я не понимаю (

[Akina]

А где DDL таблицы?

И ещё - после запуска на выполнение "долгого" запроса как выглядят расход памяти и активность диска? Не начинается ли материализация подзапроса, особенно с утыканием в недостаток дискового пространства?

И как себя поведёт

WITH cte AS (
    SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n 
    FROM sms 
    WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR
    )
SELECT * 
FROM  cte 
WHERE n=4;

?

[Антон Антон]

В любом случае я бы сортировку для начала вынес из окна в соответствующий select

[Akina]

PS. В мой последний запрос добавьте /*+ MAX_EXECUTION_TIME(60000) */ - чтобы не ребутать при слишком долгом выполнении. Ну или другой предел времени (он в миллисекундах)

[Akina]

Антон Антон,

я бы сортировку для начала вынес из окна в соответствующий select

??? это как? покажите кодом, что вы имеете в виду.

[psiklop]

Антон Антон, типа этого 4 смс, это начало работы далее что-то изменится, но получился завал на начальном этапе.

[psiklop]

Akina, попробую запрос, про DDL мне надо сначала прогуглить. С дисками все в порядке, 200gb free там точно имеется.

[Антон Антон]

Кажется, не хватает индекса по (phone, time desc) но может быть он не такой умный и не сможет сообразить, что сначала надо пронумеровать и в процессе нумеровки отсеять ненужное. Еще вопрос в том, какая часть таблицы падает в time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR и есть ли там индекс по time

[Антон Антон]

Using where уже указывает на промах с индексами, а Using filesort - это вообще жопа (свопается на диск в процессе сортировки ибо в память всё не влезает).

[psiklop]

Антон Антон, я человек простой, куда mysql свопает ему виднее, не я его писал, я им пользуюсь, я делал индексы по time и по phone, таблица даже разделена на партиции по кварталу. Тут вопрос почему n<=4 работает, а n=4 не работает.

[Антон Антон]

psiklop,

Тут вопрос почему n<=4 работает, а n=4 не работает.

потому что оптимизатор еще чуть чуть сходит с ума, то перышко, которое ломает спину верблюду. Но explain на всех запросах говорит, что индекс не используется. и я говорю про один составной индекс а не про два отдельных ибо окно такое, что ему нужно два и при этом отрезание по time. очень плохое условие.

[Ипатьев]

я бы по привычке попросил автора вывести SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
зуб даю на отсечение, что там 134217728, как минимум на локалке.
Ну и в целом посмотреть SHOW ENGINE INNODB STATUS\G

[psiklop]

Антон Антон, я не знал, что должен добавлять индексы под каждый запрос, за 4 года написано тысячи запросов и прекрасно жили до этого. Мне как программисту на php не понятно это, я так понимаю всю основную работу делает вложенный select и он завершается нормально (когда отдельно). Почему я должен делать индексы под внешний select который и должен всего лишь выбрать n=4 из уже готовых данных?

[psiklop]

Антон Антон, у меня приличные серверы там и без индексов должно мало-мальски быть результату, не петабайт данных поди, но индексы есть.

[Ипатьев]

psiklop, не на "каждый", а на каждый тяжёлый. Если все данные берутся из индекса, а не по индексу мгновенно достаются из каждой записи, то запрос очевидно работает гораздо быстрее.
Но в любом случае самим индексам нужно где-то храниться. А "программисты на пхп", которые "не писали mysql" обычно об этом не задумываются.

[psiklop]

Ипатьев, есть и потяжелее, 5 секунд это не страшно, я повторюсь в сотый раз индексы есть и есть партиции. Да SHOW VARIABLES LIKE 'innodb_buffer_pool_size' равен 134217728

[Ипатьев]

кстати, после эксплейна надо написать SHOW WARNINGS;
там будет полезная информация, в частности запрос, как его переписал оптимизатор.

[Ипатьев]

'innodb_buffer_pool_size' равен 134217728

Ну вот и ответ.
Все ваши "мощные серверы" просто работают калориферами. Их память- сколько бы её не было - не используется.
Сделайте хотя бы пару гигов в пределах возможного, и сразу увидите разницу.

[psiklop]

Ипатьев, спасибо разницу наверняка увижу. Но только врядли в случае с n<=4 и n=4

[psiklop]

Ипатьев, прямо калориферами, а теперь будут Эпики ?

[Ипатьев]

Сарказм - это свежо и остроумно. Но для человека, который за много лет использования mysql никогда не слышал слово explain, звучит довольно жалко.
Поэтому давайте вы его оставите для своих друзей-приятелей, а здесь сделаете то, что вам сказали
показать результат Show warnings после explain для всех запросов
показать результат SHOW ENGINE INNODB STATUS\G
увеличить innodb_buffer_pool_size до максимально возможных (если это выделенный сервер, то 80% от физической памяти, если совместно используемый - то хотя бы временно дать побольше памяти, скажем, половину) и попробовать свои запросы.

[Ипатьев]

Akina, под DDL спойлером в этом комментарии

[Everything_is_bad]

Ипатьев,

Но для человека, который за много лет использования mysql никогда не слышал слово explain, звучит довольно жалко.

да он даже про индексы толком не понимает, это какой-то сюр, даже для "php-программиста"

[psiklop]

Everything_is_bad, а с вами никто пись**ми не меряется, меня все устраивает в жизни. Ты вообще ничего кроме "не верю" не написал. Просто люди живут и не дергаются, надо будет выучим как сейчас.

[psiklop]

Ипатьев, поразительно innodb_buffer_pool_size помогло

[psiklop]

Получается если ваша mysql выросла до 3GB стандартные настройки уже не работают. И это не индексы.

[psiklop]

Получилось, что запрос с n<=4 выполняется 8 секунд, с n=4 целых 14 секунд требует больше ресурсов. А mysql не работает restart и весь сервер потом не ребутится это очень плохо. Ну и конфиг конечно подвел прочитал целую статью про оптимизацию mysql, но оттуда взять нечего, либо в mysql 8 это устарело и уже стоит по дефолту либо innodb это не нужно, кроме этого "скотского" innodb_buffer_pool_size

[Ипатьев]

psiklop, это индексы. Просто, как я писал выше, их тоже нужно где-то хранить.

[psiklop]

Ипатьев, я поэтому и не хочу их делать, раньше у меня две трети полей было заиндексировано. Но таблицы росли и росли, я решил оставить лишь время и айди юзера, потестил если всегда where вначале делать выборку по времени результат всегда приемлемый по скорости.

[Ипатьев]

psiklop, ну делать индексы просто от балды конечно не надо. Но и совсем без них тоже нельзя. Надо делать ровно те, которые нужны. Надо понимать, что дело не тлько в скорости выполнения запроса, но и в том, сколько ресурсов компьютера он отъедает. Если запрос выполняется 5 секунд, это значит он 5 секунд грузит комп.
Поэтому составной индекс, в котором уже лежат все нужные для запроса данные, и который позволяет запросу выполняться быстрее, полезен не только этим, а и тем что экономит ресурсы сервера.
Тем более что такой составной индекс полностью заменяет отдельный индекс по своей первой колонке.
Поэтому если есть например индекс по (phone, time) то отдельный индекс по phone не нужен. Но это уже детали.

кстати, непонятно, зачем в таблице индекс vid(id). вот этот явно лишний

[Ипатьев]

кстати, а результат show warnings всё-таки интересно было бы узнать.
мне кажется, там оптимизатор пишет совсем разные запросы. и последний как раз "выводит из строя mysql"

[psiklop]

Ипатьев, сделаю завтра show warnings.

Комп и нужно грузить, зачем ему стоять без дела. Тут логика такая была, пусть грузится если справляется, но чтобы в будущем не было проблем, потому что база растет и растет. И в длину так сказать и в ширину.

Внимание на скорость было уделено запросам с сайтов от посетителей, но ни все простые select по id юзера, в админке не так страшно. А эти самые сложные запросы вообще для крона они выполняются ночью и даже не каждый день поэтому все равно.

vid(id) сам увидел в первый раз, уже удалил. Поле переименовал еще несколько месяцев назад, а индекс нет.

[Ипатьев]

Комп и нужно грузить, зачем ему стоять без дела.

Ну если это выделенный комп, то конечно. Я просто привык к веб-окружению, идёт множество одновременных запросов, и если один запрос тормозят базу, то он замедляет и все остальные, и весь сайт начинает тупить.

На моей прошлой работе прямо был мем, "акция". Как только продажники объявляли большую скидку, то всегда падала база. До тех пор, пока не занялись нормально её настройкой. А под обычной нагрузкой всё работало. Так что лучше компьютеру под стандартной нагрузкой именно "стоять без дела". Потому что нагрузка может вырасти.

[psiklop]

Ипатьев, он выделенный там 16 ядер и 32 виртуальных, я тестил как встают в очередь запросы. Не очень понятно, но ситуация такая если открывать вкладки в одном браузере запросы выстраиваются в очередь очень непонятно, кто именно их выстроил, пока руки не дошли проверить.

Если я открываю к примеру на пк 5 браузеров все запросы обрабатываются параллельно, я это отчетливо вижу в htop загружено 5 ядер из 32. Нагрузки не страшны, сервер пока мощнее требуемого во много раз, меня больше пугают блокировки, чтобы таблица нужная посетителям не заблокировалась надолго, чтобы все ядра любой софт использовал, коннекты там всякие были безлимита.

Вот как раз блокировки хуже нагрузки, я имею ввиду когда процессор не загружен, а таблица заблокировалась долгим запросом и остальные ждут, это сложная тема, но посетители сайта не используют все эти таблицы статистик, они нужны только админам. Самое главное посетители.

[psiklop]

Ипатьев,

SHOW WARNINGS

mysql> EXPLAIN SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVA
L 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n<=4; SHOW WARNINGS;
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 3640708 | 33.33 | Using where |
| 2 | DERIVED | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7281417 | 50.00 | Using where; Using filesort |
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
2 rows in set, 2 warnings (0,01 sec)

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 3598 | To get information about window functions use EXPLAIN FORMAT=JSON |
| Note | 1003 | /* select#1 */ select `t`.`id` AS `id`,`t`.`time` AS `time`,`t`.`n` AS `n` from (/* select#2 */ select `main_files`.`sms`.`id` AS `id`,`main_files`.`sms`.`time` AS `time`,row_number() OVER (PARTITION BY `main_files`.`sms`.`phone` ORDER BY `main_files`.`sms`.`time` desc ) AS `n` from `main_files`.`sms` where ((`main_files`.`sms`.`time` > ((now() - interval 6 month))) and (`main_files`.`sms`.`time` < ((now() - interval 6 hour))))) `t` where (`t`.`n` <= 4) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

mysql> EXPLAIN SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n=4; SHOW WARNINGS;
+----+-------------+------------+----------------+------+---------------+-------------+---------+-------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+----------------+------+---------------+-------------+---------+-------+---------+----------+-----------------------------+
| 1 | PRIMARY | | NULL | ref | | | 8 | const | 10 | 100.00 | NULL |
| 2 | DERIVED | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7281417 | 50.00 | Using where; Using filesort |
+----+-------------+------------+----------------+------+---------------+-------------+---------+-------+---------+----------+-----------------------------+
2 rows in set, 2 warnings (0,00 sec)

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 3598 | To get information about window functions use EXPLAIN FORMAT=JSON |
| Note | 1003 | /* select#1 */ select `t`.`id` AS `id`,`t`.`time` AS `time`,`t`.`n` AS `n` from (/* select#2 */ select `main_files`.`sms`.`id` AS `id`,`main_files`.`sms`.`time` AS `time`,row_number() OVER (PARTITION BY `main_files`.`sms`.`phone` ORDER BY `main_files`.`sms`.`time` desc ) AS `n` from `main_files`.`sms` where ((`main_files`.`sms`.`time` > ((now() - interval 6 month))) and (`main_files`.`sms`.`time` < ((now() - interval 6 hour))))) `t` where (`t`.`n` = 4) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

SHOW WARNINGS

Answer 1

[Ипатьев]

Первый уровень оптимизации запросов - это, разумеется, индексы.

Но иногда даже если все нужные индексы есть, база тупит неимоверно. В этом случае надо сразу смотреть значение innodb_buffer_pool_size. По умолчанию значение этого параметра всего 128 мегабайт. А это, грубо говоря, максимальное значение памяти, доступное MySQL для работы.

Собственно, суть работы индекса в том, что это упорядоченные данные, лежащие в оперативной памяти, за счёт чего процессор находит нужные данные очень быстро. Но вот если памяти тупо не хватает чтобы индекс в ней поместился, то его эффективность тут же превращается в тыкву.
При этом Mysql дисциплинированно не забирает всю доступную на компьютере память, а берет столько, сколько ей скажут. И если это значение не увеличить, то на большой базе индексы не поместятся в память и всё встанет колом, поскольку индекс будет читаться частями с диска, потом еще надо будет выбранные результаты свопить на диск, потом ещё сортировать полученный результат опять же на диске...

Разумеется, у mysql есть просто миллион разных настроек, которые тоже влияют на производительность. У Перконы, кажется, даже есть калькулятор, в который ты заливаешь информацию о своей базе, а он тебе выдаёт значения параметров конфигурации. Но это уже такая тонкая настройка, третий уровень. Ничего из этого не будет работать, если innodb_buffer_pool_size недостаточного размера. И его размер рекомендуется выставлять на максимально допустимое значение. Если это выделенный mysql сервер, то 80-90% от физической памяти. Если не выделенный - то столько, сколько не жалко, чтобы не мешать остальным приложениям.

Comments

[psiklop]

Эх, 128мб использовать 0.1% от всего ram. Хотя если вспомнить и php и nginx настраивал, они тоже всего боятся и использовать ram и использовать ядра.