|
Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 1 2
Доброго времени суток!
Проводил недавно замеры с целью увеличения скорости выполнения запросов, не очень понял, как обосновать результат...
Конфигурация:
FreeBSD 7.0-STABLE-200807
mysql Ver 14.12 Distrib 5.0.67, for portbld-freebsd7.0 (amd64)
Данные расположены в БД в 24 таблицах типа MyISAM, в каждой таблице несколько миллионов записей (в пределах 10 миллионов (да, знаю, что это неправильно, но увы, пока вот так)). На эти 24 таблицы создана таблица типа mrg_myisam, которая объединяет эти 24.
Некоторые поля проиндексированы.
Сложил все эти данные в одну таблицу типа myisam (назовем ее full) и стал измерять скорости запросов.
И получилось, что запросы, которые используют индексы, гораздо быстрее выполняются в таблице full, чем в таблице типа mrg_myisam. Те запросы, что не используют индексы (например сумма какого-то поля, или максимальное значения этого поля) выполняются примерно одно и то же время, в таблице full чуть быстрее, а те, что используют индексы, там скорость иногда отличалась в 20-30 раз.
Собственно, вопрос - почему так происходит? Из-за того, что в таблице типа mrg_myisam происходит работа с каждой ее подтабличкой в отдельности?
Подскажите кто знает, плиз.
Неактивен
Отличие в скорости не в 20-30 раз, а ровно в 24 раза 
Происходит именно потому, что у Вас 24 таблицы. И чтение быстрее, потому что
спуститься один раз по индексу быстрее, чем спуститься 24 раза по индексу.
Ну и безындексные запросы не влияет скорость, т.к. они сами по себе медленные,
время на открытие файла пренебрежимо мало.
А вот на вставку данных Ваша таблица общая должна прогибаться куда сильнее,
т.к. сортировка индекса большого сложнее, чем сортировка маленьких индексов.
Неактивен
Добрый вечер, Paulus!
Спасибо за ответ.
> Отличие в скорости не в 20-30 раз, а ровно в 24 раза
Да, логично. Просто я результаты выполнения до секунд округлял :-)).
> А вот на вставку данных Ваша таблица общая должна прогибаться куда сильнее,
> т.к. сортировка индекса большого сложнее, чем сортировка маленьких индексов.
Да, конечно :-). Использую отложенное индексирование - данные копятся в часовую таблицу, потом, с началом следующего часа данные копятся в другую, а первая индексируется, иначе mysql сразу захлебывается.
Собственно, вопрос возник в связи с тем, что хочу отойти от текущей структуры хранения с mrg_isam и кидать все в суточную таблицу. Конечно же, индексировать ее - после того, как данные туда перестали писаться.
С запросами понятно...
Paulus, подскажите еще в контексте вопроса пожалуйста - есть ли какое-то разумное ограничение на количества записей в таблицах (в смысле, что если количество записей в таблице превышает какой-то порог (а то у меня был пик - 147 миллионов в таблице суточной), то стоит призадуматься насчет организации хранения данных)?
Неактивен
Единственный разумный аргумент в данном случае — это производительность. Если
Вас не устраивает производительность — то стоит призадуматься
Обычно при таких объемах нет смысла делать индексы на основной лог-таблице,
тогда ее объем не так критичен, данные просто дописываются в хвост (и можно
использовать какой-нибудь подходящий движок типа archive). А для ежеминутной
статистики можно сделать рядышком агрегирующие таблички, которые будут считать
статистику (в т.ч. и триггерами).
Неактивен
чтение быстрее, потому что спуститься один раз по индексу быстрее, чем спуститься 24 раза по индексу.
А почему не влияет, что 24 индекса - маленькие, а один - большой?
Неактивен
LazY написал:
А почему не влияет, что 24 индекса - маленькие, а один - большой?
Должно влиять логарифмически. Представь, что нужно посмотреть слово в 24 словарях или в одном большом. В маленьком словаре нужно ~5 перелистываний, чтобы найти слово, а в большом ~7. Но может оказаться, что основное время занимает не листание, а встать и взять словарь с полки. Тогда будет ровно 24.
Неактивен
Добрый вечер!
> Обычно при таких объемах нет смысла делать индексы на основной лог-таблице...
Идея ясна, правда, видимо придется смириться с тем, что запросы к такой таблице будут работать зело долго...
rgbeast, уточните пожалуйста, что значит "должно влиять логарифмически". Не очень понятно - от какой именно величины логарифм.
Неактивен
Если грубо, то время поиска по индексу пропорционально логарифму числа строк в таблице. Например, в словаре толщиной 500 страниц, вы открываете нужное слово за 7 открываний. Если вам дать словарь толщиной 1000 страниц, то вы откроете его на середине и поймете в какой из половин находится слово. Далее потребуется 7 открываний, как раньше (так как в половине словаря 500 страниц). Размер словаря удвоился, а время поиска увеличилось на единицу - это и есть логарифмическая зависимость.
Неактивен
А, ну то есть дихотомия ). Понятно, спасибо!
Неактивен
См., кстати, анимацию по работе индекса: http://people.ksp.sk/~kuko/bak/index.html
Неактивен
Благодарствую, rgbeast! Очень показательная анимашка ).
Разрешите еще уточнить такой момент (все касательно индексирования больших таблиц 
) - есть ли зависимость времени индексирования таблицы от количества записей в ней (я имею в виду тот случай, когда на безиндексную таблицу действуем alter table'ом, добавляя индексы). Ряд экспериментов (на таблице из 10 000,100 000, 1 000 000, 10 000 000, 100 000 000) показал, что зависимость нелинейная (что логично), а какая она (если вообще есть и описывается элементарными функциями)?
Неактивен
Когда создаете индексы имейте в виду этот комментарий: http://webew.ru/posts/2699.webew#2705
(все может резко замедлиться, если кончается место в каталоге /tmp/)
Какая зависимость я не знаю, если у Вас есть данные, то можете построить график и прикрепить здесь. Я бы ожидал, что хуже, чем линейная, но лучше, чем N^2, то есть что-то типа N*log(N). Учитывайте, что на малых размерах еще играет роль дисковый кэш.
Неактивен
Спасибо за ссылку! Это, пока, тьфу-тьфу, не грозит, места в том разделе, где mysql'евский tmp достаточно.
Учитывая то, что таблице 16 полей, причем размеры от 1 до 4-х байт, а индексировать нужно (исходя из задачи) несколько полей, то, видимо, действительно стоит построить график. Единственно, что хотелось бы уточнить - в том случае, если нужно индексировать несколько полей сразу - как быстрее будет работать (если вообще есть разница)- если одним запросом alter table задать индексирование всех нужных полей сразу или несколькими запросами подряд, в каждом из которых происходит индексирование отдельного поля.
P. S. Я сильно уехал с той темы, с которой начинал. Григорий Игоревич, скажите, мне следует создать отдельную тему в форуме или можно продолжить здесь?
Неактивен
Можно в этой теме продолжать, все равно речь пока о больших таблицах, но я ее перенесу в раздел "Оптимизация".
Будет быстрее создавать сразу несколько индексов. Хотя бы потому, что ALTER TABLE требует копирования всей таблицы во временный файл с другим именем. Если ALTER TABLE один, то эта операция будет выполняться один раз. Индексы скорее всего будут создаваться последовательно, но Вы можете это проверить
Неактивен
К сожалению, последовательно Бага описана на трекере в 2007 году
Неактивен
Доброго времени суток!
rgbeast написал:
Какая зависимость я не знаю, если у Вас есть данные, то можете построить график и прикрепить здесь. Я бы ожидал, что хуже, чем линейная, но лучше, чем N^2, то есть что-то типа N*log(N). Учитывайте, что на малых размерах еще играет роль дисковый кэш.
Померил на сервере, где mysql не выполняет текущих задач (т. е. не загружен).
Таблица:
CREATE TABLE `speed` (
`srcipaddress` int(4) unsigned default NULL,
`dstipaddress` int(4) unsigned default NULL,
`nexthop` int(4) unsigned default NULL,
`sif` tinyint(1) unsigned default NULL,
`dif` tinyint(1) unsigned default NULL,
`pkts` int(4) unsigned default NULL,
`octets` int(4) unsigned default NULL,
`tstart` datetime default NULL,
`tend` datetime default NULL,
`srcp` smallint(2) unsigned default NULL,
`dstp` smallint(2) unsigned default NULL,
`p` smallint(2) unsigned default NULL,
`PointX` smallint(5) unsigned default NULL,
`PointY` smallint(5) unsigned default NULL,
`dbindex` int(10) unsigned default NULL,
) ENGINE=MyISAM
Добавлял индексы на поля
tend,sif,srcipaddress,srcp,dif,dstipaddress,dstp
запросом
ALTER TABLE `speed` ADD INDEX Index_1(tend),ADD INDEX Index_2(sif),ADD INDEX Index_3(srcipaddress),ADD INDEX Index_4(srcp),ADD INDEX Index_5(dif),ADD INDEX Index_6(dstipaddress),ADD INDEX Index_7(dstp);
Вот такая табличко получилась:
Второй и третий столбец - время, за которое выполнялось индексирование
Количество записей Время (в секундах) Время (в минутах)
250000 2 0,033333333
500000 6 0,1
1000000 18 0,3
2000000 36 0,6
4000000 73 1,216666667
8000000 156 2,6
16000000 386 6,433333333
32000000 1029 17,15
64000000 2079 34,65
128000000 4329 72,15
256000000 10176 169,6
От итерации к итерации временная длительность увеличивается "чуть-чуть" больше, чем в два раза, но на N*log(N) все же непохоже, так ведь?
Отредактированно deadka (26.02.2010 12:26:07)
Неактивен
Похоже на N log(N) log(N), см. рисунок. Но, конечно, зависимость не должна быть математически точной.
Неактивен
Да, похоже. Проверю еще на другом сервере. Спасибо!
Неактивен
Степенной функцией x^1.17 фитируется еще лучше. Диапазон такой, что отличить конкретные функции нельзя.
Неактивен
Григорий Игоревич, я правильно понял, что степенная функция и сама степень (1.17) были получены, how to say it... эмпирически?
Равно как и константные коэффициенты, присутствующие на графике (gnuplot, да?).
Неактивен
В гнуплоте можно и фит сделать двухпараметрический. Правильный ответ: y = 1.472e-06 x^1.169 (я почти угадал на глаз). Все равно это черный ящик, который ведет себе медленнее N, но быстрее N sqrt(N). Само исследование эмпирическое, и нет гарантии, что дальше поведение будет таким же.
Неактивен
Да-да, я как раз в контексте дальнейшего и интересовался (с точки зрения формулы). Впрочем время индексирования 256 000 000 уже великовато (в контексте архитектуры того, что я делаю) , видимо надо индексированные таблицы (по-крайней мере таблицу ) делать не столь большими. Только поясните пожалуйста - чем обусловлен N*sqrt(N), точнее sqrt(N) (с точки зрения оценки времени индексирования).
Неактивен
Мы видим, что вроде быстрее, чем N ln N, значит степень, причем меньше 2. N*sqrt(N) просто субъективно легко запомнить и это первое, что придет в голову проверить, если без гнуплота. Все зависит от того, что хотите их этих чисел извлечь.
Неактивен
Понял.
Извлечь из этих чисел хочу исключительно верхнюю границу временной оценки - чтобы представлять, сколько примерно будет длиться индексирование перечисленных полей для таблицы с приведенной структурой, с учетом того, что в таблице N записей.
Неактивен
Если нужна экстраполяция за пределы диапазона, но нужно сделать фит разными формулами, которые придут в голову. Разница в предсказаниях и будет ошибкой оценки.
Неактивен
Страниц: 1 2
