在说明如何基准测试之前,我想聊聊我为什么要做这个事儿,话说最近做某后台的时候需要一个 ID 生成器,我不太想用 snowflake 等复杂的解决方案,也不太想用 redis 来实现,因为我手头只有 mysql,所以我琢磨着就用 mysql 实现吧。
实际上当初 flickr 就是这么干的,利用 LAST_INSERT_ID 返回最新插入的 id:
mysql> CREATE TABLE `Tickets64` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`stub` char(1) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM;
mysql> REPLACE INTO Tickets64 (stub) VALUES ('a');
mysql> SELECT LAST_INSERT_ID();
不过我没有直接拷贝此方案,因为看上去它至少有两个可以优化的地方:
因为一张表只能有一个自增字段,所以一个表只能做一个独立的 id 生成器。
REPLACE 实际上相当于先 DELETE 再 INSERT,也就是两步操作。
按照文档描述 LAST_INSERT_ID 支持表达式参数,如此说来我们可以通过它来自行维护 id,从而去掉对 auto_increment 的依赖,进而不再需要 REPLACE,直接 UPDATE 即可:
mysql> CREATE TABLE `seq` (
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL DEFAULT '',
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB;
mysql> INSERT INTO seq (id, name) VALUES (0, 'global');
mysql> INSERT INTO seq (id, name) VALUES (0, 'another');
mysql> UPDATE seq SET id = LAST_INSERT_ID(id+1) WHERE name = 'global';
mysql> SELECT LAST_INSERT_ID();
确定了解决方案,我琢磨着得 Benchmark 看看这条 SQL 语句的性能怎么样,其实 MySQL 本身有一个 Benchmark 函数,但是它只能用来测试 SELECT 这样的读操作 SQL,不能用来测试 UPDATE,REPLACE 这样的写操作 SQL,于是我到处找 SQL 性能测试工具,结果发现虽然有 mysqlslap、tpcc-mysql 之类的重量级测试工具,但是却不符合我的需求:我只想要一个能压力测试一条 SQL 的小工具!
既然没有现成的,那么我们不妨自己实现一个:
package main
import (
"database/sql"
"fmt"
"log"
"os"
"sync"
"time"
"github.com/spf13/cobra"
"github.com/spf13/viper"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
var number, concurrency int
var cmd = &cobra.Command{
Use: "benchmark sql",
Short: "a sql benchmark tool",
Args: func(cmd *cobra.Command, args []string) error {
if len(args) != 1 {
cmd.Usage()
os.Exit(1)
}
return nil
},
Run: func(cmd *cobra.Command, args []string) {
b := benchmark{
sql: args[0],
number: number,
concurrency: concurrency,
}
b.run()
},
}
func init() {
cobra.OnInitialize(config)
cmd.Flags().IntVarP(&number, "number", "n", 100, "number")
cmd.Flags().IntVarP(&concurrency, "concurrency", "c", 1, "concurrency")
cmd.Flags().SortFlags = false
}
func config() {
viper.AddConfigPath(".")
viper.SetConfigName("db")
viper.SetConfigType("toml")
err := viper.ReadInConfig()
if err != nil {
log.Fatal(err)
}
driver := viper.GetString("driver")
dsn := viper.GetString("dsn")
db, err = sql.Open(driver, dsn)
if err != nil {
log.Fatal(err)
}
}
func main() {
if err := cmd.Execute(); err != nil {
log.Fatal(err)
}
}
type benchmark struct {
sql string
number int
concurrency int
duration chan time.Duration
start time.Time
end time.Time
}
func (b *benchmark) run() {
b.duration = make(chan time.Duration, b.number)
b.start = time.Now()
b.runWorkers()
b.end = time.Now()
b.report()
}
func (b *benchmark) runWorkers() {
var wg sync.WaitGroup
wg.Add(b.concurrency)
for i := 0; i < b.concurrency; i++ {
go func() {
defer wg.Done()
b.runWorker(b.number / b.concurrency)
}()
}
wg.Wait()
close(b.duration)
}
func (b *benchmark) runWorker(num int) {
for i := 0; i < num; i++ {
start := time.Now()
b.request()
end := time.Now()
b.duration
}
}
func (b *benchmark) request() {
if _, err := db.Exec(b.sql); err != nil {
log.Fatal(err)
}
}
func (b *benchmark) report() {
sum := 0.0
num := float64(len(b.duration))
for duration := range b.duration {
sum += duration.Seconds()
}
qps := int(num / b.end.Sub(b.start).Seconds())
tpq := sum / num * 1000
fmt.Printf("qps: %d [#/sec]\n", qps)
fmt.Printf("tpq: %.3f [ms]\n", tpq)
}
代码是用 Golang 写的,运行前记得在命令同级目录编辑好数据库配置文件 db.toml:
driver = "mysql"
dsn = ":@(:)/"
下面让我们看看原始方案和我们改进的方案有什么不同:
shell> /path/to/benchmark -n 100000 -c 10 "
REPLACE INTO Tickets64 (stub) VALUES ('a')
"
shell> /path/to/benchmark -n 100000 -c 10 "
UPDATE seq SET id = LAST_INSERT_ID(id+1) WHERE name = 'global'
"
结果令人大吃一惊,所谓的改进方案比原始方案慢得多!仔细对比两个方案的表结构,发现原始方案数据引擎使用的是 MyISAM,而改进方案使用的是 InnoDB,于是我把数据引擎统一改成 MyISAM,重新测试,性能终于上来了,不过两者性能差异并不大,甚至 REPLACE 的性能还要比 UPDATE 好一点,具体原因我没有深究,就留给读者去探索吧。
虽然有一些小问题悬而未决,好在搞出一个压测 SQL 的小工具,也算是有所得吧。