0
$\begingroup$

I've assembled a large dataset (~2B+ records) of options price data in MySQL for backtesting purposes.

At a number of points, due to the sheer amount of data being retrieved and filtered, processing was extremely sluggish to the point of halting. I've spent a good amount of time creating thoughtful indexes, and relatively simple queries complete quickly, but anything over a wider range of inputs (eg, ticker, time period, price, etc) has been very slow.

I've considered TSDBs (eg, kdb+, InfluxDB) but practical considerations were limiting. Have otherwise considered leaving in flat files and manipulating with something low-level, but not terribly clear on an approach.

Anyone with experience backtesting options strategies over lengthy-ish periods (5-10y+) have guidance on what's worked with them for testing purposes.

$\endgroup$
2
  • 1
    $\begingroup$ In order for us to better understand your requirements, could you provide your typical data access patterns? Roughly, what does your schema look like? Which further performance options of MySQL did you consider, e.g. in-memory? Are you performing the backtesting operations in the database, or are you "simply" pulling data out of the system in large chunks? I think there could be some people here with good hints and ideas; but if all fails, you could also try your luck over at DBA SE. $\endgroup$ Sep 14, 2021 at 7:10
  • $\begingroup$ @Kermittfrog Hi there, thanks for opining. As a first step, data access is typically applied via MySQL Workbench in Ubuntu. The query in question draws on a single table of US equity option chains over a relatively long period (10y+). I've asked on both the dedicated DBA and MySQL SE with limited effect. Mostly looking for input from folks that have done something related that can provide guidance on what domain has been the most effective. $\endgroup$
    – Chris
    Sep 14, 2021 at 8:39

1 Answer 1

7
$\begingroup$

My answer is similar to the one given for this other question.

If you are mainly using the data for backtesting, there's very little reason to store the data in a MySQL database. The data generally follows a write-once, read-many (WORM) pattern, with no need for ACID semantics. You also don't have to enforce referential integrity on most of the data. If you want to do so for the parts relating to the instrument definitions (e.g. underlying, strike price, expiration date, put/call), you can split out only the definitions and put those in a MySQL database.

Generally I see people storing the data in a MySQL database only because of (1) limited experience with other storage formats and tools, (2) excess exposure to MySQL because of non-financial or web development background. It is reasonable to start with MySQL because it is a tool you're familiar with, but once you start running into performance constraints here, it would be one of the most obvious pieces in your stack to optimize away.

If you still want to use a DBMS for this use case so you don't have to write custom routines for processing the data, I would suggest something like Clickhouse, which I've had good experience with. It doesn't have the cost or licensing limitations that come with kdb or Vertica, and is definitely more suited for the use case than InfluxDB. ~2B+ records is actually fairly small, as there are production Clickhouse deployments that run queries and materialized views on a dataset that is growing by 6 million entries per second.

Another observation is that you're storing all of that data in 1 single, large table with 1 field identifying the ticker. This is often an anti-pattern for this use case because:

  1. Options data is fairly sparse - there are a large number of tickers over which most have very few entries.
  2. You almost never have a strategy that needs to backtest over all or most of the tickers at once.

Often, it will make more sense to split the data into smaller tables, e.g. 1 per instrument. On paper this will seem unintuitive because you have to pay linear time to merge the smaller sorted tables back together and recompose the set of tickers. However it is generally more efficient than keeping the data in 1 large table and filtering/discarding entries from the table. This is driven by practical considerations:

  1. The number of entries of interest is small relative to the total number of entries.
  2. You're saving significant I/O cost which will dominate the processing time for this type of workload.

Of course, 1 "table" per instrument could be hard to administrate whether in a DBMS or in flat files in a file system, so you may want to come up with some kind of "sharding" strategy, e.g. distribute the data by some hash of the ticker, so you end up with a more manageable number of "tables", say, something in the order of 16 to 1024. The most naive hashing strategy is to split them by the first character of the ticker. Another naive hashing strategy is to split by length of the ticker.

Note: At my day job, we store full order book data for markets with billions of records per day per market. My suggestions are motivated by experience managing petabyte-scale deployments for such data.

$\endgroup$
1
  • $\begingroup$ +1 I think this (and your other) answer perfectly show that for many applications (strategy backtesting, or other forms of time series analysis), all you need is to make sure that your data has been stored consistently (once); after that you could simply put everything in memory (I exaggerate) ... $\endgroup$ Sep 14, 2021 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.