数据库ID生成器基准测试
在说明如何基准测试之前,我想聊聊我为什么要做这个事儿,话说最近做某后台的时候需要一个 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 <- end.Sub(start)
}
}
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]
", qps)
fmt.Printf("tpq: %.3f [ms]
", tpq)
}
代码是用 Golang 写的,运行前记得在命令同级目录编辑好数据库配置文件 db.toml:
driver = "mysql"
dsn = "<username>:<passwrod>@<protocol>(<host>:<port>)/<database>"
下面让我们看看原始方案和我们改进的方案有什么不同:
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 的小工具,也算是有所得吧。
相关文章
- 直接在代码里面对list集合进行分页
- .NET Framework 4.5新特性详解
- 大数据的简要介绍
- 大数据的由来
- 高斯混合模型的自然梯度变量推理
- timing-wheel 仿Kafka实现的时间轮算法
- 使用Navicat软件连接自建数据库(Linux系统)
- 那一天,我被Redis主从架构支配的恐惧
- Redis 深入了解键的过期时间
- C#使用委托调用实现用户端等待闪屏
- 基于流计算 Oceanus 和 Elasticsearch Service 构建百亿级实时监控系统
- GRAND | 转录调控网络预测数据库
- JFreeChart API中文文档
- 临床相关突变查询数据库
- TIGER | 人类胰岛基因变化查询数据库
- 视频边缘计算网关EasyNVR在视频整体监控解决方案中的应用分析
- Apache Arrow - 大数据在数据湖后的下一个风向标
- 常见的电商数据指标体系
- AKShare-艺人数据-艺人流量价值
- MySQL中多表联合查询与子查询的这些区别,你可能不知道!