Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help debugging mysql timeouts

 
Joe Areeda
Ranch Hand
Posts: 332
2
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm at my wits end with this one and hoping someone can offer me a suggestion or two on where to look.

The situation is a scriplet (I know I'm working on making it a set of servlets) running under Tomcat with Apache on the front end and mysql on the backend, using JDBC not the Tomcat connection pool (also on my list of upgrades).

A couple of weeks ago things got really slow. I've tracked it down to some, not all, very slow mysql queries. Some take 2ms some take 2 minutes. I do have retries if the connection is lost and this is what I believe is happening. Requests are timing out, getting retried, eventually work but slow enough to tick off Mother Teresa if she came back and cared about gravitational waves.

To complicate matters I did the following experiment. The problem is on the production machine which is at a university running on a virtual machine. I have a prototype system at home also running on a virtual machine. The set ups are almost identical, my host is an I7 theirs a Xeon. Our packages sometimes have different versions as I try to keep the prototype ahead to anticipate any update problems but I don't recall any updates coincident with the problem.

I used mysqldump to copy the entire mysql database set from production to prototype. I copied the war file for my application. The Apache and Tomcat configurations are essentially identical or should be except for locations.

The prototype system does not show this behavior. My 15/2 Mbps Internet connection way out performs their 10/10Gbps pipe (tested from a 3rd site).

I've rebooted the production server and tried with other users kept out so I don't think it's a loading or stale connection issue. I've pinged long enough to rule out dropped packets.

It's very repeatable, production bad, prototype good.

I've run out of ideas. It smells like a hardware problem but

I'd appreciate any tests you can suggest. I just thought of one while typing this message. I will see if I can get anything useful out of Wireshark.

Thanks,
Joe
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Other ideas:
1) Check for a connection leak in the application
2) Check if your queries are waiting on each other for locks. You can run database queries directly from mysql to see if this is the problem.
 
Joe Areeda
Ranch Hand
Posts: 332
2
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne

those are good suggestions

Joe
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It looks like there are some tools for monitoring low level MySQL activity. This might help to narrow your search much quicker.

Let me also note that dumping and restoring the database doesn't result in an identical database on your test machine (assuming my understanding of MySQL dumps is correct, which is not guaranteed ). The data in the target database are physically stored differently. I've once seen (in an Oracle database) that exporting and importing a database improved performance significantly. The reason was that some of the original tables contained a lot of rows at some point of time, which were later deleted. But -at least in Oracle- the table segments are not automatically shrunk, so full scans over the original tables were much slower than full scans over the exported-then-imported tables, and that made a difference that was felt.

I'm mentioning this just to remind you that, unless you copy the database physically, you're introducing yet another difference into your test environment.
 
Joe Areeda
Ranch Hand
Posts: 332
2
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the link Martin,

I've wanted something to profile mysql for a while but didn't even think to look for it. D'Oh

Not very much gets deleted from this db but sometimes. I am aware of the optimization that mysqldump then restore can do and when this first happened I tried it on the production db with no discernible improvement.

Let see if I can turn on the Performance Schema Dobbs is talking about.

Best,
Joe
 
Joe Areeda
Ranch Hand
Posts: 332
2
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rats!

We're using mysql 5.1 and Performance_Schema didn't come in until 5.5

This stuff is run under Scientific Linux, a RedHat derivative than emphasizes stability and testing over latest versions. I can try to install a newer version on the prototype which doesn't have the problem and see if I can get a blessing from the PTB to at least temporarily try it on the production server.

I would like to see the disk and network stats it produces that would help a lot.

One thing I've learned is that the hypervisor system has 15 VMs running. I'm checking to see if memory or processors are over allocated. Some of the systems in this cluster could easily handle 15 copies of this VM.

Thank you all for the help.

Best,
Joe
 
Joe Areeda
Ranch Hand
Posts: 332
2
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to thank Jeanne and Martin for the helpful comments.

For the moment this seems to be resolved. As far as I can tell either changes to the hypervisor system or weekend slowness on the other VM help alot.

I'm working on a new and improved version of my code and it shows a great improvement. Nothing magical just following some of Bear's suggestions over the last year.

I still have a list of optimizations and ..uh.. standard practices to implement. In other words I'm still cleaning up mistakes I made two years ago when I started this project as a complete JavaEE noob.

Thanks again, the Ranch has been a great help. If I get to meet any of you in person the Moose Drool is on me.

Best,
Joe
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic