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

func b2u(b []byte) uint32 { 	n := uint32(0) 	for _, c := range b { 		n = n*uint32(10) + uint32(c-'0') 	} 	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 структуры! Уже ничего не должно конвертироваться — только преобразование целое-целое.
Итог оказался печальным — 49.827306314s То есть замедление вообще ужасающее. Самый тупящий вариант из всех возможных, несмотря на хорошую теоретическую основу для самого быстрого результата. В чем же дело?
Смотрим:
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-преобразования вышли на первые строчки по времени выполнения. Не зря Роб Пайк говорит об осторожном использовании рефлексии. Можно натворить дел.

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

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.

В качестве разминки я попробовал перевести функцию b2u на Go-ассемблер. Ассемблер был моим одним из первых выученных в школе языков программирования на БК-0011 без дисковода и кассетного магнитофона) Так что это было забавно. Хотя 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 секунды.

Вывод из статьи, для тех, кто просматривал текст — самый быстрый способ прочитать большой объем данных из MySQL в память — использовать db.Prepare — stmt.Query — Scan в sql.RawBytes и преобразование байт-слайса в целое число самописной функцией. То есть, показанные в стандартных примерах способы работы не всегда оптимальны. Ждем когда в Go введут generic — может это ускорит работу стандартных драйверов баз данных. Или возможно, разработчики обратят внимание на поведение драйвера. Ведь Go в тестах когда появляются SELECT-выборки из БД не блещет производительностью https://www.techempower.com/benchmarks/#section=data-r15&hw=ph&test=fortune

FavoriteLoadingДобавить в избранное
Posted in Без рубрики

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