'[sqlite-dev] sqlite4 thrashing on DELETE'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-dev
Subject:    [sqlite-dev] sqlite4 thrashing on DELETE
From:       David King <dking () ketralnis ! com>
Date:       2013-04-29 4:46:33
Message-ID: 6E569F35D86A49B291236B025D0BA7AD () ketralnis ! com
[Download RAW message or body]

Is this a good list to use for sqlite4 bug reports? If so I can move one that went \
unanswered over from the users list as well 

I have a large sqlite4 database that executing a DELETE that would affect about 5% of \
the rows thrashes (for as far as I can tell) forever. It has 109,418,022 rows over \
6.9gb

I can share the database but it's rather large so I'd rather not upload it if \
nobody's going to use it.

I used OS X's profiler to try to see what it was doing, here's the sample text. The \
summary is that it spends 99.8% of its time in lsmMCursorSeek.

Call graph:
2263 Thread_17792716 DispatchQueue_1: com.apple.main-thread (serial)
2263 start (in python) + 33 [0x100000931]
2263 _start (in python) + 224 [0x100000a12]
2263 Py_Main (in python) + 2940 [0x1000fd18c]
2263 PyRun_SimpleFileExFlags (in python) + 489 [0x1000e6ad9]
2263 PyRun_FileExFlags (in python) + 174 [0x1000e681e]
2263 PyEval_EvalCode (in python) + 54 [0x1000c1916]
2263 PyEval_EvalCodeEx (in python) + 2118 [0x1000c17f6]
2263 PyEval_EvalFrameEx (in python) + 4656 [0x1000ba650]
2263 PyObject_Call (in python) + 98 [0x1000063a2]
2263 function_call (in python) + 176 [0x100037580]
2263 PyEval_EvalCodeEx (in python) + 2118 [0x1000c17f6]
2263 PyEval_EvalFrameEx (in python) + 29974 [0x1000c0936]
2263 PyEval_EvalFrameEx (in python) + 29974 [0x1000c0936]
2263 PyEval_EvalFrameEx (in python) + 24589 [0x1000bf42d]
2263 _pysqlite_query_execute (in _sqlite.so) + 2177 [0x10165b331]
2263 pysqlite_step (in _sqlite.so) + 31 [0x10165c76f]
2263 sqlite4_step (in _sqlite.so) + 3090 [0x1016cb6f2]
2263 sqlite4VdbeExec (in _sqlite.so) + 17212 [0x1016c1a9c]
2263 kvlsmSeek (in _sqlite.so) + 40 [0x10169ece8]
2260 lsmMCursorSeek (in _sqlite.so) + 365 [0x10169df6d]
+ 1846 seekInSegment (in _sqlite.so) + 1113 [0x10169ddb9]
+ ! 1408 segmentPtrAdvance (in _sqlite.so) + 104 [0x10169b988]
+ ! : 761 segmentPtrLoadCell (in _sqlite.so) + 72,0,... [0x101697ae8,0x101697aa0,...]
+ ! : 588 segmentPtrLoadCell (in _sqlite.so) + 535 [0x101697cb7]
+ ! : | 572 sortedReadData (in _sqlite.so) + 53,0,... [0x101697555,0x101697520,...]
+ ! : | 12 sortedReadData (in _sqlite.so) + 405 [0x1016976b5]
+ ! : | + 11 lsmFsDbPageNext (in _sqlite.so) + 660 [0x101697504]
+ ! : | + ! 11 fsPageGet (in _sqlite.so) + 1382,1360,... \
[0x101696ca6,0x101696c90,...] + ! : | + 1 lsmFsDbPageNext (in _sqlite.so) + 488 \
[0x101697458] + ! : | 2 sortedReadData (in _sqlite.so) + 336 [0x101697670]
+ ! : | + 2 memmove$VARIANT$sse3x (in libsystem_c.dylib) + 1,70 \
[0x7fff8db93ebe,0x7fff8db93f03] + ! : | 1 DYLD-STUB$$memcpy (in libsystem_c.dylib) + \
0 [0x7fff8dc13646] + ! : | 1 sortedReadData (in _sqlite.so) + 728 [0x1016977f8]
+ ! : | 1 lsmFsPagePersist (in _sqlite.so) + 32 [0x101694c20]
+ ! : 59 segmentPtrLoadCell (in _sqlite.so) + 117 [0x101697b15]
+ ! : 59 lsmSqlite4GetVarint64 (in _sqlite.so) + 0,250,... \
[0x101662d10,0x101662e0a,...] + ! 416 segmentPtrAdvance (in _sqlite.so) + 492,463,... \
[0x10169bb0c,0x10169baef,...] + ! 22 segmentPtrAdvance (in _sqlite.so) + 660 \
[0x10169bbb4] + ! 13 lsmFsDbPageNext (in _sqlite.so) + 660 [0x101697504]
+ ! | 13 fsPageGet (in _sqlite.so) + 1360,1346,... [0x101696c90,0x101696c82,...]
+ ! 9 lsmFsDbPageNext (in _sqlite.so) + 477,488,... [0x10169744d,0x101697458,...]
+ 260 seekInSegment (in _sqlite.so) + 251 [0x10169da5b]
+ ! 260 segmentPtrSetPage (in _sqlite.so) + 39 [0x101695817]
+ 152 seekInSegment (in _sqlite.so) + 1031 [0x10169dd67]
+ ! 117 segmentPtrAdvance (in _sqlite.so) + 104 [0x10169b988]
+ ! : 56 segmentPtrLoadCell (in _sqlite.so) + 535 [0x101697cb7]
+ ! : | 55 sortedReadData (in _sqlite.so) + 53,36,... [0x101697555,0x101697544,...]
+ ! : | 1 sortedReadData (in _sqlite.so) + 405 [0x1016976b5]
+ ! : | 1 lsmFsDbPageNext (in _sqlite.so) + 660 [0x101697504]
+ ! : | 1 fsPageGet (in _sqlite.so) + 1382 [0x101696ca6]
+ ! : 55 segmentPtrLoadCell (in _sqlite.so) + 72,13,... [0x101697ae8,0x101697aad,...]
+ ! : 6 segmentPtrLoadCell (in _sqlite.so) + 117 [0x101697b15]
+ ! : 6 lsmSqlite4GetVarint64 (in _sqlite.so) + 250,229,... \
[0x101662e0a,0x101662df5,...] + ! 33 segmentPtrAdvance (in _sqlite.so) + 492,522,... \
[0x10169bb0c,0x10169bb2a,...] + ! 1 segmentPtrAdvance (in _sqlite.so) + 660 \
[0x10169bbb4] + ! : 1 lsmFsDbPageNext (in _sqlite.so) + 660 [0x101697504]
+ ! : 1 fsPageGet (in _sqlite.so) + 1382 [0x101696ca6]
+ ! 1 segmentPtrAdvance (in _sqlite.so) + 676 [0x10169bbc4]
+ ! 1 lsmFsPageRelease (in _sqlite.so) + 47 [0x10169573f]
+ ! 1 lsmFsPagePersist (in _sqlite.so) + 13 [0x101694c0d]
+ 2 seekInSegment (in _sqlite.so) + 394 [0x10169daea]
+ 2 segmentPtrLoadCell (in _sqlite.so) + 55,72 [0x101697ad7,0x101697ae8]
2 lsmMCursorSeek (in _sqlite.so) + 87 [0x10169de57]
+ 2 treeCursorSeek (in _sqlite.so) + 66 [0x10167c312]
+ 2 lsmTreeCursorSeek (in _sqlite.so) + 284,482 [0x101678d8c,0x101678e52]
1 lsmMCursorSeek (in _sqlite.so) + 990 [0x10169e1de]
1 multiCursorDoCompare (in _sqlite.so) + 127 [0x1016793bf]
1 multiCursorGetKey (in _sqlite.so) + 155 [0x10167910b]
Total number in stack (recursive counted multiple, when >=5):
Sort by top of stack, same collapsed (when >= 5):
segmentPtrLoadCell (in _sqlite.so) 818
sortedReadData (in _sqlite.so) 627
segmentPtrAdvance (in _sqlite.so) 449
segmentPtrSetPage (in _sqlite.so) 260
lsmSqlite4GetVarint64 (in _sqlite.so) 65
fsPageGet (in _sqlite.so) 26
lsmFsDbPageNext (in _sqlite.so) 10
Binary Images:
0x100000000 - 0x100163fef +python (??? - ???) <21ED9EF1-EBB3-61F6-98CB-5D69680D0F12> \
/usr/local/bin/python 0x1002ee000 - 0x1002f1ff7 +zlib.so (??? - ???) \
<E31D6429-FFFB-509C-E3A9-2B95943AC458> /usr/local/lib/python2.7/lib-dynload/zlib.so \
0x1002f6000 - 0x1002f8ff7 +time.so (??? - ???) <5292193C-4E94-FEFD-BAAF-3E422E919A2A> \
/usr/local/lib/python2.7/lib-dynload/time.so 0x1002fd000 - 0x1002fdfff +_bisect.so \
(??? - ???) <49759BD6-E958-7369-3388-3D47851C0B48> \
/usr/local/lib/python2.7/lib-dynload/_bisect.so 0x1004c7000 - 0x1004caff7 +strop.so \
(??? - ???) <DBEE2DF9-3918-3AD2-F220-22A1E30874F8> \
/usr/local/lib/python2.7/lib-dynload/strop.so 0x1004cf000 - 0x1004d2fef +binascii.so \
(??? - ???) <06BDBF0B-2EEF-0F15-B431-5581580B8C9D> \
/usr/local/lib/python2.7/lib-dynload/binascii.so 0x1004d6000 - 0x1004e4ff7 \
+datetime.so (??? - ???) <CBFEE497-85B3-DB5B-8DB6-FB74AFD57992> \
/usr/local/lib/python2.7/lib-dynload/datetime.so 0x1004f0000 - 0x1004f1fff \
+cStringIO.so (??? - ???) <3CA1FB2C-0AB8-B00A-E55D-7AEA985BFF06> \
/usr/local/lib/python2.7/lib-dynload/cStringIO.so 0x1004f6000 - 0x1004faff7 \
+_collections.so (??? - ???) <A59B2798-A98A-5874-78D1-4A090A7C9493> \
/usr/local/lib/python2.7/lib-dynload/_collections.so 0x101380000 - 0x1013b4fe7 \
+pyexpat.so (??? - ???) <44586767-A75C-DBCE-8C88-53F8C903F676> \
/usr/local/lib/python2.7/lib-dynload/pyexpat.so 0x1013c6000 - 0x1013caff7 +_struct.so \
(??? - ???) <08415EC1-2012-15B2-733A-09CE39C86D52> \
/usr/local/lib/python2.7/lib-dynload/_struct.so 0x1013d1000 - 0x1013d5ff7 \
+operator.so (??? - ???) <5284AC2F-E572-9F97-C1CB-0F47311A7781> \
/usr/local/lib/python2.7/lib-dynload/operator.so 0x1013dc000 - 0x1013e3fff \
+itertools.so (??? - ???) <CA2FFF03-FA07-DC6B-30B3-CA1CA6C7D14F> \
/usr/local/lib/python2.7/lib-dynload/itertools.so 0x1013ee000 - 0x1013efff7 \
+_heapq.so (??? - ???) <6C23A32F-8DA2-35E3-957C-C651B6069A3C> \
/usr/local/lib/python2.7/lib-dynload/_heapq.so 0x1013f3000 - 0x1013faff7 +_socket.so \
(??? - ???) <144D0174-B44D-3F44-BD5E-8C2E290216D3> \
/usr/local/lib/python2.7/lib-dynload/_socket.so 0x101404000 - 0x101405ff7 \
+_functools.so (??? - ???) <4AA1D456-1E4F-D3F4-00D5-B19BF4333253> \
/usr/local/lib/python2.7/lib-dynload/_functools.so 0x101408000 - 0x10140cfff +_ssl.so \
(??? - ???) <5C04251C-2667-2CDE-8376-8BEA248BF37D> \
/usr/local/lib/python2.7/lib-dynload/_ssl.so 0x101411000 - 0x101411fff +_scproxy.so \
(??? - ???) <D768C540-A649-6D2A-7F4E-4076C14892BA> \
/usr/local/lib/python2.7/lib-dynload/_scproxy.so 0x101414000 - 0x101429fff +_io.so \
(??? - ???) <0275EB66-C63A-1BC3-8655-B7C7B114032C> \
/usr/local/lib/python2.7/lib-dynload/_io.so 0x101440000 - 0x101445fff +array.so (??? \
- ???) <D3BB224C-1923-953F-CF7A-33BF82C1E8D5> \
/usr/local/lib/python2.7/lib-dynload/array.so 0x10144c000 - 0x101451fff +math.so (??? \
- ???) <01A993CC-68F2-DCA7-C310-1400D1B23ABE> \
/usr/local/lib/python2.7/lib-dynload/math.so 0x101458000 - 0x101459ff7 +_hashlib.so \
(??? - ???) <6CDC6514-398D-DE8D-6047-B2085106816D> \
/usr/local/lib/python2.7/lib-dynload/_hashlib.so 0x10145d000 - 0x10145efff \
+_random.so (??? - ???) <152DC46C-1DE9-F9D2-D860-6AF6CD644751> \
/usr/local/lib/python2.7/lib-dynload/_random.so 0x101461000 - 0x101462ff7 +fcntl.so \
(??? - ???) <B448D49E-236A-83C3-1B3F-2C9562E30C5C> \
/usr/local/lib/python2.7/lib-dynload/fcntl.so 0x101465000 - 0x101467ff7 +_locale.so \
(??? - ???) <791458AB-C286-5E3B-7838-44D20503D00B> \
/usr/local/lib/python2.7/lib-dynload/_locale.so 0x10146a000 - 0x101470ff7 \
+_speedups.so (??? - ???) <0575EAC8-AE45-3DB7-CBAE-FE8B272CEE0C> \
/usr/local/lib/python2.7/site-packages/simplejson-2.1.1-py2.7-macosx-10.4-x86_64.egg/simplejson/_speedups.so
 0x101475000 - 0x101497ff7 +re2.so (??? - ???) <52208424-1D69-3EA2-ADA1-8A46703E8A04> \
/Users/dking/.python-eggs/re2-0.2.20-py2.7-macosx-10.4-x86_64.egg-tmp/re2.so \
0x101600000 - 0x10163aff7 +libre2.0.dylib (??? - ???) \
<0FC5FB7F-F901-3745-8993-D12675C2EB36> /opt/local/lib/libre2.0.dylib 0x101656000 - \
0x101703fe7 +_sqlite.so (??? - ???) <D31806CA-ECDC-3DDC-BF74-068961C07ADC> \
/Users/dking/sync/pysqlite4/build/lib.macosx-10.4-x86_64-2.7/pysqlite4/_sqlite.so \
0x7fff6bce7000 - 0x7fff6bd1bbaf dyld (195.6 - ???) \
<C58DAD8A-4B00-3676-8637-93D6FDE73147> /usr/lib/dyld 0x7fff8b2ea000 - 0x7fff8b2eafff \
libkeymgr.dylib (23.0.0 - compatibility 1.0.0) <61EFED6A-A407-301E-B454-CD18314F0075> \
/usr/lib/system/libkeymgr.dylib 0x7fff8b2eb000 - 0x7fff8b2f0ff7 \
libsystem_network.dylib (??? - ???) <5DE7024E-1D2D-34A2-80F4-08326331A75B> \
/usr/lib/system/libsystem_network.dylib 0x7fff8b85a000 - 0x7fff8b966fff \
libcrypto.0.9.8.dylib (44.0.0 - compatibility 0.9.8) \
<3A8E1F89-5E26-3C8B-B538-81F5D61DBF8A> /usr/lib/libcrypto.0.9.8.dylib 0x7fff8b96a000 \
- 0x7fff8b96efff libmathCommon.A.dylib (2026.0.0 - compatibility 1.0.0) \
<FF83AFF7-42B2-306E-90AF-D539C51A4542> /usr/lib/system/libmathCommon.A.dylib \
0x7fff8be34000 - 0x7fff8be42fff libdispatch.dylib (187.10.0 - compatibility 1.0.0) \
<8E03C652-922A-3399-93DE-9EA0CBFA0039> /usr/lib/system/libdispatch.dylib \
0x7fff8bfb9000 - 0x7fff8bfbaff7 libremovefile.dylib (21.1.0 - compatibility 1.0.0) \
<739E6C83-AA52-3C6C-A680-B37FE2888A04> /usr/lib/system/libremovefile.dylib \
0x7fff8bfd4000 - 0x7fff8bfd6fff com.apple.TrustEvaluationAgent (2.0 - 1) \
<1F31CAFF-C1C6-33D3-94E9-11B721761DDF> \
/System/Library/PrivateFrameworks/TrustEvaluationAgent.framework/Versions/A/TrustEvaluationAgent
 0x7fff8cc11000 - 0x7fff8cc5ffff libauto.dylib (??? - ???) \
<D8AC8458-DDD0-3939-8B96-B6CED81613EF> /usr/lib/libauto.dylib 0x7fff8cd35000 - \
0x7fff8cd70fff libsystem_info.dylib (??? - ???) \
<35F90252-2AE1-32C5-8D34-782C614D9639> /usr/lib/system/libsystem_info.dylib \
0x7fff8d5fd000 - 0x7fff8d7d1ff7 com.apple.CoreFoundation (6.7.2 - 635.21) \
<62A3402E-A4E7-391F-AD20-1EF20236CE1B> \
/System/Library/Frameworks/CoreFoundation.framework/Versions/A/CoreFoundation \
0x7fff8db6c000 - 0x7fff8dc49fef libsystem_c.dylib (763.13.0 - compatibility 1.0.0) \
<41B43515-2806-3FBC-ACF1-A16F35B7E290> /usr/lib/system/libsystem_c.dylib \
0x7fff8df6d000 - 0x7fff8dfafff7 libcommonCrypto.dylib (55010.0.0 - compatibility \
1.0.0) <BB770C22-8C57-365A-8716-4A3C36AE7BFB> /usr/lib/system/libcommonCrypto.dylib \
0x7fff8dfb0000 - 0x7fff8dfb6ff7 libunwind.dylib (30.0.0 - compatibility 1.0.0) \
<1E9C6C8C-CBE8-3F4B-A5B5-E03E3AB53231> /usr/lib/system/libunwind.dylib 0x7fff8ef2e000 \
- 0x7fff8ef38ff7 liblaunch.dylib (392.39.0 - compatibility 1.0.0) \
<8C235D13-2928-30E5-9E12-2CC3D6324AE2> /usr/lib/system/liblaunch.dylib 0x7fff8eff8000 \
- 0x7fff8effdfff libcache.dylib (47.0.0 - compatibility 1.0.0) \
<1571C3AB-BCB2-38CD-B3B2-C5FC3F927C6A> /usr/lib/system/libcache.dylib 0x7fff8f07b000 \
- 0x7fff8f080fff libcompiler_rt.dylib (6.0.0 - compatibility 1.0.0) \
<98ECD5F6-E85C-32A5-98CD-8911230CB66A> /usr/lib/system/libcompiler_rt.dylib \
0x7fff8f160000 - 0x7fff8f162fff libquarantine.dylib (36.7.0 - compatibility 1.0.0) \
<8D9832F9-E4A9-38C3-B880-E5210B2353C7> /usr/lib/system/libquarantine.dylib \
0x7fff8ff5e000 - 0x7fff8ff5ffff libdnsinfo.dylib (395.11.0 - compatibility 1.0.0) \
<853BAAA5-270F-3FDC-B025-D448DB72E1C3> /usr/lib/system/libdnsinfo.dylib \
0x7fff902f8000 - 0x7fff90303ff7 libc++abi.dylib (14.0.0 - compatibility 1.0.0) \
<8FF3D766-D678-36F6-84AC-423C878E6D14> /usr/lib/libc++abi.dylib 0x7fff9046f000 - \
0x7fff90477fff libsystem_dnssd.dylib (??? - ???) \
<584B321E-5159-37CD-B2E7-82E069C70AFB> /usr/lib/system/libsystem_dnssd.dylib \
0x7fff90f24000 - 0x7fff90f25fff libunc.dylib (24.0.0 - compatibility 1.0.0) \
<337960EE-0A85-3DD0-A760-7134CF4C0AFF> /usr/lib/system/libunc.dylib 0x7fff90f3c000 - \
0x7fff90f45ff7 libsystem_notify.dylib (80.1.0 - compatibility 1.0.0) \
<A4D651E3-D1C6-3934-AD49-7A104FD14596> /usr/lib/system/libsystem_notify.dylib \
0x7fff91afd000 - 0x7fff91b01fff libdyld.dylib (195.6.0 - compatibility 1.0.0) \
<FFC59565-64BD-3B37-90A4-E2C3A422CFC1> /usr/lib/system/libdyld.dylib 0x7fff92019000 - \
0x7fff92052fe7 libssl.0.9.8.dylib (44.0.0 - compatibility 0.9.8) \
<79AAEC98-1258-3DA4-B1C0-4120049D390B> /usr/lib/libssl.0.9.8.dylib 0x7fff923ff000 - \
0x7fff92475ff7 libc++.1.dylib (28.4.0 - compatibility 1.0.0) \
<A24FC3DA-4FFA-3DD2-9DCC-2B8D1B3BF97C> /usr/lib/libc++.1.dylib 0x7fff92a5f000 - \
0x7fff92a60ff7 libsystem_blocks.dylib (53.0.0 - compatibility 1.0.0) \
<8BCA214A-8992-34B2-A8B9-B74DEACA1869> /usr/lib/system/libsystem_blocks.dylib \
0x7fff92c33000 - 0x7fff92c3afff libcopyfile.dylib (85.1.0 - compatibility 1.0.0) \
<0AB51EE2-E914-358C-AC19-47BC024BDAE7> /usr/lib/system/libcopyfile.dylib \
0x7fff94a92000 - 0x7fff94aaffff libxpc.dylib (77.19.0 - compatibility 1.0.0) \
<9F57891B-D7EF-3050-BEDD-21E7C6668248> /usr/lib/system/libxpc.dylib 0x7fff95dcb000 - \
0x7fff95e17ff7 com.apple.SystemConfiguration (1.11.3 - 1.11) \
<0A7F1982-B4EA-3424-A0C7-FE46C6224F03> \
/System/Library/Frameworks/SystemConfiguration.framework/Versions/A/SystemConfiguration
 0x7fff95efc000 - 0x7fff95f1cfff libsystem_kernel.dylib (1699.32.7 - compatibility \
1.0.0) <66C9F9BD-C7B3-30D4-B1A0-03C8A6392351> /usr/lib/system/libsystem_kernel.dylib \
0x7fff95fac000 - 0x7fff961aefff libicucore.A.dylib (46.1.0 - compatibility 1.0.0) \
<0176782F-9526-3905-813A-7A5676EC2C86> /usr/lib/libicucore.A.dylib 0x7fff96283000 - \
0x7fff96284ff7 libsystem_sandbox.dylib (??? - ???) \
<5422BA4A-4C96-3BC1-AF83-14F0CED0ECB5> /usr/lib/system/libsystem_sandbox.dylib \
0x7fff96ea1000 - 0x7fff96f14fff libstdc++.6.dylib (52.0.0 - compatibility 7.0.0) \
<6BDD43E4-A4B1-379E-9ED5-8C713653DFF2> /usr/lib/libstdc++.6.dylib 0x7fff97819000 - \
0x7fff978fde5f libobjc.A.dylib (228.0.0 - compatibility 1.0.0) \
<871E688B-CF57-3BC7-80D6-F6476DFF109B> /usr/lib/libobjc.A.dylib 0x7fff978fe000 - \
0x7fff97910ff7 libz.1.dylib (1.2.5 - compatibility 1.0.0) \
<30CBEF15-4978-3DED-8629-7109880A19D4> /usr/lib/libz.1.dylib 0x7fff9798e000 - \
0x7fff9798ffff libDiagnosticMessagesClient.dylib (??? - ???) \
<3DCF577B-F126-302B-BCE2-4DB9A95B8598> /usr/lib/libDiagnosticMessagesClient.dylib \
0x7fff97dbe000 - 0x7fff97debfe7 libSystem.B.dylib (159.1.0 - compatibility 1.0.0) \
<DA79E5BA-BBA3-3768-AAD8-B34BA877EF03> /usr/lib/libSystem.B.dylib 0x7fff97f6d000 - \
0x7fff97f73fff libmacho.dylib (800.0.0 - compatibility 1.0.0) \
<165514D7-1BFA-38EF-A151-676DCD21FB64> /usr/lib/system/libmacho.dylib Sample analysis \
of process 78288 written to file /dev/stdout


[prev in list] [next in list] [prev in thread] [next in thread]
Configure | About | News | Add a list | Sponsored by KoreLogic