Připojení na MySQL občas timeoutuje

Michal2

Připojení na MySQL občas timeoutuje
« kdy: 27. 11. 2018, 14:44:58 »
Zdravim, objevil se mi z niceho nic (zadny update na jedne nebo druhe strane) zvlastni problem.

Mam server A u soyoustart(OVH) a server na pobocce B. Oboji to jsou proxmoxy a uvnitr maji LXC. Jeden z LXC na A (naveme ho A1) se pripojuje na mysql na jednom z LXC na B (B1). V jednom ze ~20 pripadu se mi stane, ze selze pripojeni
v interaktivnim pripade po zadani hesla
ERROR 2003 (HY000): Can't connect to MySQL server on 'B1' (110 "Connection timed out") 
nebo ekvivalent z php pri volani mysqli_connect

Pokud uz se pripojeni podari tak je okamzite a pak muzu prenest opakovane obrovskou tabulku a projde to bez zavahani.
ping z A1 na B1 se -s 1400 je zcela bez problemu
Veskera pripojeni na B1 po lokalni siti - bez problemu
Pripojeni mysql z A2, A3, ... na B1 - bez problemu

Restartoval jsem server A i B a sitovou infrastrukturu v siti B (u A to delat nemuzu ;-) ). Vse bez ucinku.

Ma nekdo nejaky napad co by se dalo vyzkouset? Jako docasne reseni tam mam workaround - pconnect - coz omezi pocet chyb
 
« Poslední změna: 27. 11. 2018, 15:03:01 od Petr Krčmář »


ByCzech

  • *****
  • 1 861
    • Zobrazit profil
    • E-mail
Re:Připojení na MySQL občas timeoutuje
« Odpověď #1 kdy: 27. 11. 2018, 15:42:08 »
Připojení je na DNS jméno nebo IP adresu?

Michal2

Re:Připojení na MySQL občas timeoutuje
« Odpověď #2 kdy: 27. 11. 2018, 16:28:10 »
IP

Michal2

Re:Připojení na MySQL občas timeoutuje
« Odpověď #3 kdy: 27. 11. 2018, 18:37:32 »
TCPdump. Nejdrive jsou tam 4 uspesne komunikace (connect a nejaky dotaz) pro srovnani a nakonec tam je connect, ktery selhal.

Kód: [Vybrat]
18:32:32.054427 IP A1.44996 > B1.mysql: Flags [S], seq 525635928, win 29200, options [mss 1460,sackOK,TS val 5159828 ecr 0,nop,wscale 7], length 0
18:32:32.096447 IP A1.44996 > B1.mysql: Flags [.], ack 2657033878, win 229, options [nop,nop,TS val 5159838 ecr 3784837], length 0
18:32:32.138149 IP A1.44996 > B1.mysql: Flags [.], ack 96, win 229, options [nop,nop,TS val 5159849 ecr 3784848], length 0
18:32:32.138181 IP A1.44996 > B1.mysql: Flags [P.], seq 0:115, ack 96, win 229, options [nop,nop,TS val 5159849 ecr 3784848], length 115
18:32:32.178511 IP A1.44996 > B1.mysql: Flags [P.], seq 115:134, ack 107, win 229, options [nop,nop,TS val 5159859 ecr 3784858], length 19
18:32:32.218807 IP A1.44996 > B1.mysql: Flags [P.], seq 134:161, ack 118, win 229, options [nop,nop,TS val 5159869 ecr 3784868], length 27
18:32:32.259235 IP A1.44996 > B1.mysql: Flags [P.], seq 161:203, ack 129, win 229, options [nop,nop,TS val 5159879 ecr 3784878], length 42
18:32:32.303402 IP A1.44996 > B1.mysql: Flags [P.], seq 203:255, ack 140, win 229, options [nop,nop,TS val 5159890 ecr 3784888], length 52
18:32:32.350630 IP A1.44996 > B1.mysql: Flags [P.], seq 255:297, ack 631, win 237, options [nop,nop,TS val 5159902 ecr 3784899], length 42
18:32:32.390999 IP A1.44996 > B1.mysql: Flags [P.], seq 297:399, ack 642, win 237, options [nop,nop,TS val 5159912 ecr 3784911], length 102
18:32:32.466867 IP A1.44996 > B1.mysql: Flags [P.], seq 399:404, ack 859, win 245, options [nop,nop,TS val 5159931 ecr 3784929], length 5
18:32:32.466876 IP A1.44996 > B1.mysql: Flags [F.], seq 404, ack 859, win 245, options [nop,nop,TS val 5159931 ecr 3784929], length 0
18:32:32.509807 IP A1.44996 > B1.mysql: Flags [.], ack 860, win 245, options [nop,nop,TS val 5159942 ecr 3784940], length 0
18:32:33.019743 IP A1.44998 > B1.mysql: Flags [S], seq 3522688197, win 29200, options [mss 1460,sackOK,TS val 5160069 ecr 0,nop,wscale 7], length 0
18:32:33.073767 IP A1.44998 > B1.mysql: Flags [.], ack 500025482, win 229, options [nop,nop,TS val 5160083 ecr 3785078], length 0
18:32:33.115421 IP A1.44998 > B1.mysql: Flags [.], ack 96, win 229, options [nop,nop,TS val 5160093 ecr 3785092], length 0
18:32:33.115480 IP A1.44998 > B1.mysql: Flags [P.], seq 0:107, ack 96, win 229, options [nop,nop,TS val 5160093 ecr 3785092], length 107
18:32:33.157066 IP A1.44998 > B1.mysql: Flags [P.], seq 107:131, ack 144, win 229, options [nop,nop,TS val 5160104 ecr 3785102], length 24
18:32:33.198873 IP A1.44998 > B1.mysql: Flags [P.], seq 131:144, ack 155, win 229, options [nop,nop,TS val 5160114 ecr 3785113], length 13
18:32:33.237024 IP A1.44998 > B1.mysql: Flags [P.], seq 144:165, ack 166, win 229, options [nop,nop,TS val 5160124 ecr 3785123], length 21
18:32:33.278816 IP A1.44998 > B1.mysql: Flags [P.], seq 165:235, ack 177, win 229, options [nop,nop,TS val 5160134 ecr 3785133], length 70
18:32:33.356855 IP A1.44998 > B1.mysql: Flags [.], ack 334, win 237, options [nop,nop,TS val 5160154 ecr 3785143], length 0
18:32:33.439872 IP A1.45000 > B1.mysql: Flags [S], seq 3747551534, win 29200, options [mss 1460,sackOK,TS val 5160174 ecr 0,nop,wscale 7], length 0
18:32:33.484742 IP A1.45000 > B1.mysql: Flags [.], ack 3422044538, win 229, options [nop,nop,TS val 5160185 ecr 3785184], length 0
18:32:33.528264 IP A1.45000 > B1.mysql: Flags [.], ack 96, win 229, options [nop,nop,TS val 5160196 ecr 3785195], length 0
18:32:33.528326 IP A1.45000 > B1.mysql: Flags [P.], seq 0:115, ack 96, win 229, options [nop,nop,TS val 5160196 ecr 3785195], length 115
18:32:33.572762 IP A1.45000 > B1.mysql: Flags [P.], seq 115:134, ack 107, win 229, options [nop,nop,TS val 5160207 ecr 3785205], length 19
18:32:33.614726 IP A1.45000 > B1.mysql: Flags [P.], seq 134:161, ack 118, win 229, options [nop,nop,TS val 5160218 ecr 3785216], length 27
18:32:33.657590 IP A1.45000 > B1.mysql: Flags [P.], seq 161:203, ack 129, win 229, options [nop,nop,TS val 5160229 ecr 3785227], length 42
18:32:33.699594 IP A1.45000 > B1.mysql: Flags [P.], seq 203:255, ack 140, win 229, options [nop,nop,TS val 5160239 ecr 3785238], length 52
18:32:33.758053 IP A1.45000 > B1.mysql: Flags [P.], seq 255:297, ack 631, win 237, options [nop,nop,TS val 5160254 ecr 3785248], length 42
18:32:33.798947 IP A1.45000 > B1.mysql: Flags [P.], seq 297:399, ack 642, win 237, options [nop,nop,TS val 5160264 ecr 3785263], length 102
18:32:33.874498 IP A1.45000 > B1.mysql: Flags [P.], seq 399:404, ack 859, win 245, options [nop,nop,TS val 5160283 ecr 3785281], length 5
18:32:33.874506 IP A1.45000 > B1.mysql: Flags [F.], seq 404, ack 859, win 245, options [nop,nop,TS val 5160283 ecr 3785281], length 0
18:32:33.917033 IP A1.45000 > B1.mysql: Flags [.], ack 860, win 245, options [nop,nop,TS val 5160294 ecr 3785292], length 0
18:32:35.002631 IP A1.45002 > B1.mysql: Flags [S], seq 2433683900, win 29200, options [mss 1460,sackOK,TS val 5160565 ecr 0,nop,wscale 7], length 0
18:32:35.044960 IP A1.45002 > B1.mysql: Flags [.], ack 576627475, win 229, options [nop,nop,TS val 5160576 ecr 3785574], length 0
18:32:35.085426 IP A1.45002 > B1.mysql: Flags [.], ack 96, win 229, options [nop,nop,TS val 5160586 ecr 3785585], length 0
18:32:35.085490 IP A1.45002 > B1.mysql: Flags [P.], seq 0:115, ack 96, win 229, options [nop,nop,TS val 5160586 ecr 3785585], length 115
18:32:35.133293 IP A1.45002 > B1.mysql: Flags [P.], seq 115:134, ack 107, win 229, options [nop,nop,TS val 5160598 ecr 3785595], length 19
18:32:35.172207 IP A1.45002 > B1.mysql: Flags [P.], seq 134:161, ack 118, win 229, options [nop,nop,TS val 5160607 ecr 3785607], length 27
18:32:35.210734 IP A1.45002 > B1.mysql: Flags [P.], seq 161:203, ack 129, win 229, options [nop,nop,TS val 5160617 ecr 3785616], length 42
18:32:35.250887 IP A1.45002 > B1.mysql: Flags [P.], seq 203:255, ack 140, win 229, options [nop,nop,TS val 5160627 ecr 3785626], length 52
18:32:35.294777 IP A1.45002 > B1.mysql: Flags [P.], seq 255:297, ack 631, win 237, options [nop,nop,TS val 5160638 ecr 3785636], length 42
18:32:35.332481 IP A1.45002 > B1.mysql: Flags [P.], seq 297:399, ack 642, win 237, options [nop,nop,TS val 5160647 ecr 3785647], length 102
18:32:35.408879 IP A1.45002 > B1.mysql: Flags [P.], seq 399:404, ack 859, win 245, options [nop,nop,TS val 5160667 ecr 3785665], length 5
18:32:35.408888 IP A1.45002 > B1.mysql: Flags [F.], seq 404, ack 859, win 245, options [nop,nop,TS val 5160667 ecr 3785665], length 0
18:32:35.450757 IP A1.45002 > B1.mysql: Flags [.], ack 860, win 245, options [nop,nop,TS val 5160677 ecr 3785675], length 0
18:32:36.380886 IP A1.45006 > B1.mysql: Flags [S], seq 2329598875, win 29200, options [mss 1460,sackOK,TS val 5160910 ecr 0,nop,wscale 7], length 0
18:32:36.423623 IP A1.45006 > B1.mysql: Flags [.], ack 4022924255, win 229, options [nop,nop,TS val 5160920 ecr 3785919], length 0
18:32:36.465420 IP A1.45006 > B1.mysql: Flags [.], ack 96, win 229, options [nop,nop,TS val 5160931 ecr 3785929], length 0
18:32:36.465473 IP A1.45006 > B1.mysql: Flags [P.], seq 0:115, ack 96, win 229, options [nop,nop,TS val 5160931 ecr 3785929], length 115
18:32:36.506353 IP A1.45006 > B1.mysql: Flags [P.], seq 115:134, ack 107, win 229, options [nop,nop,TS val 5160941 ecr 3785940], length 19
18:32:36.544553 IP A1.45006 > B1.mysql: Flags [P.], seq 134:161, ack 118, win 229, options [nop,nop,TS val 5160950 ecr 3785950], length 27
18:32:36.582681 IP A1.45006 > B1.mysql: Flags [P.], seq 161:203, ack 129, win 229, options [nop,nop,TS val 5160960 ecr 3785959], length 42
18:32:36.623254 IP A1.45006 > B1.mysql: Flags [P.], seq 203:255, ack 140, win 229, options [nop,nop,TS val 5160970 ecr 3785969], length 52
18:32:36.663819 IP A1.45006 > B1.mysql: Flags [P.], seq 255:297, ack 631, win 237, options [nop,nop,TS val 5160980 ecr 3785979], length 42
18:32:36.705873 IP A1.45006 > B1.mysql: Flags [P.], seq 297:399, ack 642, win 237, options [nop,nop,TS val 5160991 ecr 3785989], length 102
18:32:36.781130 IP A1.45006 > B1.mysql: Flags [P.], seq 399:404, ack 859, win 245, options [nop,nop,TS val 5161010 ecr 3786008], length 5
18:32:36.781139 IP A1.45006 > B1.mysql: Flags [F.], seq 404, ack 859, win 245, options [nop,nop,TS val 5161010 ecr 3786008], length 0
18:32:36.820909 IP A1.45006 > B1.mysql: Flags [.], ack 860, win 245, options [nop,nop,TS val 5161020 ecr 3786018], length 0
18:32:38.019339 IP A1.45008 > B1.mysql: Flags [S], seq 3052771682, win 29200, options [mss 1460,sackOK,TS val 5161319 ecr 0,nop,wscale 7], length 0
18:32:39.016887 IP A1.45008 > B1.mysql: Flags [S], seq 3052771682, win 29200, options [mss 1460,sackOK,TS val 5161569 ecr 0,nop,wscale 7], length 0
18:32:41.020876 IP A1.45008 > B1.mysql: Flags [S], seq 3052771682, win 29200, options [mss 1460,sackOK,TS val 5162070 ecr 0,nop,wscale 7], length 0
18:32:45.028854 IP A1.45008 > B1.mysql: Flags [S], seq 3052771682, win 29200, options [mss 1460,sackOK,TS val 5163072 ecr 0,nop,wscale 7], length 0
18:32:53.044888 IP A1.45008 > B1.mysql: Flags [S], seq 3052771682, win 29200, options [mss 1460,sackOK,TS val 5165076 ecr 0,nop,wscale 7], length 0
18:33:09.092885 IP A1.45008 > B1.mysql: Flags [S], seq 3052771682, win 29200, options [mss 1460,sackOK,TS val 5169088 ecr 0,nop,wscale 7], length 0

Michal2

Re:Připojení na MySQL občas timeoutuje
« Odpověď #4 kdy: 27. 11. 2018, 18:53:17 »
Znova a lepe - zacycena komunikace obema smery. Jeden uspesny a jeden neuspesny

Kód: [Vybrat]
18:48:22.014654 IP A1.45488 > B1.mysql: Flags [S], seq 3457560171, win 29200, options [mss 1460,sackOK,TS val 5397318 ecr 0,nop,wscale 7], length 0
18:48:22.055270 IP B1.mysql > A1.45488: Flags [S.], seq 767072133, ack 3457560172, win 28960, options [mss 1460,sackOK,TS val 4022327 ecr 5397318,nop,wscale 7], length 0
18:48:22.055302 IP A1.45488 > B1.mysql: Flags [.], ack 1, win 229, options [nop,nop,TS val 5397328 ecr 4022327], length 0
18:48:22.098384 IP B1.mysql > A1.45488: Flags [P.], seq 1:96, ack 1, win 227, options [nop,nop,TS val 4022338 ecr 5397328], length 95
18:48:22.098409 IP A1.45488 > B1.mysql: Flags [.], ack 96, win 229, options [nop,nop,TS val 5397339 ecr 4022338], length 0
18:48:22.098464 IP A1.45488 > B1.mysql: Flags [P.], seq 1:116, ack 96, win 229, options [nop,nop,TS val 5397339 ecr 4022338], length 115
18:48:22.138750 IP B1.mysql > A1.45488: Flags [.], ack 116, win 227, options [nop,nop,TS val 4022348 ecr 5397339], length 0
18:48:22.144327 IP B1.mysql > A1.45488: Flags [P.], seq 96:107, ack 116, win 227, options [nop,nop,TS val 4022348 ecr 5397339], length 11
18:48:22.144405 IP A1.45488 > B1.mysql: Flags [P.], seq 116:135, ack 107, win 229, options [nop,nop,TS val 5397350 ecr 4022348], length 19
18:48:22.184518 IP B1.mysql > A1.45488: Flags [P.], seq 107:118, ack 135, win 227, options [nop,nop,TS val 4022359 ecr 5397350], length 11
18:48:22.184590 IP A1.45488 > B1.mysql: Flags [P.], seq 135:162, ack 118, win 229, options [nop,nop,TS val 5397360 ecr 4022359], length 27
18:48:22.224358 IP B1.mysql > A1.45488: Flags [P.], seq 118:129, ack 162, win 227, options [nop,nop,TS val 4022369 ecr 5397360], length 11
18:48:22.224509 IP A1.45488 > B1.mysql: Flags [P.], seq 162:204, ack 129, win 229, options [nop,nop,TS val 5397370 ecr 4022369], length 42
18:48:22.263130 IP B1.mysql > A1.45488: Flags [P.], seq 129:140, ack 204, win 227, options [nop,nop,TS val 4022380 ecr 5397370], length 11
18:48:22.263357 IP A1.45488 > B1.mysql: Flags [P.], seq 204:256, ack 140, win 229, options [nop,nop,TS val 5397380 ecr 4022380], length 52
18:48:22.303992 IP B1.mysql > A1.45488: Flags [P.], seq 140:631, ack 256, win 227, options [nop,nop,TS val 4022389 ecr 5397380], length 491
18:48:22.307197 IP A1.45488 > B1.mysql: Flags [P.], seq 256:298, ack 631, win 237, options [nop,nop,TS val 5397391 ecr 4022389], length 42
18:48:22.348300 IP B1.mysql > A1.45488: Flags [P.], seq 631:642, ack 298, win 227, options [nop,nop,TS val 4022400 ecr 5397391], length 11
18:48:22.348449 IP A1.45488 > B1.mysql: Flags [P.], seq 298:400, ack 642, win 237, options [nop,nop,TS val 5397401 ecr 4022400], length 102
18:48:22.421556 IP B1.mysql > A1.45488: Flags [P.], seq 642:859, ack 400, win 227, options [nop,nop,TS val 4022419 ecr 5397401], length 217
18:48:22.425044 IP A1.45488 > B1.mysql: Flags [P.], seq 400:405, ack 859, win 245, options [nop,nop,TS val 5397421 ecr 4022419], length 5
18:48:22.425056 IP A1.45488 > B1.mysql: Flags [F.], seq 405, ack 859, win 245, options [nop,nop,TS val 5397421 ecr 4022419], length 0
18:48:22.465009 IP B1.mysql > A1.45488: Flags [F.], seq 859, ack 405, win 227, options [nop,nop,TS val 4022430 ecr 5397421], length 0
18:48:22.465035 IP A1.45488 > B1.mysql: Flags [.], ack 860, win 245, options [nop,nop,TS val 5397431 ecr 4022430], length 0
18:48:22.468904 IP B1.mysql > A1.45488: Flags [.], ack 406, win 227, options [nop,nop,TS val 4022430 ecr 5397421], length 0
18:48:23.192754 IP A1.45490 > B1.mysql: Flags [S], seq 2775958484, win 29200, options [mss 1460,sackOK,TS val 5397612 ecr 0,nop,wscale 7], length 0
18:48:24.188889 IP A1.45490 > B1.mysql: Flags [S], seq 2775958484, win 29200, options [mss 1460,sackOK,TS val 5397862 ecr 0,nop,wscale 7], length 0
18:48:26.192880 IP A1.45490 > B1.mysql: Flags [S], seq 2775958484, win 29200, options [mss 1460,sackOK,TS val 5398363 ecr 0,nop,wscale 7], length 0
18:48:30.196883 IP A1.45490 > B1.mysql: Flags [S], seq 2775958484, win 29200, options [mss 1460,sackOK,TS val 5399364 ecr 0,nop,wscale 7], length 0
18:48:38.212891 IP A1.45490 > B1.mysql: Flags [S], seq 2775958484, win 29200, options [mss 1460,sackOK,TS val 5401368 ecr 0,nop,wscale 7], length 0
18:48:54.244888 IP A1.45490 > B1.mysql: Flags [S], seq 2775958484, win 29200, options [mss 1460,sackOK,TS val 5405376 ecr 0,nop,wscale 7], length 0
               


Michal2

Re:Připojení na MySQL občas timeoutuje
« Odpověď #5 kdy: 27. 11. 2018, 20:21:08 »
Jeste jsem zkousel na A1 udelat
telnet B1 3306
a take to po nekolika uspesnych pokusech
telnet: Unable to connect to remote host: Connection timed out

Pro jistotu jsem totez udelal mnohokrat i z A2 a bez problemu

uz fakt nevim

Michal2

Re:Připojení na MySQL občas timeoutuje
« Odpověď #6 kdy: 28. 11. 2018, 10:22:36 »
Jak se z niceho nic problem objevil tak i zmizel... Doufam, ze definitivne. Uz chapu proc maji admini zasadu nicemu se nedivit. Jinak by z udivu ani nevysli ;-)

Cek

Re:Připojení na MySQL občas timeoutuje
« Odpověď #7 kdy: 28. 11. 2018, 10:30:59 »
Muj odhad je sitovani v hostingu, obcas to maji dost prasatkove.....ale co presne, tezko rict.

ByCzech

  • *****
  • 1 861
    • Zobrazit profil
    • E-mail
Re:Připojení na MySQL občas timeoutuje
« Odpověď #8 kdy: 28. 11. 2018, 10:32:33 »
Muj odhad je sitovani v hostingu, obcas to maji dost prasatkove.....ale co presne, tezko rict.

Viděl bych to úplně stejně. Spojení mezi těmi 2 hosty prostě nebylo/není v pořádku.

Štefan

Re:Připojení na MySQL občas timeoutuje
« Odpověď #9 kdy: 28. 11. 2018, 12:19:26 »
Muj odhad je sitovani v hostingu, obcas to maji dost prasatkove.....ale co presne, tezko rict.
Měl jsem kdysi dávno podobný problem. Zjistil jsem, že je to ciscem.. nechávalo otevřené spojení na db místo jeho korektního uzavření (chyba fw), takže každé nové připojení vyžralo novou konekšnu, tím na db server došli a další jednoduše odmítal.

dustin

Re:Připojení na MySQL občas timeoutuje
« Odpověď #10 kdy: 28. 11. 2018, 13:14:30 »
Dost často se mi stává, že při ssh na vzdálený server dostanu obratem hlášku, že se připojení nepodařilo. Na druhý třetí pokus se připojí v pořádku. Nemůže být problém podobný, pokud by se do mysql jelo přes SSL?

Re:Připojení na MySQL občas timeoutuje
« Odpověď #11 kdy: 28. 11. 2018, 23:11:51 »
Jakou mas retry strategy? Snizit timeout na connection a pak zkusit 3 s exponencialnim backoff. https://aws.amazon.com/blogs/architecture/exponential-backoff-and-jitter/