Instant Analysis:
Show full percesslist;
Turn on the slow query log and analyze the log records:
Long_query_time = 1
Log-slow-queries =/data/3306/slow.log
Log_queries_not_using_indexes
Split log sent to mailbox
Add explain to see how the statement is executed, and to locate which fields are indexed, to see the unique values of the criteria fields
Select COUNT (Distinct column_name) from Tab_name;
You can also analyze help show profiles;
To monitor statements using daemons:
Mysql-uroot-pxxx-e "show full processlist;" | Grep-evi "Sleep"
To configure the MySQL timeout period:
[Mysqld]
interactive_timeout=120
wait_timeout=120
Java Tuning Connection pool (original configuration too Large)
PHP uses mysql_connect to avoid pconnect and explicitly calls Mysql_close
Mysqlsla software can be used for slow query analysis (not detected)
This article from "Magic Conch" blog, declined reproduced!
MySQL Slow query troubleshooting optimization