maybe this is offtopic
can you show some benchmarks?
even though 4,4.1,5 added performance enhancements they also added features. i haven’t looked for benchmarks or done any myself but the upgrade reasoning is don’t upgrade unless you need a feature.
All,
I appreciate everyones advice and suggestions, they have helped me substantially to determine what the probem was.
Here is what I found:
During the times my website went into a vegitative state, it was due to a whole mix and combination of reasons
a) Not enough apache child threads available to handle the load of all the requests. I upped this to about 200, so now during peak times there are about 170 requests being processed while 30 are sitting idle
b) The threads cached variable in MySQL not being set high enough.
c) An SQL query, that would pretty much bring down my whole site when it was executed on a member who had a huge number of statistics. I have since removed this query, and my site is still functional even during the high load times.
My next step would be is to set a max on the amount of usage statistics any member can have. It’s rather scary how large some of the usage statistics can get for some users.
My site still experiences a slow down during the peak times in the morning, but at least this time around, it’s not in a vegitative state, where my server needs to be rebooted to serve pages.
I’m in fact still experiencing a HIGH number of HTTPD proceses though.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2193 mysql 15 0 1784m 565m 3020 S 4.0 28.0 11:49.59 mysqld
3363 apache 25 0 27540 11m 7916 R 3.6 0.6 0:39.11 httpd
3453 apache 25 0 27584 12m 7912 R 3.6 0.6 0:38.04 httpd
2429 apache 25 0 27564 12m 7932 R 3.3 0.6 1:32.07 httpd
2482 apache 25 0 27476 11m 7888 R 3.3 0.6 3:32.26 httpd
2484 apache 25 0 27560 12m 7924 R 3.3 0.6 0:40.43 httpd
2523 apache 25 0 27568 12m 7920 R 3.3 0.6 2:44.12 httpd
3092 apache 25 0 27576 12m 7924 R 3.3 0.6 0:46.78 httpd
3103 apache 25 0 27556 11m 7924 R 3.3 0.6 1:02.83 httpd
3127 apache 25 0 27560 11m 7920 R 3.3 0.6 1:04.49 httpd
3128 apache 25 0 27560 11m 7904 R 3.3 0.6 1:33.78 httpd
3146 apache 25 0 27560 11m 7920 R 3.3 0.6 1:10.73 httpd
3166 apache 25 0 27560 11m 7924 R 3.3 0.6 0:43.91 httpd
3168 apache 25 0 27496 11m 7900 R 3.3 0.6 1:26.28 httpd
3171 apache 25 0 27572 12m 7924 R 3.3 0.6 0:43.28 httpd
3189 apache 25 0 27552 11m 7924 R 3.3 0.6 0:46.59 httpd
3212 apache 25 0 27560 11m 7928 R 3.3 0.6 0:42.93 httpd
3213 apache 25 0 27540 11m 7928 R 3.3 0.6 0:50.89 httpd
3214 apache 25 0 27560 12m 7924 R 3.3 0.6 0:38.09 httpd
3230 apache 25 0 27560 12m 7928 R 3.3 0.6 0:47.28 httpd
3242 apache 25 0 27560 11m 7920 R 3.3 0.6 0:40.90 httpd
3255 apache 25 0 27576 12m 7928 R 3.3 0.6 0:43.08 httpd
3263 apache 25 0 27560 12m 7928 R 3.3 0.6 0:42.00 httpd
3264 apache 25 0 27548 11m 7924 R 3.3 0.6 0:43.70 httpd
3282 apache 25 0 27560 11m 7924 R 3.3 0.6 0:53.71 httpd
3300 apache 25 0 27516 11m 7916 R 3.3 0.6 0:43.50 httpd
3329 apache 25 0 27560 11m 7916 R 3.3 0.6 0:43.40 httpd
3344 apache 25 0 27532 11m 7920 R 3.3 0.6 0:41.48 httpd
3349 apache 25 0 27572 11m 7912 R 3.3 0.6 0:50.23 httpd
3354 apache 25 0 27540 11m 7916 R 3.3 0.6 0:43.20 httpd
3360 apache 25 0 27588 12m 7916 R 3.3 0.6 0:41.73 httpd
3373 apache 25 0 27560 12m 7928 R 3.3 0.6 0:43.21 httpd
3378 apache 25 0 27568 11m 7916 R 3.3 0.6 0:42.70 httpd
3387 apache 25 0 27540 11m 7920 R 3.3 0.6 0:45.69 httpd
3390 apache 25 0 27560 11m 7920 R 3.3 0.6 0:46.11 httpd
Exactly as suggested, and this is probably the most significant factor. When you started out, you had little data, everything was rosy and you had power to spare. Good application design and performance or poor design, it made no difference then. As time went on, you accumulated more data, and the behaviour of your system changed. Gradually the system design and other factors became more important, and eventually you reached a critical point where performance was causing increased load, that slowed performance, causing increased load, and you had a meltdown.
It may be possible to increase the performance of your slow query by perhaps an order of magnitude.
If you haven’t yet, check the index usage with explain as suggested before, and reach an understanding as to why the query is slow. If it’s returning many rows for processing in PHP, and where where the final result is a few figures based on the analysis of those rows, look to moving the analysis into the database. If you’re not sure, post a question saying what type of data you’re retrieving, the database schema, your current query etc. MySQL has some powerful functions, and you can add more with UDF’s if necessary. Processing that through lack of understanding people often push into PHP can often be handled by the database with features such as conditional functions, left joins and groups, and selects from the same table multiple times in the same query. This can yield a massive improvement in performance over retrieving many rows and processing in PHP. If the query is returning many rows and needs to for display purposes, consider a redesign of the interface and use of limit to restrict the number of results, or restrict based on time ranges. If you do need to do much processing in PHP, it may be possible to improve the performance there with some rewriting, and it’s also always possible to construct a custom C component to replace a chunk of PHP code where you need a massive boost.
Other things to consider would be the query caching of newer MySQL versions. This may or may not make a difference, but it’s something to consider.
Another consideration is that if it’s the case that when run today, your slow query is largely sifting through and returning the same data as yesterday but with a relatively small amount of extra data, this could be wasted effort, and you could consider whether a design allowing for incremental analysis would be feasible. This would mean that you would perform a query to retrieve data from that last time that the query was run until the reporting period end, and you would use this in conjunction with the previously computed analysis or intermediate result to arrive at the final result. You would then update the result or intermediate analysis for the period just computed, ready for the next incremental analysis. This could give several orders of magnitude performance increase.
In essence, think around the problem. You may need to come up with some innovative solutions to cut down the processing requirements of your solution, or it may be that a simple fix to a design flaw is enough. Hopefully you’ll find the challenge interesting. Good luck, it’s all fun stuff 
I have no number handy but you can take a look at these articles:
The new features also allow me to do certain things with just one query directly to MySQL, instead of having to write blocks of PHP code and separated queries. In those situations, it does speed things up a fair bit. Again, I have no number here (will need to reinstall 3.23 to do tests which is too much :p) but back then when I was doing the tests, the newer versions were significantly faster.
The mysqli extension is also vastly better than the older mysql (for PHP). From Zend:
Greater speed. Enhancements in both the extension and in MySQL have made most operations faster, with certain operations becoming up to 40 times faster as compared to ext/mysql.
Improvements in replication, cluster, and partitioning (the 5.1 version) are also worth considering, but might not apply in the OP’s setup.
You can use www.touchsupport.com They are very good. You pay by the month but they are well worth it. Not very expensive either.