• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Measure Query Duration

 
J.D. Thompson
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34074
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could use an application (not database) profiler, a driver like Jamon or add printlns.
 
steve souza
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
     
    J.D. Thompson
    Greenhorn
    Posts: 28
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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!
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic