CODEWITHSUNDEEP
mysqlserver-load
千里ちゃん
千里ちゃん

Reputation: 530

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

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?

Upvotes: 1

Views: 99

Answers (4)

glglgl
glglgl

Reputation: 91029

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

Upvotes: 1

千里ちゃん
千里ちゃん

Reputation: 530

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.

Upvotes: 1

Erik
Erik

Reputation: 3777

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

Upvotes: 0

paulsm4
paulsm4

Reputation: 121649

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"?

Upvotes: 0

Related Questions