Speeding up database queries with vmprobe

March, 2016

Cache preloading

Pre-populating the filesystem cache is effective in many situations. In this white-paper we will demonstrate some results that are possible for any workload that meets certain criteria:

A typical example of this, which is relevant at nearly every company operating a large relational database, is scheduled reporting. Companies often run cron-jobs that issue a series of SQL queries in order to create business intelligence or compliance reports.

When reporting jobs run, they are often slow because they need to read in data that wasn't being used during regular daily business. Furthermore, this can result in other data being kicked out in order to make room. After the report job finishes, it doesn't put the cache back to the way it was before, meaning the next customers who access the database will suffer poor performance while the cache is re-warmed.

Our objective is to improve this situation by managing the page cache more intelligently.

Experiment

Amazon Web Services has generously provided equipment for Vmprobe to run these and other experiments through their AWS Activate program.

This experiment uses a single "m4.xlarge" server (4 CPUs, 16 GiB of RAM) running PostgreSQL 9.4. The database files reside on a 500 GiB "General Purpose SSD" (IOPs: 1500 / 3000) connected via EBS.

TPC-H's DBGEN database benchmark was used to generate an approximately 40 GiB database (schema) and some sample queries. We used a copy of DBGEN pre-configured for postgres by Dragan Sahpaski which you can find in our github fork.

For 3 of the queries (query 2, query 11, query 16) we took measurements of the following operations:

Each time we started from a cold (empty) cache.

Results

In all these cases, pre-loading the cache prior to running the query outperformed simply running the query by itself.

This may be a surprising outcome if you were expecting that accessing the disk in parallel with running the query ought to be optimal. The reality is that postgres, even though it is an outstanding database, accesses the disk less efficiently than vmprobe. Postgres can't look into the future to determine what pages it will need in order to fulfill the query. But, for the purpose of this experiment, and often in real life too, we in fact can because we saw what the query (or a similar query) did the last time it was run.

Investigation

In order to figure out what's happening, we took per-second measurements with sar -b 1 during all the runs.

The first thing to notice is that vmprobe loads blocks from storage at a higher and more consistent rate:

The red line represents when the vmprobe job finished and when the SQL query was issued.

The higher read volume is possible because we can read-ahead large amounts of data we know is needed.

The second point to notice is that using vmprobe to preload the cache issues a much smaller number of IOPs by all metrics: average rate, peak rate, and total:

Again, the red line represents when the vmprobe job finished and when the SQL query was issued.

The corresponding charts for the other queries are here:

Future

You may have been wondering why we chose those particular 3 queries. The reason is because these queries don't access the lineitem table which is too large to fit in memory. Currently vmprobe will not help for the majority of these cases. We are working on a solution to this problem called traces. If snapshots are a record of the VM state at a particular instant in time, like a photo, then (continuing the metaphor) traces are like a movie that records the VM state throughout the progress of an operation.

Traces will add several features that will provide a lot of value for our customers:

A lot of the low-level algorithms underlying our current computing infrastructure only make use of information that can be determined locally, either in space or in time. Vmprobe's mission is to use all information available.

Want to try vmprobe right now? Please see our install documentation!

Interested in beta-testing vmprobe in your company? Please contact us.