Page 1 of 1

DataBase profiler

Posted: Thu Aug 13, 2009 2:07 pm
by chidge
Hi all

I am getting to the end of a mammoth creation and learning curve or creating a PHP, JavaScript and MYSQL dynamic site and I am looking for any recommendations on profiling my database to find any bottlenecks or issues with my MYSQL.

I have implemented caching across the entire site using pear but I still want to make sure when the DB is being used it is as efficient as possible.

Has anyone got any personal recommendations for performing such profiling? As always money is tight here so an open source application would be ideal to test on my home Dev server before I release her into the wild.

Thanks in advance for any help.

Re: DataBase profiler

Posted: Thu Aug 13, 2009 2:51 pm
by Eran
How are you accessing the database? if you are using some sort of abstraction, you can hook into it to profile your queries (measure the time it takes from query invocation to results using microtime() ). If you don't use abstraction, start using it!

You could have a look at Zend_Db_Profiler. It has a nice firePHP plugin as well, that allows you to monitor all the queries and timing as your are moving between pages, without changing anything in the pages themselves.

Also, run EXPLAIN on all your (major) queries and check out that you are hitting your indexes properly and not running into temporary tables, filesort and so forth.

Re: DataBase profiler

Posted: Thu Aug 13, 2009 3:13 pm
by chidge
I am currently using prepared statements no abstraction layer, but I am looking into Pear and PDO for future works.

I have played with the new query profiler in MYSQL and Explain and things look good with my indexs.

Thanks on Zend_Db_profiler and firePHP I will have a look at them in the morning.