On Jan 27, 2010, at 4:07 AM, Noob Centos Admin centos.admin@gmail.com wrote:
Hi,
Split the TEXT/BLOB data out of the primary table into tables of their own indexed to the primary table by it's key column.
This is part of what I was planning to do, there are a lot of stuff I am planning to split out into their own tables with reference key. The problem is I'm unsure whether the added overheads of joins would negate the IO benefits hence trying to figure out more about how Centos/Linux does the caching.
The idea behind it is you don't need to execute a join if you don't need the extra data.
Think about distributing the parts to different boxes as necessary. You can start with the DBMS which is the logical candidate.
Eventually I figured that would probably have to be done but I don't know enough at this point. So I'm taking the approach of optimizing stage by stage starting with things I'm more familiar with and less likely to muck up totally, i.e.from the app/script side first. Then after getting more familiar with the setup, experiment with the hardware based solutions.
Good approach, take a look at the queries and indexes first, you can get a lot of optimization out of tuning queries and/or adding/re- indexing indexes
On the DBMS backend, give it plenty of memory, good storage for the workload and good networking.
Again problem is old server so memory is maxed, drives controller is probably not helping.
On the Apache/PHP side, look for a good DBMS inter-connect and some PHP caching module and of course enough CPU for the PHP code and network for Apache+DBMS inter-connect.
If you wanted to split it up even more you could look into some sort of PHP distributed cache/processing system and have PHP processed behind Apache.
Thanks for the heads up, I didn't realize it was possible to separate the PHP processing from Apache itself. However, for the time being, I'm probably still limited to a single server situation so will keep this in mind for future.
I was actually thinking of distributing the caching of the data rather then the PHP processing, but you can have multiple PHP front-end servers, one or two mid-line caching (and possibly pre-processing) servers and then a couple of backend DB servers (replicas) for reads and a master for writes.
You could even have all this within a single piece of highly redundant hardware running ESXi or even Xen with PV domains.
If you get a second piece of highly redundant hardware you can then look at vmotion or live migration to distribute the load between boxes and setup fail-over, etc.
-Ross