Большие таблицы innodb и жесткий диск. Как оптимизировать?

Вводная:
сервер 8гб озу, 2* hdd 7200rpm, ext4
percona 5.6.24-72.2-log, таблица с текстами 40Gb 9мил записей.
CREATE TABLE `tmp_news` (
	`guid` CHAR(50) NOT NULL,
	`s_id` INT(10) UNSIGNED NOT NULL,
	`title` VARCHAR(250) NOT NULL,
	`text` TEXT NOT NULL,
	`date` DATETIME NOT NULL,
	`category_id` INT(11) UNSIGNED NOT NULL,
	`rating` TINYINT(1) NULL DEFAULT NULL,
	`frating` TINYINT(1) NULL DEFAULT NULL,
	`trating` TINYINT(1) NULL DEFAULT NULL,
	PRIMARY KEY (`guid`),
	INDEX `i3` (`category_id`, `source_id`),
	INDEX `i4` (`emote`),
	INDEX `i2` (`date`, `category_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;


Если добавление и обновление таблицы происходит достаточно быстро, то выборка - нет.Происходит это не всегда, а закономерность определить не получилось. Один и тот же запрос в разные моменты времени может выполняться 2 секунды и 40.
Если в момент выполнения "повисшего" запроса выполнить iotop, то увидим процесс mysql который на 99% занимает IO чтением с диска.
EXPLAIN запросов показывает, что все запросы покрыты индексами и в процессе выполнения не затрагивается более 3-4 тысяч строк.

Очень хочется уйти от 40 секунд, подскажите в каком направлении идти? (аппаратных изменений, настройски системного ПО, или формирования запросов)?
  • Вопрос задан
  • 1753 просмотра
Пригласить эксперта
Ответы на вопрос 4
`guid` CHAR(50) - плохой PK.
1. Он присутствует в каждом индексе и ест много памяти, метров так по 400 на индекс в вашем случае. А в этой памяти могли бы лежать данные, за которыми не пришлось бы лезть на диск. Выход - сделать PK int autoincrement, а guid - unique key.
2. Не знаю, как guid генерируется у вас, но могу предположить, что он равномерно распределённый, в таком случае строки вставляются в случайные места таблицы, что приводит к большому количеству seek при выборке. К тому же память buffer bool расходуется неэффективно в таком случае. В сочетании с п.1 - совсем беда. Выход тот же - pk int autoincrement. Вдобавок пронумеровать строки в порядке возрастания date (предполагаю, что у вас более часто запрашиваются недавние тексты) и дефрагментировать таблицу.

Также вызывает подозрения индекс (date, category) - при поиске по интервалу дат индекс не будет использоваться для категорий, ибо секунды.
Ответ написан
Bessome
@Bessome
Администратор Linux, Windows. 1С программист
cat my.cnf | grep innodb
В идеале конечно весь, обезличенный
Нужно настраивать кэш, увеличивать размер оперативной памяти
Ответ написан
@Djadka
Web developer
При 9 миллионов записей как то много весит таблица, потому что у меня конструкция таблицы больше и при 30 миллионов весила около 4 гб. Я бы сказал надо смотреть в сторону партицирование если озу не можете больше добавить. Странно что у Вас там происходит если всё должно браться с индекса. Может запрос надо переделать.
Ответ написан
Комментировать
foboss
@foboss
Можно попробовать поставить innodb-log-file-size = 512M (innodb-buffer-pool-size / 4) и, как подсказали выше - innodb-flush-method = O_DIRECT и innodb-flush-log-at-trx-commit = 0 -- про этот параметр советую почитать перед установкой:
https://www.percona.com/blog/2007/11/03/choosing-i...

Ну и уж если начали, то [paranoid mode on]А сами таблицы в каком формате? Надо бы, чтоб было: innodb_file_format=BARRACUDA[paranoid mode off]
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы