MySQL or XML or HTML?

[Daleeburg]

Simple Question:

 

I am looking at building a website just for kicks, but I think it may see some heavy load.  The front page would deal be dynamic in the fact that it would be updated, but it would only be updated ever 3 or 4 days.  After looking at my options I have come with with 3 possible solutions:

 

1. Use MySQL to pull all the data all the time.  This is probably the simplest and most straight forward way.  Unfortunately on other websites i have made that got busy, it always seems like MySQL was the first thing to die, so I am kind of shying away from this.

 

2. Use XML.  I have been playing around with XML lately and think it might be a good fit for this.  I can keep an XML document with the data that would be on the homepage.  When a visitor comes, the XML is parsed and displayed.  I dont know how well this would work though, so I am looking for anybody with input.

 

3. Use PHP to create a static HTML page.  I have done this before, pretty much I am making a cache of the page.  It is really good at keeping CPU and memory usage at a minimum, but it is a pain to edit anything in the design.

 

Anybody have any other suggestions or comments on how to handle load without installing a PHP caching utility?

[.josh]

Perhaps your mysql was dying because you had a very poor data structure going on?

[corbin]

My theory about caching has always been this:

Content should be cached in it's farthest processed form possible/reasonable since the purpose of caching is to save time and CPU usage (which go hand in hand, usually).

 

So, there are multiple approaches that I take:

 

-The source-check method.

if(filemtime(cache file) > mod time of source) {

    regen

}

show cache

 

Problems:  If the source is a DB, this one can get kind of pointless, depending on how many sources the data has.

 

-The x time method

if(filemtime(cache file) < time() - x) {

    regen

}

show cache

 

 

-Regen the cache when the data changes

show cache

 

But else where, when the data changes, regenerate the cache.

(This one is only good in certain situations, obviously.)

 

 

 

 

 

I'm sure I could think of others too, but those are the 3 I usually use.

[Daleeburg]

Perhaps your mysql was dying because you had a very poor data structure going on?

 

I spend a lot of time going through my data structures to attempt to make them as efficient as possible without repeating any data.  On any given project I normally spend a large chunk of my time charting out my tables and the such to try to accommodate for everything.

 

I am a big believer of 3rd normal form.

 

I think the reason why the MySQL is normally the first thing to go down is because I am using shared hosting.  I know that is not the best, but since I have to work on a tight budget, it normal fits the build the best.  It just means that I have to consider things like caching.

 

I have never though about checking the file time.  It does make a lot of sense though.  Normally i just run a regen of the cache whenever the an edit was done, but this would occasional cause problems.

[.josh]

Ah okay.  Wasn't necessarily trying to take a jab at you.  Just that most people seem to kind of just throw everything into a single table and then select *.

[corbin]

Do any of your queries take a really long time?  If so, you should enable the slow query log, and try to see which ones are hogging things.  Otherwise, it sounds like you said:  MySQL just can't keep up.

 

 

Even on modest hosting though, I would think it would take quite a bit of traffic to bring a site down....  Would depend on the nature of the site though.