Profiling tips and tools for your LAMP stack

Bf6729adaf5dd05bc0312b885c9d0e5f?s=47 dracony
December 02, 2014

Profiling tips and tools for your LAMP stack

Some hints for profiling your Linux, Apache (Nginx), PHP and MySQL stack.

Bf6729adaf5dd05bc0312b885c9d0e5f?s=128

dracony

December 02, 2014
Tweet

Transcript

  1. 2.
  2. 3.

    The usual start • Turn on slow query log: slow_query_log

    = 1 slow_query_log_file=”/var/log/mysql/slow.log” long_query_time = 1 • … and also log queries not using indexes: log_queries_not_using_indexes = 1 • … and if you use replication: log_slow_slave_statements = 1
  3. 4.

    The slow log • Logging to a table is also

    possible: log_output = TABLE
  4. 6.

    Narrowing down Obviously try EXPLAIN first: If the query had

    a usable index the result would be: But what if we have too many indexes?
  5. 7.

    Persona toolkit • 32 tools for various things • pt-index-usage:

    – This tool connects to a MySQL database server, reads through a query log, and uses EXPLAIN to ask MySQL how it will use each query. When it is finished, it prints out a report on indexes that the queries didn’t use.
  6. 8.

    Monitoring • Sadly, only a bunch of free tools =(

    • Profile SQL ( http://www.profilesql.com/features/ )
  7. 9.

    PHP

  8. 10.

    Use a ruler Adding a simple usleep() call when profiling

    will provide you with a reference point for easier visual comparison of generated graphs. The bigger the usleep() block appears the faster the application performs.
  9. 14.

    Try XHProf • Doesn't slow down execution time as much

    as XDebug • Profiling log file ~10 times smaller • Memory profiling. With XDebug you have to generate a trace to get a memory profile. • Allows you to compare results from multiple runs • Sadly no KCacheGrind and the profiler output is not very pretty
  10. 15.
  11. 16.
  12. 17.

    Blackfire • More or less same thing as XDebug and

    XHProf, but... • Works in your browser • Nicer UI • May be also usable on production
  13. 18.
  14. 20.
  15. 21.

    Perf Record system resource usage overtime perf record -a -g

    sleep 10 # record system for 10s perf report # display report
  16. 22.
  17. 23.

    Munin • Spot problems before they become critical • Plugins

    to monitor virtuallly anything: – Drupal – Apache – PHP – Joomla – Reddit Karma ( seriously ) • Distributed. Generate logs for all your servers. • Demo: http://demo.munin-monitoring.org/
  18. 24.