Пишу простенький локальный веб-сервер для работы с данными из соцсети VK. Это мой личный учебный проект, чисто для локального использования. Ни с Go ни с Sqlite ранее не работад.
С помощью API выкачиваю данные, затем закладываю их в базу на sqlite.
Я ожидал что все это будет работать очень быстро, а оно реально тормозит.
Скачивание по 1000 записей происходит быстро, а вот добавление в базу (операция "upsert", т.е. добавление или обновление если данные уже есть) — крайне медленно, в среднем по полсекунды на запись. Т.е. просто глазами видно как в консоль раз в полсекунды-секунду выводятся строчки с именами юзеров.
Сделано конечно в лоб. Вот например цикл по массиву скачанной порции данных

for _, member := range members.Items {
    name := member.FirstName + " " + member.LastName
    fmt.Println(name)
    app.UpsertUser(member.ID, name)
}

Вот функция добавления и обновления юзера

func (app *Application) UpsertUser(uid int, name string) {
    user := User{Uid: uid}
    app.db.FirstOrCreate(&user, User{Uid: uid})
    if !(name == "DELETED" && user.Name != "DELETED") {
        user.Name = name
    }
    app.db.Save(&user)
}

Для работы с sqlite используется библиотека GORM.
Откуда такие огромные задержки и как можно ускорить работу с базой?

Здравствуйте, 00011011, Вы писали:

0>Откуда такие огромные задержки и как можно ускорить работу с базой?

Объедини по нескольку измемений в одну транзакцию.

По умолчанию (если явно не управлять транзакциями), sqlite считает транзакцией каждую отдельную команду. При этом она берет на себя ответственность, что результаты транзакции не просто прокрутятся логически, а долетят до диска. Что, как ты понимаешь, дело не быстрое.

Если несколько изменений объединены в одну транзакцию, то вся эта долгая возня с обеспечением синхронизации с диском делится на них на всех. Ускорение может быть на несколько порядков (грубо говоря, одно изменение и 10000 изменений, объединенных в одну транзакцию, занимают довольно сравнимое время).

Я тоже с БД очень давно не работал (30 лет). Поэтому абсолютный чайник.
Что значит — объединить в одну транзакцию ?
Весь массив 1000 записей одной командой insert зафигачить ?
Что-то типа такого:

users := []*User{
  User{Name: "Jinzhu", Age: 18, Birthday: time.Now()},
  User{Name: "Jackson", Age: 19, Birthday: time.Now()},
}

result := db.Create(users) // pass a slice to insert multiple row

result.Error        // returns error
result.RowsAffected // returns inserted records count

Здравствуйте, LaptevVV, Вы писали:

LVV>Я тоже с БД очень давно не работал (30 лет). Поэтому абсолютный чайник.
LVV>Что значит — объединить в одну транзакцию ?
LVV>Весь массив 1000 записей одной командой insert зафигачить ?

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

SVZ>Включить ручное управление транзакциями.
SVZ>Запустить транзакцию.
SVZ>Выполнить инсерты любым удобным способом.
SVZ>Закоммитить транзакцию.
Мне эти слова не помогают сделать это технически.
Слово транзакция для меня только теоретическое — неделимая операция изменения БД.
Я ж говорю — 30 лет "без права переписки"...

Здравствуйте, LaptevVV, Вы писали:

LVV>Я тоже с БД очень давно не работал (30 лет). Поэтому абсолютный чайник.
LVV>Что значит — объединить в одну транзакцию ?
LVV>Весь массив 1000 записей одной командой insert зафигачить ?

Нет. Сказать BEGIN, 10000 INSERT-ов, COMMIT.

Иначе оно будет автоматически BEGIN/COMMIT вокруг каждого INSERT-а вставлять, со всеми вытекающими.

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

P.S. Я б не стал в серьезной системе пользоваться Go-ным драйвером для SQLite, он идет через то место SQLite, которому SQL-в виде текста скармливают, и каждую команду он отдельно компилирует; у SQLite есть другой интерфейс, когда команду отдельно компилируют, отдельно исполняют. Если честно, я никогда не замерял, насколько велик выигрыш в производительности, но компилировать INSERT каждый раз звучит несколько невкусно, IMHO.

Здравствуйте, LaptevVV, Вы писали:

LVV>Слово транзакция для меня только теоретическое — неделимая операция изменения БД.

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

P.S. Я сам не большой знаток баз данных. Но это как-то из общекомпьютерной грамотности понятно (и в несложном эксперименте проверено — сейчас же никто не верит теоретическому рассуждению, не подтвержденному простым экспериментом; мы вступили в эру экспериментального программирования).

Здравствуйте, Валерий Викторович, Вы писали:

SVZ>>Включить ручное управление транзакциями.
SVZ>>Запустить транзакцию.
SVZ>>Выполнить инсерты любым удобным способом.
SVZ>>Закоммитить транзакцию.
LVV>Мне эти слова не помогают сделать это технически.
LVV>Слово транзакция для меня только теоретическое — неделимая операция изменения БД.
LVV>Я ж говорю — 30 лет "без права переписки"...

Не очень понятен вопрос. Интересует апи или что происходит под капотом?

Транзакция либо целиком сохраняется, либо целиком откатывается, это известно даже теоретику.

На уровне апи стартуем командой "begin transaction". Дальше идут команды вставки/модификации.
В конце зовём либо "commit", либо "rollback", если надо все изменения отменить.

Как оно устроено внутри я сейчас уже не вспомню — в потрохах ковырялся давно.
Но зато когда-то реализовал транзакционную модель для структур данных для редактора печатных плат. Там у меня было всё просто: транзакция это объект, в котором есть коллекция указателей на модифицированные контейнеры. Для каждого контейнера есть "журнал" — stream, в который записывается исходное состояние изменяемого объекта и код операции (вставка нового/модификация/удаление).
Если один и тот же объект модифицируется несколько раз, то запись в журнале будет только одна (исходная).

Если транзакция сохраняется, то объект транзакции кладется в список для отката. А если надо отменить, то из "журнала" последовательно вынимаются объекты и восстанавливается исходное состояние.
Получилось очень просто и эффективно. И относительно дешево.

LVV>>Слово транзакция для меня только теоретическое — неделимая операция изменения БД.
LVV>>Я ж говорю — 30 лет "без права переписки"...
SVZ>Транзакция либо целиком сохраняется, либо целиком откатывается, это известно даже теоретику.
Даже теоретику это известно...
SVZ>На уровне апи стартуем командой "begin transaction". Дальше идут команды вставки/модификации.
SVZ>В конце зовём либо "commit", либо "rollback", если надо все изменения отменить.
А, ну вот. Понятно, что смотреть в документации.

LVV>>Что значит — объединить в одну транзакцию ?
LVV>>Весь массив 1000 записей одной командой insert зафигачить ?
Pzz>Нет. Сказать BEGIN, 10000 INSERT-ов, COMMIT.
Pzz>Иначе оно будет автоматически BEGIN/COMMIT вокруг каждого INSERT-а вставлять, со всеми вытекающими.
Вот!
Pzz>Ну, там какой-то API должен быть вокруг этого, я его не помню наизусть.
Ну, разберемся.
Pzz>P.S. Я б не стал в серьезной системе пользоваться Go-ным драйвером для SQLite, он идет через то место SQLite, которому SQL-в виде текста скармливают, и каждую команду он отдельно компилирует; у SQLite есть другой интерфейс, когда команду отдельно компилируют, отдельно исполняют. Если честно, я никогда не замерял, насколько велик выигрыш в производительности, но компилировать INSERT каждый раз звучит несколько невкусно, IMHO.
В Си есть include-вариант sqllite — там всего 3 h-файла
Его через cgo подключить получится, как думаешь?

Здравствуйте, 00011011, Вы писали:

0>Скачивание по 1000 записей происходит быстро, а вот добавление в базу (операция "upsert", т.е. добавление или обновление если данные уже есть) — крайне медленно, в среднем по полсекунды на запись. Т.е. просто глазами видно как в консоль раз в полсекунды-секунду выводятся строчки с именами юзеров.

Не особо разбираюсь в Go и уж тем более в использованной тобою библиотеке, так что по факту ничем не подскажу с этой проблемой. Но на всякий случай уточню, что при нормальном использование (я это делал из C++ и из Rust) sqlite выполняет подобные запросы за микросекунды (а иногда даже за наносекунды, если типы данных соответствующие). Поэтому дело однозначно не в самом sqlite, а в его кривом использование.

P.S. Да, и надеюсь ты не забыл там, что sqlite строго однопоточная?

Здравствуйте, alex_public, Вы писали:

_>P.S. Да, и надеюсь ты не забыл там, что sqlite строго однопоточная?

Разве? Я там мьютексы какие-то видел

Здравствуйте, Marty, Вы писали:

_>>P.S. Да, и надеюсь ты не забыл там, что sqlite строго однопоточная?
M>Разве? Я там мьютексы какие-то видел

Я не в том смысле, что нельзя обращаться из разных потоков, а в том что это будет только замедлять работу, т.к. каждый запрос блокирует всю базу. Это как раз отличие sqlite от "взрослых" СУБД, в которых одновременные запросы могут реально исполняться параллельно (если они конечно не на одну строчку указывают).

Здравствуйте, alex_public, Вы писали:

_>>>P.S. Да, и надеюсь ты не забыл там, что sqlite строго однопоточная?
M>>Разве? Я там мьютексы какие-то видел

_>Я не в том смысле, что нельзя обращаться из разных потоков, а в том что это будет только замедлять работу, т.к. каждый запрос блокирует всю базу. Это как раз отличие sqlite от "взрослых" СУБД, в которых одновременные запросы могут реально исполняться параллельно (если они конечно не на одну строчку указывают).

Окей, но исходная фраза по-моему вполне однозначно трактуется как то, что с sqlite надо работать только из одного потока

Здравствуйте, Marty, Вы писали:

M>Окей, но исходная фраза по-моему вполне однозначно трактуется как то, что с sqlite надо работать только из одного потока

Там есть и такой режим (и в нём соответственно все мьютексы отключены), но по умолчанию включён другой. Там их всего 3: Single-thread, Multi-thread, Serialized. И их можно включать как на стадии компиляции, так и просто при открытие БД.

Но в любом случае, какой бы режим ты не выбрал, обращение из нескольких потоков в лучшем случае замедлит работу (а в худшем будет жопа — это если ты не тот режим выбрал).

Здравствуйте, LaptevVV, Вы писали:

LVV>В Си есть include-вариант sqllite — там всего 3 h-файла
LVV>Его через cgo подключить получится, как думаешь?

Получится. Вот у этого парня же получилось: https://github.com/mattn/go-sqlite3

Но я что-то навскидку не вижу у него компиляции SQLite-вских выражений. Exec() берет на вход строку.

А вот этот парень сделал более забавную штуку, он переписал SQLite на чистом Go, без Си: https://gitlab.com/cznic/sqlite

Я б посмотрел на обе реализации и сравнил.

Здравствуйте, 00011011, Вы писали:

0> Для работы с sqlite используется библиотека GORM.

Выбрось каку.

0> Откуда такие огромные задержки и как можно ускорить работу с базой?

1. Записи нужно вставлять пачками (по 1-10-N тыс.) в рамках одной транзакции.
2. Переключить journal_mode = WAL (ну и вообще там бывают интересные PRAGMA, стоит почитать).
3. Можно еще поэкспериментировать с prepared statement, но там уже есть тонкости и можно получить замедление вместо ускорения.

Пример вставки 100К записей за ~15 секунд (обработка ошибок заменена на паники для упрощения).

package main

import (
    "context"
    "database/sql"

    _ "github.com/mattn/go-sqlite3"
)

const SQLITE_SCHEMA string = `
PRAGMA foreign_keys = ON;
PRAGMA page_size = 32768;
PRAGMA encoding = "UTF-8";
PRAGMA journal_mode = WAL;

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS test (
        key   INTEGER NOT NULL PRIMARY KEY,
        value TEXT    NOT NULL
);

COMMIT;
`

func main() {
    db, err := sql.Open("sqlite3", "file:somefile.db")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    ctx := context.Background()

    if _, err := db.ExecContext(ctx, SQLITE_SCHEMA); err != nil {
        panic(err)
    }

    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        panic(err)
    }

    for i := 0; i < 100000; i++ {
        _, err := db.ExecContext(ctx,
            "INSERT OR REPLACE INTO `test` (`key`, `value`) VALUES (?, ?)",
            i,
            i*i,
        )
        if err != nil {
            tx.Rollback()
            panic(err)
        }
    }

    if err := tx.Commit(); err != nil {
        tx.Rollback()
        panic(err)
    }
}

Здравствуйте, Anton Batenev, Вы писали:

AB>Пример вставки 100К записей за ~15 секунд (обработка ошибок заменена на паники для упрощения).

Что-то как-то медленно. Это получается порядка 150 мкс на запрос. У меня на приблизительно таком же примере (только ещё и с обновлением индекса) было помнится порядка 4 мкс...

Да, но это было на плюсах, а не на Go — возможно там что-то на ffi тратится... Ну и от железа конечно ещё зависит.

Здравствуйте, Pzz, Вы писали:

Pzz>По умолчанию (если явно не управлять транзакциями), sqlite считает транзакцией каждую отдельную команду. При этом она берет на себя ответственность, что результаты транзакции не просто прокрутятся логически, а долетят до диска. Что, как ты понимаешь, дело не быстрое.

Небыстрое, но не пол-секунды же! Это если диск на другом континенте такие задержки

0>> Для работы с sqlite используется библиотека GORM.

AB>Выбрось каку.

Что не так с gorm? Что можешь порекомендовать взамен?