Profiling MySql queries

[kikoz]

hi folks
I am new to spring/Java world and a bit lost now. I am using Spring 3.1.0 and Hibernate 4.1.2 in the project and MySql db (5.6), also I have decided to use log4j (1.2.16).

And now I want to profile all the queries going trhough com.mysql.jdbc.Driver/org.apache.commons.dbcp.BasicDataSource
I know that there are properties like logger and profileSQL which I could use in order to get some metrics from jdbc driver. But I have no idea how I can pass this parameters to com.mysql.jdbc.Driver here (see http://dev.mysql.com/doc/refman/5.6/...roperties.html)?

I have described bean as

<bean id="jdbcDataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />

<property name="url" value="jdbc:mysql://localhost:3306/test" />
<property name="username" value="root" />
<property name="password" value="test" />

<property name="maxActive" value="100" />
<property name="maxWait" value="10000"/>
<property name="minEvictableIdleTimeMillis" value="5000"/>
<property name="timeBetweenEvictionRunsMillis" value="10000"/>
<property name="testWhileIdle" value="true"/>
<property name="validationQuery" value="SELECT COUNT(*) FROM DUAL"/>
</bean>


is there any other way to log queries and metrics?

[delgad9]

@kikoz,

I just finished a post on a sibling topic* on the matter...
Next, I would like to do RnD about queries, metrics for tuning and performance.

If you can come up with use-cases about what your try to accomplish I'll share my solution(s) with you.


Thank you
jD @ http://pragmatikroo.blogspot.com

* @ http://pragmatikroo.blogspot.com/201...ment-this.html

[kikoz]

thanks for comming back
basically I want to see SQL queries with timings in my logs - in the very same place where hibernate events is written to today for my webb app using spring and running under tomcat
so e.g. I've got two appenders now
one for console - org.apache.log4j.ConsoleAppender (good for debugging)
one for file - org.apache.log4j.RollingFileAppender (when tomcat with my app is running in standalone mode)

so is there a way to start logging SQL queries generated by hibernate and their timings? and hear I would prefer to do it from the jdbc driver as a source and not from hibernate

[delgad9]

I would suggest to take a "not to reinvent the wheel" approach...
You are totally positioned for using Spring Insight. It might be more comprehensive that you are scoping.

But, you can use the api in case the product it is too much for you.

I hope helps you

jD @ http://pragmatikroo.blogspot.com