Profiling tips and tools for your LAMP stack

by dracony

Slide 1

Slide 1 text

Random Profiling Tips And Tools for your LAMP stack

Slide 2

Slide 2 text

MySQL

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

The slow log ● Logging to a table is also possible: log_output = TABLE

Slide 5

Slide 5 text

Parsing it ● Mysqldumpslow: ● ● ● https://code.google.com/p/mysql-log-filter/ ● https://github.com/LeeKemp/mysql-slow-query-log-parser

Slide 6

Slide 6 text

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?

Slide 7

Slide 7 text

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.

Slide 8

Slide 8 text

Monitoring ● Sadly, only a bunch of free tools =( ● Profile SQL ( http://www.profilesql.com/features/ )

Slide 9

Slide 9 text

PHP

Slide 10

Slide 10 text

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.

Slide 11

Slide 11 text

CakePHP usleep(1000); // 0.001s

Slide 12

Slide 12 text

Yii Notice the difference?

Slide 13

Slide 13 text

Sometimes XDebug is weird WTF? Laravel

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

XHPROF

Slide 16

Slide 16 text

Blackfire

Slide 17

Slide 17 text

Blackfire ● More or less same thing as XDebug and XHProf, but... ● Works in your browser ● Nicer UI ● May be also usable on production

Slide 18

Slide 18 text

Blackfire

Slide 19

Slide 19 text

XDebug can colorize your var_dump() xdebug.cli_color = 1

Slide 20

Slide 20 text

Linux

Slide 21

Slide 21 text

Perf Record system resource usage overtime perf record -a -g sleep 10 # record system for 10s perf report # display report

Slide 22

Slide 22 text

Perf

Slide 23

Slide 23 text

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/