I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities associated with them.

For example, let there be a set of 100 users say U1,U2,...,U100

I need to insert their daily scores into my database.

Consider the total score obtained for a user U1 for period June 30 - July 6, is as follows

June 30 - 99
July 1 - 100
July 2 - 102
July 3 - 102
July 4 - 105
July 5 - 105
July 6 - 107

The database should keep daily scores of each users ,like

For user U1,

July 1- 1pt (100-99)
July 2- 2pt (102-100) 
July 3- 0pt (102-102) 
July 4- 3pt (105-102) 
July 5- 0pt (105-105) 
July 6- 2pt (107-105) 

Similarly the database should hold daily details of the full set of user's.

And on a later phase , I envision to take aggregate reports out of these data like total points scored on each day,week,month,etc; and to compare it with older data.

I need to start things from the scratch.I am experienced with PHP as a server side script and MYSQL. I am confused on the database side ? Since I need to process about a million insertion daily,what all things should be taken care of ?

Does MySQL fits my requirement,If so what storage engine should be used ? Initially I envision to create a user table with a foreign key user id and monthly score tables with dates as fields.And later on I got suggestion to write things into a csv/excel first and then to load them to table after a particular period.

Does file insertion makes things more favorable in this regard.

Or should I try for some other databases,NoSQL methods ?

I need to maintain this data warehouse and aggregate these info to weekly and monthly reports.In some earlier case I found the MySQL queries are taking much time in execution.

Any help will be highly appreciated. Thanks in advance.

  • I think you would get a better answer at DBA.SE. Here this is more about how rather than what (yes it does have a what component but it is limited and very specific to databases so DBA would have better degree of expertise available. I believe that it would be on topic there as well though I haven't looked at their on-topic list lately. – Nick Dickinson-Wilde Aug 18 '14 at 17:23
  • This looks more like a logging\log visualization job than database job (many inserts and no selects, weekly reporting). – Lucas Soares Feb 11 '15 at 12:45

To address your first issue:

I am experienced with PHP as a server side script and MYSQL. I am confused on the database side ?

When you were developing in PHP, MySQL was the tool you used to manage your data. Your app used it for creating, reading, updating or deleting (CRUD) records/entities/rows of information. If you were given an instance/virtual machine(vm)/compute engine from your ISP that your PHP app used then that tool is often called a database engine. So if I'm correctly understanding your statement, the database you used was MySQL.

Regarding your second issue:

Does MySQL fits my requirement,If so what storage engine should be used ?

As to which database, you need transaction-safe functionality, high capacity and (given your daily records inserted) high availability. MySQL, Oracle and Microsoft SQL are three widely used databases. They are available as both on-premise and cloud implementations. MySQL does have storage engine flavors suited to this, like their clustered database engine. At 500k users inserting 1 record daily, you're looking at 20k inserts/hour average. While this is not an uncommonly high insert rate, I'm assuming that your peak insertion rate will be significantly higher. MS Sql and Oracle are well suited to this environment, but generally require database admin-level knowledge of their functionality in order to leverage them effectively. Having said that, I've always been impressed by the computational capabilities of both Oracle and MS Sql, though you don't really need much of that, just a database that can sum columns really really fast.

Note that this requirement might exclude certain cloud data store solutions that have set insert maximums. (These can be overcome but you'll need to have decent knowledge of the storage architecture and customization features.)

I have minimal knowledge of nosql environments so I can't give you any thoughts.

As a recommendation, since you have some knowledge of MySql I would suggest an in-depth exploration of the performance issues. It could be that you're using the wrong storage engine for your requirements. Also, your testing might have been conducted on a shared instance, meaning that you could have been hitting the server in conjunction with a lot of operations from other users. And if you're using a bottom tier offering of your ISP's MySql versions, you could be experiencing other service-governing issues. You're already down the learning curve with MySql so you might find and resolve any issues quickly, rather than learning a new database operating environment.

| improve this answer | |
  • Do you suggest me InnoDB? considering the transaction safe approach. – Surabhil Sergy Jul 14 '14 at 13:02
  • Given your requirements, yes. You'll need to read up on transaction processing to leverage the ACIDity InnoDB offers. – zba Jul 17 '14 at 11:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.