Re: Difference in query speed for rotating disk and SSD

[Andy Seaborne <an...@apache.org>]

On 31/07/2020 10:37, Rob Vesse wrote:
> I don’t have any specific queries to suggest since I have no familiarity with the database but an observation on the query shown
> 
>   
> 
> Scanning the whole database and counting the triples inherently requires a full traversal of the B-Tree so what you have shown so far is a corner case extreme.  Of course more complex queries could in fact require multiple traversals over the whole database and see a wider performance difference but YMMV
> 
>   
> 
> Rob
> 
>   
> 
> From: Wolfgang Fahl <wf...@bitplan.com>
> Organisation: BITPlan GmbH
> Reply to: <us...@jena.apache.org>
> Date: Friday, 31 July 2020 at 08:51
> To: <us...@jena.apache.org>
> Subject: Difference in query speed for rotating disk and SSD
> 
>   
> 
> Dear Apache Jena users,
> 
> the experience with the http://wiki.bitplan.com/index.php/Get_your_own_copy_of_WikiData trials and the unanswered question
> https://stackoverflow.com/questions/61813248/jena-tdbloader-performance-and-limits led me to the assumption that it would be possible
> to run the wikidata import for Jena on a costly 4 TB SSD but then use the resulting database on much cheaper rotating disk and see not much of a performance difference for queries then running from the SSD.
> 
> My assumption was based on the https://jena.apache.org/documentation/tdb/architecture.html and the mentioned use of https://en.wikipedia.org/wiki/B+_tree.
> I thought the B+ tree approach is optimized for making sure that not too many time costly seeks are necessary when fetching data during a query.
> 
> My experiment at http://wiki.bitplan.com/index.php/WikiData_Import_2020-07-15#log_for_query shows a different result for the query:
> SELECT (COUNT(*) as ?Triples) WHERE { ?s ?p ?o}

This requires going through every block of one of the B+Trees (the SPO 
one unless it is default-union-graph and it's GSPO).

How long this takes not only depends on disk/SSD but also caching.

TDB uses the OS file system cache for the B+Trees.

Your queries are [5] and [7].  If only small queries have been done, 
then not only is the disk cache cold, Java has not JITed much code.

Now, if the data is all loaded at once, it will, by chance, be laid out 
on disk roughly sequentially for the SPO index which means the OS will 
tend to read ahead a bit - not much because the amount fo work per block 
is not much so the OS does not have much chance to read ahead.  Read 
head only has any effect on large scans.

> It takes 31.501 secs on a rotating disk which is only a bit slower than the SSD in raw i/o but has the seek time of a rotating disk while the SSD does not have this performance penalty and the query takes 5.516 secs for the SSD.

The important factor for this query (which is not typical) is the I/O 
throughput of the interface.  If a query hops around the graph, latency 
is the more important factor.

> 
> Would other queries see the same factor 6 difference or does the speed difference depend on the query? Please suggest some queries that i might test and then I will report the results here.

Depends on query and history of the server since launch and amount of RAM.

For an SSD, the speed ration of SSD/RAM is less that disk/RAM and the 
latency is less (how much depends on the way the SSD is connected e.g. 
NVMe vs SATA3 ... number of PCIe lanes ...)

Writing a cache that is matched to the needs of a database is hard - 
lots of factors, different workloads - which makes it a high-resource task.

Resources includes both people and hardware for experimentation and 
testing. Given the resources available, there is a limit to how much can 
be done.

> 
> Cheers
> 
>    Wolfgang
> 
>   
>