缘起
sqlite写入500条不大的记录居然要花费20多秒的时间,太慢了!!!
分析
sqlite是一个非常优秀的嵌入式数据库,读取性能非常好,写入性能就比较差一些,为什么写入性能差呢?下面做了一个测试。
下面是对500+条记录些操作的系统调用的观察,发现时间基本花费在了fdatasync系统调用上,调用2064次;write系统调用虽然8049次,但是write并不保证逻辑,所以速度很快。
$ strace -c php test.php
27.688107013702 (耗时27.6s)
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
88.73 0.073919 36 2064 fdatasync
3.88 0.003231 0 8049 write
3.77 0.003144 6 516 unlink
1.12 0.000929 0 6730 fcntl64
0.90 0.000751 0 9118 3 _llseek
0.86 0.000720 1 1218 82 open
0.24 0.000198 0 1143 close
0.20 0.000167 0 1182 read
0.16 0.000131 0 520 518 access
0.05 0.000039 1 42 1 lstat64
0.04 0.000033 0 194 mmap2
0.03 0.000027 0 92 munmap
0.03 0.000021 0 58 49 stat64
0.00 0.000000 0 1 execve
0.00 0.000000 0 20 time
0.00 0.000000 0 21 brk
0.00 0.000000 0 3 2 ioctl
0.00 0.000000 0 3 gettimeofday
0.00 0.000000 0 1 readlink
0.00 0.000000 0 2 uname
0.00 0.000000 0 28 mprotect
0.00 0.000000 0 4 poll
0.00 0.000000 0 5 rt_sigaction
0.00 0.000000 0 2 rt_sigprocmask
0.00 0.000000 0 2 getcwd
0.00 0.000000 0 1 getrlimit
0.00 0.000000 0 648 fstat64
0.00 0.000000 0 4 futex
0.00 0.000000 0 1 sched_setaffinity
0.00 0.000000 0 2 sched_getaffinity
0.00 0.000000 0 1 set_thread_area
0.00 0.000000 0 1 set_tid_address
0.00 0.000000 0 1 set_robust_list
0.00 0.000000 0 4 socket
0.00 0.000000 0 4 2 connect
0.00 0.000000 0 1 send
0.00 0.000000 0 1 recvfrom
0.00 0.000000 0 1 shutdown
0.00 0.000000 0 5 setsockopt
------ ----------- ----------- --------- --------- ----------------
100.00 0.083310 31693 657 total
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
$strace-cphptest.php
27.688107013702(耗时27.6s)
%timesecondsusecs/callcallserrorssyscall
--------------------------------------------------------------
88.730.073919362064fdatasync
3.880.00323108049write
3.770.0031446516unlink
1.120.00092906730fcntl64
0.900.000751091183_llseek
0.860.0007201121882open
0.240.00019801143close
0.200.00016701182read
0.160.0001310520518access
0.050.0000391421lstat64
0.040.0000330194mmap2
0.030.000027092munmap
0.030.00002105849stat64
0.000.00000001execve
0.000.000000020time
0.000.000000021brk
0.000.000000032ioctl
0.000.00000003gettimeofday
0.000.00000001readlink
0.000.00000002uname
0.000.000000028mprotect
0.000.00000004poll
0.000.00000005rt_sigaction
0.000.00000002rt_sigprocmask
0.000.00000002getcwd
0.000.00000001getrlimit
0.000.0000000648fstat64
0.000.00000004futex
0.000.00000001sched_setaffinity
0.000.00000002sched_getaffinity
0.000.00000001set_thread_area
0.000.00000001set_tid_address
0.000.00000001set_robust_list
0.000.00000004socket
0.000.000000042connect
0.000.00000001send
0.000.00000001recvfrom
0.000.00000001shutdown
0.000.00000005setsockopt
--------------------------------------------------------------
100.000.08331031693657total
查资料
发现: 如果使用事务的方式批量插入数据,效果会有明显改善,因为默认情况下每次写入操作都会落地才返回的(更加安全靠谱),如果使用事务,则批量数据一次性落地。
修改代码,分析系统调用如下:(发现fdatasync调用12次)
$ strace -c php test.php
0.20949816703796 (耗时 209ms)
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
71.64 0.000998 83 12 fdatasync
7.61 0.000106 1 155 read
5.24 0.000073 0 194 mmap2
4.16 0.000058 0 195 write
2.58 0.000036 0 135 fstat64
1.65 0.000023 0 92 munmap
1.58 0.000022 1 28 mprotect
1.51 0.000021 1 42 1 lstat64
1.44 0.000020 20 1 readlink
1.29 0.000018 0 238 3 _llseek
1.29 0.000018 0 58 49 stat64
0.00 0.000000 0 192 82 open
0.00 0.000000 0 117 close
0.00 0.000000 0 3 unlink
0.00 0.000000 0 1 execve
0.00 0.000000 0 20 time
0.00 0.000000 0 7 5 access
0.00 0.000000 0 21 brk
0.00 0.000000 0 3 2 ioctl
0.00 0.000000 0 3 gettimeofday
0.00 0.000000 0 2 uname
0.00 0.000000 0 4 poll
0.00 0.000000 0 5 rt_sigaction
0.00 0.000000 0 2 rt_sigprocmask
0.00 0.000000 0 2 getcwd
0.00 0.000000 0 1 getrlimit
0.00 0.000000 0 61 fcntl64
0.00 0.000000 0 4 futex
0.00 0.000000 0 1 sched_setaffinity
0.00 0.000000 0 2 sched_getaffinity
0.00 0.000000 0 1 set_thread_area
0.00 0.000000 0 1 set_tid_address
0.00 0.000000 0 1 set_robust_list
0.00 0.000000 0 4 socket
0.00 0.000000 0 4 2 connect
0.00 0.000000 0 1 send
0.00 0.000000 0 1 recvfrom
0.00 0.000000 0 1 shutdown
0.00 0.000000 0 5 setsockopt
------ ----------- ----------- --------- --------- ----------------
100.00 0.001393 1620 144 total
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
$strace-cphptest.php
0.20949816703796(耗时209ms)
%timesecondsusecs/callcallserrorssyscall
--------------------------------------------------------------
71.640.0009988312fdatasync
7.610.0001061155read
5.240.0000730194mmap2
4.160.0000580195write
2.580.0000360135fstat64
1.650.000023092munmap
1.580.000022128mprotect
1.510.0000211421lstat64
1.440.000020201readlink
1.290.00001802383_llseek
1.290.00001805849stat64
0.000.000000019282open
0.000.0000000117close
0.000.00000003unlink
0.000.00000001execve
0.000.000000020time
0.000.000000075access
0.000.000000021brk
0.000.000000032ioctl
0.000.00000003gettimeofday
0.000.00000002uname
0.000.00000004poll
0.000.00000005rt_sigaction
0.000.00000002rt_sigprocmask
0.000.00000002getcwd
0.000.00000001getrlimit
0.000.000000061fcntl64
0.000.00000004futex
0.000.00000001sched_setaffinity
0.000.00000002sched_getaffinity
0.000.00000001set_thread_area
0.000.00000001set_tid_address
0.000.00000001set_robust_list
0.000.00000004socket
0.000.000000042connect
0.000.00000001send
0.000.00000001recvfrom
0.000.00000001shutdown
0.000.00000005setsockopt
--------------------------------------------------------------
100.000.0013931620144total
结论:
1. 借用事务采用批量写入的方式来加速写操作
2. 如果业务上不能批量操作呢?似乎有一个nosync的sqlite版本(不知道为什么不是一个配置选项)
3. 如果数据量太大,可以分多批提交事务,因为事务是需要内存的。(不过,sqlite一般不会存放N个G的数据的,几百MB已经算是比较大的了,这样的数据量内存还是吃的消的)
参考资料:
关于sqlite的系列分析文章(可以看看)
SQLite的原子提交原理