Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

This will be the first project of this scale that I have attempted, and the first time I have run a website at all (much less dozens) using an off-site database. In particular, I'd like to know:

  1. what sort of optimizations I should read up on to make this run as smoothly as possible?

  2. any pitfalls/gotchas wiser, more experienced folk are aware of I should be on the lookout for, and

  3. what damage-control and preventative measures I should take against the nightmare scenario of the main server (hosting the database) having an outage, grinding over 100 websites to a halt (because they have no access to the product data).

share|improve this question

closed as too broad by MichaelT, GlenH7, gnat, Bart van Ingen Schenau, Kilian Foth Sep 2 at 11:23

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

    
PS - I realize this is in the same vein as what I was trying to ask in my previous question, but I've got more thoughts together now and I've tried to put together a far more specific and coherent question this time in order to respectfully ask the community to reconsider. No intention of being nagging or insistent so please do not take it as such. –  user56874 Jun 17 '12 at 1:11
    
Is there any duplication? By which I mean either duplicate keys that aren't duplicate products OR duplicate products that need to be mapped. If not then you're going to find it reasonably easy, I would imagine. Also, is there a real need to be particularly optimised, or can the data merge take as long as it takes? –  pdr Jun 17 '12 at 1:24
    
The database is being built expressly for this purpose so there's no data merge to worry about at all. –  user56874 Jun 17 '12 at 16:50

1 Answer 1

up vote 0 down vote accepted
  1. You want the DB schema to be mostly normalized and avoid pessimizations like the n+1 query problem or multiple redundant queries during a single operation. And that will be hard enough to do.
  2. It would probably be better to have an app server layer with a well-defined API on top of the DB rather than having every site access the DB directly. And you definitely need automated tests both for performance and functionality right from the beginning.
  3. Redundancy, of course. If both the app server layer and the DB are clustered, single-server outages won't be a problem and it will help performance as well. Clustered DBs tend to be pretty hard to do and expensive, but this doesn't sound like a project with a tight budget. Make sure to host in a facility that has redundant power supplies and network connections. If outages must be prevented at any cost, consider redundant locations.
share|improve this answer
    
3 is what scares me, because that's not something we can afford to do at all. We're talking a small company upgrading to its first VPS account. How could 3 be reworked on THAT budget, or is that gonna be a dealbreaker? –  user56874 Jun 17 '12 at 16:53
    
@qpalintropos: How does a small company have dozens of retail sites and/or over 100 websites? Or are they providing the IT infrastructure to the actual retail companies? If the overall number of transactions isn't that high after all, you wouldn't need redundancy for performance, and two app servers and DB servers each, perhaps with the second DB as a replica (cheaper than real clustering) that is only activated in case the main one goes down should protect you from most outages. –  Michael Borgwardt Jun 18 '12 at 8:00
1  
@palintropos: It's really a business question: how much would a complete outage really cost? Can you get an SLA from your hosting facility that covers those costs at least partially? Maybe you can even get insurance to cover it. Lots of buisnesses think that they absolutely need 100% availability until they see how much even 99.9% costs and decide that one cumulative day of outage per year isn't all that bad after all. –  Michael Borgwardt Jun 18 '12 at 8:05
    
First off -- thank you. You totally deserve an upvote and accepted answer. We have 95-100 t-shirt websites that do about the business of one gigantic site with multiple categories. It's not really a whole lot of load at all. What concerns me is that these websites are about 93% content 7% template. During an outage, every website loses all images, product names, and product descriptions. If we got indexed during that time we could take a huge SEO hit which is pretty much the owner's worst nightmare. –  user56874 Jun 19 '12 at 1:37
1  
@palintropos: here's a resource about replication: dev.mysql.com/doc/refman/5.0/en/replication.html - and it sounds like the sites should be able to cache all the inventory content (which is what you really want to avoid being unreachable) either in the web framework or through a caching proxy so that displaying those pages doesn't even require going to the DB, since it's not content that changes minute-to-minute through user actions. –  Michael Borgwardt Jun 19 '12 at 7:29