2

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 ?

EDIT

I am summarizing my requirements,I need to have a database of a million user's whose points are to be updated every day as separate entries. This will be done regularly such that there should a field per day for each using showing daily points,which could be aggregated in weekly/monthly/yearly basis. I am confused on the database design as well issue which could happen ever after deployment. A million or more DB operations are done every day.How the server and other things are to be considered in this case.

Any help will be highly appreciated.Thanks in advance.

  • 6
    1 million inserts in a day? That's only about 11.5 inserts per second. Many DBMSes can do this easily. – Colin 't Hart Jul 2 '14 at 13:13
  • @Colin'tHart Thanks for this.What about MySQL in this case? Also will there be any difficulties while summing up the results? – Surabhil Sergy Jul 2 '14 at 13:26
  • MySQL has a good reputation and is running many large web sites. Summing the results quickly (Is that what you meant?) should not be a problem if there are useful indexes and good code design. – RLF Jul 2 '14 at 17:11
  • @RLF My application is not a simple website by means.Its a data warehouse. – Surabhil Sergy Jul 4 '14 at 6:49
  • You will not like MySQL's clustered indexes. – user32234 Jul 15 '14 at 16:02
8
+25

Let's break this question up into a few parts.

Q: I need to insert 1mm rows a day. Is that a lot?

Not really. 1mm divided by 24 hours divided by 60 minutes divided by 60 seconds gives you about 12 inserts per second. For a rough frame of perspective, it's not unusual to see 1,000 inserts per second in typical commodity servers with no tuning.

Granted, your load won't be perfectly averaged out like that - you'll have bursts of load - but I wouldn't make database platform decisions based on less than 10k-20k inserts per second. Any platform out there will work fairly well.

Q: How should I structure the data?

Zoom out - don't think table, think databases. If you're going to be keeping this data permanently, and it's truly insert-only with no updates, then you probably want to start a new database for time lengths. Your inserts may only go into one table in one database, but every year, create a new database (MyApp_2015) and seal the old 2014 data as read-only. You can stop backing it up (as long as you've still got a good backup once), stop doing index maintenance, statistics updates, etc.

The PHP will only ever have to know about the current database for inserts, making your design a lot easier. The archival process becomes a DBA task much later down the road as long as you go in knowing that there will be more than one database involved.

If you were doing more than 1,000 inserts per second sustained, and you wanted easier performance management, then I'd also suggest building sharding into the initial design regardless of the database platform. Don't get me wrong, any modern database can handle over 1,000 inserts per second, but designing sharding in now just gives you more flexibility later on. At 12 inserts per second, it's just not worth the design/testing hassle.

Q: How should I do reporting?

In an ideal world, reports would not be done against the live server. Run the reports against a restored or replicated copy of the database. This does two things: it reduces load on the live server, and it validates your backups, guaranteeing that you've got your valuable data elsewhere.

  • Thanks for this.This helped me to some extent.Well organized reply for somewhat messy question :) So as suggested my preferred technologies PHP and MYSQL seems to be fine enough in this case. In case of MySQL, I am confused on choosing the storage engine. And on the DB design part.I understood your concept of arranging databases by year. And what would be your suggestion in case of daily entries? I envision making a monthly tables with days as fields. Does this affect speed of insertion in some ways? – Surabhil Sergy Jul 16 '14 at 7:07
  • I don't work with MySQL, so I wouldn't be equipped to answer that one. You're best off clearly defining the table and the access styles, and then asking that as a separate question. That way you can tag the question with just the tags relevant to the question, and get better attention. DO NOT MAKE MONTHLY TABLES WITH DAYS AS FIELDS. Bad bad bad idea. Grab a book on database normalization quick. In the SQL Server world, I like Louis Davidson's Pro SQL Server 2012 Relational Database Design and Implementation. – Brent Ozar Jul 16 '14 at 7:10
  • Ok,but still I had read certain articles suggesting- limiting the use of database normalization's in case of these kind of data ware housing applications.May be I am wrong. – Surabhil Sergy Jul 16 '14 at 7:20
  • Data warehousing, yes - but that's the reporting side of it, not the data-insert side. Reading usually calls for different data structures than writing. Your question focused on the inserts. Put the reporting summary tables in a separate database (data warehouses are usually different from the heavily-inserted source data). Those are updated only once per day (or hourly, whatever) with batch jobs. – Brent Ozar Jul 16 '14 at 7:34
  • //Q: How should I do reporting? In an ideal world, reports would not be done against the live server. Run the reports against a restored or replicated copy of the database.// Here are you suggesting to export the full database of data stored into another server for report generation? – Surabhil Sergy Jul 16 '14 at 10:51
4

If it's a data warehouse system you're looking at, you may wish to consider InfiniDB. It's an Open Source columnar storage engine designed for DW type loads - it falls into the NewSQL paradigm as defined by Michael Stonebraker. There's also InfoBright, which is a similar offering. Note about InfiniDB - it's now under the aegis of MariaDB.org/com - not sure where this project will go in the future.

Of course, you may not require any special software at all - vanilla MySQL on a good server may be right up your alley. As has been pointed out, 11 operations/second is not a great deal on modern (even relatively modest) servers.

Obviously, this depends on your particular queries/application, load &c. and of course, budget :-). I would urge you to evaluate solutions and test before committing to any given approach.

[EDIT]

I was thinking that I only considered MySQL related solutions. Take a look here (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many (and especially me, a more capable DB server than MySQL.

[EDIT - in response to comments]

There is not so much of a learning curve here - one of the key ideas behind NewSQL is that it explicitly retains much of OldSQL such as the SQL language and ACID transactions. The fundamental point is that NewSQL splits data processing into two types of task and takes a different approach for each.

OLTP (Online Transaction Processing - banking, shopping &c.) is done on a shared nothing sharded in-memory architecture and OLAP (Online Analytical Processing - your issue - data warehousing/DW - i.e. reporting/aggregating over long periods) is done with compressed columnar stores of data.

As regards the speed issue - MySQL may be faster (and not by a whole lot) for simple read-heavy applications with little data-processing, but once you start writing complex DW type queries, PostgreSQL begins to shine!

Take a look here and here. If you are looking at comparisons, pay no attention to any sites that mention MyISAM table types - the default for MySQL now is InnoDB which does enforce ACID transactions and referential integrity.

[EDIT - in response to OP's further comment about having no knowledge of PostgreSQL]

The fundamentals of SQL are essentially the same for MySQL and PostgreSQL. I had never worked with PostgreSQL before last year (I'm a mature student) and had to install a PostgreSQL instance on College machines. It worked a treat - straight out of the box - compiled from source. At heart, the fundamentals of database servers are reasonably similiar (without being identical!).

However, it is worth noting that PostgreSQL is a) much more standards compliant than MySQL and b) it's SQL language is significantly richer. Check constraints work in PostgreSQL -they don't in MySQL (I still find this a shocking lack!). What's even worse is that if you include them in your CREATE TABLE statements, the server will accept them without throwing an error!

PostgreSQL has windowing functions and common table expressions (CTEs) - MySQL doesn't (at least not mainstream - yet - InfiniDB does). If you want a fast, read-heavy OLTP database, I'd go with MySQL. If you want a serious DW database and you have a choice from the get-go, I'd go with PostgreSQL.

IMHO, you'll save yourself a lot of work in the long run by not having to implement CTEs and windowing functions in your own code. Take a look at what Baron Schwartz wrote about this here and here. Schwawtz wrote the book on MySQL high performance.

  • Thanks for this.I am excited on hearing about NewSQL.I had experience only with MySQL and I have heard of NoSQL. I am worried on the learning curve involved. – Surabhil Sergy Jul 14 '14 at 12:20
  • I could see some articles complaining PostgreSQL speed.I need high speed insertion as well as fast select queries to be made. – Surabhil Sergy Jul 14 '14 at 12:28
  • Data ware housing queries with PostgreSQL? Seems interesting ,but I dn't have any knowledge on PostgreSQL. :( – Surabhil Sergy Jul 16 '14 at 7:26
0

My read is that your main challenge will not be storing and updating the data - as others have suggested, I would expect most DBMS can handle that on commodity hardware.

I expect your main challenge will be aggregations at scale. For that I would look into cube/OLAP technology e.g. SQL Server Analysis Services.

  • I am currently following MySQL.On hearing these comments on dataware house approach,Im really interested to do adopt that.But I doubt where and how to start from.Starting from db design,type of storage engines to be used,etc; – Surabhil Sergy Jul 15 '14 at 5:50
  • The best resources (books, articles, training) on Data Warehousing IMO are from the Kimball Group. – Mike Honey Jul 15 '14 at 7:43
0

MySQL will fits your scenario and InnoDB engines will suits. Also pl have a look on the below link of storage engines.

http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

  • What kind of InnoDB table design are you suggesting me? – Surabhil Sergy Jul 15 '14 at 11:36
  • Archive Storage Engine will do since it can able to store large amount of data with out indexes – Karthick Jul 15 '14 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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