Database type and engine for medium scale user content website [closed]

Closed. This question needs to be more focused. It is not currently accepting answers.

---

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 9 years ago.

Improve this question

Using MySQL for basic storage of system/user data. I need to store/search messages (adding webmail functionality), and have analytic (on the fly reports/graphs from current and historical data, so kind of a data wearhouse) + this means i need to store historical data.

So the question is:

1) Do I use MySQL for everything or use separate databases for messages, analytics/historical data/user data? There is also live feeds on the site which I heard MySQL isn't very good with.

2) For tables that use MySQL is InnoDB good enough or do i need more advanced engines like TukoDB?

For messaging a friend mentioned to use a non relational database instead of MySQL. But if i use separate datbase types then that adds int he headache of keeping them all in sync and given this is a 24-7 user content site, I cant have any downtime.

-- Website is a high traffic user content website, lots of reads/writes but no money so I need to depend less on servers/hardware and more on the database/software to keep me afloat, hence open to only free/open source databases a开发者_高级运维nd database engines.

---

Before answering the question directly I should say that your concerns are premature. A lot of assumptions will be wrong before you have a working project. So instead of designing perfect architecture, just make the project (non-perfect) and be ready for the changes.

Now, back to the question.

For messaging a friend mentioned to use a non relational database instead of MySQL. But if i use separate datbase types then that adds int he headache of keeping them all in sync and given this is a 24-7 user content site, I cant have any downtime.

That was a good recommendation. You don't need to keep them in sync because you will persist different data in different databases.

• SQL database - for transactional data.
• Document Database - for historical and non structured.

Website is a high traffic user content website, lots of reads/writes but no money so I need to depend less on servers/hardware and more on the database/software to keep me afloat, hence open to only free/open source databases and database engines.

If we will look at the CAP theorem and your requirements, we can tell that SQL database is not suitable for you because you sacrifice Partition tolerance or Availability for the sake of Consistency (which you don't seem to need).

From what you have described, you'll be better off with the database supporting Eventual Consistency. MongoDB is one of the most popular document databases.

I heard good things about Cassandra. It allows you to tune between Consistency, Availability and Partition Tolerance.

Redis can be used for extremely high loads providing Consistency and Availability.

So I would recommend to:

• Use SQL database (InnoDB is good enough) for the critical data that requires high consistency.
• Use Document Database for higher traffic data (MongoDB, CoachDB).
• Use key/value (hash) Database for extremely hight traffic data (~Redis).
• Use the database you are the most comfortable with for the rest.

---

What we do, in my company is this: we make a backup of our data every day, and compute statistical data from this backup. It's perfect for marketing purpose, for example. And so, we can stick to InnoDB...

For the moment, you don't need to think (too much) about what will happen when you will have 10 million users. Priority for you is to get something up and running! So MySQL with InnoDB will do just fine.

When the first users will arrive, you will see what you need to improve...

---

http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html

if you read the first paragraph you'll see this:

"InnoDB is a high-reliability and high-performance storage engine for MySQL. Starting with MySQL 5.5, it is the default MySQL storage engine. Key advantages of InnoDB include..."

do i need to say more ?