Looking back, it would have been a lot easier to change configuration, wait 30 minutes for it to propagate, replace the MySQL instance, change the configuration again, and wait 30 minutes for it to come back up. At the time, however, I thought of a simpler solution. I would use iptables to reject all traffic from web servers to this one database server. Once the connection was rejected, the failover would take place, and the replication would continue to work.
Not only did my plan not work, it caused about 5 minutes of downtime on our production servers.
`man iptables`
Straight from the manual:
ACCEPT means to let the packet through. DROP means to drop the packet on the floor.... [REJECT] is used to send back an error packet in response to the matched packet: otherwise it is equivalent to DROP so it is a terminating TARGET, ending rule traversal.So, ACCEPT was the state I was in. If I had gone to DROP, I would have expected a timeout from the application as it tried to connect to the "downed" database server. Since I didn't want to wait for a timeout of 1 second (PHP applications only have second granularity), I chose to use REJECT, which would send an error packet, just like the operating system would do if MySQL was not running.
Here is the set of rules (in order) I planned to apply to the database server.
- iptables -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
- iptables -A INPUT -m tcp -p tcp --dport 3306 --source <other db server ip>/32 -j ACCEPT
- iptables -A INPUT -m tcp -p tcp --dport 3306 -j REJECT
What this says, for anyone who doesn't read iptables:
- Allow all incoming packets whose TCP handshake is already completed. This rule should take care of transactions that are currently being processed. We don't use persistent connections in the application, so this should be fine.
- Allow all incoming MySQL traffic from the other database server.
- Reject, with an error packet, all incoming MySQL traffic from all other sources (including web servers).
Downtime
Armed with my iptables commands that I had reviewed by another person, so as to not negatively affect our production environment, I was ready to replace the server. Immediately after I ran the commands, I checked the website for connectivity. It was up and running, so I figured it was a success. A couple minutes later, I received a disaster notification from Zabbix - "Site Down".
I had already checked the website after I made the change, so I figured somebody else must have changed something, or we were getting hit with a big spike. Long story short, it was definitely the iptables commands that I ran. Once I figured it out, I flushed the iptables on the system, and waited a couple minutes for everything to chill out. Once it did, I immediately went about trying to find my misunderstanding of iptables.
Diagnostics
Looking at graphs, response times, and other goodies from Zabbix and New Relic, I found that the database connection was timing out after 1 second, and then going to the second server. This was fine for a couple minutes. After that small amount of time, the request pool had grown considerably, and all of our web servers started context switching and swapping. It was at this time that the site went down.The weird thing was the 1 second timeout. That's exactly what I was trying to avoid by doing the iptables tricks. That the error occurred anyway baffled me. I turn to code as a diagnostic tool.
<?php
while (true) {
echo "connecting...\n";
$db = new mysqli('127.0.0.1', 'user', 'password');
if (!$db->connect_errno && $db->ping()) {
echo "got it!\n";
mysqli_select_db($db, 'lucid');
$rs = mysqli_query($db, 'SELECT COUNT(1) as c FROM users');
$row = mysqli_fetch_assoc($rs);
echo $row['c'] . "\n";
mysqli_close($db);
}
sleep(1);
}
As the code ran, I tried various iptables rules on it. Whenever I did the following commands, I would experience a 1 second timeout, just like in production.
- sudo iptables -A INPUT -m tcp -p tcp --dport 3306 -j REJECT
- sudo iptables -A INPUT -m tcp -p tcp --dport 3306 -j REJECT --reject-with icmp-net-unreachable
- sudo iptables -A INPUT -m tcp -p tcp --dport 3306 -j REJECT --reject-with icmp-host-unreachable
- sudo iptables -A INPUT -m tcp -p tcp --dport 3306 -j REJECT --reject-with icmp-port-unreachable
- sudo iptables -A INPUT -m tcp -p tcp --dport 3306 -j REJECT --reject-with icmp-proto-unreachable
- sudo iptables -A INPUT -m tcp -p tcp --dport 3306 -j REJECT --reject-with icmp-net-prohibited
- sudo iptables -A INPUT -m tcp -p tcp --dport 3306 -j REJECT --reject-with icmp-host-prohibited
The error I got from the PHP code looked like this:
connecting...
<wait 1 second>
PHP Warning: mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on '127.0.0.1' (111) in /path/to/file.php on line 4
<wait 1 second>
connecting...
<wait 1 second>
PHP Warning: mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on '127.0.0.1' (111) in /path/to/file.php on line 4
<wait 1 second>
connecting...
<wait 1 second>
PHP Warning: mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on '127.0.0.1' (111) in /path/to/file.php on line 4
<wait 1 second>
So, apparently there is something in php5-mysql that thinks REJECT == DROP.
Solution
There are probably better ways of doing this. One way would have been to change configuration, let it propagate, and handle it later. Another way would have been to shut down the mysql port, start mysql on a different port, and update the replication information on the slave. There are dozens of other ways. I just happened to choose the one that worked the least and sucked the most.If you find yourself thinking that you know how REJECT should work, and running this is production, here is the solution. Use this REJECT command:
sudo iptables -A INPUT -m tcp -p tcp --dport 3306 -j REJECT --reject-with tcp-reset
That's it! Simple, yet frustrating because PHP shouldn't hang on a REJECT.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.