LINUX.ORG.RU

SQLite3 насколько велика фрагментация

 ,


0

3

Допустим, есть база SQLite 3.27.x. В нее ежедневно попадают десятки тысяч записей. Но два раза в сутки (каждые 12 часов) все старые записи, старше недели - удаляются. Носитель - CompactFlash.

Благодаря тому что это CF флэшка, хочется верить, что фрагментация не приведет к замедлению более чем в два-три раза скорости чтения базы (выполняя запросы, не просто копирование файла), если сделать кэш в ОЗУ побольше (есть соответствующая pragma).

Но стоит ли мне бороться с фрагментацией страниц, врубать всякие там vacuum? Есть старая реализация базы данных в виде отдельных файлов самопального текстового формата, наверняка они тоже за годы работы системы тоже будут увеличивать (увеличивали) фрагментацию самой файловой системы. До сих пор никто не жаловался на замедление.

Или при таком режиме, удаляя сразу большой шмат данных за 12 часов, будет очищаться большое сплошное пространство внутри файла и фрагментация на краешках этих временных интервалов будет ничтожной? Фрагментация подозреваю касается лишь БД с произвольно добавляемыми и удаляемыми записями, у меня же всё плоско, монотонно и последовательно.

Данные просто добавляются монотонно последовательно, единственное что происходит в плане редактирования - тупо удаление старых записей за период 12 часов одним махом. В процессе прочистки отдельных таблиц - новые данные не добавляются.

Нет смысла. У SSD скорость не зависит от фрагментации, так как скорость доступа к ячейке памяти не зависит от её адреса. Есть мелкие оговорки - авторы программ дефрагментации дисков нашли способ оптимизировать не файлы, а пустое пространство на них. Все известные мне такие программы - для Windows, и сомневаюсь, что эффект заслуживает внимания, если есть.

Карточка памяти не отличается от SSD по принципу работы, разве что имеет упрощённый контроллер. Это проявляется например в том, что она может не воспринимать команду TRIM. Но производители карточек не сообщают о них, поддерживают ли. В общем, можно выбрать карточку, какая побыстрее.

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

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

Вообще-то, SQLite не славится быстродействием. Но замена её на другую базу требует программирования, трудоёмкость которого мне неизвестна, поэтому ограничиваюсь этим замечанием.

Partisan ★★★ ()
Ответ на: комментарий от Partisan

У SSD скорость не зависит от фрагментации, так как скорость доступа к ячейке памяти не зависит от её адреса

Application launching times increase up to 3 times on 2-year used smartphones

https://www.usenix.org/sites/default/files/conference/protected-files/atc17_s...

anonymous ()
Ответ на: комментарий от Partisan

Вообще-то, SQLite не славится быстродействием.

Вообще-то, SQLite как раз таки и славится быстродействием. Но в определённых пределах, если не насиловать частыми запись-удаление, то у неё просто фантастические характеристики для такого рода продукта.

menangen ★★★★★ ()
Ответ на: комментарий от menangen

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

Но будет фрагментация страниц SQLite3 на диске, какие то будут незаполнены на 100%. Учитывая что записи сами несколько сотен байт и даже килобайт каждая, я ожидаю, что до 25% места будет потеряно со временем.

Можно ли ожидать, что эта величина будет устоявшейся? Например, если первоначальной записи база была гигабайт ровно, то за месяцы эксплуатации, она не вылезет за пределы 1.5 Гбайт?

I-Love-Microsoft ★★★★★ ()
Ответ на: комментарий от Partisan

Вообще-то, SQLite не славится быстродействием

Индексировать тоже надо уметь.

Но замена её на другую базу требует программирования

Это в 99,9% случаев из-за плохой, негодной архитектуры. Так что да, требует-с.

malbolge ★★ ()

Но два раза в сутки (каждые 12 часов) все старые записи, старше недели - удаляются. Носитель - CompactFlash.

vacuum практически не поможет.

десятки тысяч записей

Если размер записи до 100+ кб, то можно вообше не иметь себе мозги с «удалением старых записей». 100Гб флешки тебе хватит более чем на 2 года.

Если очень жаба давит не тереть - страдай и... это.. «кохайся» от изначально неправильно выбранных технологий.

malbolge ★★ ()
Ответ на: комментарий от I-Love-Microsoft

Какова правильно выбранная технология?

Другая.

Сам факт того, что у тебя возникают такие вот, самые элементарные вопросы, какие имеем стыд читать в стартовом сообщении, говорит о качестве решения куда больше, чем любая критика в треде :)

Я знаю о чем пишу, поскольку нахожусь в ровно той же ситуации что и ты. 108 Гб sqlite говна одним файлом есличо.

malbolge ★★ ()
Ответ на: комментарий от malbolge

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

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

I-Love-Microsoft ★★★★★ ()
Ответ на: комментарий от malbolge

Работа в рамках ACID, которое является единственным режимом для SQLite, неизбежно приводит к накладным расходам. В твоем случае для принятия решения нужно принять разобраться с такими условиями, как: - нужна ли гарантия сохранности данных уровня ACID? Если да, то, прежде всего, нужно бесперебойное питание, а там уже обычно фрагментация является не таким важным фактором; - если ACID не нужно, то насколько оно не нужно? Можно использовать базы в памяти и два раза в сутки синхронизировать состояние с диском после удаления старых записей.

108 Гб sqlite говна одним файлом есличо

Нормальное решение, есличо. Репликацию главное настроить, и можно долго счастливо жить. Вот когда за 10 Тб переваливает размер, то есть смысл распределять данные по нескольким машинам, а в этом случае сам по себе SQLite не может дать тот самый ACID.

byko3y ()
Ответ на: комментарий от byko3y

нужна ли гарантия сохранности данных уровня ACID

Допускается потеря данных. Но поломка всего файла базы на пару гигабайт - недопустимо. SQLite такое гарантирует?

нужно бесперебойное питание

Оно есть, специально стоят ИБП на каждой системе. Но кто-то может нажать reset аппаратно, а также если прозевать разряд ИБП - тоже внезапно выключится.

Про память - надо подумать, хороший вариант. На 12 часов может не хватит памяти оперативной, можно будет чаще синхрить.

I-Love-Microsoft ★★★★★ ()
Ответ на: комментарий от byko3y

Хотя вот сам же нашел список «How To Corrupt An SQLite Database File» https://www.sqlite.org/howtocorrupt.html - я так понял, пока файл журнала лежит рядом (пусть он тоже быть может поврежден), то база может отбросить битое. Пишут что специально прицельно тестят на внезапные вырубы питания. Также вероятно тестят на внезапные падения программы, в рамках которой работает SQLite.

I-Love-Microsoft ★★★★★ ()
Последнее исправление: I-Love-Microsoft (всего исправлений: 1)
Ответ на: комментарий от I-Love-Microsoft

Ничего не спасет от современных умных алгоритмов контроллеров SSD. ОС думает, что она fsync сделала, что всё записала, что файлы переходят из одного цельного состояния в другое, а контроллер SSD так не думает, он думает о чем-то своем.
Единственная гарантия - это резервная копия.

byko3y ()
Ответ на: комментарий от byko3y

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

Если учесть что у мну SSD специальные промышленные, то мне кажется эта догадка тем более справедлива, что ничего не должно теряться.

I-Love-Microsoft ★★★★★ ()
Ответ на: комментарий от I-Love-Microsoft

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

byko3y ()
Ответ на: комментарий от Partisan

Вообще-то, SQLite не славится быстродействием. Но замена её на другую базу требует программирования, трудоёмкость которого мне неизвестна, поэтому ограничиваюсь этим замечанием.

Зависит от того, на чём написана прикладная часть. К примеру, QtSql даёт над разными СУБД достаточно сильно унифицированный API, я так рулил из одной программы SQLite и PostgreSQL по выбору пользователя.

Но у ТС, судя по «в нее ежедневно попадают десятки тысяч записей», в базу пишет какой-то демон, вряд ли он на чём-то столь высокоуровневом написан.

hobbit ★★★★★ ()
Последнее исправление: hobbit (всего исправлений: 1)
Ответ на: комментарий от hobbit

в базу пишет какой-то демон, вряд ли он на чём-то столь высокоуровневом написан

Так и есть, на Си под QNX4.25. Не могу сказать что нативный интерфейс Sqlite3 сложный, наоборот он прост как тапок.

I-Love-Microsoft ★★★★★ ()