On Jan 27, 2010, at 4:07 AM, Noob Centos Admin <centos.admin at 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