MySQL & PHP Performance Optimization Tips
In high performance web applications you will always have bottlenecks within your application. Identifying these bottlenecks and optimizing is a tedious task and typically show themselves underload. A single bad/unindexed query can bring a server to its knees. A large number of rows will also help to highlight any poor queries, and on very large datasets you may come to the point where you may have to make decisions whether to denormilize database schema.
Explain each page
Whilst I develope sites, I typically print out all queries, EXPLAIN each select statement at the bottom of each page, and highlight it red if its doing a full table scan, temp tables or a filesort. As well as displaying SHOWS INDEXES FROM TABLE…
Not only will it help you to optimize sites, you can also see bad logic and areas to optimize such as a query for each loop when looking through a users table for example.
How do you identify where bottlenecks occur?
# Appends file with processlist watch -n1 “mysqladmin -uroot processlist >>watch.processlist.txt” # Count the number of locked processes watch -n1 “mysqladmin -uroot processlist | grep -i ‘lock’ | wc -l “; # Count the number of processes sleep watch -n1 “mysqladmin -uroot processlist | grep -i ’sleep’ | wc -l “; # Run a specific query every second watch -n1 “mysql -uadmin -p`cat /etc/psa/.psa.shadow` trade_engine –execute “SELECT NOW(),date_quote FROM sampleData WHERE 1=1 AND permission = ‘755′ AND symbol=’IBZL’ GROUP BY date_quote;” ” # Emails mysqlreport every 60 seconds watch -n60 mysqlreport –all –email email@example.com # Displays process list as well as appending the contents to a file watch -n1 “mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` processlist | tee -a process.list.txt”
Watching the processlist is very handy in identifying locked, sleeping or sorting process states. If you have a large number of locked processes you typically should change the table type to INNODB, which supports row level locking. if you have a large number of sleeping connections, and you have persistent connections enabled, most likely indicates that connections are not being reused.
Running a specific query every second is exceptionally handy, the example I gave indicates whether one of our crons is correctly functioning and as each row is inserted you can watch something being either inserted or updated. mysqlreport gives numerous peices of information, extremely helpful in identifying issues, you can see more indepth at hackmysql.com/mysqlreportguide.
Look at the mysql slow query log and optimize each query starting with the most common, think whether you have to execute that query at all and use a cache such as memcached.
I also typically tend to look at the following:
- vmstat -S M
- ps axl | grep -i ‘mysql’
- pstree –G
- free –m