aspose file tools*
The moose likes JDBC and the fly likes Measure Query Duration Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Measure Query Duration" Watch "Measure Query Duration" New topic
Author

Measure Query Duration

J.D. Thompson
Greenhorn

Joined: Mar 08, 2006
Posts: 28
I am conducting performance testing on a Java Application which uses a MySQL db. I am attempting to use MySQL Query Profiler to measure db performance. Both are on the same Linux machine.

I initiate the profiler by logging into the MySQL client and "set profiling=1". However, this captures only the queries executed from the command line and not those from the application.

Any ideas on how I need to go about this?

Thanks in advance for your insight and help!

JD
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30913
    
158

You could use an application (not database) profiler, a driver like Jamon or add printlns.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
Some advantages of jamon:
  • jamon does not require code changes
  • It works with any jdbc driver.
  • It takes about 5 minutes to configure. Here's how: http://jamonapi.sourceforge.net/#WhatsNew22
  • jamon will allow you to see the following: Every query's performance (min/max/hits/avg), current running queries, open connections, SQL exceptions and more.
  • Also use jamon to monitor page hits, http status codes, servlet exceptions
  • Also monitor log4j log calls (number of warnings, errors, etc. You can also 'tail' the log with jamon
  • It's free



  • A link to a demo is below.
    [ June 09, 2008: Message edited by: steve souza ]

    http://www.jamonapi.com/ - a fast, free open source performance tuning api.
    JavaRanch Performance FAQ
    J.D. Thompson
    Greenhorn

    Joined: Mar 08, 2006
    Posts: 28
    Ok, I have the answer!!!

    Mysql allows multiples sessions to the server instance. As a result, you can have different profiling for different sessions, thus it is critical that you set the profiling for the built-in MySQL Profiler from INSIDE the application you are wanting to profile, IF you are desiring to profile live queries versus profiling from the command line on a query by query basis.

    Once you have obtained a connection to the database, you execute the following query:

    set profiling=1;

    and a temporary table within the information_schema db will be created. ALL queries run by the application will then be measured and recorded and values stored. However, you must capture that information BEFORE your session ends, i.e. the application instance vanishes.

    There is alot more to share but the following link is a great help once you know how to start the profiling from inside the app:

    http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

    Hope this helps the next guy!!!

    Take care!
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Measure Query Duration