• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

Measure Query Duration

 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41086
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You could use an application (not database) profiler, a driver like Jamon or add printlns.
 
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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!
     
    I promise I will be the best, most loyal friend ever! All for this tiny ad:
    Free, earth friendly heat - from the CodeRanch trailboss
    https://www.kickstarter.com/projects/paulwheaton/free-heat
    reply
      Bookmark Topic Watch Topic
    • New Topic