Хабрахабр

Go: ускоряем выборку больших таблиц из MySQL

Я использую Go для написания рекламной сети вот уже почти год. Разработку веду на сервере Intel i7-7700, 16Gb RAM, 256Gb SSD. И в скрипте который выполняется раз в сутки появилась задача выбрать все показы за прошедшие сутки и пересчитать на этой основе статистику за день сразу по нескольким объектам (сайт, кампания, баннер).

По идиомам Go делается всё достаточно тривиально:

type Hit struct { siteID, zoneID, poolID, mediaID, campaignID uint32
}
rows, err := db.Query("SELECT siteID, zoneID, poolID, mediaID, campaignID FROM "+where)
if err != nil { log.Fatal("Query fail", err)
}
defer rows.Close()
var ( c uint32 h Hit
)
for rows.Next() { rows.Scan(&h.siteID, &h.zoneID, &h.poolID, &h.mediaID, &h.campaignID) campCounter.Inc(h.campaignID) siteCounter.Inc(h.siteID) zoneCounter.Inc(h.zoneID) poolCounter.Inc(h.poolID) mediaCounter.Inc(h.mediaID) c++
}
if err := rows.Err(); err != nil { log.Fatal("Scan Rows err", err)
}
log.Println(name, " ", c, " ", where, "in", time.Since(now))

Всё работает. И скорость выборки 36 секунд для почти 56 миллионов записей.

hit_20180507 55928930 time BETWEEN 1525640400 AND 1525726799 in 36.331342451s

Под капотом анализатора производительности go tool pprof видим примерно следующее

flat flat% sum% cum cum% 7130ms 18.32% 18.32% 10800ms 27.75% runtime.mallocgc 2380ms 6.12% 24.43% 5710ms 14.67% fmt.(*pp).doPrintf 2140ms 5.50% 29.93% 13300ms 34.17% github.com/go-sql-driver/mysql.(*textRows).readRow 1800ms 4.62% 34.56% 2170ms 5.58% runtime.mapassign_fast32 1700ms 4.37% 38.93% 1700ms 4.37% runtime.heapBitsSetType 1170ms 3.01% 41.93% 36350ms 93.40% main.loadHits 1110ms 2.85% 44.78% 8500ms 21.84% runtime.convT2Eslice 1070ms 2.75% 47.53% 1970ms 5.06% fmt.(*fmt).fmt_integer 950ms 2.44% 49.97% 1380ms 3.55% github.com/go-sql-driver/mysql.readLengthEncodedString 930ms 2.39% 52.36% 1060ms 2.72% runtime.freedefer 930ms 2.39% 54.75% 930ms 2.39% runtime.mapaccess1_fast32 910ms 2.34% 57.09% 2070ms 5.32% runtime.deferreturn 860ms 2.21% 59.30% 1220ms 3.13% runtime.scanobject

Можно заметить что мы работаем в текстовом протоколе MySQL по mysql.(*textRows).readRow, соответственно пришедшие строки Scan конвертирует в uin32 типы. Но на первом месте по времени у нас функция выделения памяти.

Что тут можно ускорить?

Что же. Случайно на глаза мне попался тип RawBytes который гарантирует что байты из драйвера базы данных будут переданы пользователю без копирования. RawBytes и переконвертируем сами потом []bytes в uint32 с помощью наскоро написанной функции bu2, выбросив проверки на ошибки (ведь вы не станете их искать в пришедшем от БД тексте, да?) Попытаемся извлечь Scan в промежуточную структуру с полями sql.

func b2u(b []byte) uint32 return n
}
type HitRaw struct { siteID, zoneID, poolID, mediaID, campaignID sql.RawBytes
}

В итоге время обработки сократилось до 28 секунд, что дает уже чтение 2 миллионов строк в секунду!

И профайлер даёт уже такую картину

4690ms 15.68% 15.68% 7630ms 25.51% runtime.mallocgc 2400ms 8.02% 23.70% 2700ms 9.03% runtime.mapaccess1_fast32 1660ms 5.55% 29.25% 1660ms 5.55% runtime.heapBitsSetType 1640ms 5.48% 34.74% 28110ms 93.98% main.loadHits 1590ms 5.32% 40.05% 1860ms 6.22% runtime.mapassign_fast32 1300ms 4.35% 44.40% 12450ms 41.62% github.com/go-sql-driver/mysql.(*textRows).readRow 1140ms 3.81% 48.21% 2090ms 6.99% runtime.deferreturn 1060ms 3.54% 51.76% 1470ms 4.91% github.com/go-sql-driver/mysql.readLengthEncodedString 1050ms 3.51% 55.27% 1050ms 3.51% main.b2u 1040ms 3.48% 58.74% 1130ms 3.78% database/sql.convertAssign 910ms 3.04% 61.79% 8640ms 28.89% runtime.convT2Eslice 730ms 2.44% 64.23% 2540ms 8.49% database/sql.(*Rows).Scan

Что же, неплохо как для начала. Далее я полез изучать драйвер MySQL, который как оказалось написан специально для Go и реализует низкоуровневые протоколы сам, с помощью сокетов. И вот второй протокол MySQL оказался бинарным. Что в теории дает более быструю генерацию ответа MySQL-сервера. Соответственно и драйвер, меньше вызывает функций конвертаций текст-целое число. Чтобы задействовать бинарный протокол надо перейти от db.Query до db.Prepare — stsm.Query — минимум изменений исходного кода и вуаля — 26.70 секунд выполнения.

stmtOut, err := db.Prepare(sqlQ)
defer stmtOut.Close()
if err != nil { log.Fatal("prepare", err, sqlQ)
}
rows, err := stmtOut.Query()
if err != nil { log.Fatal("query", err, sqlQ)
}
defer rows.Close()

Профилировщик показывает, что протокол уже действительно бинарный по (*binaryRows).readRow, но при чтении в RawBytes всё равно проходит конвертация в текст, а потом обратно.

flat flat% sum% cum cum% 2910ms 10.79% 10.79% 3310ms 12.27% runtime.mallocgc 2280ms 8.45% 19.24% 2600ms 9.64% runtime.mapaccess1_fast32 1960ms 7.27% 26.51% 7070ms 26.21% database/sql.convertAssign 1530ms 5.67% 32.18% 1810ms 6.71% runtime.mapassign_fast32 1460ms 5.41% 37.60% 6660ms 24.69% github.com/go-sql-driver/mysql.(*binaryRows).readRow 1420ms 5.27% 42.86% 26680ms 98.92% main.loadHits 1210ms 4.49% 47.35% 3010ms 11.16% strconv.AppendInt 1100ms 4.08% 51.43% 1320ms 4.89% strconv.formatBits 950ms 3.52% 54.95% 1650ms 6.12% runtime.deferreturn 820ms 3.04% 57.99% 820ms 3.04% reflect.ValueOf 810ms 3.00% 60.99% 4120ms 15.28% runtime.convT2E64 750ms 2.78% 63.77% 4240ms 15.72% database/sql.asBytes

Давайте же Scan делать сразу в uint32 структуры! Уже ничего не должно конвертироваться — только преобразование целое-целое.

827306314s То есть замедление вообще ужасающее. Итог оказался печальным — 49. В чем же дело? Самый тупящий вариант из всех возможных, несмотря на хорошую теоретическую основу для самого быстрого результата.

Смотрим:

4620ms 9.22% 9.22% 29230ms 58.32% database/sql.convertAssign 3610ms 7.20% 16.42% 4010ms 8.00% runtime.mallocgc 3010ms 6.01% 22.43% 8610ms 17.18% reflect.(*rtype).Name 2980ms 5.95% 28.37% 5600ms 11.17% reflect.(*rtype).String 2770ms 5.53% 33.90% 3330ms 6.64% runtime.mapaccess1_fast32 2570ms 5.13% 39.03% 2570ms 5.13% reflect.ValueOf 1760ms 3.51% 42.54% 1980ms 3.95% runtime.mapassign_fast32 1640ms 3.27% 45.81% 6630ms 13.23% github.com/go-sql-driver/mysql.(*binaryRows).readRow 1540ms 3.07% 48.88% 3870ms 7.72% strconv.FormatInt 1240ms 2.47% 51.36% 49600ms 98.96% main.loadHits 1150ms 2.29% 53.65% 1150ms 2.29% reflect.Value.Type 1120ms 2.23% 55.89% 1120ms 2.23% reflect.Value.Elem 1070ms 2.13% 58.02% 30950ms 61.75% database/sql.(*Rows).Scan 1070ms 2.13% 60.16% 1070ms 2.13% strconv.ParseUint

Судя по наличию strconv.ParseUint — преобразование 2 типов выполняется через строку! Серьезно? reflect-преобразования вышли на первые строчки по времени выполнения. Не зря Роб Пайк говорит об осторожном использовании рефлексии. Можно натворить дел.

Scan делаем в структуру Изучив драйвер MySQL я наткнулся на то, что с бинарного протокола все данные преобразуются в int64 — попробуем извлечь из этого пользу.

type HitRaw struct { siteID, zoneID, poolID, mediaID, campaignID int64
}
...
h.siteID = uint32(raw.siteID)
h.zoneID = uint32(raw.zoneID)
h.poolID = uint32(raw.poolID)
h.mediaID = uint32(raw.mediaID)
h.campaignID = uint32(raw.campaignID)

Результат получился 33.98 сек. С таким раскладом по функциям

3600ms 10.48% 10.48% 14360ms 41.79% database/sql.convertAssign 2860ms 8.32% 18.80% 3340ms 9.72% runtime.mallocgc 2560ms 7.45% 26.25% 2920ms 8.50% runtime.mapaccess1_fast32 1660ms 4.83% 31.08% 6730ms 19.59% github.com/go-sql-driver/mysql.(*binaryRows).readRow 1540ms 4.48% 35.56% 33970ms 98.86% main.loadHits 1410ms 4.10% 39.67% 1690ms 4.92% runtime.mapassign_fast32 1340ms 3.90% 43.57% 1340ms 3.90% reflect.ValueOf 1290ms 3.75% 47.32% 4010ms 11.67% reflect.Value.Set 940ms 2.74% 50.06% 15960ms 46.45% database/sql.(*Rows).Scan 900ms 2.62% 52.68% 900ms 2.62% reflect.Value.Elem 840ms 2.44% 55.12% 840ms 2.44% reflect.Value.Type 840ms 2.44% 57.57% 1500ms 4.37% runtime.deferreturn 810ms 2.36% 59.92% 810ms 2.36% reflect.directlyAssignable 760ms 2.21% 62.14% 760ms 2.21% runtime.getitab 730ms 2.12% 64.26% 900ms 2.62% reflect.Value.assignTo 720ms 2.10% 66.36% 4060ms 11.82% runtime.convT2E64

Видно, что sql.convertAssign уменьшает всю выгоду от использования бинарного протокола. И теперь данные не копируются через текст, но внутри reflect определить что int64 можно копировать в переменную int64 пользователя — ещё довольно сложно. И копирование числа в текст и обратно идет быстрее, чем reflect.directlyAssignable — reflect.Value.assignTo.

Ассемблер был моим одним из первых выученных в школе языков программирования на БК-0011 без дисковода и кассетного магнитофона) Так что это было забавно. В качестве разминки я попробовал перевести функцию b2u на Go-ассемблер. Хотя Go генерирует практически оптимальный код и если вы не придумаете алгоритмические трики или использование нестандартных команд языка ASM — то смысла особого в написании этих функций нет.

// func b2u(data []byte) uint32
//
// memory layout of the stack relative to FP
// +0 data slice ptr
// +8 data slice len
// +16 data slice cap
#include "textflag.h" TEXT ·B2u(SB),NOSPLIT,$0-24 // data ptr MOVQ data+0(FP), SI // data len MOVQ data+8(FP), CX // result in AX MOVBLZX (SI), AX // - '0' SUBL $48, AX // check end of loop DECQ CX JZ AX2RET LOOPBYTE: //move to one byte upper INCQ SI MOVBLZX (SI), BX //prev result *= 10 IMULL $10, AX // bx -= '0' SUBL $48, BX ADDL BX, AX // check end of loop while (cx--) DECQ CX JNZ LOOPBYTE AX2RET: MOVL AX, ret+24(FP) RET

По тестам она даёт ускорение 2-20%, от Go-версии. Зависит от кол-ва цифр в числе.
В итоге рабочий пример ускорился до 26.94 секунды.

Prepare — stmt. Вывод из статьи, для тех, кто просматривал текст — самый быстрый способ прочитать большой объем целочисленных данных из MySQL в память — использовать db. RawBytes и преобразование байт-слайса в целое число самописной функцией. Query — Scan в sql. Ждем когда в Go введут generic — может это ускорит работу стандартных драйверов баз данных. То есть, показанные в стандартных примерах способы работы не всегда оптимальны. Ведь Go в тестах, в которых появляются SELECT-выборки из БД, не блещет производительностью. Или возможно, разработчики обратят внимание на поведение драйвера.

Итог оказался при чтении через
rows. UPD: В комментариях привели пример чудодейственного драйвера github.com/lazada/sqle якобы рассчитанного на быстрое чтение. 307942824s
И если посмотреть на то, чем занималась минуту программа, становится понятно, что об оптимизации этого случая там просто не задумывались. Scan(&h.siteID, &h.zoneID, &h.poolID, &h.mediaID, &h.campaignID) в uint32 переменные очень печальный
55928930 time BETWEEN 1525640400 AND 1525726799 in 1m0. 63s 7. Стандартный драйвер выигрывает в 2 раза.
flat flat% sum% cum cum%
4. 62% 29. 62% 7. 11% database/sql.convertAssign
4. 25s 48. 94% 14. 22s 6. 68s 7. 56% 4. 97s 4. 70% runtime.mallocgc
2. 44% 8. 88% 19. 95% reflect.(*rtype). 48s 13. 96s 4. Name
2. 31% 5. 87% 24. 06% reflect.(*rtype). 51s 9. 90s 4. String
2. 08% 41. 77% 29. 89% github.com/lazada/sqle.(*Rows). 28s 67. 51s 4. Scan
2. 21% 2. 13% 33. 85% runtime.mapaccess1_fast32
2. 95s 4. 91% 37. 38s 3. 38s 3. 12% 2. ValueOf
1. 91% reflect. 16% 40. 92s 3. 69s 6. 28% 3. 77s 2. 07% runtime.assertE2I2
1. 19% 1. 91% 43. 91% runtime.getitab
1. 77s 2. 71% 45. 65s 2. 04s 11. 90% 7. 49s 2. 58% github.com/go-sql-driver/mysql.(*binaryRows).readRow
1. 36% 1. 45% 48. 06% runtime.mapassign_fast32
1. 86s 3. 22% 50. 35s 2. 27s 99. 58% 60. 31s 2. 13% main.loadHits
1. 73% 4. 15% 52. 60% github.com/lazada/sqle.typeCheck
1. 01s 6. 15% 54. 31s 2. 19s 6. 88% 4. FormatInt
1. 89% strconv. 11% 56. 28s 2. 88s 4. 99% 2. 25s 2. 74% strconv.formatBits
1. 05% 1. 06% 59. 06% reflect.(*rtype). 25s 2. 12s 1. Kind
1. 89% 31. 84% 60. 07% database/sql.(*Rows). 05s 51. По-моему, дальше можно не тестировать, какой замечательный заменитель стандартного database/sql отписали ребята. Scan

Если читать Scan в []byte переменные и конвертировать через b2u() в uint32 получается 44 секунды. Очередной миф про ускорение развенчался об практические тесты.

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Проверьте также

Закрыть
Кнопка «Наверх»
Закрыть