MySQL : getting the most intensive/called/slow requests

Posted on 2011-10-06
Last Modified: 2012-05-12

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

Question by:mchkorg
    LVL 24

    Accepted Solution

    Some of that can be accomplished by using the slow query log, see
    LVL 7

    Author Comment

    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)
    LVL 24

    Expert Comment

    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.
    LVL 7

    Author Closing Comment

    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.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now