Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Оптимизация по скорости под большие объемы. / 19 сообщений из 19, страница 1 из 1
16.10.2011, 20:06
    #37484302
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
Вопрос к практикам. Если нет практиков, сойдут и теоретики. :)

Хотелось бы услышать ваши мнения по оптимизации по скорости для SQLite (версия не ниже 3.7.0). В основном интересует использование прагм и режимов журнала.
Собственно, прагмы: http://sqlite.org/pragma.html#toc
Журнал: http://sqlite.org/pragma.html#pragma_journal_mode


Пока по логике и экспериментам установил, что наиболее значим размер кеша страниц. У кого-нибудь выработалась формула расчета необходимого размера кеша?

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

Какие режимы журнала оптимальнее при монопольном и множественном доступах?

В режиме WAL имеет ли смысл изменение wal_autocheckpoint? А отключение?

Имеет ли смысл увеличивать размер страницы при работе базы на ПК?
Типичный размер кластера/блока сейчас 4 кБ. Не понятно, почему по дефолту стоит размер страницы 1 кБ. Да и страницы виртуальной памяти у нас по 4 кБ.
Думаю, для хранения множества TEXT/BLOB размерами больше страницы тоже стоит увеличить страницу до размера среднего BLOB-а.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2011, 22:28
    #37484396
MBG
MBG
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
RXL...пробовал на объемах порядка 2 ГБ, 8 млн. строк...

У вас типичная небольшая база, обычно на таких объемах ничего и оптимизировать-то не надо. Если есть конкретные проблемы/вопросы - озвучьте их.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2011, 00:32
    #37484468
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
MBG,

Меня интересуют "все" аспекты. Знакомство с SQLite у меня началось недавно и хотелось бы составить для себя обобщенный набор рецептов для той или иной ситуации. Раньше косо смотрел на эту базу, пока не попробовал с ней работать и захотелось узнать получше.

В данный момент рассматриваю оптимизацию под заливку данных в базу, но хотелось бы узнать и про оптимизацию под остальные ситуации. К примеру, ведение лога, накопление результатов по триггеру и частое чтение результатов накопления: ситуация с частой выборкой в условиях частых вставок/модификаций (скажем, отношение 1:2 ... 1:1 и порядка 10-30 вставок/модификаций в секунду).

Еще, сейчас тестирую с заливкой одних и тех же данных в различных условиях: размер страниц, режимы лога, режимы синхронизации, наличие или отсутствие индексов - ищу зависимости, подбираю более быстрые варианты. Подметил плавное, но постоянно падение скорости вставки этих 2 ГБ от объема получаемой базы: от 7 МБ/с в начале до 300 кБ/с к концу. Зато, скорость выборки по индексу меня поразила: в условиях одной машины, одних и тех же данных и аналогичного индекса SQLite отработала вдвое быстрее, чем MySQL+Innodb.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2011, 00:38
    #37484471
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
Еще подмеченный аспект: при страницах 4 кБ одни и те же данные занимают на 9% места меньше, чем при страницах 1 кБ. Может мелочь, но мне интересно.


MBGЕсли есть конкретные проблемы/вопросы - озвучьте их.

В данный момент у меня конкретики нет. Изучаю пока.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2011, 17:06
    #37485438
MBG
MBG
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
RXL,

См. параметры моей сборки, например. Страница, скажем, 8к разумнее для "типичных" применений и т.д. Тесты и заметки есть у меня в блоге
http://geomapx.blogspot.com/search?q=sqlite
Патчи и модули см. в моем репозитории
http://sqlite.mobigroup.ru/

Ну и плюс еще море всего в сети найдется, и в рассылке sqlite-users немало интересного проскакивает. Оптимизация же БД зависит от очень многих вещей - например, чтобы быстро работать с базой в десятки и сотни гиг, придется архитектуру приложения соответствующую делать, если же у вас пара гигабайт - вероятно, достаточно выставить разумные прагмы (дисковая страница, кэш, версия формата БД) и, при необходимости конкурентности, включить режим WAL.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2011, 18:56
    #37485667
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
MBG,

Спасибо.
Тему я уже читал. Пойду дальше по ссылкам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.10.2011, 08:00
    #37486040
MBG
MBG
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
На тему выбора размера дисковой страницы интересно посмотреть "Internal Versus External BLOBs in SQLite":

http://www.sqlite.org/intern-v-extern-blob.html

Учтите, что для других сценариев использования результаты несколько отличаются (как правило, выбор все равно стоит между страницами 4к/8к/16к).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.10.2011, 10:45
    #37486282
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
MBG,

Я написал тест массовой вставки: данные находятся в MySQL+MyISAM (2 ГБ, 8 млн строк) и через perl-скрипт перегоняются в SQLite. В таблице есть целые столбцы, и небольшие текстовые (менее 1 кБ). Прогоняю по разным режимам журнала с разными страницами. Памяти на машине (1.5 ГБ) явно не достаточно для кеширования данных. Т.ч. каждый прогон работает в одинаковых условиях с одним и тем же патерном. Пока итоги рано подводить, но есть некоторая закономерность:
1. Выключение журнала резко снижает производительность и использовать его можно только с отключенной синхронизацией или с базой в памяти. Данный тест я повторю еще раз для чистоты, т.к. результат не соответствует ожиданию.
2. Для режимов без журнала или с журналом MEMORY оптимальнее страницы 4 кБ.
3. Для журнала на диске страницы 4 кБ и 8 кБ близки, но 8 кБ немного быстрее.
4. Прочие страницы дают потерю производительности. Причем, 16 кБ - хуже, чем 1 кБ. На BLOB не проверял - такой тест тоже надо будет сделать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.10.2011, 12:02
    #37486469
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
Есть, кстати, у меня оракловая база с картинками: размер 9 ГБ, около 100 тыс. строк. Если не надоест, сделаю тесты и для BLOB-ов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.10.2011, 22:18
    #37487847
MBG
MBG
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
RXLЕсть, кстати, у меня оракловая база с картинками: размер 9 ГБ, около 100 тыс. строк. Если не надоест, сделаю тесты и для BLOB-ов.

Если уж тестировать, то при размере базы, более чем в 10 раз превышающем объем ОЗУ. Я тестировал на 100М записей по 1к и 100к записей по 1М - то есть в обоих случаях размер базы 100Гб+, данные генерировались случайным образом. Для задач чтения и записи - оптимальны разные дисковые страницы, разумный компромисс был равен 8к.

Вставка миллиарда записей только с PK не дает измеримой деградации скорости, с обычным индексом - зависит от данных, часто есть неприемлемая для больших баз деградация скорости вставки (с выборками все ок), с FTS индексом - нет деградации вставки, но на 435М записей вылетал сегфолт (думаю, что баг я тогда нашел и сейчас все будет ок, но этот тест я не повторял). На десктопном железе - до 50М особых проблем нет, если воткнете SSD (кардинально уменьшает время модификации индексов при вставке) - то и сотни миллионов записей обработаете. С год назад я репортил баг, возникающий при некоторых условиях для баз более 5Гб размером (сейчас соответствующий тест есть в апстриме), так что сильно старые версии SQLite для таких баз не стоит брать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2011, 11:59
    #37490362
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
MBG,

Как раз такие тесты и провожу сейчас: 10 ГБ исходных данных (база может быть вдвое больше) и 1.5 ГБ оперативки. Вероятно, тесты у меня протянутся до конца недели. После напишу отчет. Уже есть интересные особенности выделения страниц под данные в районе page_size/2.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2011, 12:07
    #37490386
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
Про индексы оно понятно: чем их больше и чем они длиннее, тем медленнее обрабатываются. Ну и размер кеша на индексы сильно влияет. Т.к. универсальных тестов не бывает, то для чистоты эксперимента я не использую индексы. Известно же, что много-много быстрее удалить индексы, залить данные и после создать индексы заново. В обычных же ситуациях столь большие объемы вставок маловероятны (либо для них был выбран не подходящий инструмент).

Думаю, что самый-самый быстрый способ вставки будет через временные таблицы в памяти: вставить в таблицу по N строк и скопом перенести их в базу на диске через "INSERT SELECT". Такое я еще не тестировал.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2011, 12:12
    #37490400
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
RXL,

Я использую версию 3.7.0.1. К сожалению, в CentOS 5 нет более свежей. Собирать сам не хочу - это нарушает принцип обновления из репозитория и потом может выйти боком.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2011, 17:07
    #37491049
MBG
MBG
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
RXLИзвестно же, что много-много быстрее удалить индексы, залить данные и после создать индексы заново.

Не везде и не всегда! В эскулайт такая оптимизация сделана только в последнем релизе, см.
http://www.sqlite.org/draft/releaselog/3_7_8.html

Если тестировать эскулайт именно как бэкенд хранения - до миллиарда записей все хорошо :) Деградация скорости вставки возникает только из-за индексов и может быть заметна уже на десятке миллионов записей. Чувствуете разницу? Так что без индексов тестировать смысла мало.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2011, 17:29
    #37491095
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
MBG,

Кстати, на счет поиска по индексам. Как я прочитал в доках, уровень дерева у SQLite фиксированный: root page -> internal pages (interior pages) -> leafs. Появляются overflow страницы (цепочки страниц на одном уровне), но сам уровень косвенности не растет. Т.е. при больших количествах строк (от 10^6) не должно быть большой разницы в скорости поиска между смежными порядками. Особенно если индекс организованный. Хорошо бы придумать тест для проверки. У вас нет идей на этот счет?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2011, 22:14
    #37491420
MBG
MBG
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
RXLMBG,

Кстати, на счет поиска по индексам. Как я прочитал в доках, уровень дерева у SQLite фиксированный: root page -> internal pages (interior pages) -> leafs. Появляются overflow страницы (цепочки страниц на одном уровне), но сам уровень косвенности не растет. Т.е. при больших количествах строк (от 10^6) не должно быть большой разницы в скорости поиска между смежными порядками. Особенно если индекс организованный. Хорошо бы придумать тест для проверки. У вас нет идей на этот счет?

А чем не устраивают тесты, выложенные в моем блоге? Вот тестирование скорости индексной выборки на таблицах 1М, 10М, 100М записей:
Тестирование SQLite 3.6.17-mobigroup.2 на больших таблицах

А вот другая сторона проблемы - скорость вставки:
Degradation of indexing speed in SQLite 3.6.20
Как видим, индексирование большой таблицы могло быть проблемой до выхода последнего релиза.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.10.2011, 21:33
    #37493200
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
MBG,

Читается плохо и там показаны только значения user и system, но не полное время, куда входит ожидание IO.
Вот вам пример:

Код: plaintext
1.
2.
3.
4.
5.
$ time sleep 2

real    0m2.003s
user    0m0.000s
sys     0m0.001s

Хотя прошло две секунды, user - 0 и system - 1 мс. При ожидании процесс не использует процессорное время.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.10.2011, 23:13
    #37493279
MBG
MBG
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
RXLMBG,

Читается плохо и там показаны только значения user и system, но не полное время, куда входит ожидание IO.
...


В данном случае не используется ни IO ни CPU, и здесь вовсе нет "ожидание IO". Замерять время простоя - бессмысленно, но, если хотите, никто не мешает делать
Код: plaintext
time echo "select ..." | sqlite3 test.db
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.10.2011, 23:38
    #37493307
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация по скорости под большие объемы.
MBG,

Понятно, что в "sleep" нет IO. Я только для примера привел.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Оптимизация по скорости под большие объемы. / 19 сообщений из 19, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 174ms
Закрыть
start [/forum/topic.php?fid=54&tablet=1&tid=2009107]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 67ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 11ms
total:  174ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]