Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

CodeIgniter has a very nice feature called a Profiler, which displays information at the foot of a page, information such as GET, Memory, POST data. It also shows database queries and how long they took to run which I find very useful.

I have recently taken over a legacy PHP application and it would help a lot to see what tables in the database are being queried by PHP pages. Is there a library for raw PHP applications that could produce something similar to CodeIgniter's Profiler? I am already using xdebug, I am only interested in profiling database queries.

share|improve this question
3  
CI's profiler gets its query info from CI's database class, it won't work in isolation and you probably won't find an equivalent for a legacy app. If query data is all you're interested at, and assuming you're working with mysql, then look no further than the query log. –  Yannis Rizos Jan 2 '13 at 15:26
    
Hi Yannis, Query Log looks very promising. I'll read through it and find out if there is a way to collect some query data from the log and perhaps add it to the footer of the PHP I'm looking at. Thank you! –  Gortron Jan 2 '13 at 15:34
1  
@YannisRizos I think that should be an answer. Query logs can be extracted from most (all?) RDBMs and will usually provide execution times, and they can often be massaged to provide information about which tables are hit most often etc. It isn't exactly a "library", but it achieves most of what the OP seems to be after and is probably about as close to a real (and generic) answer as you can get. –  Michael Kjörling Jan 2 '13 at 16:13
    
Thanks to YannisRizos pointing me to the mySQL Logs, which is new to me, I have begun to put together a very basic mySQL profiler for PHP applications. The goal is to create a profiler to help find out what mySQL is doing in a PHP application. I have added the code so far to Github. Go forth, be forkful, and multiply your pull requests. at github.com/murrion/php_mysql_profiler –  Gortron Jan 2 '13 at 21:25
1  
Adding debugging info to the page footer has three problems though: it won't work when you redirect (which you should always do after a successful POST), it interferes with headers if you output anything before you're ready to start output ("headers already sent" anyone?), and it's insanely scary - one little but of misconfiguration, and all that debugging information is out on the street. Better put debugging information in the error log or something, where the worst thing that can happen is that you produce a whole lot of log data. –  tdammers Jan 3 '13 at 0:02

1 Answer 1

up vote 2 down vote accepted

CI's profiler is a nifty little class that assembles data from CI's database class and CI's controller class and gives you a pretty thorough view of what's going on. Unfortunately it's not going to work in isolation, it's tightly coupled with the rest of the framework, and afaik there isn't a simple alternative that you could plug in into a legacy application.

Since you are mainly interested in profiling database queries and assuming you are using MySQL, you should check out the General Query Log:

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed. This logging order contrasts to the binary log, for which statements are written after they are executed but before any locks are released. (Also, the query log contains all statements, whereas the binary log does not contain statements that only select data.)

From MySQL 5.1.6 you can store the log in a MySQL table, thus the logs are extremely easy to query and present.

PostgreSQL has a similar mechanism that's also quite easy to setup (PostgreSQL manual > 18.8. Error Reporting and Logging). If you are using PostgreSQL, you should check out pgFouine, a log analyzer written in PHP. I'd imagine every other major RDBMS provides similar logging mechanisms.

The query log will give you all the information you'll need for your database queries, and it's quite easy to hack together a simple script to prettify the reports. If you're interested in profiling HTTP requests as well, then you should look at Apache's logs (or your favourite web server's logs). For log analyzers, you should check out AWStats, apache-scalp and PHPIDS.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.