Memory usage while using Profiler/logSlowQueries in Connector/J
Posted by: Format Dynamics
Date: May 18, 2012 12:18PM

I'm using Tomcat 7.0.23 with Connector/J 5.1.20 on CentOS 5.8 x86_64 to MySQL cluster 7.1.18

My context connection string is:
jdbc:mysql:loadbalance://hosts/db?failOverReadOnly=false&loadBalan
ceStrategy=bestResponseTime&connectTimeout=1000&queriesBeforeRetryMaster=10&logSlowQueries=true&slowQueryThresholdMillis=2000&gatherPerfMe
trics=false&reportMetricsIntervalMillis=30000

Since adding the slow query parameters i've noticed that my tomcat memory free decreases every day until i get an out of heap memory error and the jvm crashes, which occurs after about 7 days (i do about 3.5M mysql queries during that time)

Analyzing the heap dump with MAT i've noticed that there are thousands of
HashMaps responsible for eating up most of the heap. For the tree below, there are about 15,000 instances of HashMap$Entry as shown.

org.apache.catalina.loader.WebappClassLoader
_com.mysql.jdbc.ProfilerEventHandlerFactory
__java.util.HashMap
___java.util.HashMap$Entry[]
_____java.util.HashMap$Entry <---- 15,000 of these
______java.util.HashMap$Entry
______com.mysql.jdbc.JDBC4LoadBalancedMySQLConnection
______com.mysql.jdbc.profiler.LoggingProfilerEventHandler

Are these kept in memory to calculate statstics of response times for slow queries? If so, why so many? If not, what are they and is this expected behavior or a bug?

2nd question:
Even though I have 2000ms set above as the slow query threshold, it still logs queries much faster than that as a slow query, such as:

INFO: Profiler Event: [SLOW QUERY] at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) duration: 10 ms, connection-id: 178161, statement-id: 244920, resultset-id: 0, message: Slow query (exceeded 95% of all queries ms, duration: 10 ms):INSERT INTO...

This doesn't seem like expected behavior either.

Any help is appreciated.

Thank you,

-Tony

Options: ReplyQuote


Subject
Written By
Posted
Memory usage while using Profiler/logSlowQueries in Connector/J
May 18, 2012 12:18PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.