DataBase profiler

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
chidge
Forum Commoner
Posts: 29
Joined: Sat May 10, 2008 4:03 pm

DataBase profiler

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: DataBase profiler

Post 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.
chidge
Forum Commoner
Posts: 29
Joined: Sat May 10, 2008 4:03 pm

Re: DataBase profiler

Post 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.
Post Reply