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-выборки из БД, не блещет производительностью.

UPD: В комментариях привели пример чудодейственного драйвера github.com/lazada/sqle якобы рассчитанного на быстрое чтение. Итог оказался при чтении через
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

Источник