CODEWITHSUNDEEP
phpmysqlperformancehostingmybb
hugelgupf
hugelgupf

Reputation: 387

Storing a lot of tables on a DB server

MySQL/PHP: I have the following situation: I will have one software with approximately 70 tables installed many times on one server (to be clear: it's a MyBB forum). Say, I will be installing this 500 times (500 different forums for, let's say, 10 different themes and 5 different languages, just as an example).

I'm looking at the following options:

  1. Store all forums in one schema and alter their prefixes per forum.
  2. Store every forum in a different schema, same prefixes.
  3. Create only one set of 70 tables, and append a "forumID" to every table for selection.
  4. Create many sets of 70 tables, append a forumID to every table, and use one set of tables for ~10-100 forums.

For 1 & 2, I wouldn't have to alter much of the MyBB code (easy maintenance), for 3 & 4 I'd have to write some sort of search pattern to be used on every query to detect all the tables selected and append the WHERE clause (or create it if not exists) to also consider the forumID (difficult maintenance).

If I were to judge this by MyBB maintenance, it'd be one of the first two options. But I should also consider: What's more performant and faster for the MySQL server?

Thanks, Chris

/edit: All forums will be using the same code. I don't want to have to maintain 500 different installations of this thing...

Upvotes: 1

Views: 175

Answers (3)

RandomSeed
RandomSeed

Reputation: 29759

I think you should forget about options 3 and 4 because:

  • Performances

You tables would get larger, and their indexes too. Larger indexes means slower inserts. Selects may not be impacted too much.

As far as I know, all buffers and caches are instance-wide, so in this regard, all options are equivalent.

  • Administration

The hassle of refactoring the code, plus the need to redo the work after each upgrade would be enough (for me) to drop the idea. Besides, even the upgrade procedures would probably need to be tweaked to account for your custom layout.

You will probably want to backup and restore your forums separately. Then you need to write the backup script yourself, mysqldump can't help in this case. And update the backup script every time a new table is added (may happen during upgrade).

During upgrade, database structure may change. Changing the whole structure in one go may sound appalling at the first look. But you will then need to bring down all your 500 forums at the same time, and make a very long structure change, instead of 500 smaller, quicker changes.

  • Scalability

(applies mostly to option 4) It seems more difficult to move one individual forum database to another server, in case of need.

  • Security

If an intruder compromises your application, I believe it would be easier for her to gain access to all forums, but this is just a wild guess.

Options 1 and 2 will perform equally well, whether the tables are on the same database or not is just a matter of syntax.

I have a preference for Option 2, because it is easier to grant access rights on a per-database basis.

Upvotes: 1

Kerbocat
Kerbocat

Reputation: 388

I would just duplicate the schema pr. forum.

Arguments for:

  1. This is an easily automated proces, and automating it will likely be quicker than rewritting all your SQL statements to use "forumid" columns.
  2. This solution will allow you to easily scale to new hardware. Scaling a database is always complex and costly (compared to scaling for instance webservers).
  3. Making each forum seperate also enables you to move, say, the top 5 forums to a seperate machine. If this is something you make money from, you can have "premium forums" (10 per server, x$/month) and "Freemium forums" (Free, but they are all on one server, no performance guarantee)
  4. If you have 500 different forums, there is going to be A LOT of difference in the utilization of each of them. Any solution that allows you to use different tables for each forum will lead to a higher cache-hit-rate on the database-server, due to it being able to cache much more effeciently (And cache-hit-rate is the KEY to high performance on a database)

Upvotes: 1

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47038

I would go for alternative 3 (Create a Forum table and a forumID column on the other tables).

This way you can creating new forums from your application without having to manage different schemas or tables.

In my opinion, making schema changes is an order of magnitude worse than creating a new row in a forum table.

Upvotes: 0

Related Questions