3 Replies Latest reply on Apr 13, 2017 4:16 PM by mandevil

    Top SQL (MySQL) is just a "COMMIT" statement

    lucienj64

      Every day, DPA shows that the Top SQL in my MySQL (Community 5.6.27, Windows Server 2012) is a solo "COMMIT" statement.

      MySQL Trend images.png

       

      Solo COMMIT.png

       

      This is not very helpful in drilling down to find bottlenecks or poor-performing SQL code. Is this a factor of how MySQL is parsed, a side-effect of how the code is constructed in our environment, or just the way that DPA sees and reports on the engine's performance and behavior? Is there some way within DPA to connect this "query" to the transaction that is being committed? I want to use this tool to improve my code (and server performance), but I'm not getting any useful information at this point.

        • Re: Top SQL (MySQL) is just a "COMMIT" statement
          mandevil

          I'd start by checking whether you have autocommit enabled or not.

          If not, then your code is opening a transaction (which is a separate statement as seen by MySQL), then when finished, issuing a commit.

          Keep in mind that autocommit will be disabled if using a start transaction statement even if enabled at the system level.

          As such, the transaction itself (insert, update, delete) is a separate statement from the actual commit statement.

          Think about it this way - at the command line, you can issue

           

          start transaction;

          update table1 set fname='Rob' where empid=1;

           

          Then just sit there. The transaction is already complete, but it's waiting on you to make a decision on whether to commit or rollback. Two activities as seen by MySQL.

           

          I'd check out a couple things in your environment:

          • Check disk write latency where you have your binary logs located
          • Look at the code logic and how it's doing the transactions - is it one row at a time, then commit or is it batching large row transactions for more efficiency

           

          Hope that helps.

            • Re: Top SQL (MySQL) is just a "COMMIT" statement
              lucienj64

              mandevil

               

              Thanks for the reply; AUTOCOMMIT is ON.

               

              I should have explained that I am an experienced DBA, but I am new to DPA and am still figuring out how to effectively use it. The Home screen shows me a red exclamation point icon, indicating that there are "Critical query issues" on this instance (I am monitoring one other MySQL instance and 3 separate SQL Server instances in addition to this one). I click the icon and see the "Trends" screen that I included in my original post; the stacked bar graphs (and the icon in the Advisors section) show me that one "query" is responsible for a disproportionate percentage of total execution time. I click on "read more..." and see that the query in question is nothing more than a single "COMMIT" statement. This database is not subject to ad-hoc, interactive user queries; it is the support database for a public-facing web site and is fed queries from our application code. The queries do not come from live users, but the application itself. When you recommend that I "Look at the code logic", you are telling me to do exactly what I *want* to do but cannot; I can't find out how to trace a given execution of "COMMIT" back to any other code or transaction. It's likely that this is not one COMMIT that is taking up so much time, but the cumulative effect of *all* of the COMMITs; since they are being treated as individual statements, DPA sees them as repeated executions of the same "code" and is reporting that a single, identical transaction is being executed over and over, adding up to a lot of execution time. In this scenario, the COMMIT statement is not so much responsible for the CPU usage itself but reflects the time to COMMIT each transaction. Since the processing time for the transactions is not being aggregated under the SQL code in each transaction, but is being aggregated against the COMMIT statement (according to DPA's reporting), I can't determine if any one (or more) of those "source" transactions is in need of optimization.

               

              If this is a fact of life for how MySQL 5.6.27 executes SQL code, in combination with how DPA records and reports that activity, then I will simply ignore this COMMIT statement and pursue tuning actions another way. I am here asking if anyone else has experienced the same symptom, and if there is a way to reconfigure MySQL and/or DPA to show each transaction along with its COMMIT operation, instead of grouping all COMMITs together. If that is, in fact, the correct interpretation of what I am seeing.

                • Re: Top SQL (MySQL) is just a "COMMIT" statement
                  mandevil

                  lucienj64 - thanks for the clarification. This is likely a result of how MySQL is tracking activity (thus how we observe it). So your interpretation of how DPA observes the commit activity as an aggregate across all transactions is accurate. One thing you might do is take a representative slice of time (click into a day, set the interval to a day, then click on the one vertical bar that represents that day). Then look at the top SQL statements and see if you can identify any transaction statements that have a lot of executions (note, it may not be the ones accruing the most wait time). If you see a pattern, that may be something you can follow up with the application vendor on (they may be able to tell you more about the logic being used).

                   

                  On your side of the house, I'd still recommend making sure write latency is low where you have the binary logs.

                  Between the way the application is running the transactions, the way MySQL is tracking the activity, and the way DPA is displaying it, you may not be able to get the association exactly as you want it (commit activity contributed to specific transaction statements/executions).