每秒50万行——MySQL写入压测并发实践
上篇文章写了MySQL写入压测的几种单线程的方式，本来想抛砖引玉，只是提供一些个人的经验和思路。后来有粉丝后台留言，想看看并发怎么处理，所以有了今天这篇文章。
并发在性能测试中应用十分广泛。根据我个人的经验，几乎所有压测都会用到并发。下面我来分享一下MySQL写入性能测试当中并发的使用。
首先，我们需要明确一个问题：并发对象。针对MySQL测试当中的实际情况，我列举了3个并发对象：java.sql.Statement 、 java.sql.Connection 以及 database 。
先说我自测最大的每秒写入行数：50w，如果再优化一下程序，应该会更高，但就测试结果，高也不会高很多了。粗估100w以内。
基准测试
我们先来进行一次基准测试，因为我的电脑已经处于一个薛定谔状态，性能非常不稳定。为了简单快速演示使用方法，这次我用了固定的sql。
用例如下：
package com.funtest.temp
import com.funtester.db.mysql.FunMySql
import com.funtester.frame.SourceCode
class MysqlTest extends SourceCode {
public static void main(String[] args) {
StringBuilder s = new StringBuilder();
String sql = "insert into user (name, age, level, region, address) values ('FunTester', 23, 2, '地球村', '八组一对')";
String ipPort = "127.0.0.1:3306";// 服务端地址
String database = "funtester"// 服务端地址
String user = "root";// 用户名
String password = "funtester";// 密码
def base = new FunMySql(ipPort, database, user, password);// 创建数据库操作基础类
def statement = base.connection.createStatement();// 创建 SQL 语句对象
while (true) {
statement.executeUpdate(sql);// 执行插入语句
}
statement.close();// 关闭资源
base.close();// 关闭资源
}
}
测试结果如下：
| 行数 | 秒数 |
|---|---|
| 9826 | 36 |
| 10278 | 37 |
| 10208 | 38 |
| 10220 | 39 |
| 9802 | 40 |
| 8975 | 41 |
| 9957 | 42 |
| 9412 | 43 |
| 9884 | 44 |
| 9412 | 45 |
| 9640 | 46 |
| 10304 | 47 |
| 可以看出来比之前的测试结果要好很多，这下大家应该能理解我的电脑薛定谔性能了吧。 |
Statement
之前讨论过 Statement 在查询场景当中实际上是不支持并发的，当时还分析了源码，有兴趣的同学可以翻一翻原来的文章，这里不再赘述原因。至于写入场景，并没有进行相关源码，为了简单，我们直接进行测试了。
下面是用例case：
package com.funtest.temp
import com.funtester.db.mysql.FunMySql
import com.funtester.frame.SourceCode
import java.util.concurrent.ExecutorService
import java.util.concurrent.Executors
class MysqlTest extends SourceCode {
public static void main(String[] args) {
StringBuilder s = new StringBuilder();
String sql = "insert into user (name, age, level, region, address) values ('FunTester', 23, 2, '地球村', '八组一对')";
String ipPort = "127.0.0.1:3306";// 服务端地址
String database = "funtester"// 服务端地址
String user = "root";// 用户名
String password = "funtester";// 密码
def base = new FunMySql(ipPort, database, user, password);// 创建数据库操作基础类
def statement = base.connection.createStatement();// 创建 SQL 语句对象
ExecutorService executors = Executors.newFixedThreadPool(10);// 创建线程池
10.times {
executors.execute {// 10个线程
while (true) {
statement.executeUpdate(sql);// 执行 SQL 语句
}
}
} statement.close();// 关闭资源
base.close();// 关闭资源
}
}
简单用了10个线程跑跑看。结果如下：
| 行数 | 时间 |
|---|---|
| 9584 | 42 |
| 10263 | 43 |
| 10098 | 44 |
| 9744 | 45 |
| 8864 | 46 |
| 9019 | 47 |
| 10133 | 48 |
| 9768 | 49 |
| 9613 | 50 |
| 9886 | 51 |
| 9835 | 52 |
| 6585 | 53 |
可以看出，其实没多大区别。在测试过程中也没有报错，说明 Statement 是可以支持并发的，但是实际效果并不明显。 |
Connection
下面我们对 Connection 进行并发，每个线程都创建一个 Statement 这方方案设计既简单又避免相互干扰，是一种很好的隔离策略。
用例的Case如下：
import com.funtester.db.mysql.FunMySql
import com.funtester.frame.FunPhaser
import com.funtester.frame.SourceCode
import java.util.concurrent.ExecutorService
import java.util.concurrent.Executors
class MysqlTest extends SourceCode {
public static void main(String[] args) {
StringBuilder s = new StringBuilder();
String sql = "insert into user (name, age, level, region, address) values ('FunTester', 23, 2, '地球村', '八组一对')";
String ipPort = "127.0.0.1:3306";// 服务端地址
String database = "funtester"// 服务端地址
String user = "root";// 用户名
String password = "funtester";// 密码
def base = new FunMySql(ipPort, database, user, password);// 创建数据库操作基础类
ExecutorService executors = Executors.newFixedThreadPool(10);// 创建线程池
def phaser = new FunPhaser()// 创建 Phaser 10.times {
phaser.register()// 注册线程
executors.execute {// 10个线程
def statement = base.connection.createStatement();// 创建 SQL 语句对象
while (true) {
statement.executeUpdate(sql);// 执行 SQL 语句
}
phaser.done()// 完成线程
}
} executors.shutdown();// 关闭线程池
phaser.await()// 等待所有线程执行完
base.close();// 关闭资源
}
}
测试结果如下：
| 行数 | 时间 |
|---|---|
| 10193 | 57 |
| 10095 | 58 |
| 9952 | 59 |
| 9991 | 0 |
| 9893 | 1 |
| 9880 | 2 |
| 8195 | 3 |
| 7834 | 4 |
| 8695 | 5 |
| 8633 | 6 |
| 9078 | 7 |
| 8613 | 8 |
| 可以看出，性能依旧一般般，相差无几。 |
database
下面我们进行 database 级别的并发，创建更多的 Connection 来实现期望中更好的写入性能。
| 行数 | 时间 |
|---|---|
| 38549 | 32 |
| 43925 | 33 |
| 32172 | 34 |
| 44419 | 35 |
| 42545 | 36 |
| 40741 | 37 |
| 34487 | 38 |
| 47211 | 39 |
| 43269 | 40 |
| 45396 | 41 |
| 36748 | 42 |
| 这性能一下子就上去了。 |
下面我们再重复一下单线程性能最高的方法，单词插入N行的方案，再次测试，结果如下：
| 行数 | 时间 |
|---|---|
| 241440 | 12 |
| 250660 | 13 |
| 252880 | 14 |
| 246870 | 15 |
| 242760 | 16 |
| 214790 | 17 |
| 257260 | 18 |
| 250010 | 19 |
| 251720 | 20 |
这下是不是感觉 MySQL 写入性能符合要求了呢？
结语
再实际的工作中，场景会更加复杂，影响写入性能的因素比较多。像前两个Case，虽然理论上性能会提升很多，但实际结果就是相差无几，很可能就是因为触达了单个 Connection 的性能瓶颈。
而MySQL写入性能影响因素比较多，除了硬件以外，我简单列举几个。
MySQL写入性能受多个因素影响，了解并优化这些因素可以显著提升数据库的写入效率。以下是一些主要的影响因素：
数据库配置
- innodb_buffer_pool_size：适当增加InnoDB缓冲池大小，使更多数据和索引可以被缓存在内存中，减少磁盘I/O。
- innodb_log_file_size：较大的日志文件可以减少日志切换的频率，从而提高写入性能。
- innodb_flush_log_at_trx_commit：设置为1可以确保每个事务提交时日志都写入磁盘，保证数据安全，但会降低性能。设置为2或0可以提高性能，但可能会导致数据丢失。
索引 - 索引数量和类型：适当的索引可以提高查询速度，但过多的索引会增加写操作的开销。需要平衡查询性能和写入性能。
- 复合索引：合理使用复合索引可以减少需要维护的索引数量，从而提高写入性能。
表设计 - 表分区：将大表分成多个分区，可以减少每次写入时需要处理的数据量，从而提高写入性能。
- 列的数据类型：使用合适的数据类型可以减少存储空间和I/O操作。例如，用TINYINT而不是INT来存储小范围的整数。
- 归档和清理历史数据：定期归档和清理不再需要的历史数据，减少表的大小和写入开销。
事务管理 - 批量插入：使用批量插入而不是逐行插入可以显著提高写入性能。
- 事务大小：适当的事务大小可以提高写入性能，太大或太小的事务都可能影响性能。
- 锁争用：避免长时间持有锁，可以减少锁争用，提高并发写入性能。
并发控制 - 连接池：使用连接池可以减少建立和释放连接的开销，提高写入性能。
- 并发连接数：合理设置并发连接数，避免过多的连接导致资源争用和性能下降。
数据库引擎 - InnoDB vs MyISAM：InnoDB支持事务和行级锁定，适用于高并发写入操作。MyISAM的写入性能较好，但不支持事务和行级锁定。
网络 - 网络延迟：尽量减少客户端和服务器之间的网络延迟，特别是在分布式系统中。
- 网络带宽：确保有足够的网络带宽，避免因带宽不足导致的性能瓶颈。
操作系统和文件系统 - 操作系统调优：调整操作系统的I/O调度算法、文件系统缓冲等参数，可以提高写入性能。
- 文件系统选择：选择高性能的文件系统，如EXT4、XFS，优化文件系统的性能。
其他 - 查询优化：确保写操作尽量简单高效，避免复杂的查询和子查询。
- 数据库版本：使用最新的数据库版本，包含最新的性能优化和补丁。
在真实的场景中，针对不同的因素采取不同的策略，在不断学习当中，提升技术实力。
