Interesting. Does OSX provide any way to get a measure of the internal memory throughput usage? Clearly something has to be hitting the a limit since a free running program with no internal blocking should always push some physical constraint to the limit.
I agree that the bursting you see will be sqlite flushing pages to disk, or the OS block/device cache flushing out pages. SQLite's internal page cache needs to be ample enough to permit required table (B-Tree) inserts and any associated indexes manipulations (particularly at non-leaf nodes) to be contained within the page cache (as in a working set of pages). Like any paging system, if the size is to small it will thrash to the next level of cache (in the case of page I/O, this is the OS block/device cache and the underlying storage). I do not know how paging operations (technically I/O) between the SQLite's page cache and the OS page cache get accounted for. They may not be accounted as "user CPU" and might not appear as actual I/O, but those operations will be consuming system resources. If the bulk load is entirely single-threaded, have you compiled with the SQLITE_THREADSAFE=0 option? This will avoid having any extra synchronization semaphores being used in the code path to ensure that multiple threads don't step on each other. While this processing overhead may be completely insignificant, it may be causing operations to briefly enter a wait state in the scheduler. By the same token, if the database file is not expected to be opened by any other process while it is being loaded, does opening it in EXCLUSIVE mode as well make any difference? >-----Original Message----- >From: Kevin Xu [mailto:accol...@gmail.com] >Sent: Wednesday, 2 April, 2014 14:57 >To: Keith Medcalf; General Discussion of SQLite Database >Subject: Re: [sqlite] Improving Bulk Insert Speed (C/C++) > >The app seems to use between 60-80% CPU while it is running (from >Activity Monitor) while disk use (using sudo iotop -P on OSX) seem to >suggest about 20%-30% I/O use. (spikes to over 30million% every 10s or >so, might be when sqlite decides to page out) > >Clearly my computations on the data are not complex enough to max out the >processor (the bulk inserts, at least, is single threaded) while iotop >results suggests I/O isn't maxed out either, which is why I suspect >performance improvements are still possible. > >On the other hand, it is difficult for anyone to suggest more ideas >without seeing the actual source code (professor wants it private for >now), so I will try coding up the virtual table, and pure binary file >dump (as a baseline) to see if I discover any more issues. > >Kevin Xu > > >On Apr 2, 2014, at 1:28 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > >> On Tue, 1 Apr 2014 20:58:14 -0700 >> Kevin Xu <accol...@gmail.com> wrote: >> >>> I am trying to see if it is possible to achieve even higher >>> throughput - my professor was adamant that he managed >>> to insert 3 million rows in 7 seconds (which works out to >>> over 420K inserts per second) though he could not find >>> his code that did it or records of his insertions. >> >>> When I profiled the application (using Instruments), and after >>> inverting the call tree, the time spent within the program is >>> broken down as follows: >> >>> 2170ms 15.1% - sqlite3VdbeExec -> sqlite3_step -> insert function >>> 2142ms 14.9% - pwrite -> unixWrite -> pager_write/pager_write_pagelist >>> 1925ms 14.9% - std::string::insert -> boost::spirit >>> 539ms 3.7% - pack (my compression function) >> >> A question that nobody seems to have bothered to ask, and that will >> entirely direct your solution search: >> >> Is the limit I/O or CPU? >> >> That is, which has hit 100% usage, CPU or I/O? If is is I/O then you >> might want to look for consumers of I/O. The best way to make I/O go >> faster is not to do it. >> >> If the limit is CPU, then you need to devise a way to consume less CPU >> (or get a faster CPU) or to get more processors and parallelize your >> processing. >> >> Secondly, what is the headroom you have available? For example if you >> are using 100% CPU and 97% I/O, then you are damn close to balanced and >> the additional couple of K per second you can gain in I/O is unlikely >> to make any significant difference, and will likely end up being the >> bottleneck even if you spend a couple of million dollars on CPU and >> MEMORY (to drive CPU usage down to 0.0001% but peg I/O to 100%). >> >> If the problem is CPU, then the first place to attack is boost:spirit >> (or use multiprocessing) -- after checking to ensure that you I/O >> system is properly offloaded from the main CPU and not consuming spin >> cycles while doing I/O, of course. >> >> If neither I/O nor CPU is pegged at 100% then you have a crappy >> scheduler or simply insufficient overlap between I/O and compute, and >> you need to fix this first. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
