Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a MySQL Server operating just fine if I access him from his local LAN (192.168.100.0/24). If I try to access hin from another LAN (192.168.113.0/24 in this case) it hangs for a really long time before delivering the result. SHOW PROCESSLIST; shows this process in Sleep, State empty.

If I strace -p this process I get the following Output (23512 is the PID of the corresponding mysqld process):

Process 23512 attached - interrupt to quit
restart_syscall(<... resuming interrupted call ...>) = 1
fcntl(10, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
accept(10, {sa_family=AF_INET, sin_port=htons(51696), sin_addr=inet_addr("192.168.113.4")}, [16]) = 33
fcntl(10, F_SETFL, O_RDWR)              = 0
rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, ) = 0
getpeername(33, {sa_family=AF_INET, sin_port=htons(51696), sin_addr=inet_addr("192.168.113.4")}, [16]) = 0
getsockname(33, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
open("/etc/hosts.allow", O_RDONLY)      = 64
fstat(64, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(64, "# /etc/hosts.allow: list of host"..., 4096) = 580
read(64, "", 4096)                      = 0
close(64)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
open("/etc/hosts.deny", O_RDONLY)       = 64
fstat(64, {st_mode=S_IFREG|0644, st_size=880, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(64, "# /etc/hosts.deny: list of hosts"..., 4096) = 880
read(64, "", 4096)                      = 0
close(64)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
getsockname(33, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
fcntl(33, F_SETFL, O_RDONLY)            = 0
fcntl(33, F_GETFL)                      = 0x2 (flags O_RDWR)
setsockopt(33, SOL_SOCKET, SO_RCVTIMEO, "\36\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
setsockopt(33, SOL_SOCKET, SO_SNDTIMEO, "<\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
fcntl(33, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
setsockopt(33, SOL_IP, IP_TOS, [8], 4)  = 0
setsockopt(33, SOL_TCP, TCP_NODELAY, [1], 4) = 0
futex(0x7f9cea5c9564, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f9cea5c9560, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x7f9cea5c6fe0, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=10, events=POLLIN}, {fd=12, events=POLLIN}], 2, -1) = 1 ([{fd=10, revents=POLLIN}])
fcntl(10, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
accept(10, {sa_family=AF_INET, sin_port=htons(51697), sin_addr=inet_addr("192.168.113.4")}, [16]) = 31
fcntl(10, F_SETFL, O_RDWR)              = 0
rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, ) = 0
getpeername(31, {sa_family=AF_INET, sin_port=htons(51697), sin_addr=inet_addr("192.168.113.4")}, [16]) = 0
getsockname(31, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
open("/etc/hosts.allow", O_RDONLY)      = 33
fstat(33, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(33, "# /etc/hosts.allow: list of host"..., 4096) = 580
read(33, "", 4096)                      = 0
close(33)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
open("/etc/hosts.deny", O_RDONLY)       = 33
fstat(33, {st_mode=S_IFREG|0644, st_size=880, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(33, "# /etc/hosts.deny: list of hosts"..., 4096) = 880
read(33, "", 4096)                      = 0
close(33)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
getsockname(31, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
fcntl(31, F_SETFL, O_RDONLY)            = 0
fcntl(31, F_GETFL)                      = 0x2 (flags O_RDWR)
setsockopt(31, SOL_SOCKET, SO_RCVTIMEO, "\36\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
setsockopt(31, SOL_SOCKET, SO_SNDTIMEO, "<\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
fcntl(31, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
setsockopt(31, SOL_IP, IP_TOS, [8], 4)  = 0
setsockopt(31, SOL_TCP, TCP_NODELAY, [1], 4) = 0
futex(0x7f9cea5c9564, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f9cea5c9560, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x7f9cea5c6fe0, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=10, events=POLLIN}, {fd=12, events=POLLIN}], 2, -1^C <unfinished ...>
Process 23512 detached

This output repeats itself until the answer gets send. It could take up to 15 Minutes until the request gets served. In the local LAN its a matter of Milliseconds.

Why is this and how can I debug this further?

[Edit] tcpdump shows a ton of this:

14:49:44.103107 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [S.], seq 1434117703, ack 1793610733, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 0
14:49:44.135187 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 106:145, ack 182, win 4345, length 39
14:49:44.135293 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [P.], seq 182:293, ack 145, win 115, length 111
14:49:44.167025 IP 192.168.X.6.64624 > cassandra-test.mysql: Flags [.], ack 444, win 4280, length 0
14:49:44.168933 IP 192.168.X.6.64626 > cassandra-test.mysql: Flags [.], ack 1, win 4390, length 0
14:49:44.169088 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [P.], seq 1:89, ack 1, win 115, length 88
14:49:44.169672 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 145:171, ack 293, win 4317, length 26
14:49:44.169726 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [P.], seq 293:419, ack 171, win 115, length 126
14:49:44.275111 IP 192.168.X.6.64626 > cassandra-test.mysql: Flags [P.], seq 1:74, ack 89, win 4368, length 73
14:49:44.275131 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [.], ack 74, win 115, length 0
14:49:44.275149 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 171:180, ack 419, win 4286, length 9
14:49:44.275189 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [P.], seq 89:100, ack 74, win 115, length 11
14:49:44.275264 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 180:185, ack 419, win 4286, length 5
14:49:44.275281 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [.], ack 185, win 115, length 0
14:49:44.275295 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [F.], seq 419, ack 185, win 115, length 0
14:49:44.275650 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [F.], seq 185, ack 419, win 4286, length 0
14:49:44.275660 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [.], ack 186, win 115, length 0
14:49:44.275910 IP 192.168.X.6.64627 > cassandra-test.mysql: Flags [S], seq 2336421549, win 8192, options [mss 1351,nop,wscale 2,nop,nop,sackOK], length 0
14:49:44.275921 IP cassandra-test.mysql > 192.168.X.6.64627: Flags [S.], seq 3289359778, ack 2336421550, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 0
share|improve this question
    
Is there systemc alls that are taking a long times ? (lines in strace that stay as last line for a few seconds or more) or is this trace repeating fast in a loop ? –  Kwaio Oct 24 '13 at 12:14
    
repeating fast in a loop. Seems to hang on for a few milliseconds every now and than but its impossible to figure out when. –  Subito Oct 24 '13 at 12:30
    
updated question with tcpdump output. strace -f -p doesn't show anything more than strace -p –  Subito Oct 24 '13 at 12:54
    
Hum... since it's doing network communication, maybe a tcpdump of those conversations would help (filter on hosts and ports) –  Kwaio Oct 24 '13 at 12:54
    
What kind of latency/packet loss are you seeing between the two hosts that are on different networks? What kind of networking hardware translates between the two networks? –  devicenull Oct 24 '13 at 13:07
show 16 more comments

3 Answers

On tcpdump picture one can see connection pooll and/or frequent restarting connections. Check if maximal connection limit is not reached.

share|improve this answer
    
Its not reached. If I execute the same app on a host in the same LAN, it gets served immediatly. –  Subito Oct 25 '13 at 12:06
    
What application do you use? Does it use connection pool? –  Veniamin Oct 26 '13 at 8:53
    
ahh like if casandra is sharded, it may not support cross network nodes, due to broadcast traffic? –  nandoP Oct 26 '13 at 23:24
    
my application is one I'm currently writing. Does not use connection pool but only has 1-3 Connections open at a time. –  Subito Oct 27 '13 at 14:24
    
@Subito 1. Try to connect with standard mysql client. 2. How do your mysql access rules look like? –  Veniamin Oct 28 '13 at 9:50
show 2 more comments

Subito,

Have you tried to add skip-name-resolve in your /etc/my.cnf file?

[mysqld]
skip-name-resolve
share|improve this answer
    
I did so just now. No success. It seems to correlate with the size of the resultset - smaller resultsets (~10 rows) take approx. a minute while bigger (~100 rows) take easily 10-15 minutes –  Subito Oct 24 '13 at 12:10
    
what about network load? –  dr-evil Oct 24 '13 at 12:15
    
nearly 0. Just two hosts accessing this DB-Server –  Subito Oct 24 '13 at 12:29
add comment

so at first i was thinking you conf'ed mysql to only allow the local subnet (bind 192.168.100.0/24). but this would result in immediate tcp/3306 reject respone ("connection dropped stop hacking"). because there is a prolonged timeout before it dies, perhaps this is a linux "deny" rule, if you have a linux iptables router between two subnets, ("deny" is not always/ever vendor specific syntax)

EDIT: oh wait... just saw connection is successful after 15 minutes..oops

EDIT: it may not hurt to look at network layer. is there another router that can be used between the two subnets for testing?

share|improve this answer
    
I'll try that. I actually have another Router at hand and will try to access the Server via the other one. –  Subito Oct 27 '13 at 14:25
    
Doesn't change a thing. Same problem. Used another VPN and another Router. –  Subito Oct 30 '13 at 9:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.