MYSQL Social Networking website - Database Design, Data Growth and Hardware

Question

Introduction

I have a system around one million users (community based). It has around 6000 groups (fixed , no change). But there is no discussion board for these groups yet.

Building discussion forum

We are building a discussion forum for the above groups. Each group has 12 categories (fixed, never change). The discussion forum is like Facebook wall. We will have 12 Facebook walls for each group (one wall for one category).

Database Design

For the above mentioned features I have 12 main tables (for each category) and 12 comments tables (for each category). Total 24 tables.

All the tables have similar structure (main and comments table).

CREATE TABLE `tdb_forum` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
   group_id mediumint unsigned NOT NULL,  
 ..... Other columns .....
 ) 
 CREATE TABLE `tdb_forum_comment` (
   `id` int unsigned NOT NULL AUTO_INCREMENT,
    forum_id int unsigned NOT NULL ,  
  ..... Other columns ..... )

I am going to use MySQL 5.5 and InnoDB

Data growth expectations

Since one million users are ready to use it immediately after launching, average 1 entry for person in main table per day and average 4 entries for person in comments table. So per month comments table reach 60 million records in month.

Hardware I am planning

Base Hardware

Intel Xeon E3-1230 (3.20GHZ 8MB Cache)
16GB DDR3 ECC
Primary Disk: Hardware Raid-1
2x2000GB 7.2k RPM SATAII RE4 64MB Cache

Additional Options

  Hardware RAID Controller : Adaptec 2405 - 128MB Cache - 4 Ports
    Chassis : Standard SuperMicro Rackmount
    Public Port Speed : 100Mbs Public
    Public Bandwidth : 10TB Premium Transfer
    IP Addresses : 8 IP Addresses
    Control Panel : cPanel / WHM

Questions I have

1. Since the data growth is 60 Million per table (per month), should I create a separate database for each group (6000 databases) having all the 12 tables, so in future I don't have to go for any partitioning or sharding? It will be straight forward in reading and writing if my code handles all the database connections well. Even for future growth (max 15 million users), I will not have any problems. But having 6000 databases, do you think any issues like for back up and for replication?

2. For the main tables and comments table, is it OK to have int as an ID or can I change it to BigInt or any other suggestions?

3. Do you think my hardware is good for initial one million users for an average use? Or am I renting too much? If so, what is the best hardware for my case, at least for first year?

Answer 1

You need to increase the amount of RAM, by a LOT. Based on the load you are expecting I'd want at least 64 Gigs in there, if not 128 or 256. Given that most of the database will become pretty stale pretty quick I'd say that one database should be just fine. I've got tables with billions of rows which are OLTP. Granted they are MSSQL, not MySQL but the ideas are the same.