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?
One of my favourite linux commands lately is the watch command. For Mac users you can get this from macports via “sudo port install watch”. Also a few other handy applications are mysqlreport, mytop.
# 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 andrew@email.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
Reference:
http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html
Powered by FireStats
on July 25th, 2007 at 8:26 am
[...] Johnstone has posted a few handy tips to help you find out where in your application a certain kind of issue might lie - MySQL/PHP [...]
on July 28th, 2007 at 7:45 am
Test
on July 28th, 2007 at 7:52 am
Nice post, would be nice if you did some SQL profiling to FireStats and let me know what you find. :).
on July 30th, 2007 at 9:53 am
[...] mysql and php performance and optimization tips [...]
on July 30th, 2007 at 10:16 am
innotop is similar to mytop, but much more capable:
http://innotop.sourceforge.net/
on August 15th, 2007 at 6:59 am
[...] MySQL & PHP Performance Optimization Tips [...]
on October 6th, 2007 at 11:07 am
Another article about optimizing the performance of apache & php for high traffic loads:
http://kevin.vanzonneveld.net/techblog/article/survive_heavy_traffic_with_your_webserver
on June 12th, 2008 at 5:32 pm
[...] MySQL & PHP Performance Optimization Tips [...]
on June 15th, 2008 at 1:42 pm
[...] MySQL & PHP Performance Optimization Tips [...]
on June 16th, 2008 at 3:52 pm
[...] MySQL & PHP Performance Optimization Tips [...]
on June 19th, 2008 at 1:38 pm
[...] MySQL & PHP Performance Optimization Tips [...]
on June 22nd, 2008 at 8:41 am
[...] MySQL & PHP Performance Optimization Tips [...]
on June 25th, 2008 at 5:19 am
[...] MySQL & PHP Performance Optimization Tips [...]
on July 2nd, 2008 at 11:18 pm
[...] MySQL & PHP Performance Optimization Tips [...]
on August 25th, 2008 at 2:01 pm
Amazine site
Thanks, webmaster.
on August 25th, 2008 at 11:44 pm
Nice site
Thanks, webmaster.
on August 26th, 2008 at 7:39 am
Beautifull design
Thanks, webmaster.
on August 26th, 2008 at 7:47 am
Nice site
Thanks, webmaster.
on August 26th, 2008 at 10:48 am
Cool blog
Thanks, webmaster.
on August 26th, 2008 at 10:56 am
Amazine site
Thanks, webmaster.
on August 26th, 2008 at 4:52 pm
Beautifull design
Thanks, webmaster.
on August 26th, 2008 at 5:08 pm
Nice site
Thanks, webmaster.
on August 26th, 2008 at 11:01 pm
Nice site
Thanks, webmaster.
on August 26th, 2008 at 11:10 pm
Cool blog
Thanks, webmaster.
on August 27th, 2008 at 8:13 am
Cool blog
Thanks, webmaster.
on September 3rd, 2008 at 8:03 am
Amazing site.
Thanks, admin.
on September 5th, 2008 at 12:32 am
dtjie fguhdxpy eszvlcrnh qlpfduho fiblg tbwsryq iwpndk
on September 5th, 2008 at 7:13 am
Cool blog
Thanks, webmaster.
on September 5th, 2008 at 7:22 am
Nice site
Thanks, webmaster.
on September 5th, 2008 at 9:42 pm
Cool blog
Thanks, webmaster.
on September 5th, 2008 at 10:05 pm
Beautifull design
Thanks, webmaster.
on September 7th, 2008 at 8:15 am
Amazing site.
Thanks, webmaster.
on September 7th, 2008 at 12:09 pm
Nice site.
Thanks, webmaster.
on September 8th, 2008 at 5:37 am
Beautiful design.
Thanks, webmaster.
on September 8th, 2008 at 1:33 pm
Nice site.
Thanks, admin.
on September 8th, 2008 at 6:42 pm
Cool blog.
Thanks, admin.
on September 8th, 2008 at 6:47 pm
Amazing site.
Thanks, webmaster.
on September 11th, 2008 at 9:19 am
Amazing site.
Thanks, webmaster.
on September 11th, 2008 at 1:18 pm
Beautiful design.
Thanks, admin.
on September 11th, 2008 at 1:25 pm
Cool blog.
Thanks, webmaster.
on September 11th, 2008 at 3:52 pm
Amazing site.
Thanks, webmaster.
on September 11th, 2008 at 7:52 pm
Cool blog.
Thanks, webmaster.
on September 11th, 2008 at 7:57 pm
Amazing site.
Thanks, webmaster.
on September 11th, 2008 at 11:43 pm
Amazing site.
Thanks, admin.
on September 12th, 2008 at 2:58 pm
Amazing site.
Thanks, webmaster.
on September 12th, 2008 at 5:57 pm
Nice site.
Thanks, webmaster.
on September 12th, 2008 at 10:32 pm
Nice site.
Thanks, admin.
on September 13th, 2008 at 6:21 am
Beautiful design.
Thanks, webmaster.
on September 14th, 2008 at 12:24 am
Nice site
Thanks.
on September 14th, 2008 at 5:13 pm
Amazing site.
Thanks, webmaster.
on September 14th, 2008 at 7:32 pm
Beautiful design.
Thanks, admin.
on September 14th, 2008 at 7:37 pm
Nice site.
Thanks, webmaster.
on September 14th, 2008 at 10:28 pm
Beautiful design.
Thanks, admin.
on September 14th, 2008 at 10:32 pm
Cool blog.
Thanks, admin.
on September 15th, 2008 at 1:24 am
Beautiful design.
Thanks, webmaster.
on September 15th, 2008 at 1:28 am
Cool blog.
Thanks, webmaster.
on September 15th, 2008 at 9:08 am
Nice site.
Thanks, admin.
on September 15th, 2008 at 12:06 pm
Amazing site.
Thanks, webmaster.
on September 15th, 2008 at 12:10 pm
Amazing site.
Thanks, admin.
on November 24th, 2008 at 4:05 am
[...] MySQL & PHP Performance Optimization Tips [...]
on December 1st, 2008 at 6:22 am
[...] it. Learn how to use explain. A primer for this can be found here. Tune MySQL server settings (1,2,3,and more). Eventually throw in MySQL Proxy and read-write split (read from slave, write to [...]
on December 2nd, 2008 at 9:26 am
application acceleration…I’ ve had occasion to try out taksi, it worked well for GDI capture, but for Direct3D capture on the engine I used it failed in CTaksiDX9:: GetFrame during GetRenderTargetData. I’ ve found a solution by disabling the avi feature (I didn’ t need i…
on December 3rd, 2008 at 12:51 am
1iaDf9 hko9E3dnVxga1jOps
on December 6th, 2008 at 9:17 pm
Health Insurance Aetna Health Insurance Universal Life Insurance…I didn’t agree with you first, but last paragraph makes sense for me…
on December 12th, 2008 at 9:43 am
Enjoyed your site very much. Thank you! 8-)) Keep up a good work!
on December 12th, 2008 at 11:04 am
Excellent time. And nice site! 8-)) Keep up a good work!
on December 12th, 2008 at 12:35 pm
Perfect time. And nice site! 8-)) Keep up a good work!
on December 20th, 2008 at 5:52 pm
Good and nice site!
Keep up a good job!
on December 21st, 2008 at 1:10 pm
I’ll bookmark your site! Keep it up.
on December 22nd, 2008 at 3:40 am
Excellent and nice site!
on December 24th, 2008 at 6:20 pm
Enjoyed your site very much. Thank you!
Keep up your site!
on December 24th, 2008 at 6:21 pm
It is pretty!
Keep up your site!
on December 24th, 2008 at 9:05 pm
I’ll bookmark your site!
Keep up your site!
on December 24th, 2008 at 9:07 pm
on December 24th, 2008 at 11:11 pm
Good and nice site! ;-)) Keep up your site!
on December 25th, 2008 at 12:16 am
;-)) Keep up your site!
on December 25th, 2008 at 12:18 am
Excellent and nice site! ;-)) Keep up your site!
on December 25th, 2008 at 12:24 am
It is pretty! ;-)) Keep up your site!
on December 25th, 2008 at 3:28 am
I’ll bookmark your site! ;-)) Keep up your site!
on December 25th, 2008 at 3:43 am
Marvelous and nice site! ;-)) Keep up your site!
on December 25th, 2008 at 9:23 am
I love this post and the comparisons you have made.
on December 26th, 2008 at 10:10 am
Enjoyed your site very much. Thank you! Keep up a good job!
on December 26th, 2008 at 11:13 am
Keep up a good job!
on December 26th, 2008 at 3:19 pm
It is pretty! Keep up a good job!
on December 26th, 2008 at 3:41 pm
Good and nice site! Keep up a good job!
on December 27th, 2008 at 12:04 pm
I love this post and the comparisons you have made. Totally true and I guess some of this can be used in many applications in life!
on December 27th, 2008 at 7:36 pm
I like this post and the comparisons you have made. Totally true and I guess some of this can be used in many applications in life!
on December 29th, 2008 at 2:40 am
I like this site. Totally awesome and I guess this can be very usefull in life!
on December 29th, 2008 at 4:50 am
I enjoyed this site. Totally awesome and I guess this can be very usefull in life!
on December 29th, 2008 at 4:54 am
I love this site. Totally awesome and I guess this can be very usefull in life!