DB Message error

[kujista]

Hello I get DB error when I add my strategy to ninjatrader.
I reset DB - it's size is about 50MB. I add strategy and in a few seconds error message occurs. In my strategy I use often mysqldata.dll and connection to mysql database. Could this be the reason why message occurs, how can i resolve it? I also include piece of code how i use mysql. Thanks for any tip. Strategy is working anyway, but it is very uncomfortable.

Thanks for any suggestions

Pavel Kujal

[NinjaTrader_Bertrand]

Hi Pavel, we could unfortunately not really support your custom SQL DB connection - however I would suggest isolating out if its usage would be the main contributing factor. So if you would for example run a script without this connection used, would the same error come up or not?

[kujista]

DB Message because of object size

Hello i've been "digging" really deep last days to figure out why i get DB Message Error. There is no trouble in connecting to mysql db using ninjatrader. But problem is when i dynamically generate strategy object during intialization. I have 62 patterns to load and each takes about 5,5MB -> depending on this grows ninjatrader.sdf file and when it exceeds 256MB annoyng message shows up, but strategy is Enabled and trading. when i remove it from chart. ninjatrader.sdf shrinks to previous size about 15MB.

So, what exactly is stored in ninjatrader.sdf file when i add my strategy and enable it? I cannot make patterns smaller but i have 8Gigs of RAM so there should be no problem with memory. Can I define that ninjatrader.sdf can enlarge to more than 256MB? this is very crucial for me to solve this.
Please give me valuable advice.
Thanks a lot.

[NinjaTrader_Bertrand]

Hi Pavel, we would definitely not expect the db size to grow that much from just the enabling your one scirpt. I have tested a script for example with a high # of executions and data and KB burden on the DB was minimal for it - we would not have a way to change that max size warning and would not suggest that approach, since this is really optimized for maximum performance and sizes of that kind should not be needed. So I understand correctly, you're just starting one NS strategy here that would then handle all your patterns from the custom DB? You're not trying to work directly with our NinjaTrader.sdf file here?

[kujista]

Yes Betrand.

I perhpaps didnt make myself clear. I apology for that. I do not work with ninjatrader.sdf database or file. I browsed the ninjatrader.sdf and looks like here:

https://www.dropbox.com/s/7awjqautg50san1/ninjatrader.sdf.png

I do not know what is actually stored in userdata but these are the only rows that cause growing sdf file. In my strategy i work with other database which has nothing to do with ninjatrader.sdf and data in that database cause generating patterns = a lot of lists and arrays with values... is this "generated code" stored in nijatrader.sdf? could this be the problem?

[kujista]

Here is piece of code that does the initialization which causes to growth.. see the lines below
Inicializuji objekt Patterns - this ninjatrader.sdf exceeds 256MB when I enable more than 50 patterns.. by linearizing i calculated pattern size to 5.5MB (i added and decreased number of enabled patterns).

[NinjaTrader_Bertrand]

Thanks for the added infos Pavel - am I correct in understanding that create here multiple strategy instances from your 'master' script for each pattern found?

[kujista]

There is always one strategy (add 1 strategy into chart). this strategy handles all patterns in itself..

[kujista]

The records in DB are zombies But even when i delete all records - file is ok but when i add one strategy = there is new record in nt_strategy db and file keeps getting bigger.

The real trouble is, when strategy places order -> it tries to save order into ninjatrader.sdf and error message pops up. it is real problem when i have 4 charts and on each runs strategy and sometimes happens it can enter at the same time -> means 3 error messages at the same time :-(

[NinjaTrader_Matthew]

The user data is a record of mainly executions, but also position and some bar data information.

If you're seeing this database size grow when the strategy is started, the most likely reason is the number of historical executions that are being calculated by the strategy. You're basically running a backtest over the entire period/bar type when the strategy is started and it will hold onto all those records during that instance of the strategy.

The suggested solution would be see if you can cut down on the number of historical executions, or only run the strategy on real-time data:

http://www.ninjatrader.com/support/helpGuides/nt7/historical.htm

As a hint: since the max database size is 150% of the current size on application start up, you could technically increase the size of your database, to say 1GB, restart NinjaTrader, and you'll then have a max database size of 1.5GB. Or 2GB DB would give you 3GB, etc (SQL CE will support up to 4GBs)

As far as how to increase the size if your database, you'll have to add some superfluous information, such as run a backtest over ten years of data, generating several thousand executions, then save your backtest results from the Strategy Analyzer. You can repeat this a few times until you get to the size you desire. Once you hit the max size, restart NinjaTrader and your new max size will have increased (i.e., you will not be able to add anything up to 256MB, but restarting will allow up to 384MB).

Just keep in mind the larger the database, the longer it's going to take to proceed with certain operations and could lead to some instability problems which we have never really tested databases larger than the recommend 256MB warning that you're seeing.

[egan857]

Hi Kujista,

You asked in another thread if I was still experiencing this problem. Yes, I have a very noisy strategy that creates many trades and modifies existing trades (and yes, those order modifications, even if they modify it to the same price, still eat up space). The solution to that problem was to slow down the pace of the modifications, among other fixes.

But, it sounds like you have a different problem, which I have also encountered. Your database appears to be corrupt with zombie orders. The best way to get rid of those is to shut down NinjaTrader, rename NinjaTrader.sdf to something else, and then restart NinjaTrader. NT will see that it has no database file and will recreate an empty one. You will lose the history of all your orders and any instruments that you've added through the Instrument Manager, but you will be up and running quickly. I have to do this every few days, but you will most likely only need to do this every few months.

[kujista]

egan: thank you for post, but what matthew wrote is exactly the solution which did the trick.

but i have problem that DB enlarges and when i shut down ninja and start again, the DB is small again... i mean i add strategy -> ninjatrades.sdf is 270MB. i remove it from chart, it shrinks back to 30MB.

[kujista]

MMedia example

Hi, let me show you how the DB grow looks:

https://www.dropbox.com/s/0u7wvf9iuhkicro/NinjaTrader_Trouble.mp4

and how does the ninjatrader.sdf is enlarged.

https://www.dropbox.com/s/5lwszsostlwrgy1/Ninjatrader.zip

[NinjaTrader_Bertrand]

kujista, did you try having an XmlIgnore attribute on your custom properties that should not be serialized as being too data heavy for the setup? Would this make a difference? If not, do you think you could come up with a sample script that would use your skeleton so we could reproduce with development here to review more detailed?