每秒50万行——MySQL写入压测并发实践

FunTester

发布于 2024-07-05 14:15:52

960

发布于 2024-07-05 14:15:52

文章被收录于专栏：FunTesterFunTester

上篇文章写了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，优化文件系统的性能。其他
查询优化：确保写操作尽量简单高效，避免复杂的查询和子查询。
数据库版本：使用最新的数据库版本，包含最新的性能优化和补丁。