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).
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
Intel Xeon E3-1230 (3.20GHZ 8MB Cache) 16GB DDR3 ECC Primary Disk: Hardware Raid-1 2x2000GB 7.2k RPM SATAII RE4 64MB Cache
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
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?
For the main tables and comments table, is it OK to have
intas an ID or can I change it to
BigIntor any other suggestions?
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?