5 Replies Latest reply on Feb 2, 2010 8:45 AM by mazz

    JDBC tracing rules

    mazz Master

      Pavel Sher had an interesting use-case he mentioned in thread: http://community.jboss.org/thread/146132 where he wanted rules to check if a JDBC statement was closed (using the $! notation, which is now implemented in Byteman 1.2.1).

       

      I would like to start a new thread here where we can colloborate and come up with some JDBC rules that can trace JDBC queries.

       

      Here's the use case - I want to know all the SQL queries (either select, insert, update or delete) that were executed AND I want to know how long each query took (in fact, I want to know the number of times a particular SQL was executed, its maximum execution time, minimum execution time and average execution time). I think this is now possible, now that $! is implemented.

       

      Here's my thoughts. Its not finished, I was hoping others had some ideas to help complete this.

       

      First, we need to know when each prepare statement is created, because its during its creation when we know the SQL:

       

      RULE Prepare SQL Statement 1
      INTERFACE java.sql.Connection
      METHOD prepareStatement(String)
      HELPER helper.JdbcTracerHelper
      AT EXIT
      IF TRUE
      DO prepareStatement($!, $1),
         traceln("JDBCTrace: prepare1=[" + $1 + "]")
      ENDRULE

       

      Note that we'll need several of these because java.sql.Connection.prepareStatement is overloaded - but all have "String sql" as their first argument - and we really only care about the SQL string here (we can ignore the other parameters). Notice I assume there is a helper method called "helper.JdbcTracerHelper.prepareStatement(PreparedStatement stmt, String sql)". This will be used to cache the SQL in some data structure. It needs to remember the SQL string, but it also has to remember the instance of the PreparedStatement for the next rule.

       

      RULE Start Execute Prepared Statement

      INTERFACE java.sql.PreparedStatement

      METHOD executeQuery

      HELPER helper.JdbcTracerHelper

      AT ENTRY

      IF TRUE

      DO startTimerExecutePreparedStatement($0),

         traceln("JDBCTrace: startExecutePS=[" + $0 + "]")

      ENDRULE

       

      Note again there will need to be a few more of these rules, when executing the "execute" and "executeUpdate" methods in java.sql.PreparedStatement. This rule will start or reset a timer for the given prepared statement (it can utilize the built-in helper methods for Timer functionality)

       

      Of course, now we need an AT EXIT to stop the timer and get the elapsed time:

       

      RULE Stop Execute Prepared Statement

      INTERFACE java.sql.PreparedStatement

      METHOD executeQuery

      HELPER helper.JdbcTracerHelper

      AT EXIT

      IF TRUE

      DO stopTimerExecutePreparedStatement($0),

         traceln("JDBCTrace: stopExecutePS=[" + $0 + "]")

      ENDRULE

       

      We'll probably want another rule when when an exception occurs (we could use this to track SQL errors too!). I don't have an example of that.

       

      Finally, we'll need to trace java.sql.Statement instances, too (rather than PreparedStatements). This is in the case when someone directly invokes java.sql.Statement.executeQuery(String) and related methods. We'll have similar rules as above, but I don't have examples.

       

      So the things I'm hoping some others can help with are:

       

      1) fill out the rest of the rules to cover all overloaded methods in java.sql.Connection, java.sql.Statement, java.sql.PreparedStatement

      2) how do we actually dump reports? Rather than rely on traceln log messages, it would be cool if somehow, someway we could get these rules to dump their data/reports in some file that can be read later.

       

      Hopefully, others out there might find this kind of thing useful and can help fill out these rules.

        • 1. Re: JDBC tracing rules
          Pavel Sher Newbie

          Just a couple of notes. First of all, there are tools already which can track execution time of SQL statements, for example, we (at Jetbrains) are using YourKit for this (http://yourkit.com), very cool profiler btw. Also  db engines (for example, MySQL) can log statements (with actual values instead of ? marks) which executed more than specified amount of time. MSSQL has profiler which solves almost the same task.

           

          As for reporting, it would be useful to have more convenient way to perform an action when JVM exits (see another related thread: http://community.jboss.org/thread/18886?tstart=0). I think everyone who uses Byteman to gather some statistical data (like SQL queries duration) would benefit from having more convenient way to dump report when JVM exits.

           

          Another way that I found useful when experimented with Byteman - is to dump report when Ctrl-C is pressed in the console when Java process runs. This can be done with help of signal handlers (an article on this topic: http://www.ibm.com/developerworks/java/library/i-signalhandling/). This is especially useful for server side applications.

           

          Just my 2 cents.

          • 2. Re: JDBC tracing rules
            Andrew Dinn Master

            Hi Pavel,

             

            Thanks  for pointing John at the discussion we had about shutdown hooks.

             

            While the forums were down last week I discussed a few ideas with with John via email concerning how to perform periodic operations such as collating and dumping statistics gathered by rules. This may interest you as I believe you were also trying to perform periodic operations.

             

            Essentially we came up with two alternative approaches. One was to extend the rule language to support this sort of behaviour. The other was to scan  life-cycle methods attached to helper classes and invoke them at suitable intervals.

             

            As an example fo the first approach we might consider implementing rules such as

             

            RULE timer example

            AT INTERVAL 1000

            HELPER org.my.IntervalStats

            IF TRUE

            DO dumpAndResetStats()

            ENDRULE


            RULE timer setup

            AT LOAD

            HELPER org.my.IntervalStats

            IF TRUE

            DO initializeStats()

            ENDRULE


            RULE timer teardown

            AT UNLOAD

            HELPER org.my.IntervalStats

            IF TRUE

            DO clearStats()

            ENDRULE

             

            . . .

             

            These rules are attached to events occuring in  the rule engine/runtime rather than the running program. Obviously, the INTERVAL rule would be triggered periodically, in this case every 1000 millisecs, by a rule engine timer thread while the LOAD and UNLOAD rules would be triggered at rule load and rule unload time. Other events might include e.g. AT SHUTDOWN . . . perhaps you could suggest some others?

             

            These rules differ from the current  rules in that they are not associated with a trigger location in a specific class or method. This implies that the BIND, IF and DO clauses must root their expressions either in static data or built-in methods defined by their helper -- there is no method context to provide bindings for parameters or local variables and that RETURN and THROW actions would be disallowed in DO clauses. It also means that types mentioned in the rules would have to be resolved via a specific class loader, probably the system class loader (normally, the type checker resolves via the classloader of the triggger class).

             

            The alternative approach is to provide life-cycle management for rules which employ a given helper by invoking associated methods of the helper class where present. So, for example, assume org.my.IntervalSats implemented the following  methods:

             

              public static void load(Rule)

              public static void unload(Rule)

              public static  void start()

              public static  void stop()

              public static  void shutDown()

              public static int getTimerInterval()

              public static void notifyTimer()

             

            etc

             

            These would be detected and called by the agent at appropriate points:

             

            load() and unload() would be called as individual rules employing the helper class get loaded and unloaded.

             

            start() would be called when the the first rule employing the helper is loaded (or at the first reload after all such rules have been unloaded).

             

            stop() would be called when all rules employing the helper are unnloaded.

             

            etc.

             

            These methods might be best presented via interfaces e.g. The timer functionality would be presented by making the helper class implement the following interface

             

            interface RuleTimer

            {

            public static int getTimerInterval()

            public static void notifyTimer()

            }


            class IntervalStats extends Helper implements RuleTimer, RuleLoader, . . .

             

            So, what do you think of these alternatives? John preferred the use of helper methods -- this has the advantage that it will be much easier to implement since it does nto require changing the rule engine parser/type checker but I am still interested in getting more feedback before confirming this choice.

             

            Also, according to these two schemes what else would you suggest by way of triggers/life cycle methods?

             

            Finally do you have any suggestions for alternative ways of achieving this sort of functionality?

             

            I guess this discussion might have been considered more appropriate to the design forum. However, to me this is very much a user and usablity issue. I hope you can find  cents or more to contribute. Thanks for anythin you can provide.

             

            regards,

             

             

            Andrew Dinn

            • 3. Re: JDBC tracing rules
              Pavel Sher Newbie

              Hello Andrew,

               

              Sorry for delay. Unfortunately I can't say anything about timers or other events, I did not use timers when worked with Byteman, so I can't say for sure what would be better. As for shutdown, I beleive having AT SHUTDOWN would be just enough (to me AT SHUTDOWN looks more natural than some method in helper class). Actually, if I need more complex features, like timers, I would implement them by myself, using Java 1.5 executors.

               

              Also I can't see how the approach with interfaces will work. Interface can't have static methods, probably you can use some annotations to mark methods in the helper class.

               

              If you can't chose an aproach, probably it is better to make a small step and implement AT SHUTDOWN only , then the more feedback you'll get the better you'll see how to implement other events.

              • 4. Re: JDBC tracing rules
                Andrew Dinn Master

                pavel.sher wrote:

                 

                Sorry for delay. Unfortunately I can't say anything about timers or other events, I did not use timers when worked with Byteman, so I can't say for sure what would be better. As for shutdown, I beleive having AT SHUTDOWN would be just enough (to me AT SHUTDOWN looks more natural than some method in helper class). Actually, if I need more complex features, like timers, I would implement them by myself, using Java 1.5 executors.

                 

                No problem with taking your time. I want to see what others think they need before I rush to implement anything. Thanks for your feedback. I'll add it into the design melting pot.

                pavel.sher wrote:

                 

                Also I can't see how the approach with interfaces will work. Interface can't have static methods, probably you can use some annotations to mark methods in the helper class.

                 

                 

                Doh!

                 

                Must think before posting crazy ideas.

                Must think before posting crazy ideas.

                Must think before posting crazy ideas.

                . . .

                 

                pavel.sher wrote:

                If you can't chose an aproach, probably it is better to make a small step and implement AT SHUTDOWN only , then the more feedback you'll get the better you'll see how to implement other events.

                 

                You mean that's all you want but ASAP ;-). I'm still unsure which apporach will be best: rules or helper lifecycle calls. I would prefer not to have two mechanisms to do the same thign -- more code to go wrong, more to document and, probably, more work done at runtime. So starting on one solution would mean either ruling out the second or decommissioning it later if I decide to switch. I'd rather conisder it a bit longer. Anyway, I'm busy on other work for the next week or two so it'll be a while before anythign changes.

                • 5. Re: JDBC tracing rules
                  mazz Master
                  I still prefer the helper methods.