We were doing OG2List performance tuning today. We cam up with a series of content, group, and user generating scripts:
http://cvs.drupal.org/viewcvs/drupal/contributions/modules/devel/generate
We starting monitoring what processes were CPU bound. We discovered MySQL was bound, which was great news! First we changed the my.conf to my-large.cnf to have a better performance. Then we ran the mysql process viewer so we could see the running mysql queries and their effect on MySQL. We identified the most resource intensive queries and then used MySQL explain.
We found that select distinct was causing a hidden Group By in addition to a Where. The net result was that we were creating temp table which is almost the worst case, excluding file creation in MySQL. We looked at the query and quickly added three indicies using alter table add index(column name). Presto! MySQL tuning meant it was no longer the bottle neck.
Anyone else with tuning experiences they want to share? What's next PHP Accelerators, Apache Tuning, Kernel tuning?
Cheers,
Kieran