Two Laptop Bag
The moose likes JDBC and Relational Databases 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 and Relational Databases
Bookmark "Measure Query Duration" Watch "Measure Query Duration" New topic

Measure Query Duration

J.D. Thompson

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!

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33117

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

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
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:
  • 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 ] - a fast, free open source performance tuning api.
    JavaRanch Performance FAQ
    J.D. Thompson

    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:

    Hope this helps the next guy!!!

    Take care!
    I agree. Here's the link:
    subject: Measure Query Duration
    It's not a secret anymore!