TEXT/BLOB в InnoDB и размер таблицы

[FB3]

TEXT/BLOB в InnoDB и размер таблицы

Попытаюсь упрощенно описать проблему:
Есть большая таблица InnoDB с примерно миллионом записей.
В таблице всего два поля, одно - первичный ключ, второе - содержит данные в текстовом виде. Думаем перейти на BLOB, но пока не об этом.
Проблема в том, что и TEXT и BLOB - это поля переменной, а не фиксированной длины. Соответственно, когда данные изменяются, то они не затираются новыми поверх старых и при активной работе с этими данными размер таблицы вырастает за неделю или даже быстрее до такого, что мускул уже не влезает в отведенный ему объем памяти и начинает свопить на винт. После этого помогает только OPTIMIZE TABLE, который вычищает весь мусор, скопившийся в таблице и никому не нужный. Удаление и добавлений новых записей происходит намного реже, чем изменение данных. Времени оно отнимает до 20 минут, если еще не начало свопить и 40-60 минут - если уже MySQL свопит во всю.
В поле фиксированного размера эти данные ну никак не влезут. Средняя длина поля около 2400 байт после сжатия zip и упаковки mime64 (если чо, не я это придумал ). Максимальное же поле фиксированного размера это 255 символов. Даже если попытаться разбить эти данные на какие-то части (для записи в разные поля), все равно остается логическая единица данных, которая больше 255 байт займет и не влезет в поле фиксированной длины.

Варианты:
1. Добавить памяти на сервак - все равно не обойтись без OPTIMIZE TABLE, просто реже делать придется, да и памяти в серваке уже по максимуму.
2. Купить еще один сервак под шардинг БД - поддержка шардинга имеется, но опять же без OPTIMIZE TABLE, только реже, не обойдется.
3. Разбивать логическую единицу данных на несколько фиксированных полей - в принципе реально, но очень уж криво на мой взгляд.
4. Разбить эту логическую единицу данных на совсем мелкие логические единицы данных, записывать их в новую таблицу как отдельные записи и выбирать отдельным запросом эти данные - автоматом получаем табличку с кол-вом записей в 500 раз больше, чем в оригинальной таблице.
5. Ваши советы и предложения?

Может коряво объяснил, но резюмирую суть вопроса: проблема в том, что из-за постоянного изменения полей переменной длины довольно быстро растет размер таблицы InnoDB, хотя кол-во хранимой информации в самой таблице растет намного медленней.

 

[Fortop]

Средняя длина поля около 2400 байт

VARCHAR до 64K (при этом можно даже использовать индекс до 1000 первых символов)

плюс
4й вариант мне нравится, не понятна цифра 500, логические единицы этого куска по 5 байт что ли?

-~{}~ 29.03.10 23:49:

меня смущает миллион часто-меняющихся записей...
Может это все же должны быть не меняющиеся? А, добавляемые записи?

P.S. а сколько памяти-то выделено, что сервер не справляется?

 

[dimagolov]

FB3, с чего ты решил, что таблица вся в память тянется? или ты делаешь запросы select * from tabl; ? хотя даже при этом непонятна связь между объемом используемой оперативки и OPTIMIZE TABLE, который влияет на объем занимаемого дискового пространства.

п.с. сжимать zip-ом и паковать в mime64 это маразм. разве что данные нужно отдавать куда-то дальше (экспорт) именно в таком виде.

 

[Fortop]

Гм. сессии что ли храните?

 

[FB3]

Автор оригинала: Fortop
VARCHAR до 64K (при этом можно даже использовать индекс до 1000 первых символов)

VARCHAR - поле фиксированной длины? Пойду ман почитаю.

Автор оригинала: Fortop
плюс
4й вариант мне нравится, не понятна цифра 500, логические единицы этого куска по 5 байт что ли?

Ну грубо говоря да, сейчас на вскидку не помню, дома потому что.

Автор оригинала: Fortop
-~{}~ 29.03.10 23:49:

меня смущает миллион часто-меняющихся записей...
Может это все же должны быть не меняющиеся? А, добавляемые записи?

P.S. а сколько памяти-то выделено, что сервер не справляется?

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

-~{}~ 30.03.10 01:14:

Автор оригинала: dimagolov
FB3, с чего ты решил, что таблица вся в память тянется? или ты делаешь запросы select * from tabl; ? хотя даже при этом непонятна связь между объемом используемой оперативки и OPTIMIZE TABLE, который влияет на объем занимаемого дискового пространства.

п.с. сжимать zip-ом и паковать в mime64 это маразм. разве что данные нужно отдавать куда-то дальше (экспорт) именно в таком виде.

На самом деле не совсем правильно я описал выше. Мне поставили задачу придумать решение проблемы. Проблему объяснили так: процесс mysql начинает свопить через несколько дней, проблему решают не с помощью OPTIMIZE, а дампом базы, ее убиванием (чтобы удалить еще и служебные файлы InnoDB) и импортом ранее полученного дампа. Почитав мануал понял, что скорей всего, как и в варианте с OPTIMIZE TABLE, проблема в полях переменной длины, которые физически не удаляются из таблицы при апдейте записи.
Вообщем выдал предполагаемое за действительное
Какими запросами выборка производится - проверю завтра, сегодня теорию изучал. В любом случае там в таблице всего два поля, первичный ключ и поле данных.
п.с. как вариант, не паковать в mime64 и поле сделать blob

-~{}~ 30.03.10 01:14:

Автор оригинала: Fortop
Гм. сессии что ли храните?

Ну почти, чем-то похоже на сессии, да Много расписывать всю архитектуру приложения ради решения одной проблемы ну совсем не хочется Тем более что всё равно сразу всё никто не будет (не даст времени) переделывать, по частям в силу знаний, умений, опыта и т.д. я конечно буду переписывать.

 

[Fortop]

поле фиксированной длины?

В честь чего?
Просто TEXT и BLOB, если я правильно помню, хранятся
несколько иначе.

mysql man
Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types (see Section 7.5.10, “How MySQL Uses Internal Temporary Tables”). Use of disk incurs a performance penalty, so include BLOB or TEXT columns in the query result only if they are really needed. For example, avoid using SELECT *, which selects all columns.

-~{}~ 30.03.10 00:21:

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

Так предположим или точно?

Если речь идет о полном обновлении таблицы за сутки, то почему бы просто не добавлять новые записи (вместо апдейта старых).
И периодически заниматься переносом ненужных данных в архив.

Где такая нагрузка, если не секрет? 100к записей в час это немало.

-~{}~ 30.03.10 00:22:

И, да, если это все же сессии и нечто подобное(пользовательские вьюстейт например), то почему не воспользоваться memcache?

 

[dimagolov]

Fortop, твоя цитата дает судя по всему ответ на вопрос, что происходит. Запрос к такой таблице всегда дает запись временной таблице на диск. Скорее всего у них в какой-то момент в запрос попадает слишком много данных и никто не свопиться, а просто пытается создать временную таблицу. Хотя, может и сповиться, если временные таблицы зело большие получаются в памяти.

Только с таким анализом нет шансов найти проблему и решить ее.

 

[FB3]

Автор оригинала: Fortop
Так предположим или точно?

Если речь идет о полном обновлении таблицы за сутки, то почему бы просто не добавлять новые записи (вместо апдейта старых).
И периодически заниматься переносом ненужных данных в архив.

Где такая нагрузка, если не секрет? 100к записей в час это немало.

И, да, если это все же сессии и нечто подобное(пользовательские вьюстейт например), то почему не воспользоваться memcache?

Точной статистики я не видел, могу сказать, что вся таблица в течение суток точно не обновляется и сомневаюсь, что даже половина обновляется.
Имелось ввиду, что 100к записей в течение суток могут обновиться, но каждая запись далеко не по одному разу, а минимум десяток раз.
Нагрузка такая на приложение вконтактовское.
memcache - думали об этом, но почему-то, уже не помню почему, не подходит оно по какой-то причине. Например, что первое в голову приходит: мы не знаем момент, когда в памяти затирается старые данные от одного пользователя новыми данными от другого, следовательно не можем сохранить в базу данные старого пользователя перед тем, как они пропадут в кэше. Может эта проблема тоже как-то решается, но пока я не знаю как.

-~{}~ 30.03.10 02:30:

Автор оригинала: dimagolov
Fortop, твоя цитата дает судя по всему ответ на вопрос, что происходит. Запрос к такой таблице всегда дает запись временной таблице на диск. Скорее всего у них в какой-то момент в запрос попадает слишком много данных и никто не свопиться, а просто пытается создать временную таблицу. Хотя, может и сповиться, если временные таблицы зело большие получаются в памяти.

Только с таким анализом нет шансов найти проблему и решить ее.

Это цитата, которая из мануала? Пойду читать про временные таблицы тогда.
Правильно я понимаю, что чем больше записей мы выбираем запросом, тем больше временная таблица может получиться?

 

[Fortop]

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

Этого не может быть, потому что этого не может быть никогда.
Данные могут быть вытеснены из кеша по причине нехватки памяти.
По причине протухания.
Но никак не по причине перекрытия информации другим пользователем.

Память же можно посчитать и установить с запасом.
Данные сбрасывать в БД по крону (этим мы уменьшим число записей), а можно и демоном, который частоту сохранения в БД будет делать в зависимости от нагрузки.

Плюс можно не делать собственный велосипед, а посмотреть memcachedb.

Более того если пользователи между собой никак не пересекаются(ну, пожалуй, за исключением статистики), то и вставку и чтение можно неограниченно партиционировать/шардить на произвольное число отдельных серверов.

Если пользователи пересекаются, то чуть сложнее, но тоже варианты есть.

-~{}~ 30.03.10 04:38:

Правильно я понимаю, что чем больше записей мы выбираем запросом

Омг, вы там всю таблицу что ли выбираете?

Я не могу знать вашего приложения, но выборка более чем нескольких десятков (ну до сотни) записей на одного пользователя за один раз - это уже странно.

P.S. И я до сих пор не могу понять, что создает у вас нагрузку, то ли 100к апдейтов, то ли не менее(а по логике даже более) интенсивное чтение. Вы это хоть выясняли?
Профайлинг запросов делали?

 

[FB3]

Автор оригинала: Fortop
Этого не может быть, потому что этого не может быть никогда.
Данные могут быть вытеснены из кеша по причине нехватки памяти.
По причине протухания.
Но никак не по причине перекрытия информации другим пользователем.

Память же можно посчитать и установить с запасом.
Данные сбрасывать в БД по крону (этим мы уменьшим число записей), а можно и демоном, который частоту сохранения в БД будет делать в зависимости от нагрузки.

Плюс можно не делать собственный велосипед, а посмотреть memcachedb.

Более того если пользователи между собой никак не пересекаются(ну, пожалуй, за исключением статистики), то и вставку и чтение можно неограниченно партиционировать/шардить на произвольное число отдельных серверов.

Если пользователи пересекаются, то чуть сложнее, но тоже варианты есть.

-~{}~ 30.03.10 04:38:


Омг, вы там всю таблицу что ли выбираете?

Я не могу знать вашего приложения, но выборка более чем нескольких десятков (ну до сотни) записей на одного пользователя за один раз - это уже странно.

P.S. И я до сих пор не могу понять, что создает у вас нагрузку, то ли 100к апдейтов, то ли не менее(а по логике даже более) интенсивное чтение. Вы это хоть выясняли?
Профайлинг запросов делали?

Ну да, я и имел ввиду, что по причине нехватки памяти данные вытеснятся, но при этом момент, когда данные вытеснятся, определить не возможно.
Нет, вся таблица не выбирается, всегда только одна запись. Это я уже для общего развития уточнить решил.
Про memcachedb пошел тоже почитать...
Проблема на данный момент не в скорости выполнения запросов, а в том, что мускул свопить начинает через какое-то время, так что профайлингом лично я еще не занимался.
Вообщем, нужно разобраться, почему свопит и уменьшить кол-во данных в таблице, если это зависит от размера таблицы и постоянного обновления данных в ней.

-~{}~ 30.03.10 14:42:

Проверил, что временных таблиц не создается при выборке, поскольку сортировок и группировок не используется.

 

[Fortop]

так что профайлингом лично я еще не занимался

Совершенно зря не занимались.

Как минимум включите
log-slow-queries = /var/log/mysql/mysql-slow.log

Плюс в начале работы приложения выдать такую команду
set profiling=1;

затем после запросов, собрать информацию
show profiles

 

[FB3]

Ну в данный момент нет большой проблемы со скоростью выборки, по крайней мере даже если какие-то выборки делаются не очень быстро, это не так критично сейчас по сравнению с вышеописанной проблемой.
Конечно потом и профайлингом заниматься будем. Про лог медленных запросов я в курсе, но все равно спасибо

 

[dimagolov]

FB3, а как вообще пришли к выводу, что свопиться именно MySQL и причина в структуре таблицы? Кстати, а какое отношение у вас попадания в кеш MySQL-ем к числу исполненных SELECT-ов?

Кстати, а какая ОС, версия MySQL, сколько памяти/процов в системе и сколько процессов MySQL в наличии? И что показывает LA при этом? Еще полезно my.conf выложить, а то, у меня правка конфига дала рост попаданий в кеш с почти 0 до 60% от общего числа select-ов и при этом синхронно упал LA (хотя он и так был децельный).

 

[FB3]

dimagolov, попадание в кеш MySQL-ем к числу SELECT-ов наверняка не велико, поскольку кэшируем в memcache данные и, соответственно, если они там есть, то забираем их оттуда. На вторую часть вопроса чуть попозже постараюсь ответить, потому что прямо сейчас нет доступа к серверу у меня.

Сервак
1 штука Intel Core2 Duo E7500 @ 2.93GHz (сам удивился, что не Xeon какой-нибудь)
8 Gb RAM (вероятнее всего DDR2)
Материнка - неизвестно
Скорей всего софтовый зеркальный RAID
CentOS
MySQL 5.1 работает в один процесс, судя по этому:

top - 18:08:47 up 111 days, 13:26,  1 user,  load average: 0.30, 0.31, 0.28
Tasks:  63 total,   2 running,  61 sleeping,   0 stopped,   0 zombie
Cpu(s):  4.3%us,  1.2%sy,  0.0%ni, 89.9%id,  2.8%wa,  0.0%hi,  1.8%si,  0.0%st
Mem:   8071020k total,  8024324k used,    46696k free,    44484k buffers
Swap:  4008120k total,     3724k used,  4004396k free,  1654860k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                    
26813 mysql     15   0 7356m 5.9g 6804 S 11.7 76.7 512:47.38 mysqld

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
innodb_file_per_table
key_buffer = 512M
max_allowed_packet = 32M
table_cache = 512
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size = 128M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency =4
set-variable = max_connections=4096
connect_timeout=30
interactive_timeout=28800
wait_timeout=28800
net_write_timeout=30
net_read_timeout=60
skip-locking
skip-name-resolve
skip-host-cache

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:5000M;ibdata2:100M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 5G
innodb_additional_mem_pool_size = 750M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 1024M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method_DIRECT
innodb_lock_wait_timeout = 50

bind-address = 0.0.0.0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

-~{}~ 31.03.10 00:33:

Вообщем, во всех мануалах пишут, что в InnoDB, чтобы уменьшить размер таблицы, стоит прибивать полностью таблицу тем способом, который я описал выше. Но нигде не пишут, как сделать, чтобы она не росла так быстро.
Фиксированные типы данных очень тяжело в таблицу вписать, потому что опять же инфа может начать занимать такой объем, что в одну запись просто не будет влезать. Максимальная суммарная длина фиксированных полей в одной записи не может превышать 8126 байт (вероятно на это значение незначительно влияет кол-во и длина отдельных полей).
В итоге, даже если пересматривать архитектуру с разделением на несколько таблиц и потом делать JOIN, получается, что начнут медленнее работать выборки, поскольку выбирать уже нужно не из одной таблицы, а размер базы все равно будет больше.
Опять же архитектура серверной части сильно зависит от клиентской и если хранить данные не в том виде, в котором они нужны клиенту - то получается, что в коде нужно будет заниматься преобразованием этих данных, что тоже может замедлить работу.
Вообщем буду эксперименты завтра разные проводить, потому что особых вариантов решить проблему не вижу, кроме как уменьшать кол-во данных (а там наверняка есть лишние данные) и, следовательно, размеры таблицы.

 

[Fortop]

Вообщем буду эксперименты завтра разные проводить, потому что особых вариантов решить проблему не вижу, кроме как уменьшать кол-во данных (а там наверняка есть лишние данные) и, следовательно, размеры таблицы.

Уф. Ну вот почему решили, что надо уменьшать размеры таблицы?
На основании каких данных?
Где статистика по запросам, пользователям, соотношению чтение/запись?

В итоге, даже если пересматривать архитектуру

Это мне даже комментировать страшно. Мы еще не знаем что болит, почему болит. Но уже твердо решили отрезать ногу/руку/голову

А у пациента был всего лишь насморк!

 

[FB3]

http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

У нас как раз много изменений в столбце текстового типа.
Запросы все одинаковые в этой таблице, либо взять одну запись из таблицы, либо записать одну запись в таблицу. Перед записью в таблицу пишется в memcache, соответственно, вместо того, чтобы прочитать из MySQL, данные могут взяться из memcache. Проще некуда и я не вижу здесь вариантов уменьшить кол-во записей в таблицу, для того чтобы она не росла так быстро (только замена MySQL на memcachedb как вариант). Поэтому считаю, что статистика вряд ли как-то может здесь помочь.
Попробую во второй половине дня собрать статистики, но больших надежд на нее не возлагаю. А пока буду заниматься экспериментами.
http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html

Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:

Use mysqldump to dump all your InnoDB tables.

Stop the server.

Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.

Remove any .frm files for InnoDB tables.

Configure a new tablespace.

Restart the server.

Import the dump files.

 

[Fortop]

Во-первых, остается в силе

dimagolov
сжимать zip-ом и паковать в mime64 это маразм

Абсолютно непонятно почему данные хранятся именно так.

Во-вторых, непонятно, а нужно ли вообще хранить все данные, которые хранятся.
Возможно использование in-memory таблиц решит все вопросы. Хотя использование БД в качестве временных файлов... не самое лучшее решение.

В-третьих, непонятно как между собой связаны данные, кроме того что они лежат в одной таблице.
Т.е. что военного произойдет если данные о Васе и Пете будут хранится на разных серверах, никак не связанных между собой?

Ну, и вопрос статистики по-прежнему в силе.

 

[dimagolov]

Да, еще вопрос почему InnoDB, а не MyISAM?

 

[FB3]

Fortop
Данные хранятся так, потому что их кто-то (возможно я даже знаю, кто именно ) придумал сначала сериализовать в JSON и писать в текстовое поле, потом вдруг места стало не хватать, решили JSON жать зипом и потом base64, чтобы таблица меньше объемом была.
Вообщем весь профайл пользователя, который по большей части из дерева объектов состоит, энкодится в JSON. Типа из расчета на то, что это можно быстрее десериализовать, чем выбирать из БД массивы и потом из них создавать все эти объекты, т.е. строить полностью профайл из данных. Профайл в этом определенном формате нужен клиенту игры.
Все это было сделано до меня, поэтому спрашивать меня, почему и зачем - бесполезно

Да, все данные нужно хранить. Только они сейчас хранятся в убогом формате, который занимает очень большой объем. В моем понимании бОльшая часть этих данных должна храниться в бинарной строке (около 500 объектов с 2-3 свойствами, которые можно легко выразить числовым значением), часть в отдельных полях (сколько фантиков и опыта в игре).

Данные и так хранятся на разных серверах и шардятся, поэтому ничего военного не происходят.

Статистики пока нет, она еще не собирается, а мне доступ к серверам хотя бы на чтение логов еще не сделали.

InnoDB, а не MyISAM опять же выбрано не мной, но по идее основной причиной было, то что MyISAM лочит всю таблицу на чтение при апдейте одного поля в ней.

Вот как-то так...

 

[dimagolov]

решили JSON жать зипом и потом base64, чтобы таблица меньше объемом была.

ты понимаешь, что base64 занимает на 25% больше места, чем бинарные данные? и что тебе ничего не мешает поменять text на blob и убрать base64 с минимальными усилиями как первый этап оптимизации.