Проблема со вставкой

[BRat]

Проблема со вставкой

С поражающей периодичностью в slow-логе появляются след. запросы -

Count: 1611 Time=6.80s (10958s) Lock=0.66s (1056s) Rows=0.0 (0), fighting[fighting]@localhost
insert into `GAME` set `CREATED` = 'S', `GOT_EXP` = null, `GOT_LEVEL` = null, `GOT_LIFEUNITS` = null, `GOT_POWER` = null, `RIVAL_EXTID` = 'S', `RIVAL_UID` = null, `TYPE_UID` = N, `USER_UID` = N, `USER_WIN` = null

Создание таблицы -

 CREATE TABLE `GAME` (
  `UID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_UID` int(11) NOT NULL,
  `TYPE_UID` int(11) NOT NULL,
  `CREATED` datetime NOT NULL,
  `RIVAL_UID` int(11) DEFAULT NULL,
  `RIVAL_EXTID` varchar(45) NOT NULL,
  `USER_WIN` tinyint(1) DEFAULT NULL,
  `GOT_EXP` int(11) DEFAULT NULL,
  `GOT_LIFEUNITS` int(11) DEFAULT NULL,
  `GOT_POWER` int(11) DEFAULT NULL,
  `GOT_LEVEL` int(11) DEFAULT NULL,
  PRIMARY KEY (`UID`),
  KEY `fk_GAME_USER1` (`USER_UID`),
  KEY `fk_GAME_CODE1` (`TYPE_UID`),
  KEY `fk_GAME_USER2` (`RIVAL_UID`),
  CONSTRAINT `fk_GAME_CODE1` FOREIGN KEY (`TYPE_UID`) REFERENCES `CODE` (`UID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_GAME_USER1` FOREIGN KEY (`USER_UID`) REFERENCES `USER` (`UID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_GAME_USER2` FOREIGN KEY (`RIVAL_UID`) REFERENCES `USER` (`UID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

Количество записей в таблице -
mysql> SELECT COUNT(*) FROM GAME;
+----------+
| COUNT(*) |
+----------+
| 510085 |
+----------+


Конфиг innodb-части для mysql-сервера:

innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2

Общий размер innodb-таблиц порядка 250Мб.

В чём может быть причина тормозов при вставке, как можно данную ситуацию исправить?

 

[prolis]

[sql]
KEY `fk_GAME_USER1` (`USER_UID`),
KEY `fk_GAME_CODE1` (`TYPE_UID`),
KEY `fk_GAME_USER2` (`RIVAL_UID`),
[/sql]
четыре индекса тяжело обновлять при частой вставке, причем type_uid наверно не селективный, а RIVAL_UID может быть null. Убейте эти три индекса и создайте один составной, возможно ещё и уникальный

 

[BRat]

Прошу прощения что не написал - я делал для этой таблицы DISABLE KEYS, это не помогло. По этим индексам поиск не производится, они нужны только для FOREIGN KEY

 

[prolis]

Ну рассмотрим три основные причины:
1. Долгая физическая вставка данных (I/O на уровне OS)
2. Ожидание разлочки ресурса
3. Перестройка индексов

Вот тут они более подробно рассмотрены:
http://www.mysqlperformanceblog.com/2008/09/16/when-is-it-a-time-to-upgrade-memory/
там же в одном из вопросов есть пример конфига с пояснениями сайзинга. Если доступ к серверу есть - давай копать

 

[BRat]

Доступ к серверу конечно есть, любую информацию могу предоставить.

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

FILE I/O
--------
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
5983 OS file reads, 504523 OS file writes, 87163 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 5.83 writes/s, 1.08 fsyncs/s
------

Buffer pool hit rate 1000 / 1000

Значения util колеблются в пределах 1-6%, await - 4-40%

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda1              0.00    19.68    0.30   13.29     2.40   449.15    33.24     0.12    9.06   2.85   3.88
sda3              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

По поводу конфига - от моего он отличается наличием настроек
innodb_file_per_table=1
innodb_doublewrite=0
Вторая настройка, судя по этой статье http://www.mysqlperformanceblog.com/2006/08/04/innodb-double-write/ дает падение производительности в пределах 5-10% (не критично).
Первую судя по статье http://umangg.blogspot.com/2010/02/innodbfilepertable.html лучше не включать

 

[prolis]

тогда следующее: innodb_lock_monitor
А в полном статусе не было ничего интересного при такой транкзации?

 

[BRat]

Включил innodb_lock_monitor, буду следить. Сегодня данные запросы в slow-log еще не появлялись

А в полном статусе не было ничего интересного при такой транкзации?

Не понял вопроса, полный статус это где?

 

[prolis]

SHOW STATUS, SHOW INNODB STATUS

 

[BRat]

К сожалению нагрузка на проект упала, и запросы с INSERT INTO GAME перестали появляться в slow log. Буду продолжать следить за сервером, и когда ситуацией повторится, отвечу на вышезаданный вопрос.

prolis
Спасибо за помощь, узнал кое-что новое о мониторинге и настройке innodb