Home
MySQL and drop packet
Written by Marco Tusa   
Friday, 08 February 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}

Last Updated on Sunday, 18 August 2013 17:14
 
I don
Written by Marco Tusa   
Thursday, 24 January 2013 16:07

 

I don't ....
  • Believe in one God coming from One religion, instead I think we are part of it/him/her.
  • Trust money, money should be a tool to prevent going around with a cow to get what you want.
  • Trust banks, I give them my money but if I go there and ask for it they don't have it. Why?
  • Believe in friends, it is sad but seems to me that find a friend is like finding the pot at the end of the rainbow, mostly supposed friend just take advantage of you.
  • See any way out for my country, I would prefer to have a civil war then this slow unstoppable decline, I will be ready to come back and fight, but this is ... the slow death of a dead country.
  • Trust news papers, 99% are bullshits, whatever country you live in newspapers now are just the speakers of the power.
  • Think we are all equal, we are not, be different is good, the ones try to convince all of us about that are the ones that like to take advantage of you.
  • think being different means be better or worse, or good and bad, instead just different.
  • See how someone can trust the partner (or him/her/self) to exclude any possible external sex relation, relax and do it.
  • See why we should be scare of sex, sex is fun, sex is a good way to make experience, sex is better then a cinema show or a day at work, damn do it!
  • Think we should have parents 60 years old with newborn, is a matter of clock... when 20 - 80 are you going where with your daughter/son?
  • Like the people, in general.
  • Trust any government, instead I think that we should be so mature to drive our self, in respect of each other, with small circuit of interaction.
  • Like consumerism, and the model attach to it, ask more from the insight not from the outside, but this is a lost discussion today.
  • Think we are in the age of the Aquarius, unfortunately we are still in the era of the sharks.
  • Love can move mountains, but it can do much more then money.
  • Dream enough, not anymore, may be this is a sign of the age... when you have lost your last dream, it is time to die, sounds good enough for me.
  • Spend enough time with my family.
  • Show all my love to my kids as I should.
  • Get why we should pay for time and not for results, pay for the first do not imply I will get the second.
  • Trust doctors(med), well this is well known they are just mechanics that believe to be god, or something close to.
  • Close my eyes, or mind, and at any Earth revolution I still wonder how it could be possible we are so stupid.
  • Disagree with the statement that human being is a virus, or at least we act as such.
  • Know at what point in time we have lost the right path.
  • Like Mexican food.
  • Like to work with products, but on solutions cross products.
  • Like to work in the morning, my brain start-up after 3PM.
  • Like the ones that want to be your friend at work, 99% are double-dealers.
  • Like big corporation, on in particular.
  • Know why people get jobs without reading the job description, and then complain about their job, damn you stay at home!
  • Know why north Americans do not understand sarcasm, they loose 50% of what is happening around them.
  • Drink to get drunk, but because I want to enjoy what I drink, and I drink until I can enjoy it.
  • Discuss at all with people unless I give them credit.
  • Talk about philosophy or religion anymore, anyone think to have enough understanding about that, also if they have no idea of what they are talking about.
  • Have met the Dalai Lama yet... but I would really love to.
  • Read enough books, need more time to read and write.
  • Want to sell my time any longer.

{joscommentenable}

Last Updated on Sunday, 18 August 2013 16:37
 
«StartPrev12345678910NextEnd»

Page 10 of 16
 

Who's Online

We have 23 guests online