Storing ticks

[nybondking]

Hey NP-



Been doing a bit of digging with respect to storing tick data locally.



Right now I have a java program listening for all ticks (right now, the MQ topic sends out ticks for most major front month futures contracts, a total of around 20-30 instruments max. Some are high volume (ES,etc.), others are low volume. The database I am using right now is SQLite to capture and store the ticks throughout the day, and then at a specified time (1615 eastern) it shuts off the subscription to the MQ and begins adding all of the ticks to a MYSQL database. Each symbol has it's own table.



The problem I am running into is two-fold:

1) High disk I/O rates during the day causes some app monitoring tools to go nuts and send out alerts.



2) Somewhere in the process the memory is getting eaten up at an alarming rate. So much that after 3-4 hours the server (running Linux) slows to a crawl and eventually dies.



I think I figured out the high disk i/o rate issue... When each tick is received, the program inserts it into the SQLite database.



My main question moving forward as I try to refactor this thing this weekend is what would be a common approach to handling/capturing intraday tick data? I have another program listening to the same MQ feed, for all ticks, and interfacing with R at specified intervals to run tick level stats. That program is storing all the ticks for each symbol in memory. I haven't run into memory issues with that one.



I'm more concerned about what happens if the app crashes during the day. If the app crashes, my original reasoning would be that the SQLite DB would have all ticks up until the point of crashing, but I see that committing each and every tick is.. not the most optimal solution.



I was thinking more about it as I was writing the post. I could do a timer solution where every X secs/mins a separate thread would read the matrix & then do a quick batch update of the ticks to SQLite, clear the matrix and start over. Any thoughts on this?



thx

[scottl]

If you have access to the hardware, you might try solid state disks. I've been successfully using SQLite on an SSD for local cacheing of expensive method calls in one of my projects.



Alternatively, split your system into parts: To recover from crashes, allow the data gatherer and database loader to run as separate processes, and have a watcher process to immediately restart any failed portion. You could also go directly to one of the many database configurations out there to handle your write volume, and remove the complexity of the local cache altogether.

[FDAXHunter]

This is trivial:



Your feed handler logs the incoming data not to a database, but to a journal file (binary). Flush buffers once they exceed a threshold (say 1MB). At the end of the day, you load the journal file into the database. Should the feed handler crash, you write a new journal file. You don't want any DB on the Feed handling side. Build something robust that can give you a solid recovery in a short period of time.



Then you push to the DB.



Also, having a separate table for different symbols is a bad idea (on a scale of 0 to 10 it's definitely a zero, it's going to give you very poor performance due to excessive disk seeking).

[signalseeker]

Follow FDAX's advice. You can even log to a text file to make things simpler, but loading would take more time later. Not sure why you need sqllite here. Either ways inserting every tick is definitely very very expensive. If you insist on using sqllite, you should do batch inserts - say do one insert for 1000 ticks. Try to isolate the memory issue - unless your feed handler is holding all data in memory as well, its memory foot print should be very small. 20 - 30 futures symbols is hardly any data.

[jslade]

FWIIW, what are you guys using for a DB? I don't have the dough for KDB and have been screwing around with HDF and DBM. Is MonetDB the shizz? Like many, I don't need all the spooge associated with multi-access: I just want to store my junk and get 'em back when I need it.

[braincat]

If you insist on using sqlite, trying doing many inserts in a single transaction, between begin/commit block. Please see

this link

and the rest of the FAQ.

[cowpoke]

@jslade: I ditched MySQL and went back to HDF (PyTables from Python). It's so darn simple, which matches my needs. Definite inefficiencies, although some of those are my lack of sophistication rather than limitations of PyTables vs MySQL. I'm usually only working with a few Gig at a time, so I just slurp the whole thing into memory and select/massage it with Python or whatever... I've never enjoyed the SQL commands anyways.

[braincat]

On the topic of HDF5, I think the following would be interesting:



http://www.puppetmastertrading.com/blog/2009/01/06/tick-data-hdf5-part-2/#more-280

[nybondking]

Interesting. I'll take a look into the journalling method here in a bit.



Part of the reason why I decided on sep. tables was to make the integration with the R package 'quantmod' a bit easier, (actually it's more laziness on my part). Their model calls for setting symbol defaults (i.e. for US2Y, you specify that it belongs to their MYSQL handler instead of the regular Yahoo function) that use the separate symbol/table methodology.



However, I'll try to tinker with the quantmod code to see if I can't create something that has all the symbols in one table (in addition to trying the journalling method)



Thanks FDAX and everyone for the responses, I'll try them out and report back my findings.



Scottl - That's one area I've looked at, however I'd like to at least optimize the routine on non-SSD first, and flush out any crappy design considerations at first.



JSlade - it's a mix.. I chose SQLite just to have a local "copy" of the data in case something went wrong, I could manually run a little tool to extract what data it did save and put it into a mysql db. The main reason I chose mysql was because of the quantmod/R integration mentioned above. I definitely don't have the dough for kdb

[nybondking]

EDIT2 (Didn't want to keep posting each update over and over):



2010-05-02 18:06:45 [INFO] - Inserting data into database

2010-05-02 18:06:49 [INFO] - Committing Batch Transaction

2010-05-02 18:06:58 [INFO] - STATS: MktData: [271001]

2010-05-02 18:07:02 [INFO] - Batch transaction complete. All ticks stored

2010-05-02 18:07:02 [INFO] - Closing MYSQL connection

2010-05-02 18:07:02 [INFO] - Database connection closed



Boom. Worked well, the entire batch commit for all the ticks took under 30 seconds. The tick 'journal' file (csv) is 2.93MB.



Seems to have done the trick for now. I'll work on the other stuff that I mentioned earlier later on. For now, I'm getting out, enjoying the sun and a few (ok more than a few) beers.



Cheers NP, thanks to FDAX and all.







EDIT: Suggestions seem to be working great so far (haven't gotten to the DB dump yet, just letting the playback feeds hit it with around 50 symbols) ... Memory consumption is minimal, and disk I/O hasn't triggered any alarms yet. So far, I seem to be on the right track. Will continue to watch it. Again, much appreciated. hope this helps someone else out in the future..



-nyb



Ok, I think I got it working somewhat acceptably, at least testing on the weekend replay feed.



FDAX, the object coming across the wire in this case on the MQ, was not serializable. I just took the lazy path for right now (itching to get outside) and used openCSV to write each tick to a csv file in realtime (thx signalseeker). I'll circle back to the binary journalling idea later, there's gotta be some changes made to the initial architecture to fix some flaws I found during this little experiment. At least now I know they're there.



I changed the DB to just have one table for each asset class (EquityTicks, FuturesTicks, CurrencyTicks, BondTicks, etc.)



Using the symbol as a distinct table method, storing each tick in respective tables took about 10 minutes just for about 3 minutes of sample data. Clearly not optimal. Using your suggestion FDAX, I kept everything in one table, and after each tick is added via a method, it adds that to a PreparedStatement. After all the ticks from the file are read, one call to do a batch update is done. That took 4 minutes, but I am accessing the database remotely and on a quite crappy connection.



So, it's an improvement. I'll put this copy of the thing on the server under question and let the playback feed bang on it for a couple hours to see if any I/O alerts get generated or I run into memory issues.



Next up, I guess will be to poke around the 'quantmod' getSymbols.MYSQL code to see if I can make that work with the new single table layout.



Thanks for all your help fellas in pointing me in the right direction and also for helping me see some design flaws in the overall architecture.