Can someone provide me tips in terms of apache server and mysql server issues I have to look out for. How many silmultaneous connection mysql can handel and any other tips. Planning to setup one dedicated server running apache and mysql on it.
1. Use a 2nd server You can use a tiny, lightning fast server to handle static documents & images, and pass any more complicated requests on to Apache on the same machine. This way Apache won't tie up its multi-megabyte processes serving simple streams of bytes. You can have Apache only get used, for example, when a php script needs to be executed. Good options for this are: TUX / "Red Hat Content Accelerator" - http://www.redhat.com/docs/manuals/tux/ kHTTPd - http://www.fenrus.demon.nl/ thttpd - http://www.acme.com/software/thttpd/ 2. Use a proxy cache A proxy cache can keep a duplicate copy of everything it gets from Apache, and serve the copy instead of bothering Apache with it. This has the benefit of also being able to cache dynamically generated pages, but it does add a bit of bloat. 3. Use hdparm to tune your hard disk. With this utility, you can control the input/output controls for the standard IDE driver included in most stock Linux distributions. Most of these options work with the regular IDE driver, but some require the more modern EIDE drivers that comes in kernels later than version 2.0.10. If you are using a default Linux install, you could speed up Linux's access to your hard disk by 200%. hdparm is mostly useful for IDE hard disks, but some hdparm settings work with SCSI also. 4. MaxRequestsPerChild setting determines how many requests a single child can handle before Apache spawns a new child. The default value is 30, but you should consider raising it: Apache is robust enough to handle a good load. If you use mod_perl, reconsider modifying this value from its default, since that module causes the child processes to have bloated memory pages. 5. Serve graphics from a seperate machine, a shared hosting account or even a VPS if needed, a second dedicated server if you have the budget.
* MySQL is interpreted from right to left so you should put the most significant limiters as far to the right as possible. * Only select fields you need, instead of selecting * (everything). * Don't put things that changes very rarely in the database, instead put it in a global array in some include file. * Use indexes on the columns in the WHERE clause and on the columns you want to ORDER BY. * Indexes are great if you search the table alot, but it slows down insertion. * Use the EXPLAIN command to analyze your indexes. * If you only want one line as a result from the database you should always use LIMIT 1. This way mysql stops searching when it finds the first line instead of continuing through the whole database, only to find that there weren't any more lines that matched the query. * If you use $line = mysql_fetch_array($result) you'll get two ways of accessing the columns, $line[0] and $line['columnname']. If you only use the $line['columnname'] you should use $line = mysql_fetch_assoc($result) instead, then there will not be any $line[int index] array. * Sometimes mysql_free_result() end up wasting more memory than it saves. Check the difference with memory_get_usage(). * Don't ask the database for the same stuff over and over again, save the result. * Use NOT NULL as default value as much as you can, it speeds up execution and saves one bit. * Use datatypes that fits your data, not too large. For example, INT can hold values up to 4294967295 unsigned, which is often unnecessarily big. Use MEDIUMINT or SMALLINT where applicable. * Make use of the default values, only insert values that differs from the default values to speed up the insertion.
The best advice for tuning on the cheap is simply to make the page that's Dugg static. If you *must* have MySQL on the landing page, make sure you've got as few queries as possible. In terms of hardware, if you're doing a lot of disc I/O go for SCSI (I'd never use IDE/SATA for a serious site), but make sure you have as much RAM as possible, so that you're caching as much as you can.
...good advice...even with a dynamic database driven site think about pregenerating landing and high volume pages from the database using cron or similar, you can always leave less visited pages as dynamic.
Don't use Wordpress is my tip. Or if you do at least make sure you are caching the pages. WP uses 17 queries to load a simple blog page. My blog software uses 3.
WOW! Very useful tips in this thread. Added a green to all who suggested useful stuff. I would like to add that never put a query in a loop, especially if the query is based on user input. Hold the result in an array, and get values from there. Bye