How do I change the tables I use when my site has a lot of traffic?

Question

Well, I have built a set of tables that run are meant to run with fewer joins, but I want to keep my normalized database around. Is there a way to switch between them when my site has a lot of traffic?

Answer 1

If it is in order to simplify your queries, you might want to create a view which in turn can bundle some joins.

Answer 2

You can do this "in production", but keep in mind that this can become critical very fast!

• Alter your tables by adding the additional columns you need.
• Populate the new columns (with a script, so that you can control how many rows are touched per time to make sure you do not generate to much load)
• Change your (website)-scripts using the new structure. Best way would be to update all scripts at the same time.
• Re-run your "populate-script" to ensure all rows are really in the new format
• Remove unneccessary columns/tables

Answer 3

Q: Is there any way to switch between them? A: Sure - just redirect to a different PHP page which does a different query of a different set of tables.

... however ...

Q: How are you going to keep the tables in synch?

Q: What mechanism is available to alert you that "your site has a lot of traffic"?

Answer 4

Create a global that is incremented each time a page is accessed and resets to 0 de-incremented by 1 per minute per incrementation called 'pages_accessed_per_minute'.

Create a function that takes two arguments, one the global mentioned above, and the other the limit for your throttle: throttleOn('pages_accessed_per_minute', '1000').

If the number of pages being accessed per minute increases beyond a certain point (1000 in this case), swap in the alternate php scripts which use the other databases.

if throttleOn('pages_accessed_per_minute', '1000') == true){
  include_once('dbAccess1.php');
}
else{
  include_once('dbAccess2.php');
}

I'm not 100% sure that I'm using include_once correctly, though.

I also discovered that most servers have a log symlinked to their users' home directories on shared hosts. These logs can be monitored live with the $tail -f (command line). It's better than polling because the server may become busy, and it's better to rely on hooks when that happens.