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 ^[1] который гарантирует что байты из драйвера базы данных будут переданы пользователю без копирования. Что же. Попытаемся извлечь 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-выборки из БД, не блещет производительностью ^[2].

UPD: В комментариях привели пример чудодейственного драйвера github.com/lazada/sqle ^[3] якобы рассчитанного на быстрое чтение. Итог оказался при чтении через
rows.Scan(&h.siteID, &h.zoneID, &h.poolID, &h.mediaID, &h.campaignID) в uint32 переменные очень печальный
55928930 time BETWEEN 1525640400 AND 1525726799 in 1m0.307942824s
И если посмотреть на то, чем занималась минуту программа, становится понятно, что об оптимизации этого случая там просто не задумывались. Стандартный драйвер выигрывает в 2 раза.
flat flat% sum% cum cum%
4.63s 7.62% 7.62% 29.25s 48.11% database/sql.convertAssign
4.22s 6.94% 14.56% 4.68s 7.70% runtime.mallocgc
2.97s 4.88% 19.44% 8.48s 13.95% reflect.(*rtype).Name
2.96s 4.87% 24.31% 5.51s 9.06% reflect.(*rtype).String
2.90s 4.77% 29.08% 41.28s 67.89% github.com/lazada/sqle.(*Rows).Scan
2.51s 4.13% 33.21% 2.95s 4.85% runtime.mapaccess1_fast32
2.38s 3.91% 37.12% 2.38s 3.91% reflect.ValueOf
1.92s 3.16% 40.28% 3.69s 6.07% runtime.assertE2I2
1.77s 2.91% 43.19% 1.77s 2.91% runtime.getitab
1.65s 2.71% 45.90% 7.04s 11.58% github.com/go-sql-driver/mysql.(*binaryRows).readRow
1.49s 2.45% 48.36% 1.86s 3.06% runtime.mapassign_fast32
1.35s 2.22% 50.58% 60.27s 99.13% main.loadHits
1.31s 2.15% 52.73% 4.01s 6.60% github.com/lazada/sqle.typeCheck
1.31s 2.15% 54.88% 4.19s 6.89% strconv.FormatInt
1.28s 2.11% 56.99% 2.88s 4.74% strconv.formatBits
1.25s 2.06% 59.05% 1.25s 2.06% reflect.(*rtype).Kind
1.12s 1.84% 60.89% 31.05s 51.07% database/sql.(*Rows).Scan

Если читать Scan в []byte переменные и конвертировать через b2u() в uint32 получается 44 секунды. По-моему, дальше можно не тестировать, какой замечательный заменитель стандартного database/sql отписали ребята. Очередной миф про ускорение развенчался об практические тесты.

Автор: sania

Источник ^[4]