I agree with Matthew. A database is a lot like a car. You can have a well-built, high-powered vehicle that could do 200mph while still carrying 15 people (good hardware + good tuning) but if you drive it off-road, alone, and in second gear (bad table structure, poor index coverage, poor SQL statement optimization, and poor application concurrency) all of that capacity and performance do you no good.
I went over your data. This is what I noticed first: | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 1 | | Select_range_check | 0 | | Select_scan | 301 | Which implies me that when you needed to combine data between two tables (JOINs), you could not use an index 301 of 302 times. I might be reading it wrong so I hope the list will correct me if I am mistaken.. I also that you are at the point you need to read your slow query log and do a lot of EXPLAIN SELECTs to improve your index coverage. long_query_time = 5 | Slow_queries | 626 | | Com_select | 23934 | I read this as: 626 or 23934 queries took more than 5 seconds to complete. At the bare minimum those queries consumed 626x5=3130 query-seconds ( or just over 52 query-minutes) to execute. That may be a big part of your bottleneck. Also, one more thing to worry about in a high-traffic situation. Connection bloat: | Max_used_connections | 78 | Make sure your code opens its connections AS LATE AS POSSIBLE and closes them AS EARLY AS POSSIBLE. Sometimes connections can be pooled, it depends on your library and/or the available developer's skills. Many designers when building a web-based application (PERL, PHP, ASP, etc) will forget to close the connection before the end of the script *on each page*. Sure, the database library may eventually close it for you (as your variables go out of scope and get garbage collected) but it is MUCH more efficient to ALWAYS close it yourself (or manually return it to the pool). In some scripting languages, the garbage collection utilities will go on "indefinite hold" during high-traffic conditions. This is generally not good for database performance as it means you will have orphaned objects (possibly still open database connections and result datasets) hanging around on the heap waiting to be recovered. Implementations vary but you can usually help your garbage collectors by manually de-referencing all object variables before you exit the script. (I know you have probably already read this already but...) Here is the page in the docs describing all of the system variables: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html You may want to re-examine all of your "buffer" settings to make sure you are getting the most out of your available memory. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matthew McNicol <[EMAIL PROTECTED]> wrote on 07/22/2004 03:25:29 AM: > > My 5 cents... tuning the MySQL database config file is half a solution. > In my experience optimizing the application code and table/index > structure delivers performance results. > > > > Michael Sleman wrote: > > > Hello, > > > > We're running 1 web server (apache 2 & php) / 1 dedicated DB server > > (MySQL 4.0.20-standard) and are experiencing serious performance issues > > on the DB during some load testing. > > > > Hardware on both > > Dual Xeon 2.8GHz, 2GB RAM > > > > The database size is a little under 1 GB. > > > > Naturally, we started taking a look at MySQL config and have gotten some > > significant speed improvements but the app is really slow and queries > > take 10secs on average to execute. From my past experiences with MySQL, > > I still think the DB can do much better. > > > > Any recommendations for additional configuration changes? > > > > The stats during a 15 minute load test was up the roof at: > > Load Average: 12 avg, 26 peak, > > MySQL processes: 20 avg, 80 peak > > Memory usage was 280MB used, 1.4GB cached, 122MB buffers, 216MB FREE. > > > > I'm attaching my.cnf, show status, show variables from a 15 minute load > > test. > > > > Thanks, > > -Michael > > ----------8<--- clipped for space ----8<-----------
