Sqlite学习笔记(三)&&WAL性能测试
WAL是SQLite3.7.0版本引入的一个重大改进。SQLite官网宣称在很多使用场景下，WAL模型的性能都要好于默认的DELETE模式。下面将针对几个主要场景对WAL性能做测试，测试的硬件与xxx保持一致。
纯写场景
1) 测试说明
测试简单更新事务在WAL模式下与DELETE日志模式性能对比，测试语句形如：update user set c1=c1+1 where id=xxx，id为主键，串行执行更新。通过执行1w个更新事务，统计程序运行时间。
2) 测试结果
测试时间结果以毫秒计算，TPS是每秒执行的事务数，通过事务数与时间相比获取结果。
|
|
WAL模式 |
DELETE模式 |
|
第一轮 |
55176 |
159196 |
|
第二轮 |
54442 |
164341 |
|
第三轮 |
56171 |
162329 |
|
第四轮 |
56167 |
166117 |
|
第五轮 |
55969 |
163139 |
|
平均值 |
55581 |
163024 |
|
TPS |
179.9 |
61.3 |
3) 结果分析
从测试结果来看，WAL日志模式下，纯写性能要远远好于DELETE模式，前者大概是后者的3倍。这个性能差异主要源于每次事务提交，WAL模式只需要将更新的日志写入磁盘即可，而DELETE模式修改过程中，首先需要将原始数据页拷贝到日志文件中，并进行fsync；然后再将修改页写入磁盘，同时也需要fsync，确保数据落盘，并且还要将日志文件清除。因此，写事务在WAL模式下，只需要一次fsync，并且是顺序写；而DELETE模式下，需要至少两次fsync(日志，数据)，并且更新的数据可能离散分布在多个page中，因此可能需要多个fsync来完成。众所周知，fsync调用是非常耗时的，导致DELTE模式下写性能不如WAL模式。
纯读场景
1) 测试说明
测试WAL模式下，纯读性能数据，测试语句形如：“select * from user where id = xxx”，id为主键，对比WAL模式与DELETE模式下读性能。每个线程执行10w次查询，统计最终执行时间。QPS为每秒执行的查询数
2) 测试结果
|
|
WAL模式 |
DELETE模式 |
||
|
2线程 |
4线程 |
2线程 |
4线程 |
|
|
第一轮 |
2847 |
5050 |
4124 |
8168 |
|
第二轮 |
2730 |
5795 |
3833 |
8074 |
|
第三轮 |
2688 |
5675 |
3819 |
8077 |
|
第四轮 |
2959 |
5894 |
3881 |
7787 |
|
第五轮 |
2836 |
5924 |
3873 |
7958 |
|
平均值 |
2817 |
5667 |
3906 |
8012 |
|
QPS |
7w |
7w |
5.1w |
5w |
3) 结果分析
从结果来看，WAL模式下，读性能要好于DELETE模式，大概提高了将近30%。这说明开启WAL模式下，对读性能也有提高。目前我还没有找到性能提升的原因，后续仔细阅读源码后，再做分析。
读写场景
1) 测试说明
在WAL模式下，事务提交时实际是以append方式写wal日志文件，因此读写不冲突；而DELETE模式下，写事务需要写DB(缓存，文件)，与读事务读DB(缓存，文件)冲突，因此只能串行读写，通过前面的测试可知，WAL模式下，每秒读写事务数比例大概是500:1；DELETE模式下，每秒读写事务数比例大概是800:1，这里的测试不打算比较读写同时存在时两种模式的对比，而是单独测试WAL，以读写比为500：1场景，看看读写的QPS和TPS是否与单独执行有差异。测试中，读写线程都并发读写同一张表，读采用5个线程，主键查询，每个线程执行100w个读；写采用1个线程，主键更新，执行1w次写。测试时间的单位为毫秒，QPS和TPS分别为每秒的读和写，QPS=500w/时间，TPS=100w/时间。
2) 测试结果
|
|
WAL模式 |
|
|
第一轮 |
67543 |
|
|
第二轮 |
66812 |
|
|
第三轮 |
72946 |
|
|
第四轮 |
78844 |
|
|
第五轮 |
74501 |
|
|
平均值 |
72129 |
|
|
QPS|TPS |
6.94w |
138 |
3) 结果分析
通过读写比500:1的测试模型，基本保证了读写是同时完成的，从另外一方面来说，就是读写是充分竞争的。从测试结果来看，WAL模式下，读的QPS为6.94w，与单独测试读7w的性能几乎无差异；写的TPS为138， 较单独测试写的179略有下降，这可能与设置的读写比有关系。通过测试结果可以充分说明，WAL模式下，读写是充分并发的，并且几乎没有性能损耗。
批量导入场景
1) 测试说明
这个测试主要了了解WAL模式和DELETE模式下，导入数据性能对比。创建2个表，每个表导入10w条记录，总共20w记录，观察导入的时间。
2) 测试结果
|
|
WAL模式 |
DELETE模式 |
|
第一轮 |
28019 |
18036 |
|
第二轮 |
28500 |
17959 |
|
第三轮 |
28575 |
17578 |
|
第四轮 |
29078 |
18076 |
|
第五轮 |
29184 |
17989 |
|
平均值 |
28671 |
17909 |
|
TPS |
6975 |
11167 |
3) 结果分析
从结果来看，DELETE模式的批量导入性能要好于WAL模式，这主要源于，DELETE模式记录的是old-page，而insert操作没有old-page，所以无需记录日志，而WAL记录的是修改页，所以代价比DELETE模式高。
wal文件大小测试
1) 测试说明
wal模式下，日志采用单独的wal文件，事务将更新写入wal文件，事务执行过程中，不断写wal文件，直到提交后，才有机会做检查点，控制wal文件不继续膨胀。由于端设备，空间资源也非常稀缺，我们来看看wal 模式和delete模式下日志文件对空间的损耗。WAL模式下， wal_autocheckpoint用来控制做检查点时机，这个参数对WAL文件影响至关重要，测试也围绕、这个参数展开。
2) 测试结果
|
场景 |
日志文件容量 |
|
|
|
WAL模式 |
DELETE模式 |
|
批量导入(大事务) |
200M |
4k |
|
主键更新(1w行事务) wal_autocheckpoint=1000 |
20M |
19M |
|
单条更新(单行事务) 更新1w行 wal_autocheckpoint=1000 |
1M |
4k |
|
单条更新事务 更新1w行 wal_autocheckpoint=4000 |
4M |
4k |
|
单条更新事务 更新1w行 wal_autocheckpoint=0 |
42M |
无 |
3) 结果分析
从测试结果来看，批量导入大事务时，测试中DB文件200M，产生的WAL日志文件也是200M，由于是INSERT操作，DELETE模式下，日志文件记录原始数据页，所以依然为4k，这4k应该是控制信息。对于大事务更新操作，WAL模式和DELETE模式产生的日志量基本相当，只不过一个记录修改后的数据，一个记录修改前的数据。对于单行事务，DELETE模式的日志文件基本没有增长，而WAL模式下，日志文件与wal_autocheckpoint参数相关，默认情况下该参数值为1000个page，每个page_size为1k，因此大约为1M；调整为4000后，日志文件也随之膨胀到4M；设置为0，亦即关闭检查点后，更新1w条记录，将使wal膨胀到42M。因此，无论是WAL模式还是DELETE模式，事务大小都对日志文件大小有影响，对于WAL模式，为了控制日志文件大小，wal_autocheckpoint参数非常重要。
