sqlite php 性能优化,关于sqlite的事务的使用

缘起

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的原子提交原理