|
Задавайте вопросы, мы ответим
Вы не зашли.
Здравствуйте!
Поясните, пожалуйста, работает ли partition pruning при вставке в разбитую таблицу? А конкретнее, открывается ли единственный файл для вставки или задействуются все файлы составляющие таблицу?
Дело в том, что имеется несолько лог-таблиц, разбитых по дню года. И когда идет значительный поток вставок, сервер часто выдает ошибку типа Out of resources when opening file './<db_name>/CustomLog#P#p22.MYD для различных разбитых таблиц.
При этом после flush tables значение open_files падает, но за минуту-две достигает нескольких десятков тысяч (в итоге - порядка 60К)
Характер нагрузки и вид запросов таковы, что без разбитых таблиц такого наплыва открытых файлов быть практически не может.
Все запросы на вставку в разбитые таблицы имеют конкретное значение для поля разбиения, т.е. при аналогичном select сработал бы partition pruning.
Существуют ли способы избежать неоптимального использования файлов разбитых таблиц при вставке?
Неактивен
Естественно, работает, т.к. вставляется строка всегда только в один раздел.
В Вашем случае, Вы упираетесь или в количество одновременных соединений
на процесс (изменяется ulimitом), или на систему (если на систему, то это, скорее
всего, VDS, и надо менять в хост-системе).
А открывает таблички, скорее всего, какой-нибудь плохой select 
Неактивен
Ясно, будем искать злобный select. Кстати, есть ли способ найти его по какой-либо штатной отчетности mysql (аналогично случаям с полным сканированием таблиц при неиспользовании ключей) ?
И еще вопрос: в случае вычисления значения поля, по которому разбита таблица, в триггере (before, конечно) - сработает pruning?
Отредактированно shutdown (18.05.2009 07:51:28)
Неактивен
Можете включить --log-slow-queries и --log-queries-not-using-indexes, тогда оно
будет писать в журнал все запросы без индексов.
Про триггер: вставка значения происходит всегда в один раздел. Нельзя положить
одно яблоко сразу в два ведра. Нельзя положить одну строчку в два раздела.
Неактивен
paulus написал:
Можете включить --log-slow-queries и --log-queries-not-using-indexes, тогда оно
будет писать в журнал все запросы без индексов.
да, это понятно, спасибо. Просто индекс у меня обыно по дате, а по дню нет - день только для прунинга. Покажет везде что индекс используется (выборка по дате и т.д.), но прунинга при этом не будет 
paulus написал:
Про триггер: вставка значения происходит всегда в один раздел. Нельзя положить
одно яблоко сразу в два ведра. Нельзя положить одну строчку в два раздела.
Ну да, что-то я уже подтупливаю - мозг замылен проблемой
Неактивен
При большом количестве таблиц pruning на выборках может не работать (вроде, была
такая бага). Для конкретного запроса Вы можете посмотреть explain, оптимизатор явно
покажет, из каких разделов производится выборка. Может, в этом дело?
Неактивен
Да похоже не в этом, там 90% - записи на вставку. Селекты только по расписанию - агрегирование статистики. А так льются инсерты. И при этом открытые файлы зашкаливают... Даже когда нет залоченных запросов, т.е. соединения не копятся.
после flush tables успеваю только набрать show status - и уже в open_files 7К набегает. Еще несколько секунд - уже 15К. и так активно растет до 40К. Поработает несколько часов - в районе 80-90К. Странно что не закрывает файлы - нагрузка-то небольшая.
Неактивен
Инсерты льются в разные разделы или в один и тот же? Сколько RPS ориентировочно льется?
Неактивен
Льются в один раздел для каждой таблицы. Везде, где есть разбиение, оно идет по дню года. Т.е. в течение дня - 1 раздел.
status:
Open tables: 256 Queries per second avg: 188.608
по rps это типичная картина.
Неактивен
А таблиц сколько?
Неактивен
в которые активно пишется - 13-14 штук
сервер 5.1.34 MySQL Community Server (GPL) by Remi
пишет ПХП в основном (втупую, без пулов пока) и С++ демон через пул коннектов.
Неактивен
Насколько я понимаю, таблиц тоже сотни. Я все-таки склоняюсь к тому, что их поднимает
какой-нибудь злостный SELECT. Предлагаю закостылить проблему уменьшением open_files_limit
и поиском неприятеля
А вообще — Вы уверены, что Вам нужно 100к таблиц на одной машинке?
Неактивен
Мне точно не нужно 100К да и нет их там. Я бы знал что создал столько
Буду копать еще select. Возможно, в хранимке какой-то упустил.
Неактивен
В общем, вернулся к этому вопросу, и попробовал сделать синтетический тест. В разбитую по дню года таблицу MyISAM лью данные за текущий день. 40 процессов пхп. open_files распухают с 4К (без теста) до 20К.
Убираю партишены. Файлы стоят на 4К!
Снова разбиваю но движок InnoDB. Файлы на 4K, но загрузка системы доходит до 11 по LA против 3-4 в случае с MyISAM.
во всех случаях show full processlist показывает наличие блокировок запросов на вставку (так специально и делал по нагрузке, чтоб к реальности ближе). При менее иненсивной нагрузке, когда не видно блокировок, файлы растут гораздо слабее.
В чем же может быть причина? open_tables на 64 во всех случаях, opened_tables=0.
Отдельный дескриптор на поток для MyISAM ? но тогда бы это не зависело от наличия разбиения (если конечно юзается только 1 файл для нужного раздела таблицы, а не все 366), т.к. в обоих случаях создавался бы 1 добавочный.
тестовая таблица: CREATE TABLE `TestPartitions` ( `VisitDate` date NOT NULL, `Cnt` int(10) unsigned NOT NULL DEFAULT '1', `IdUser` char(32) NOT NULL DEFAULT '', `Version` varchar(10) NOT NULL DEFAULT '', `UserAgent` varchar(1024) DEFAULT NULL, `LogDay` smallint(5) unsigned NOT NULL, `IP` varchar(15) DEFAULT NULL, PRIMARY KEY (`IdUser`,`VisitDate`,`LogDay`,`Version`), KEY `Version` (`Version`), KEY `VisitDate` (`VisitDate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LINEAR KEY (LogDay) PARTITIONS 366 */ | запрос: Insert into TestPartitions (VisitDate,LogDay,IdUser) values (date(now()),dayofyear(now()),'$rnd') on duplicate key update Cnt=Cnt+1; $rnd - случайное число.
таблица скопирована с реальной, запрос приближен к реальному (просто не все поля задействованы).
Неактивен
Удивительно, что InnoDB вообще работает хоть как-то с 52-байтным основным ключом
Все время процессорное уходит на сортировку этих индексов.
Неактивен
А что-то более конструктивное по проблеме еще будет? 
Или переписывать пхп-шки на С++ с пулом коннектов? Неохотаааа....
Неактивен
Так, попробовал менять кол-во партишенов в тестовой таблице. Вот что получается:
без теста - open_files примерно 3.5К
при 10 партишенах файлы доходят до 5К, при 100 - до 8К, при 366 - до 19К.
Неактивен
Более конструктивное — пересмотреть архитектуру базы. Огромный PK в табличке InnoDB —
это всегда очень-очень медленно. Особенно при вставке. Особенно при наличии других
индексов.
Я не верю, что IdUser правильно иметь тип CHAR(32). Я не верю, что в один ключ нужно
засовывать отдельно дату и день от нее же. Попробуйте выбросить все из этой таблички
и добавить туда только нужные поля.
Неактивен
Данная структура и такой IdUser продиктованы особенностями системы. К тому же предварительные тесты показали, что такой IdUser при вставке-выборке имеет пренебрежимо малую разницу по скорости и по загрузке системы по сравнению с INT (MyISAM).
День и дата в ключе - следствие того, что главный ключ (без дня) нужен для выполнения "on duplicate key update Cnt=Cnt+1" при совпадении оставшихся 3 полей, и того, что таблица разбита по дням года и в результате пришлось впихнуть туда и день как следствие ограничения механизма партишенов.
А в продакшене используется MyISAM, InnoDB я привел только как промежуточный результат тестов с open_files, где InnoDB не приводит к их распуханию. Понятно, что в реале с таким главным ключом это преимущество ничего не даст из-за повышенной загрузки ЦП....
Да и какой повод упрощать структуру? Вы считаете что проблема с open_files вызвана сложностью ключа?
Я же привел результаты тестов одной и той же структуры таблиц с разным разбиением (и без него) и разными движками.
Пытаюсь докопаться до первопричины проблемы. Может она все же кроется в (MyISAM + много партишенов)?
Неактивен
Мне всегда казалось, что просто «большое число» — не повод для переживаний. Повод для переживаний —
«медленно работающая база», «плохо написанный сервис», что-то такое.
Если у Вас MyISAM, плохо сделанная табличка будет работать чуть быстрее, чем InnoDB, но это не повод
делать так... с моей точки зрения.
Что касается количества файлов — да, проблема в количестве табличек. Каждая табличка MyISAM использует
по три файла на каждый раздел, чем больше разделов — тем больше файлов.
Неактивен
paulus написал:
Что касается количества файлов — да, проблема в количестве табличек. Каждая табличка MyISAM использует
по три файла на каждый раздел, чем больше разделов — тем больше файлов.
Проблема в количестве разделов, наверное имеется в виду? Табличек самих несколько штук.
Хорошо, если так, то почему все-таки большое количество разделов является проблемой? Если при вставке задействуется только 1 конкретный раздел? Откуда в тестах зависимость от количества разделов?
Отредактированно shutdown (19.06.2009 00:55:05)
Неактивен
Аааа, Вы хотите меня добить
Ладно, вот testcase:
[aquatica] root test > create table p100 (a int) engine=myisam partition by key(a) partitions 365;
Query OK, 0 rows affected (0,11 sec)
[aquatica] root test > flush tables;
Query OK, 0 rows affected (0,00 sec)
[aquatica] root test > show status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 0 |
+---------------+-------+
1 row in set (0,00 sec)
[aquatica] root test > insert p100 values (10);
Query OK, 1 row affected (0,02 sec)
[aquatica] root test > show status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 730 |
+---------------+-------+
1 row in set (0,00 sec)
MySQL открывает все созданные разделы, и потом вставляет данные в один. По поводу того,
почему он открывает все таблички (при том, что достаточно открыть одну), — можно написать
баг. Подозреваю, что его никто не будет исправлять, т.к. больших чисел никто не боится, а
данные все равно пишутся только в одну табличку:
aquatica:/var/lib/mysql/test# ls p100*.MYD -l | awk '{print $5,$NF}' | egrep -v '^0'
224 p100#P#p186.MYD
Неактивен
paulus написал:
Аааа, Вы хотите меня добить
...............
MySQL открывает все созданные разделы, и потом вставляет данные в один. По поводу того,
почему он открывает все таблички (при том, что достаточно открыть одну), — можно написать
баг. Подозреваю, что его никто не будет исправлять, т.к. больших чисел никто не боится, а
данные все равно пишутся только в одну табличку:
.........
Во! Так бы сразу! И не пришлось бы добивать Вот это и хотел выяснить. Значит, тут только один путь: сокращать значение (кол-во разделов * кол-во одновременных коннектов)...
А зря не боятся больших чисел - кто даст гарантию, что при росте нагрузки или вводе дополнительных разбитых таблиц установленные лимиты не переполнятся?
Ну что ж, спасибо за помощь! Теперь понятно куда двигаться и главное - почему .
Неактивен
Да, и напоследок - а при select, когда срабатывает pruning, имеется подобное расточительство?
К сожалению, нет возможности воспроизвести Ваш тест-кейс, может, Вас не затруднит?
Неактивен
Да, открываются в любом случае все файлики — виртуально то табличка одна.
Неактивен
