I’ve started to benchmark a circular replicated mysql cluster last week, and found some strange behaviour. The cluster contains 10 nodes. The storage engine is MyISAM. We mostly do writes in that cluster. The benchmark was to create a simple SQL select, through JDBC (directly):
SELECT * FROM users WHERE id=[fixed value]
The queries were runned with 50ms pause between them. I’ve done the same thing from bash using the native mysql client. Check the graphs:
As you can see on the graphs, both of the measurements have smaller peaks, but Java has big peaks up to 5 seconds. Although I think, that the small peaks are not normal, the original reason for the benchmark was, that we discovered the big peaks. Check the measurements displayed on the same graph (you can see, that the small peaks are correlated, or at least the “native” peaks are a “subset” of the Connector/J peaks):
WTF? Everybody who expected 4 second peaks for SHOW VARIABLES, please leave a comment, and I will buy you a beer, if we once met :-). If you look very closer, you can see, that the native measurement has peaks also, but they are max 250ms long. (The reason for that the peaks are not in the same place on the x axis, is that the above measurement was not started at the same time, and I display only relative time on the x axis.)
After a while I’ve created a statistics from the peaks, and it turned out, that they are appearing always with nearly 5 minutes between them. There is only one thing, which is done repeatedly with 5 minute delay: Hyperic monitoring.
So currently I have a suspect, and a few of unanswered questions:
- why the peaks appear only through Connector/J and not from native client
- why the peaks appear for SHOW VARIABLES
- why the peaks appear only on circular replicated database (master slave db is also monitored)
- why are the peaks 5 seconds long, when the Hyperic measurement is only a few milliseconds
I will write a follow-up for this post, if I manage to find the reason for this strange behavior. If anybody has ideas, what to measure, what can be the reason, please dont hesitate to share it with me!