0

Broader View: Database plan to maintain Stock EOD data.

Tools at hand: I am planning to use MySQL database 4.1+ alongwith PHP. A custom DBAL (based on mysqli) is implemented in PHP to work with MySQL. However I am open to any other database engines provided they are available free and work with SQL statements :P

Problem Domain: I need to plan out a database for my project to maintain the EOD data for stocks. Since, the number of stocks maintained in the database is going to be huge, so the updation process of EOD data for the same is going to be pretty heavy process at end of day. Needless to say I am on shared hosting and have to avoid MySQL performance bottleneck, during initial startup. However may move to VPS later.

Questions:

1. Can normalized schemas take the heavy updation process without creating a performance issue ?

2. Analysis based on popular algorithms like MACD and CMF has to be done on the EOD data so as to spot a particular trend in the stocks, the data from analysis will again have to be stored for further reference. Analysis data will be calculated once the EOD data is updated for the day. So is going with normalized schemas here is fine, keeping the performance issue in view ? Also I would need to fetch both the EOD and Analysis data quite often !

Elaboration:

1 INSERT statement (to insert EOD data) + 1 INSERT statement (to insert analysis data)

= 2 INSERT statements * 1500 stocks (for startup)

= 3000 INSERT statements done back 2 back !

I am further planning to add more stocks as the project grows, so I am looking here at scalability as well.

Although I don't know about the concept of DW (only heard about it) but, if from performance point of view, it is more viable than OLTP, I am ready to give it a shot.

Paul Turner
  • 38,949
  • 15
  • 102
  • 166
Bhaskar Pramanik
  • 191
  • 2
  • 7
  • MySQL 4.1 is ancient. If you want community support, use a current version. – Jim Garrison Jan 03 '13 at 06:10
  • 1500 rows? this is a non issue. I have MySQL run tables with 500 million rows plus heavy updated and read without performance issues. My advise to you is to create a model then populate it and test. Analyse the performance metrics and then tune. Without a model you are speculating and this is not going to help you. – Namphibian Jan 03 '13 at 07:31
  • Thanks for your input @Namphibian I am in the process of prototyping. Planning to test the system over a trade week with a small number of stocks ~150 – Bhaskar Pramanik Jan 03 '13 at 08:47
  • @splashingbee it is impossible to say this model is going to perform better than that model without testing it. Generally it is better to create the models test analyse and then make informed choices. I hope you find the best one for you. If you have any particular specific problems let us know and we can help. – Namphibian Jan 03 '13 at 09:55
  • @Namphibian >> Even I realized subject is too broad to comment/answer. Let me test and get back on specifics... – Bhaskar Pramanik Jan 03 '13 at 11:49

0 Answers0