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

Question

[Multigame]

Вводная:
сервер 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 секунд, подскажите в каком направлении идти? (аппаратных изменений, настройски системного ПО, или формирования запросов)?

Answer 1

[Александр Мелеховец]

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

Также вызывает подозрения индекс (date, category) - при поиске по интервалу дат индекс не будет использоваться для категорий, ибо секунды.

Comments

[Александр Мелеховец]

Btw, 4000 записей при 100 random seek в секунду за 40 секунд и выгребаются как раз.

[Александр Мелеховец]

innodb-flush-method = O_DIRECT и innodb-flush-log-at-trx-commit = 0 - тут скорее производительность записи, с этим у вас и так всё хорошо.

Answer 2

[Дмитрий Лупонос]

cat my.cnf | grep innodb
В идеале конечно весь, обезличенный
Нужно настраивать кэш, увеличивать размер оперативной памяти

Comments

[Multigame]

[mysqld]
default-storage-engine = InnoDB
# MyISAM #
key-buffer-size = 2M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 3
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# INNODB #
innodb-flush-method = O_DSYNC
innodb-log-files-in-group = 2
innodb-log-file-size = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 4096M
innodb_thread_concurrency = 6
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

[Дмитрий Лупонос]

However, setting innodb_flush_method to O_DIRECT causes InnoDB to use direct I/O only for data files, not the log files.
innodb_flush_log_at_trx_commit=0
Подумалось еще о производительности HDD. Ставят рейд, а я мыслю что файлы должны быть на разных дисках - tmp и базы данных.
P/S На свой страх и риск!

Answer 3

[Djadka Maxho]

При 9 миллионов записей как то много весит таблица, потому что у меня конструкция таблицы больше и при 30 миллионов весила около 4 гб. Я бы сказал надо смотреть в сторону партицирование если озу не можете больше добавить. Странно что у Вас там происходит если всё должно браться с индекса. Может запрос надо переделать.

Answer 4

[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]