mysql database code sample

[ctrlbrk]

auspiv, all:

I wanted to thank you again. I now have a dedicated Ninja box capturing tick data 24/7.

I have several plans for the data.

As time progresses, I'll be happy to upload my DB to share with others.

Mike

[auspiv]

auspiv, all:

I wanted to thank you again. I now have a dedicated Ninja box capturing tick data 24/7.

I have several plans for the data.

As time progresses, I'll be happy to upload my DB to share with others.

Mike

you're very welcome.

24/7 tick data capture was the exact reason i wrote it and wanted to share it. i have a dedicated ninja box as well, capturing each tick on eight instruments. i know we all want tick data, and i just wanted to get something out there so people could use it.

the sharing of the data is also something i've had in mind. when i get done with my first year of college here in a few weeks, i'm planning on writing a website to share/exchange tick data.

[ctrlbrk]

auspiv,

Have you made any other changes to the code?

[auspiv]

no, it has been working good enough for me, so i haven't changed anything. it does eat CPU and memory, but when i have a spare computer to just collect tick data, it doesn't matter how intensive it is. when i start running strategies on the computer i might have to optimize the code thought.

[ctrlbrk]

It seems v6.0 of the .net connector has been released. I didn't notice it earlier today when I was setting all this up, and installed 5.2. The webpage says 6.0 is much faster, I will try it later.

My next step is to get an Excel spreadsheet to access to MySQL data. I've not done this before. I am not sure, but I think I need to download the ODBC connector. I will be trying to get this setup next, as part of my other project:

Excel statistics, trending, patterns, probabilities
http://www.ninjatrader-support2.com/...ad.php?t=15878

Mike

[ctrlbrk]

Wow that was easy. Here are my steps to access via Excel 2007:

1) Download the mySQL 5.1 ODBC connector here: http://dev.mysql.com/downloads/connector/odbc/5.1.html

2) Installed.

3) Control Panel -> ODBC connections.

4) Setup a new User DSN.

5) Back to Excel, selected Data -> Other Sources -> Data Connection Wizard -> ODBC.

6) My ninja db was listed.

Then I just choose which instrument (I have multiple tables in the db), and it populated!

Cool!





Mike

[ctrlbrk]

I successfully upgraded the mysql connector from 5.2 to 6.0.2 from here: http://dev.mysql.com/downloads/connector/net/6.0.html

Uninstalled the first one, installed the 6.0.2 version, and then modified the Ninja reference DLL to the new path.

I can confirm I didn't get any errors and new tick data is being written to the db.

Hope this helps someone else,

Mike

[roonius]

Is there any preference for mysql? why not MSSQL express which is free and with full internet of support?

[ctrlbrk]

Is there any preference for mysql? why not MSSQL express which is free and with full internet of support?

I chose mySQL because I already had a mysql server up, phpmyadmin, etc. So was easiest for me.

Mike

[ctrlbrk]

auspiv,

Are you just loading multiple Charts and adding the indicator to the chart (or using a template)?

That is what I've done. Taken a starting template of "none" and added the indicator for that ticker, only.

But, for eight charts I am concerned about Ninja crashing due to out of resources/memory/1.3GB limit etc. Have you found you can leave the charts open for weeks on end without them crashing? Or are you using another way?

Mike

[ctrlbrk]

Well been working on this off/on tonight. First, I am playing with changing the INSERT to INSERT DELAYED. With my config it seemed to have helped smooth the burden/load. Since we don't care about immediate access, delayed is fine. Still playing with this, as if delayed isn't required then it is actually more costly in the long run.

I also modified the .cs code a bit to add a Parameter so when you add the indicator to the list on the chart, you can specify the ticker symbol for the mysql table. This is better than one indicator per instrument, especially when making code changes

I think the next step is optimizing the connections. Opening a new connection ever OnMarketDepth is expensive I think. Should be able to open a connection, specify a timeout period, and leave it open unless there is a timeout (like during overnight). My first stab at this failed and ended up opening a few hundred connections on mysql box. I will need to try again

I've got about 1 million ticks in the db so far, and it's averaging almost 100 per second on the db. phpmyadmin is complaining about one thing which I need to research further. Each table as a PRIMARY auto_increment already:

Handler_read_rnd_next 9,131

The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Mike

[NinjaTrader_Dierk]

Guys let me provide you some info before you spend a lot of time on that issue: historical chart data in NT7 will be stored as files in the file system and no longer in the database. We will provide details as we roll first beta of NT7.

[TAJTrades]

Guys let me provide you some info before you spend a lot of time on that issue: historical chart data in NT7 will be stored as files in the file system and no longer in the database. We will provide details as we roll first beta of NT7.

Many, many thanks for the heads up on this. I was getting ready to follow the lead of the others but will now hold off.

[kuhasu]

Guys let me provide you some info before you spend a lot of time on that issue: historical chart data in NT7 will be stored as files in the file system and no longer in the database. We will provide details as we roll first beta of NT7.

Thank you so~~ much!

[decus]

I think the next step is optimizing the connections. Opening a new connection ever OnMarketDepth is expensive I think. Should be able to open a connection, specify a timeout period, and leave it open unless there is a timeout (like during overnight). My first stab at this failed and ended up opening a few hundred connections on mysql box. I will need to try again

Have a look at persistent connections and the min/max pool sizes.

As for mySQL vs MSSQL, well I would argue there is more user support for mySQL than MSSQL free edition, but it basically comes does to user choice.

Myself, I'm using mySQL with ninjatrader strats to not trade around news events. I wrote a java app to strip forex news from a website every day then when I go to enter a trade my strat checks the db to see if there is news coming up.

I decided to use a db and not parse the website directly in NT mainly so that I backtesting would be quicker, and also I didn't want to put extra burden on the news website I was parsing.

Also, my mySQL db is on another computer within my LAN, seems to work fine so far.

edit: I spell good