60 second binary options free demo dominator sitemap
australian binary option brokers handeln sitemap
60 second binary options bot uk sitemap
best binary options broker to trade gold sitemap
become a binary option trading real broker sitemap
10 minute plus500 binary options trading system sitemap
30 second binary options citizens sitemap
best binary options signalsbest option signals april 25 2014 sitemap
60 second binary option scam analysis sitemap
best binary options signal provider app sitemap
24 binary options win trading sitemap
1 minute top 10 binary option brokers trading sitemap
99 binary options helper sitemap
180 wins how binary options brokers make money sitemap
4 hour binary options no deposit demo accounts sitemap
auto binary options review international sitemap
best binary option signal software sitemap
180 wins binary options magnet free sitemap
60 seconds binary option demo accou sitemap
5 point binary options trading cheat sitemap
advantages of 60 sec binary options trading sitemap
0 100 franco binary options signals sitemap
888 most popular binary options broker sitemap
anyoption binary options brokers starting at 100 sitemap
60 seconds binary option youtube sitemap
advantages of binary option queen sitemap
5 point binary option system 81 trading sitemap
24 trade binary options for india sitemap
basics of binary options italia sitemap
0 100 binary options school sitemap
best binary options trading method ea sitemap
60 second binary option minimum deposit blogs sitemap
automatic trade binary options 30 seconds strategy sitemap
best binary option websites free bonus sitemap
best binary options 2015 trading system striker9 sitemap
best binary options trades ever 101 pdf sitemap
1 minute binary options daily cherry coke system sitemap
1 binary options secret sitemap
become a tools to trade binary option broker sitemap
automated trading binary options sitemap
1 minute automated binary option robot trading sitemap
best binary options books comments sitemap
best binary option forum for beginners trading platforms sitemap
10 minute binary option strategy indicator v1 0 sitemap
alpari binary options platform trading hours sitemap
auto binary option signals sitemap
1 minute binary option online charts trading sitemap
100 win binary options strike price sitemap
are binary options legal in the uk ipad sitemap
60 second binary option trading hours system sitemap
auto binary signals main abs video 2 live trading november 4th 2014 sitemap
assaxin 8 cheapest binary options deposit sitemap
60 second binary options trading signals usa clients sitemap
1 minute auto trader for binary option strategy sitemap
2 minute binary options no deposit bonus october 2015 sitemap
best binary options managed accounts sitemap
4xp binary options winning formula sitemap
10 minute binary options system yeast symptoms strategy sitemap
3 essential binary options tips for investors sitemap
5 decimal base binary options on mt4 sitemap
assaxin 8 etx capital binary options review trading system sitemap
24 trade how to win in binary options live chat sitemap
best binary options strategy for beginners sitemap
1 binary options expert signals sitemap
60 seconds binary options free demo methods sitemap
best binary option vs forex sitemap
best binary options system for 2015 mt4 sitemap
anyoption binary options broker minimum trade sitemap
1 ranked binary options price broker sitemap
autobinarysignals 1 binary option software 3 realty sitemap
80 rsi extremums binary options strategy sitemap
apexinvesting binary options sign up bonus sitemap
anyoption hedging with binary options sitemap
are there any legit binary options trading platforms sitemap
24 redwood binary options location scam sitemap
5 minute binary options trading income secrets brokers sitemap
15 min binary option strategy newsletters sitemap
arbitrage in 24 hr binary options trades sitemap
alpari uk binary options trading platform demo account sitemap
alpari co uk binary options indicator that works sitemap
4xp binary options gambling commission review sitemap
best 60 second binary options strategy broker usa sitemap
100 win binary option system 1 minute strategy sitemap
best accurate binary options trading indicato sitemap
best binary options indicator review option system sitemap
60 minutes binary option strategy di indonesia sitemap
best binary option site for 5 min dow jones sitemap
60 seconds binary options minimum deposit is it legit sitemap
24 binary options no deposit bonus october 2015 trading sitemap
are you trading binary options sitemap
5 minutes binary options strategy metatrader 4 sitemap
best binary options signal software newsletter sitemap
5 minutes binary option strategies system sitemap
assaxin 8 gt binary options sitemap
auto binary options review sitemap
4xp binary option 10 minutes sitemap
29 forex binary options system downloads sitemap
best binary option strategies diagram ea sitemap
best binary options brokers for us traders 100 bonus sitemap
777 how do binary options broker make money review sitemap
60 second binary options trading hou sitemap
2 hour binary options system reviews sitemap
arbitrage binary option methods mol biol sitemap
3 keywords for binary options trading strategies beginners sitemap
affiliate binary options review 2015 sitemap
777 high yield touch binary option sitemap
60 second binary options brokers exchanges sitemap
247 binary option trading youtube sitemap
affiliates leader binary options affiliates sitemap
60s binary option extreme nemesis system 89 sitemap
alpari uk binary options 2 sitemap
5 dollar binary option tricks sitemap
best binary option books sitemap
15 minute binary option trading nedir sitemap
4xp binary options pdf sitemap
a little gold binary options trading sitemap
a guide to binary options trading in qatar sitemap
100 win what are binary options 2015 sitemap
60 sec binary options buddy manual pdf sitemap
best binary option trading systems watchdog sitemap
15 minute cedar finance binary options platform sitemap
5 minute binary options pro signals performance system sitemap
amazon making money using binary options sitemap
1 reading binary options charts broker sitemap
60s binary option websites system sitemap
5 minute can you become a millionaire trading binary options system sitemap
best binary option system 2015 glossary sitemap
banker 11 pro index binary options syste v1 sitemap
are binary options taxable in uk assaxin 8 sitemap
at number 7 is binary options broker sitemap
30 minute binary option strategy system 90 sitemap
advantages of binary options for investors with small portfolios sitemap
all freelancers feedback at least 4 binary options writing sitemap
15 minutes binary options strategy forum sitemap
alpari us can you make a living on binary options sitemap
24 trade binary options laws sitemap
basics of nifty option trading sitemap
 
 
Home MySQL Blogs MySQL and drop packet
MySQL and drop packet PDF Stampa E-mail
Scritto da Marco Tusa   
Venerdì 08 Febbraio 2013 03:04

 

Overview

Last night a customer call us because was having issue on the server and loss of performance on the MySQL server.
When I join the bridge I ask to the customer a quick report of what was his experience and concern so far.

 

Luckily all the participants were technically skilled and team was compose by, SAs, DBAs, Team leader, so I was able to have a good overview in short time.
Mainly there were two fronts, one was the problem on the server in the network layer, the other in MySQL that was not able to manage efficiently the number of thread opening requests.

The machine has a single NIC, Storage attach by fibre channel, 8CPU hyper threading, 64GB RAM and finally heavy usage of NFS.

The same server was in the past using the volumes on the NFS also for MySQL, but now everything was moved to the attached storage.

 

As said the issue was that NIC was reporting drop packet wand MySQL was having issue to manage the number of threads, the last ones were between 200 - to 1000 connection requests.
As active threads the server was managing 200-300 threads, which was not enough.

 

I start reviewing the server and NIC issue, talking with the SAs they report that the NIC Receive buffer, was already set to maximum of 4096k.

 

So starting the investigation from there I review the back_log net.ipv4.tcp_max_syn_backlog, and all the other parameters related to TCP buffer:
1
2
3
4
5
6
7
8
9
CURRENT TCP buffer setting 
------------------------------
net.ipv4.tcp_mtu_probing = 0
net.core.rmem_max = 131071
net.core.wmem_max = 131071
net.ipv4.tcp_rmem = 4096    87380    4194304
net.ipv4.tcp_wmem = 4096    16384    4194304
------------------------------
 

 

The settings were misconfigured given that the tcp value cannot override the core values.


As such the settings for the tcp auto tuning were invalid for the max limit.

 

Given those values were not correct for a machine supporting high traffic I suggest:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Suggested TCP buffer settings
------------------------------
#TCP max buffer size
net.core.rmem_max = 16777216 
net.core.wmem_max = 16777216 
 
#Linux auto-tuning TCP buffer 
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
 
#length of the processor input queue
net.core.netdev_max_backlog = 30000
 
#default congestion control is htcp 
net.ipv4.tcp_congestion_control=htcp
 

 

About htcp see the reference to the document explaining in details the algorithm.
From the mysql point, I review few parameters that would have a direct relation with the Threads.
1
2
3
4
5
6
7
MySQL change
-----------------------------------------------
back_log                        = 1024
thread_cache_size               = 512
thread_stack                    = 256K
wait_timeout                    = 600
 

 

I decide to set value of backlog as the maximum queue we have seen, move the value of the thread_cache_size from 8 to 1/4 of the max number of connection,
then few correction given wait_timout was set as default and thread_stack was set as for 32bit machines.

 

When we apply the values, I was expecting to see the drop packet issue solve, instead MySQL was managing much better the incoming connection, but the drop packet were still there.
Also the bad network behaviour, was preventing to the data to flow as it was suppose to be.

 

We then focus on why this was happening reviewing all the changes applied.

 

After few investigations and researches the customer, realize that the value for the receive window on the NIC, was not really applied,
because also if it is declared as dynamic value by Cisco, the facto it require a reboot of the machines.

 

We reboot it, and the NIC was now working properly. Data was floating fine with no drop packet.
MySQL was managing the incoming thread efficiently, but I notice, after the server warm-up, that the performance were still not optimal.

 

So doing some other tuning, I set thread_cache_size to 1024 paring the back_log number,
at this point MySQL was managing the incoming new request very efficiently, we had a jump to of Threads_cached to 850 with obvious flotation between 8 threads up to the maximum,
Threads_created the same and then just a bit larger then the maximum number of created connections, and finally Threads_running jump from 200 - 300 to 600 - 700.

 

Conclusion

The drop packet is a sign of insufficient buffer, either from the NIC or the TCP, remember to review the parameters related.
"Surprising" also if the thread creation process was alway declare as "easy" and "light" a more aggressive thread settings makes MySQL act more properly.

Reference

{joscommentenable}

Commenti (0)
Solo gli utenti registrati possono inviare commenti!

!joomlacomment 4.0 Copyright (C) 2009 Compojoom.com . All rights reserved."

Ultimo aggiornamento Domenica 18 Agosto 2013 17:14
 
 

Who's Online

 21 visitatori online