Оптимизация MYSQL DB - 5 GB. 50 000 000+ строк

Это в какой это версии он уже перестает?

Странное решение после перехода на myisam. В innodb выигрышь от memcache был бы - оно умеет с ним работать, myisam нет.

revered,

Вам надо попробовать переехать на настоящий mysql, желательно 5.7. mariadb отделилась аккурат незадолго до появления весьма существенных плюсов по скорости в mysql (часть появилась в 5.6, часть в 5.7), поэтому несмотря на развитие maria - mysql 5.7 ее нередко уделывает.

revered,

Вы упоминали что у Вас сервер.

Мы как бы отметим, что база данных у Вас всего 5гб.

Наиболее тупой и самое смешное - скорее всего самый эффективный способ решить проблему будет

а) выделить гигов на 10 временный диск в памяти

б) скинуть на него место для создания временных таблиц

в) скинуть на него саму основную базу (сделав слейва на харде естественно)

Вот так приложив минимум усилий Вы скорее всего (если нет проблем с индексами) получите офигенную скорость, которую Вам ни один ssd не даст, оператива это всё-таки оператива. Более того, возможно получите даже более высокую скорость чем получили бы от хитровыделанной оптимизации структуры БД, т.к. индексы индексами, а в итоге всё равно всё сводится к рандомному чтению данных с диска.

Вроде как для PHP 7 нужен не Memcache, а Memcached

https://habrahabr.ru/company/avito/blog/338140/

LEOnidUKG правильно советует обновить мускуль, очевидно же что это ваше самое слабое место, а вы оптимизируете все кроме него :)))

Начиная с 8.0 начнётся выпиливание его, сначала урезание функционала и далее по списку.

https://www.percona.com/blog/2016/10/11/mysql-8-0-end-myisam/

По ссылке ничего ни про выпиливание, ни про урезание функционала и никакого списка.

Напротив, In MySQL 8.0 (DMR version as of writing), the MyISAM storage engine is still available

Единственное что выпилено, это you can’t just copy MyISAM tables into a running MySQL server, но и раньше никто в трезвом уме и твердой памяти на работающий сервер просто файлы с таблицами не копировал.

Перенес базу на mysql 5.7

Заметил, что легкие запросы стали наоборот дольше отрабатывать, примерно в 2 раза. Но это не страшно, т.к. все равно очень быстро выполняются.

А вот сложные запросы - стали в 4 раза быстрее отрабатывать.

Спасибо за рекомендацию!

У меня много запросов:

SELECT * FROM table WHERE id IN (1,2,3.......9998,9999)

Если в IN несколько элементов - то отрабатывает мгновенно.

Если в IN несколько сотен элементов то 0.1-0.2 сек. запрос выполняется.

Если в IN несколько тысяч элементов то до 20 сек. запрос выполняется.

Поиск идет по PRIMARY id.

Я так понимаю, что это можно ускорить настройкой сервера, просто не хватает знаний как правильно настроить.

SELECT * FROM table WHERE id IN (1,2,3.......9998,9999)

1) Если элементы реально идут подряд, попробуйте id>=1 and id<=5 или еще лучше - id between 1 and 5

2) Если в последнем случае выбирается больше чем (примерно) 30% строк от размера таблицы, индекс может не использоваться, тут может помочь force index

3) select * в общем случае это ад и трэш. Наверняка хотя бы пару полей можно исключить, лучше писать select field2, field5 .. чем просто тупо *

Спасибо за ответ.

1. Элементы идут не подряд.

2. Выбирается максимум 5-10% строк. В большинстве случаев не более 1%. (таблица 9 000 000 строк)

3. Да, знаю. Здесь так написал, чтобы сократить.

По большому счету это никак не сократить.

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

Если значения идут так или иначе подряд, то иногда помогает alter table order by field - что бы подряд стоящие значения были рядом и могли выбираться за "один проход" (это актуально даже в случае ссд, хотя и менее чем с хдд).

Еще момент - посмотрите не выбираете ли Вы случаем text/blob/varchar поля, за ними мускулу приходится лезть в "отдельное место" каждый раз, т.е. если выбирается 1 строка, а в ней 4 текстовых поля - это по сути требуют 1+4=5 обращений к диску.

При этом не забывайте, что если в таблице есть хоть одно поле с переменной длиной (varchar, text, blob), то даже поля с фиксированной длиной тоже будут хранится как поля с переменной.

Да, несколько varchar полей выбираю.

SELECT `id`,`name`,`url` FROM `organization` WHERE `id` IN (123,345,346 ... 34534) AND `city`=518 AND `status`=1 ORDER BY `name` ASC LIMIT 0,20

Попробовал выбирать только id

SELECT `id` FROM `organization` WHERE `id` IN (123,345,346 ... 34534) AND `city`=518 AND `status`=1 ORDER BY `name` ASC LIMIT 0,20

Время выполнения примерно такое же.

Если есть строки с переменной длиной в БД, например название организации. Как быть?

Лучше оставить varchar или сделать char и пускай пробелами заполняется?

Загрузили бы my.cfg или my.ini т.е. настройки mysql вашей.