MySQL & PHP Performance Optimization Tips

In: MySQL|PHP

25 Jun 2007

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.

MySQL indexing optimization

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

Share

1 Response to MySQL & PHP Performance Optimization Tips

Avatar

Jared - Regina Web Design

October 24th, 2011 at 12:24 am

Thanks for the info, it was exactly what I was looking for. BTW, some of your syntax examples are spilling out of the containers…

Comment Form

About this blog

Andrew Johnstone is a software engineer / lead developer working at Everlution Software.

Photostream

  • Multiple Ucarp on the same host | PaoloBertasi: […] http://ajohnstone.com/achives/running-several-vips-on-the-same-interface-with-ucarp-and-ha [...]
  • mohit: I am working the same on windows server 2008 and mysql version 5.1.39 ,but it is not working and i a [...]
  • Steve: Hi there... sorry that this is old, but I'm trying to use your script to check for my usage per seco [...]
  • vinodh: great. thanks . My boss wanted me to configure multiple mysql instances on same physical machine . i [...]
  • andrew.johnstone: In the example above this was in fact using spl_autoload_register. I've never debugged this properly [...]

GitHub

GitHub Octocat

ajohnstone @ GitHub

  • Status updating...