Weblog entry #18 for lykwydchykyn
See all weblog entries made by lykwydchykyn.
#18
Baffling MySQL issue after Lenny upgrade
Posted by lykwydchykyn on Wed 25 Feb 2009 at 15:09
Let me lay out the situation. I have a legacy app written in gtksharp that communicates directly to a mysql server. The users of the app are all on subnet A. The server is on subnet B. Most users are running this app in windows xp, but I run it in Ubuntu intrepid and another person runs it on Ubuntu Hardy.
After upgrading the server (Dell 2950) to Lenny and MySQL 5.051a, the program is unusably slow. When I say "slow", I don't mean "a little sluggish", I mean it takes literally 10 minutes for the program to load up, and 2-3 minutes to do even the smallest things in it. For all practical purposes it doesn't work.
However, I soon discovered that not everyone had this problem. So let me lay out the facts in this case as I have discovered them.
- It is slow on my computer, running Ubuntu Intrepid
- It is not slow on the Hardy heron computer
- Downgrading to the hardy version of mono/gtksharp did not fix it on my computer.
- It is slow on Windows XP running in virtualbox on my computer, running the exact same version of mono as the other (non-virtual) Windows XP boxes on Subnet A.
- It is slow on fresh installs of Lenny or Intrepid on subnet A.
- It is not slow on Lenny boxes on subnet B (can't put an intrepid box on subnet B).
- It is not slow on my Intrepid box if I change to a test server on Subnet A, also running Lenny and MySQL 5.051a with the same data dumped in.
- It is not slow when a Lenny box on Subnet B is using said test server on Subnet A.
EDIT: new info
- It is slow on a fresh install of Windows XP on subnet A.
- It is slow on a different Lenny server on subnet B for all scenarios that were slow pointing to the first server.
END EDIT
I have checked syslog, the mysql logs, and dmesg, but I don't see anything different being logged when a slow machine connects. I have run a packet sniffer on the server, and haven't seen any different traffic when a slow machine connects. I've run a program trace on the client app, and while I noticed a lot of socket exceptions being thrown, it was that way on the ones working correctly as well.
I don't realistically expect anyone to have an idea of how to fix this, but if you have any thoughts on the next thing to investigate, I'm all ears.
Comments on this Entry
I've been examining the packet sniffing data at the clients. One thing I notice about the slow client is that there is a delay in the traffic of EXACTLY 5 seconds (give or take < 50ms) at the same point in the packet sequence. It goes like this:
CLIENT: Request Query
SERVER: Response OK
CLIENT: Request Use Database
SERVER: Response OK
CLIENT: Request Prepare Statement
SERVER: Response
CLIENT: Request Execute Statement
SERVER: Response
CLIENT: tcp SYN
SERVER: tcp SYN ACK
CLIENT: tcp ACK
CLIENT: tcp ACK
*********5 second delay*************
SERVER: Server greeting
CLIENT: Request Query
SERVER: Response OK
CLIENT: Request Use Database
SERVER: Response OK
CLIENT: Request Prepare Statement
SERVER: Response
CLIENT: Request Execute Statement
SERVER: Response
CLIENT: tcp SYN
SERVER: tcp SYN ACK
CLIENT: tcp ACK
CLIENT: tcp ACK
*********5 second delay*************
SERVER: Server greeting
[ Parent | Reply to this comment ]
Posted by Anonymous (72.253.xx.xx) on Wed 25 Feb 2009 at 20:30
DNS lookup / lag, or related thing?
[ Parent | Reply to this comment ]
Shouldn't be; it's connecting via ip address
[ Parent | Reply to this comment ]
Posted by Anonymous (213.232.xx.xx) on Wed 25 Feb 2009 at 22:19
skip-name-resolve? I believe MySQL will still do the lookup if if you are using IPs - unless you explicitly disable it...
[ Parent | Reply to this comment ]
I don't know who you are, but I owe you a beer. Or the beverage of your choice.
I didn't know about skip-name-resolve (shows what an unworthy mysql admin I am), but after popping that into the config the problem goes away. Wow!
You just solved a day and a half of heartache for me. I cannot express how much you totally rock.
I didn't know about skip-name-resolve (shows what an unworthy mysql admin I am), but after popping that into the config the problem goes away. Wow!
You just solved a day and a half of heartache for me. I cannot express how much you totally rock.
[ Parent | Reply to this comment ]
Posted by Anonymous (72.253.xx.xx) on Wed 25 Feb 2009 at 22:47
I remember reading somewhere recently (and my experiences in the past pay testament to it):
If you see a baffling problem, it's surprising how often its down to DNS or host name resolving.
Tragically, despite how often I've seen such behaviour, I almost always without fail forget to double check such stuff first and instead investigate everything but until lightbulb moment strikes "I wonder if..."
If you see a baffling problem, it's surprising how often its down to DNS or host name resolving.
Tragically, despite how often I've seen such behaviour, I almost always without fail forget to double check such stuff first and instead investigate everything but until lightbulb moment strikes "I wonder if..."
[ Parent | Reply to this comment ]
I've confirmed by looking at a trace of the MySQL server, the slow clients are sending queries at a rate of 1 every 5 seconds. The fast clients send about 50 queries per second. The server is answering queries almost instantly, and the queries are identical in both cases.
[ Parent | Reply to this comment ]
Posted by Anonymous (72.253.xx.xx) on Wed 25 Feb 2009 at 20:57
Hesitate to suggest this, not knowing how busy the MySQL server is, but does anything interesting show up in a tcpdump on the server (filtered for a slow client) is it trying and failing to pass the messages on to the slow client?
[ Parent | Reply to this comment ]
The server isn't so busy as that; I did a tcpdump both on the server and the slow client. On either end, nothing happens during those 5 second breaks. I can't parse the packets well enough to understand if something is waiting and timing out, or if it's only sending a query once per five seconds. I don't think anything is waiting, but there aren't a bunch of packets going out from one end or the other during the wait. They're just waiting, doing nothing.
[ Parent | Reply to this comment ]