Database scans need to be faster somehow. about myria-web HOT 4 CLOSED

Problem: DanH performed a simple filter + project query over the NCCDC dataset. He observed that the scan (via SqlLite) was the dominant overhead; he observed 42 seconds of scan overhead. By contrast, Linux can scan this data in ~6 seconds, as shown below.

• time cat nccdc_edges.txt > /dev/null

cold cache: 45.4 seconds (6.4 GB / 45.4 seconds => 144 MB/sec)
warm cache: 1.6 seconds

• Raw disk performance (hdparm -t /dev/sda)
  => 148.19 MB/sec

So, these results are essentially the same. Linux can move sequential file data off the disk at ~150 MB/sec. Pretty damn fast.

Obviously, we spread the data across eight workers. So, reading the mccdc data off disk in parallel should take ~6 seconds if we were purely disk-bound and there were no other sources of overhead.

Questions:

• Where does the extra overhead come from? How much overhead does sqllite introduce? How much do we add?
• Do we get a similar warm cache speedup? The dataset is less than 1 GB per machine, which should trivially fit in the file system buffer cache. It's lame for multiple scans of the same relation to repeatedly hit disk. Memory == cheap. Keeping datasets in memory is part of why spark does so well.

from myria-web.

A sqllite dump of the same data takes 10X longer than the corresponding cat command in Linux:

• Linux cat of raw text file: 45.4 seconds
• sqllite .dump of ingested data: 451 seconds

Details

sqllite test.db

CREATE table nccdc(source varchar(64) not NULL, target varchar(64) not NULL, proto smallint, timestamp integer, col5 integer, col6 integer, col7 integer);
.separator |
.import ./nccdc_edges.txt nccdc

[leave sqllite]

time echo '.dump' | sqlite3 test.db > /dev/null

echo '.dump' 0.00s user 0.00s system 0% cpu 0.001 total
sqlite3 test.db > /dev/null 451.43s user 5.09s system 99% cpu 7:37.42 total

Note that the underlying sqllite database file was only 4% larger than the raw input file, so the slow down is not due to extra disk I/O. sqllite is just slow.

from myria-web.

Sorry to keep delving into minutiae: is it feasible to try this on
literally the same data that was taking 45s to scan in Myria? I suspect a
dump to /dev/null will also take about 45s, confirming that getting data
out of sqllite is the bottleneck.

On Tue, Sep 24, 2013 at 4:14 PM, Andrew Whitaker
[email protected]:

A sqllite dump of the same data takes 10X longer than the corresponding
cat command in Linux:

• Linux cat of raw text file: 45.4 seconds
• sqllite .dump of ingested data: 451 seconds

Details

sqllite nccdc.db

CREATE table nccdc(source varchar(64) not NULL, target varchar(64) not
NULL, proto smallint, timestamp integer, col5 integer, col6 integer, col7
integer);
.separator |

.import ./nccdc_edges.txt nccdc

[leave sqllite]

time echo '.dump' | sqlite3 test.db > /dev/null

echo '.dump' 0.00s user 0.00s system 0% cpu 0.001 total
sqlite3 test.db > /dev/null 451.43s user 5.09s system 99% cpu 7:37.42 total

Note that the underlying sqllite database file was only 4% larger than the
raw input file, so the slow down is not due to extra disk I/O. sqllite is
just slow.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/10#issuecomment-25050140
.

from myria-web.

Using above methodology with a single "shard" containing 1/8 of the database:

Linux cat > /dev/null (cold cache): 5.86 seconds (= 140 MB/sec)
sqllite dump (cold cache): 58.7 seconds

Note that this result is even worse than what Dan was seeing (58 seconds compared to 45 seconds). Maybe the dump operation itself introduces some extra overhead? Both results are much worse than raw / native Linux "scans".

Another bit of sadness: I didn't observe much speedup for a successive run (i.e., with a warm cache). sqllite dump took 57.5 seconds in this case (likely just statistical noise).

from myria-web.