Need help inserting large amounts of data in realtime

Question

0.00/5 (No votes)

See more:

Hi,
I'm building a multi-threaded app that's using the producer consumer queue threading logic...I have a real-time stockmarket data-feed that's pushing data into the queue which the worker thread then processes (inserts and updates database tables). The problem is towards the end of the stockmarket session the volume becomes VERY heavy and the queue builds at a faster rate than the worker thread can process causing latency with the inserts by 2 or 3 minutes!

Does anyone have any solutions or suggestions on how to flush the entire queue into the table vs. individual inserts or some other way to resolve this?

By the way, I'm using MySQL 5.5 with .net connector (API) and C# 4.0

Thanks,
Donald

Posted 25-Feb-11 10:52am

d.allen101

Updated 25-Feb-11 18:20pm

v8

Add a Solution

2 solutions

Solution 2

C#

Thread thrd;
EventWaitHandle wh = new AutoResetEvent(false);
Queue<string> dataQueue = new Queue<string>();
object locker = new object();
MySQLConnection con;
MySQLCommand cmd;

private void button1_Click(object o, EventArgs e)
{
  con = new MySQLConnection (conString);
  cmd = new MySQLCommand();
  con.Open();
  cmd.Connection = con;

  thrd = new Thread(new ThreadStart(worker));
  thrd.Start();
}
// data feed event handler
private void (object o, QuoteFeedArg quoteArg)
{
  lock(locker)
  {
    dataQueue.Dequeue(quoteArg.QuoteString);
  }
  wh.Set();
}
private void worker()
{
  while(true)
  {
    List<string> data = new List<string>();
    int length = 0;
    
    lock(locker)
    {
      if(dataQueue.Count > 0)
      {
        data.AddRange(dataQueue);
        length = dataQueue.Count;
        dataQueue.Clear();
      }
   }

   if(data.Count > 0)
   {
     for(int i = 0; i < length; i++)
     {
       List<string> quotes = new List<string>(data[i].Split(',')); // quoteArg is in comma delimited format
       
       string stkSymbol = quotes[1];
       double bid = quotes[3];
       double ask = quotes[4];
       double last = quotes[5];
       // etc...
      
       cmd.CommandText += "INSERT INTO table_ " + stkSymbol + "(symbol,bid,ask,last) VALUES(" + stkSymbol + "," + bid + "," + ask + "," + last + etc...)";
     }
     cmd.ExecuteNonQuery();
   }
   wh.WaitOne();
}

</string></string>

Posted 28-Feb-11 11:04am

d.allen101

Updated 28-Feb-11 11:52am

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Espen Harlinn · Accepted Answer · 2011-02-25T12:27:00

Solution 1

MySQL permits an insert to insert multiple records:

INSERT INTO querty(field1, field2, field3) VALUES
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' ');

If you're smart you'll use a separate table for each ticker - provides for more efficient storage utilization and faster inserts. but don't use the ticker name as the table name - remember that both the name and the ISIN number is subject to change - something you usually need to keep track of.

Using separate tables for each security will also allow you to split the load between several computers.

I guess you need at least the following data in each table

SQL

CREATE TABLE
( 
/* bidders id is included with the bid registration - this order may require several transactions */
BidId Integer, /* at least a 64 bit integer */
/* sellers id is included with the bid registration - this order may require several transactions */
OfferId Integer, /* at least a 64 bit integer */
TradeType Integer, /* Trade agreement type */
Price decimal,
Volume decimal
)

Well Donald - it's getting interesting, you're right, it's a lot of data, and if you are doing it right it's an astounding amount of data - good luck :)

If you're not keeping track of bids and offers, their sizes and conditions, you are loosing information that is often valuable to traders. Personally I'd like the whole order book for analysis since this may be used to either implement or counter automatic trading systems, or detect other interesting market conditions.

Update
The System.IO.Log[^] namespace defines an interface for logging to a record-oriented sequential I/O system. Using the classes from this namespace, you can implement your own transaction processing system using the Common Log File System (CLFS) provided by ws2003r2 and Windows Vista.

Another simple alternative is to use H2Sharp[^] with h2[^] - as I think you will find the performaance[^] more to your liking. Use memory tables for high perfomance - and use MSMQ to enque information to another process that will persist the information in the rdbms of your choice (MySQL).

I've used a similar approach, implemented in java, and it works beautifully. Running H2 as an embedded engine allows you to quite easily deal with 50 000 statements per second - so I guess performance will not be an issue. In my solution external .Net processes was able to use the Postgesql ODBC driver to communicate with the embedded server.

BTW: Serialization in .Net can be a real performance killer - but here is a very good solution[^]

Use binary formatter with MSMQ and use FastSerializer, or roll your own, to pass the information as byte[] to MSMQ.

If you are not comfortable with H2, there is always Oracle TimesTen[^] - but I've got no idea about the performance or price - but if it isn't way faster than the Oracle RDBMS it's kind of pointless :)

Update 2
Microsoft Message Queuing – Log Trade information using Microsoft SQL Server[^] - you may find this interesting, given reasonable hardware it should solve your problems :)

Regards
Espen Harlinn

Posted 25-Feb-11 12:27pm

Espen Harlinn

Updated 14-Mar-11 14:38pm

v5

Comments

d.allen101 25-Feb-11 18:40pm

hey what's up espen! I'm not clear on how to implement the solution you're suggesting. Do I write an arbitrary number of 'VALUES (null,'','')'? and I wouldn't know how many items in the queue and it wouldn't be constant. Can you give me an example and a little more explanation on how to go about it? Thanks! Donald

fjdiewornncalwe 25-Feb-11 19:04pm

Hey Donald, Espen is suggesting to use "bulk insert" functionality as I was going to. It would mean that in your application, you cache up to 10, 20, or 100 for that matter, records coming in from your feeds and then write them all to the database at the same time when the cache is full or when the thread decides to flush its queue. That way you remove a ton of latency from the database write process which then allows you to process more in a given period of time.
I'm not sure how good this article is but check out http://metabetageek.com/2010/02/04/learning-mysql-find-in-set-and-bulk-insert-options/.

Espen Harlinn 26-Feb-11 4:51am

Donald, are you working on a real trading system, or just distributing information on a “best effort” basis where nobody is going to get pissed if data gets lost?

The requirements for a real trading system is vastly more complicated, as your system *must* be able to recover from failure at all times. In that case you need both redundancy and transacted persistence for just about everything.

d.allen101 25-Feb-11 21:20pm

thanks marcus, I'm going to check out the article...

d.allen101 26-Feb-11 6:49am

hey espen, yes I'm working on a realTime trading system that's going to be used by traders...but at this point its no where near ready for distribution

Espen Harlinn 26-Feb-11 7:14am

Are you familiar with the FIX protocol? (see:http://www.fixprotocol.org/)

d.allen101 26-Feb-11 7:20am

Yes I've heard about FIX and done a little reading about it but at this stage in the development cycle its not needed for what I'm doing. Right now I'm only concerned with getting the threading process correct and being able to manipulate the data coming off the feed i.e. (archiving and updating DB tables). Hey Espen is the consumer producer queue thread the best/correct way to go about this? And also can give me a little more info on the this whole insert thing?

Espen Harlinn 26-Feb-11 8:13am

Donald, you are going to have to be able to deal with an extraordinary amount of data – managing the order book is going to be way more complicated than just dealing with the financial transactions – it also going to be way more data intensive.

I find it likely that you will have to think about parallelism using multiple redundant servers. For the most heavily traded securities, or forex, a single high performance server will be required for a single instrument – and it’s unlikely that a general purpose rdbms system will be able to keep up with all the traffic as everything will have to be stored – every bid, and every offer, and there is quite a lot more of them than actual trades.

Before you have a firm grasp of the requirements – you will not be able to design your server/solution as you might end up requiring a single server just to execute the order book for one instrument, with several slaves tied in to handle the persisting of the information – think euro/us dollar – about 4 trillion US$ each day. In the last trading session 52,238,756 Intel shares were traded, while I don’t have the number of trades executed - I guess that the number of changes made to the order book was possibly 20 times higher (or more).

Even if you are tying in with an existing system – your solution will have to be able to deal with this extraordinary information flow.

You are going to have to design a persistent distributed publish/subscribe based message queuing system with quite complicated routing requirements, that *ensures guarantied delivery*.

d.allen101 26-Feb-11 8:29am

ok...here's the situation Espen, I'm working for a proprietary options trading firm at the CBOE. There are a total of 8 traders and they're not watching the entire universe of equities or options. They normally follow a handful of ETFs and the most liquid corresponding options. At this time what they're interested in is a few relatively simple analytical tools i.e. (correlations, arbitrage opportunities, etc...) I've written most of the front-end code and it's not that complicated at all but where I'm having most of my trouble is on the back-end when comes to dealing with the ETF inserts! The volume is SOOOO heavy towards the close that my queue build too fast where my worker threads can't process them fast enough therefore causing latency...If I could just get the a handle on the inserts I'd be able to move forward and continue learning but I seem to be at a standstill...

Espen Harlinn 26-Feb-11 9:08am

I've made an update to my answer - it's something I guess will remove your performance bottlenecks for a long time - and it's actually really easy to implement - even if it sounds a bit complicated.

d.allen101 26-Feb-11 9:10am

I'm about to check it out right now Espen

Espen Harlinn 28-Feb-11 16:27pm

I’m a bit curious as to how it worked out, and there is one project jni4net that may help you to run H2 in the java virtual machine, and still have relatively painless integration with .Net
jni4net - http://jni4net.sourceforge.net/

d.allen101 28-Feb-11 17:04pm

Hi Espen. Well I opted out of switching db's but I'm working on the multiple row insert instead but I'm to having a little there also. Here's an example of what the code looks like:

Espen Harlinn 28-Feb-11 18:27pm

I'm playing around with a few thoughts - seems like you are moving towards something I wrote in an answer to one of your previous questions. That answer was a bit more complicated than it needed to be - I'll get back to you in a day or two, it's just something I'd like to test.

Espen Harlinn 14-Mar-11 20:39pm

I've written an article I believe solves your problem, you may find it interesting :)

d.allen101 14-Mar-11 21:52pm

hi espen!where is the article?

Espen Harlinn 15-Mar-11 4:29am

http://www.codeproject.com/KB/cs/Harlinn_Messaging_2.aspx

Espen Harlinn 3-Apr-11 5:07am

Hi Donald - I'm just wondering how it worked out. Your posting on 26 Mar '11 seems to indicate that you are still trying to roll your own queue/thread management solution. I'm just wondering what, if anything, was missing in the article I posted?

Albin Abel 15-Mar-11 15:41pm

This is a Good effort and informative. My 5++, still reading it

Espen Harlinn 15-Mar-11 15:54pm

Thank you, AlbinAbel!

Sergey Alexandrovich Kryukov 15-Mar-11 16:06pm

So much effort! My 5. Looking and those identical like would be enough to divert me from this page immediately...
--SA

Espen Harlinn 15-Mar-11 16:44pm

Thanks SAKryukov!