SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 27.09.2010 16:28:34

boa
Завсегдатай
Зарегистрирован: 22.06.2010
Сообщений: 38

Индексы и высокая нагрузка в innodb

Добрый день!
У меня есть вопрос по индексам.

Есть табличка содержащая около 6млн записей и занимающая на диске около 4.5 Гб
С этой таблицей часто производятся операции insert, update и delete. Нагрузка на таблицу около 2000-3000 запросов в сек. 90% запросов это insert-ы, остальные 10% - удаление и update.
Структура таблицы:
CREATE TABLE `table1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` tinytext NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key` (`key`(32)),
  KEY `key_sign` (`key`(32),`sign`)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Общий алгоритм работы такой: в таблицу постоянно вставляется много разных данных с разными ключами key. И иногда посылается запрос на удаление записей у которых key='некоторое_значение'.
Проблема в том что эта таблица хорошо работала до некоторого размера, а потом начала тормозить.
вставки и удаления выполняются долго.
Что может влиять на производительность?
Как mysql обновляет индексы при удалении большого числа записей (например delete from table1 where key='123' может удалить 10-20% таблицы) и при инсертах? Может ли такое прореживание индексов влиять на производительность?

Неактивен

 

#2 27.09.2010 18:21:56

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Индексы и высокая нагрузка в innodb

Скорее всего, просто табличка перестала влезать в innodb_buffer_pool_size smile

Ключ key точно лишний, потому что у Вас есть ключ key_sign. Но и вообще
не очень удачное применение InnoDB — ни TEXT, ни BLOB в нем лучше не
хранить.

Неактивен

 

#3 28.09.2010 10:55:36

boa
Завсегдатай
Зарегистрирован: 22.06.2010
Сообщений: 38

Re: Индексы и высокая нагрузка в innodb

paulus написал:

Скорее всего, просто табличка перестала влезать в innodb_buffer_pool_size smile

Ключ key точно лишний, потому что у Вас есть ключ key_sign. Но и вообще
не очень удачное применение InnoDB — ни TEXT, ни BLOB в нем лучше не
хранить.

Но если применять myisam то возникнут проблемы с блокировками. каждый запрос будет блокировать таблицу.
получается что выход на данный момент это наращивание памяти?
Еще вопрос - можно ли сделать partitioning на этой таблице по key? я пытался делать, но mysql пишет что разбивка должна производится по первичному ключу

Неактивен

 

#4 28.09.2010 20:35:37

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Индексы и высокая нагрузка в innodb

Это было всего лишь предположение. InnoDB, разумеется, нормально работает
с базой, которая не влезает в ОЗУ. Иначе какой в нем был бы смысл?

Но 2k вставок блобов в секунду — это действительно много. А какой размер
этих блобов?

Partitioning можно действительно сделать только по столбцам, которые входят
в primary key.

Неактивен

 

#5 30.09.2010 13:03:09

boa
Завсегдатай
Зарегистрирован: 22.06.2010
Сообщений: 38

Re: Индексы и высокая нагрузка в innodb

paulus написал:

А какой размер этих блобов?

выполнил запрос
select
avg(length(`data`)), avg(length(`key`)),
max(length(`data`)), max(length(`key`))
from hashtable

результаты:
avg(length(`data`))=447.5808
max(length(`data`))= 33596
avg(length(`key`)) =16.9595
max(length(`key`))=96

Отредактированно boa (30.09.2010 13:03:35)

Неактивен

 

#6 30.09.2010 14:47:06

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Индексы и высокая нагрузка в innodb

Ну то есть Вы можете заменить key на varchar(100) и пользоваться
полноценными строками?

Неактивен

 

#7 30.09.2010 14:52:34

boa
Завсегдатай
Зарегистрирован: 22.06.2010
Сообщений: 38

Re: Индексы и высокая нагрузка в innodb

`key` - возможно. Но что это даст?
если бы все поля были varchar тогда прирост был бы заметный, тут blob есть.

Неактивен

 

#8 30.09.2010 16:01:27

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Индексы и высокая нагрузка в innodb

Ну, второе поле можно преобразовать в varbinary, все равно же у Вас
BLOB, а не TEXT smile

Неактивен

 

#9 30.09.2010 16:06:33

boa
Завсегдатай
Зарегистрирован: 22.06.2010
Сообщений: 38

Re: Индексы и высокая нагрузка в innodb

что дадут эти преобразования?

Неактивен

 

#10 30.09.2010 16:18:50

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Индексы и высокая нагрузка в innodb

Второе — ничего. Первое — позволит держать ключ внутри строки, позволит
сделать индекс над всем полем, что потенциально даст возможность доставать
данные по равенству (key, sign), не используя range.

Неактивен

 

#11 30.09.2010 16:55:30

boa
Завсегдатай
Зарегистрирован: 22.06.2010
Сообщений: 38

Re: Индексы и высокая нагрузка в innodb

интересно! не знал о такой особенности индексации полей разного типа.
Спасибо за советы!

Еще вопросик есть(к вышеобсуждаемой таблице не имеет отношения): для какого поля индекс будет эффективнее для binary(16) или для char(16)

Неактивен

 

#12 30.09.2010 17:14:11

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Индексы и высокая нагрузка в innodb

Особенности тут нет — есть ограничение в 1000 байт на длину строки индекса. BLOB
занимает больше 1000 байт, поэтому над ним индекс не строится целиком smile

BINARY(16) занимает 16 байт, а CHAR(16) занимает 16 символов. Если кодировка
этого поля однобайтная (например, latin1), то размер индекса будет тоже 16 байт,
а если, например, utf8, то 16 × 3 = 48 байт. Ну и короткие индексы работают,
разумеется, быстрее.

Неактивен

 

#13 01.10.2010 16:41:02

boa
Завсегдатай
Зарегистрирован: 22.06.2010
Сообщений: 38

Re: Индексы и высокая нагрузка в innodb

спасибо за ответыsmile

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson