On Thu, Dec 6, 2012 at 9:16 AM, Keith Chew <keith.c...@gmail.com> wrote: > Thank you so much for all the input. I tried 2 things, using only a > single connection (as suggested by Clemen) and to change the page size > to 32K. Single connection did not reduce the average latency, but did > help in reducing the spikes. So, I believe Clemen has helped identify > one issue (write concurrencies). Using 32K page size also did help > reduce the spikes (not sure logic behind this, have yet to do a > strace), but average latency is still there.
I used sar and iostat to profile what is happening at the disk level, and below are the results. You can see that the TPS on the disk is very small, only 6-7 tps, and both mysql and sqlite profiles show very close patterns, except sqlite is writing a bit more. I would expect at this tps level, writes should be very fast. Is there anything else I can try to narrow this down further? Sqlite 12/06/2012 12:43:10 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 6.93 0.00 6.93 0.00 110.89 16.00 0.35 64.57 25.14 17.43 12/06/2012 12:43:11 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 9.00 0.00 6.00 0.00 120.00 20.00 0.45 59.33 29.33 17.60 12/06/2012 12:43:12 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 6.00 0.00 7.00 0.00 104.00 14.86 0.37 67.43 24.57 17.20 12/06/2012 12:43:13 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 5.94 0.00 5.94 0.00 95.05 16.00 0.36 59.33 30.00 17.82 12/06/2012 12:43:14 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 6.00 0.00 4.00 0.00 80.00 20.00 0.46 34.00 39.00 15.60 12/06/2012 12:43:15 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 6.00 0.00 9.00 0.00 120.00 13.33 0.49 89.78 27.11 24.40 Mysql 12/06/2012 12:50:22 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 3.06 0.00 6.12 0.00 73.47 12.00 0.38 62.67 27.33 16.73 12/06/2012 12:50:23 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 2.91 0.00 4.85 0.00 62.14 12.80 0.31 63.20 30.40 14.76 12/06/2012 12:50:24 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 3.03 0.00 6.06 0.00 72.73 12.00 0.34 57.33 24.67 14.95 12/06/2012 12:50:25 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 3.00 0.00 5.00 0.00 64.00 12.80 0.30 57.60 29.60 14.80 12/06/2012 12:50:26 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 2.97 0.00 5.94 0.00 71.29 12.00 0.36 60.00 26.00 15.45 12/06/2012 12:50:27 PM Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 3.00 0.00 5.00 0.00 64.00 12.80 0.29 57.60 28.80 14.40 Sqlite 12:43:44 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:43:45 PM dev8-0 6.93 0.00 102.97 14.86 0.39 56.00 21.71 15.05 12:43:45 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:43:46 PM dev8-0 5.94 0.00 95.05 16.00 0.31 52.67 24.67 14.65 12:43:46 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:43:47 PM dev8-0 6.93 0.00 102.97 14.86 0.41 58.86 22.86 15.84 12:43:47 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:43:48 PM dev8-0 6.00 0.00 96.00 16.00 0.33 54.67 28.00 16.80 12:43:48 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:43:49 PM dev8-0 7.00 0.00 120.00 17.14 0.46 66.29 26.86 18.80 12:43:49 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:43:50 PM dev8-0 5.94 0.00 95.05 16.00 0.36 60.00 31.33 18.61 12:43:50 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:43:51 PM dev8-0 6.00 0.00 96.00 16.00 0.32 53.33 25.33 15.20 12:43:51 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:43:52 PM dev8-0 6.93 0.00 102.97 14.86 0.41 58.86 22.86 15.84 Mysql 12:49:15 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:49:16 PM dev8-0 3.96 0.00 71.29 18.00 0.18 45.00 29.00 11.49 12:49:16 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:49:17 PM dev8-0 7.07 0.00 80.81 11.43 0.55 77.14 36.57 25.86 12:49:17 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:49:18 PM dev8-0 3.96 0.00 55.45 14.00 0.19 49.00 31.00 12.28 12:49:18 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:49:19 PM dev8-0 5.94 0.00 71.29 12.00 0.39 65.33 31.33 18.61 12:49:19 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:49:20 PM dev8-0 5.00 0.00 64.00 12.80 0.26 51.20 24.80 12.40 12:49:20 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:49:21 PM dev8-0 5.94 0.00 71.29 12.00 0.34 57.33 26.00 15.45 12:49:21 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:49:22 PM dev8-0 5.00 0.00 64.00 12.80 0.29 57.60 27.20 13.60 12:49:22 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:49:23 PM dev8-0 6.06 0.00 72.73 12.00 0.37 60.67 27.33 16.57 12:49:23 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:49:24 PM dev8-0 10.78 0.00 172.55 16.00 0.94 86.91 18.18 19.61 Regards Keith _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
