MySQL : getting the most intensive/called/slow requests

Hi,

With MySQL (the community edition I guess, the one packaged in Debian and many other distros I think), what (free) tool do I have to check the performance?
I'd like to be able to get the most disk intensive requests, the ones reading too many lines, the requests called too many times (let say, a lot of times), that kind of top-tens. My goal is to highlight some bad requests and algorithms we've probably written in our application.
This way, we'll change some programs, create indexes where we need and so on...

I found some commercial tools (I don't remember the company name right now), but I don't think they're free.

In comparison with Oracle, I know I have all the information inside the database, and I can extract it. I don't think it's the same with MySQL

Regards,
LVL 7
mchkorgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mankowitzCommented:
Some of that can be accomplished by using the slow query log, see http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mchkorgAuthor Commented:
OK, I'll tell our "partner" [supposed to be] managing our database to tell them to activate it.
But I'd prefer to give them every configuration modification at the same time.

So, any other idea? (including or not an optional DB reconfiguration)
johanntagleCommented:
Have the slow query log enabled first, then after at least one busy day (more would likely be better), ask for a copy.  Tune your queries and add indexes based on that.  Better if you have that covered before you move to server tuning.  After you've performed that, wait another few days, then ask for  the following:

1.  Copy of the slow query log
2.  Copy of my.cnf
3.  Output of "show variables" (this is just in case somebody put some setting that overrides that of my.cnf)
4.  Output of "show global status"

The combination of the above will help you formulate what configuration settings need to be changed.
mchkorgAuthor Commented:
Thank you, with this, I have tons of awful requests to give to the development team.
I'm closing this question now as I don't event need further analysis right now. I'll check my.cnf and other variables later, when those awful requests and algorithms are corrected.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.