|
Задавайте вопросы, мы ответим
Вы не зашли.
Ребята, привет всем!
Прошу ваших советов по оптимизации базы.
Есть база данных MyISAM.
Версия MySQL 5.0.77.
Четыре основных рабочих таблицы. Эти четыре таблицы взаимосвязаны между собой.
Проблема в следующем. У меня нет особых познаний в построении индексов, построил несколько индексов согласно тому, как определены связи между полями внутри таблиц. При наличии ~50.000 записей в каждой из таблиц, поиск по ним осуществляется довольно быстро (~30 мс). Но вот загрузка данных в базу работает очень медленно. Загрузка 50.000 записей затянулась на десятки минут, я не стал дожидаться окончания... Другое дело, что при занесении каждой записи выполняется масса дополнительной работы в виде проверки данных (главным образом связано с отсутствием внешних ключей в MyISAM, на хостинг с InnoDB переходить пока не готов). Если прибивать индексы перед загрузкой данных, с последующим восстановлением, то поиск (при проверке данных) будет работать также медленно.
Одним, словом не могли бы вы дать некоторые общие советы для описанной ситуации - как увеличить скорость вставки?
---
База, подразумевается, будет 90% рабочего времени работать на выборку и только 10% на вставку.
Сижу курю explain, analyse, log-slow-queries...
Отредактированно FiMko (19.04.2010 00:48:05)
Неактивен
Из описания проблемы не очень понятно что именно работает медленно. Если речь про саму операцию INSERT, то см. тему http://sqlinfo.ru/forum/viewtopic.php?id=1556
Но, возможно медленно работает логика приложения при проверке данных до вставки. Поясните, что это за проверки. Нельзя ли вставить все данные без проверки, а потом ненужное удалить?
Неактивен
rgbeast написал:
Из описания проблемы не очень понятно что именно работает медленно. Если речь про саму операцию INSERT, то см. тему http://sqlinfo.ru/forum/viewtopic.php?id=1556
Но, возможно медленно работает логика приложения при проверке данных до вставки. Поясните, что это за проверки. Нельзя ли вставить все данные без проверки, а потом ненужное удалить?
Скорее всего логика работает медленно. Я подробнее исследую медленные запросы и пр. и отпишусь подробно 
Неактивен
Попробовал, посмотрел... log-slow-queries ничего не дал - нет ни одного запроса, который выполнялся бы дольше секунды. Чтобы перепроверить себя включил лог для всех запросов, да, действительно "медленных" запросов нет. Procedure analyse() тоже никаких предложений по изменению типов полей не дал, но я и старался использовать минимально возможные типы данных по обстоятельствам. Explain, да, выявил кучу подзапросов для некоторых больших запросов, где не используется индекс. В любом случае приступил к изменению логики. Узкое место в ней.
Вопрос: я хочу хранить несколько значений типа unsigned int (~100 значений) в одной "ячейке" колонки.
1. Какой тип данных выбрать? VARCHAR(1000)?
2. Можете ли вы спрогнозировать, имеет право на существование таблица на ~10 млн. записей с единственной колонкой VARCHAR(1000) (ну и колонкой primary key) и индексом на это поле. Это будет вообще передвигаться?
3. Если ответ на второй вопрос положительный, то, допустим, имею значения полей:
a. 4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,...
b. 429496,429496,429496,429496,429496,429496,429496,429496,...
Будет ли индекс адекватно использоваться для таких данных (напр. индекс по первым 10 символам). Имею в виду, что встречаются разделители, также длина самих значений неодинакова...
Отредактированно FiMko (21.04.2010 11:21:44)
Неактивен
Может быть нет медленных запросов, но запросов слишком много?
1. Можно, если хотите в текстовом виде хранить. Можно BINARY(400) и прямо бинарный массив туда без разделителей помещать
2. Таблица 10 гигабайт существовать может. Не забудьте кодировку таблицы установить в latin1.
3. Индекс будет работать для строкового сравнения. То есть строка "23," будет больше, чем "100,". Если нужно числовое сравнение дополняейте первое число нулями в начале.
Неактивен
rgbeast написал:
Может быть нет медленных запросов, но запросов слишком много?
Запросов тоже немного. Оказалась неудачной структура таблицы. Слова в отдельной таблице, слова надо собирать во фразы. Чтобы собрать во фразу приходилось хранить порядок слов во фразе, идентификаторы слов и пр. и пр. Вот такие вот расходы на сборку разборку очень получаются накладными...
rgbeast написал:
1. Можно, если хотите в текстовом виде хранить. Можно BINARY(400) и прямо бинарный массив туда без разделителей помещать
Вот это очень интересно!
1. Это потом побитовыми операциями разбирать?
2. Как будет правильней в таком случае хранить информацию о начале нового uint?
3. Как индекс работает для таких полей, например если длина значений опять же неодинаковая получилась.
rgbeast написал:
3. Индекс будет работать для строкового сравнения. То есть строка "23," будет больше, чем "100,". Если нужно числовое сравнение дополняейте первое число нулями в начале.
Я так и думал. Плохо, не хочется эти нолики таскать...
Отредактированно FiMko (21.04.2010 11:36:48)
Неактивен
FiMko написал:
Вот это очень интересно!
1. Это потом побитовыми операциями разбирать?
2. Как будет правильней в таком случае хранить информацию о начале нового uint?
3. Как индекс работает для таких полей, например если длина значений опять же неодинаковая получилась.
1. На каком языке приложение? Если на C, то binary это char*, его нужно преобразовать в unsigned int* и будет сразу указатель на массив целых чисел.
2. Такая информация не нужна, каждый uint занимает ровно 4 байта. Нужно отдельно хранить их число.
3. Индекс должен бинарно сравнивать. Он (по идее) будет работать полностью правильно, если последовательность байтов от старшего к младшему. По умолчанию на x86 последовательность обратная - от младшего к старшему, так что нужно будет массив int[100] преобразовать к указателю на char*, переставить байты в каждой четверку и внести в базу как binary-запись.
Неактивен
rgbeast написал:
1. На каком языке приложение? Если на C, то binary это char*, его нужно преобразовать в unsigned int* и будет сразу указатель на массив целых чисел.
3. Индекс должен бинарно сравнивать. Он (по идее) будет работать полностью правильно, если последовательность байтов от старшего к младшему. По умолчанию на x86 последовательность обратная - от младшего к старшему, так что нужно будет массив int[100] преобразовать к указателю на char*, переставить байты в каждой четверку и внести в базу как binary-запись.
А усилиями самого MySQL не получится?
А так серверная часть на PHP, но можно и на Си попробовать, просто это для меня пока будет новый опыт. В смысле применения Си в таких условиях.
Отредактированно FiMko (21.04.2010 12:08:00)
Неактивен
MySQL не удобно. Вам же надо разобрать 200 чисел - на C это мгновение, а на MySQL передавать запрос, получать ответ.
Нужно что-то типа
Неактивен
rgbeast написал:
MySQL не удобно. Вам же надо разобрать 200 чисел - на C это мгновение, а на MySQL передавать запрос, получать ответ.
Нужно что-то типаASCII(SUBSTRING(b,1,1))*2^24 + ASCII(SUBSTRING(b,2,1))*2^16 + ASCII(SUBSTRING(b,3,1))*2^8 + ASCII(SUBSTRING(b,4,1))
Но ведь это ужасно.
В PHP можно использовать http://jp2.php.net/manual/en/function.unpack.php , см. пример http://en.kioskea.net/faq/998-parsing-a … ile-in-php
Если решите писать на C/C++, см. статью http://sqlinfo.ru/articles/info/13.html
Да, очевидно, Си будет наиболее правильным решением и очень полезным опытом Попробую Си, отпишусь о результатах (видимо не очень скоро )
rgbeast, спасибище!!!
---
php unpack не умеет работать с uint
php.net написал:
Note that PHP internally stores integral values as signed. If you unpack a large unsigned long and it is of the same size as PHP internally stored values the result will be a negative number even though unsigned unpacking was specified.
Отредактированно FiMko (21.04.2010 12:34:14)
Неактивен
rgbeast написал:
3. Индекс должен бинарно сравнивать. Он (по идее) будет работать полностью правильно, если последовательность байтов от старшего к младшему. По умолчанию на x86 последовательность обратная - от младшего к старшему, так что нужно будет массив int[100] преобразовать к указателю на char*, переставить байты в каждой четверку и внести в базу как binary-запись.
А всё же не очень понял это высказывание. Как порядок следования может повлиять на индексирование? Где-нибудь можно про это почитать подробнее? Нашел только, что
Некоторые серверы используют порядок следования байт bigEndian, а некоторые — littleEndian. Большинство серверов в Интернете использует порядок следования байт bigEndian, поскольку он является "порядком следования байт в сети". Порядок следования байт littleEndian популярен потому, что используется в архитектуре Intel x86. Необходимо использовать тот порядок следования байт, который соответствует протоколу сервера, отправляющего и получающего данные.
Как всё это может меня затронуть. непонятно...
Неактивен
Сортировка бинарной строки делается так. Сначала сравнивается первый байт, если первый байт больше - строка больше. Если равны - сравнивается второй байт и.т.д. Все это будет нормально работать, если первый байт старший. Вы будете преобразовывать целое число в бинарную 4байтовую строку. По умолчанию она запишется начиная с младшего байта (как если бы числа писались в строку задом наперед).
А вам точно нужна такая упакованная структура данных?
Неактивен
rgbeast написал:
ортировка бинарной строки делается так. Сначала сравнивается первый байт, если первый байт больше - строка больше. Если равны - сравнивается второй байт и.т.д. Все это будет нормально работать, если первый байт старший. Вы будете преобразовывать целое число в бинарную 4байтовую строку. По умолчанию она запишется начиная с младшего байта (как если бы числа писались в строку задом наперед).
Получается для корректного сравнения даных типа binary важно чтобы их длина также совпадала? То есть также придется "дополнять" значения до нужной длины?
rgbeast написал:
А вам точно нужна такая упакованная структура данных?
Ничего хитрее пока придумать не могу... Задача, собственно, следующая: имеем таблицу words (int word_id, varchar(1000) word). Слова собираются во фразы, информацию о которых необходимо хранить. Таблица phrases хранит эту информацию в виде (int phase_id, int word_id, tinyint order_id). Такая схема работает в моем случае очень неэффективно....
Отредактированно FiMko (21.04.2010 17:51:38)
Неактивен
На то они и binary, что они фиксированной длины. int - 4 байта, то есть всегда 4 байта. Если Вы присвоите переменной int значение 5, то процессор будет помнить 3 нулевых байта и один байт, содержащий 5.
Неактивен
rgbeast написал:
На то они и binary, что они фиксированной длины. int - 4 байта, то есть всегда 4 байта. Если Вы присвоите переменной int значение 5, то процессор будет помнить 3 нулевых байта и один байт, содержащий 5.
Я имел в виду, что разные фразы могут состоять из разного количества слов (1, 10, 20,...). Таким образом, очевидно, нужно априори дополнять все фразы до одной длины (скажем 100 слов максимум) для оптимальной работы индекса. Если это так, то снова получается куча избыточной информации.
Отредактированно FiMko (21.04.2010 21:17:31)
Неактивен
Это не обязательно. Можно использовать varbinary, а число слов хранить в отдельной переменной.
Неактивен
rgbeast написал:
Это не обязательно. Можно использовать varbinary, а число слов хранить в отдельной переменной.
Правильно ли я понял, что индекс будет отлично работать независимо от разницы в длине сортируемых данных, а переменная необходима лишь для меня чтобы получить реальные данные?
Не подскажите для развития общего кругозора как это примерно обрабатывает движок - он длину тоже учитывает при сортировке?
Неактивен
Я не очень понимаю как Вам поможет индекс. Он ведь будет сравнивать первое число и только если первое число совпадает, то второе. Индекс всегда в MySQL фиксированной длины, но Вам достаточно сделать его на первые 16 байт например (4 первых числа).
Неактивен
Вопрос по работе с данными BINARY: как осуществлять поиск? Например имею таблицу:
Отредактированно FiMko (22.04.2010 23:24:21)
Неактивен
hex() возвращает не бинарник. Нужно именно сделать бинарную строку. Попробуйте функцию char, но проследите за порядком байт. Тестировать удобно на числе 1299796817, оно в строковом виде выглядит "MySQ", а если от младшего байта, то "SQyM".
Неактивен
Осознал, что способ с binary строками ущербный. В принципе всё сделал, попробовал - на вставку ощутимо быстрее пошло (весь процесс добавления с ~2,5 часов должен произойти по прикидке за ~0,5 часа), но поиск страдает. Что делать если придется искать подстроки, т.е. имеем binary записи "MySQ", "MySQMySQ", нужно выбрать все записи, содержащие "My", тогда остается использовать LIKE "%My%", fulltextsearch или подобные способы. При таких запросах не используются индексы, а если они есть, то скорее еще больше замедляют процесс. Вот и получается, что предыдущий вариант (с хранением id слов и их порядка в таблице "phrases") был в этом отношении успешней. Вариант же с бинарными строками больше похож на мой самый первый вариант с хранением фраз 1 в 1 ("dog", "dog fight", ...), т.е. подряд идущими словами с строке. В таком случае тоже только регулярные выражения, like или fulltextsearch.
К тому же, похоже, varbinary, binary не поддерживают full text index (#1283 - Column cannot be part of FULLTEXT index), ну и опять же сложности отладки при работе с binary данными...
---
rgbeast, поражаюсь как вы умудряетесь подмечать даже такие вещи, как порядок следования байт... Снимаю шляпу...
Отредактированно FiMko (25.04.2010 13:09:23)
Неактивен
