Tools for searching for heavy php(5.2) scripts and resource-intensive queries to the database (mysql 5.1) on the server (freebsd 8.1)

Question

PHP

Guys, help advice.

Initial data:
There is a site on Bitrix, which began to slow down, although nothing seemed to be changed in the code and there were no more visitors.

Server:

• FreeBSD lapsi.ru 8.1-RELEASE-p1,
• apache-2.2.17_1,
• php52-5.2.14_1,
• mysql-server-5.1.51_1,
• nginx-0.8.53 (gives static)

Task:
Determine which module (in particular, which php file) began to cause the load.
Where heavy database queries come from (like this: SELECT SQL_CALC_FOUND_ROWS e.name,e.id,e.DETAIL_PICTURE,price.PRICE many times)

Answer 1

you can log slow requests.
my.cf
log_slow_queries = ON;
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 5
queries that last longer than 5 seconds will be logged

Answer 2

I usually look for such requests in mc with regexps

Answer 3

For scripts, use a profiler, for example, Zend Debugger, for the base it was already answered above, there is slow.log

Answer 4

xdebug for profiling and pinba pinba.org/wiki/Main_Page

Answer 5

I usually parse apache logs to see the longest executing scripts.

Answer 6

Everything described above is useful, but I will add what we use
Profiler - mirror.facebook.net/facebook/xhprof/doc.html Really cool thing. This is an extension that is easily managed from PHP. Of course the results don't look as pretty as xdebug + kcachegrind but there is a very useful feature for you. The results of profiling different pages can be aggregated into one averaged report. That is, you can profile in production, say 0.01% of all queries, and then watch the aggregated report. You will immediately see which functions are slowing down. With xdebug, you can go wrong and profile the wrong URLs.
I will immediately answer the questions, but we tried to turn it on under load and did not notice a strong overhead.
For SQL, I strongly recommend www.maatkit.org/doc/mk-query-digest.htmlAble to use SHOW PROCESSLIST, binlog as a source and even analyze network traffic via tcpdump. The most useful thing is that it can combine queries into logical classes up to parameters.

Answer 7

I set up xdebug profiling normally (!) and that's it.

Answer 8

Your question already contains the answer to it - "website on Bitrix"