Hi Clemens, See below..
sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM ordercallback cb WHERE sessionuuid=(select uuid from session where date = (select max(date) from session)) AND endstate=0 AND working=1 AND cb_seq_num = (SELECT max(cb_seq_num) FROM ordercallback WHERE server_order_id=cb.server_order_id AND sessionuuid=cb.sessionuuid AND working=1); 0|0|0|SEARCH TABLE ordercallback AS cb USING INDEX ordercallback_index5 (sessionuuid=? AND endstate=? AND working=?) 0|0|0|EXECUTE SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE session USING INDEX sqlite_autoindex_session_2 (date=?) 1|0|0|EXECUTE SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE session USING COVERING INDEX sqlite_autoindex_session_2 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3 3|0|0|SEARCH TABLE ordercallback USING INDEX ordercallback_index3 (server_order_id=? AND sessionuuid=? AND working=?) The cpu's are not very busy: top - 14:52:57 up 481 days, 18:07, 5 users, load average: 0.32, 0.36, 0.32 Tasks: 769 total, 1 running, 761 sleeping, 0 stopped, 7 zombie Cpu0 : 18.8%us, 7.0%sy, 0.0%ni, 64.1%id, 0.0%wa, 0.0%hi, 10.1%si, 0.0%st Cpu1 : 8.8%us, 3.4%sy, 0.0%ni, 87.5%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Cpu2 : 9.5%us, 4.1%sy, 0.0%ni, 86.1%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Cpu3 : 7.4%us, 3.0%sy, 0.0%ni, 89.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 5.0%us, 3.0%sy, 0.0%ni, 91.6%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Cpu5 : 6.7%us, 2.0%sy, 0.0%ni, 91.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu6 : 3.4%us, 1.0%sy, 0.0%ni, 95.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 8.3%us, 1.3%sy, 0.0%ni, 90.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu8 : 7.9%us, 2.7%sy, 0.0%ni, 89.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu9 : 8.4%us, 2.7%sy, 0.0%ni, 88.6%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Cpu10 : 10.1%us, 2.4%sy, 0.0%ni, 87.2%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Cpu11 : 4.0%us, 2.0%sy, 0.0%ni, 94.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu12 : 6.4%us, 2.0%sy, 0.0%ni, 91.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu13 : 2.3%us, 1.3%sy, 0.0%ni, 96.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Cpu14 : 2.0%us, 1.0%sy, 0.0%ni, 97.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu15 : 1.7%us, 0.7%sy, 0.0%ni, 97.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu16 : 10.9%us, 6.0%sy, 0.0%ni, 83.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu17 : 8.6%us, 1.7%sy, 0.0%ni, 89.4%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Cpu18 : 3.3%us, 1.7%sy, 0.0%ni, 95.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu19 : 1.0%us, 1.0%sy, 0.0%ni, 98.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu20 : 0.7%us, 0.7%sy, 0.0%ni, 98.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu21 : 1.7%us, 0.3%sy, 0.0%ni, 98.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu22 : 0.7%us, 0.3%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu23 : 0.3%us, 0.3%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu24 : 3.7%us, 1.3%sy, 0.0%ni, 95.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu25 : 4.6%us, 1.3%sy, 0.0%ni, 94.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu26 : 0.7%us, 0.7%sy, 0.0%ni, 98.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu27 : 1.0%us, 0.7%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu28 : 1.3%us, 0.3%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu29 : 2.6%us, 0.7%sy, 0.0%ni, 96.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu30 : 1.7%us, 0.0%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu31 : 0.7%us, 0.7%sy, 0.0%ni, 98.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 32787924k total, 22357144k used, 10430780k free, 52244k buffers Swap: 16465912k total, 38740k used, 16427172k free, 5721572k cached And below is on the machine where the number of inserts is much higher but where the query _does_ always return (and also always quite fast). As you see the cpu's are more busy and the query plan is the same. sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM ordercallback cb WHERE sessionuuid=(select uuid from session where date = (select max(date) from session)) AND endstate=0 AND working=1 AND cb_seq_num = (SELECT max(cb_seq_num) FROM ordercallback WHERE server_order_id=cb.server_order_id AND sessionuuid=cb.sessionuuid AND working=1); 0|0|0|SEARCH TABLE ordercallback AS cb USING INDEX ordercallback_index5 (sessionuuid=? AND endstate=? AND working=?) 0|0|0|EXECUTE SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE session USING INDEX sqlite_autoindex_session_2 (date=?) 1|0|0|EXECUTE SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE session USING COVERING INDEX sqlite_autoindex_session_2 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3 3|0|0|SEARCH TABLE ordercallback USING INDEX ordercallback_index3 (server_order_id=? AND sessionuuid=? AND working=?) top - 14:59:00 up 186 days, 22:28, 7 users, load average: 2.55, 2.24, 2.18 Tasks: 433 total, 1 running, 420 sleeping, 0 stopped, 12 zombie Cpu0 : 16.0%us, 6.8%sy, 0.0%ni, 73.7%id, 0.0%wa, 0.0%hi, 3.4%si, 0.0%st Cpu1 : 97.0%us, 3.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 14.3%us, 5.0%sy, 0.0%ni, 80.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 98.0%us, 2.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 24.3%us, 1.0%sy, 0.0%ni, 74.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu5 : 31.6%us, 0.7%sy, 0.0%ni, 67.4%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Cpu6 : 8.5%us, 12.2%sy, 0.0%ni, 79.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 2.7%us, 1.7%sy, 0.0%ni, 95.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu8 : 4.0%us, 2.7%sy, 0.0%ni, 93.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu9 : 7.0%us, 1.0%sy, 0.0%ni, 92.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu10 : 6.7%us, 1.7%sy, 0.0%ni, 91.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu11 : 3.0%us, 0.3%sy, 0.0%ni, 96.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 32774964k total, 32386456k used, 388508k free, 318704k buffers Swap: 8183800k total, 354796k used, 7829004k free, 20063048k cached On 09/25/2015 01:27 PM, Clemens Ladisch wrote: > gunnar wrote: >> the sqlite client is stuck with the following stack traces > According to these stack traces, the client is not stuck but is > busy searching and reading data from the database. > > What is the EXPLAIN QUERY PLAN output for this query? > >> Disk is not busy > And the CPU? > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
