MySQL performance tuning

ramCount=`awk 'match($0,/vmguar/) {print $4}' /proc/user_beancounters`
ramBase=-16 && for ((;ramCount>1;ramBase++)); do ramCount=$((ramCount/2)); done

cat <<EOF > /etc/my.cnf
[mysqld]
# Basic settings
user = mysql
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# Security settings
local-infile = 0
symbolic-links = 0

# Memory and cache settings
query_cache_type = 1
query_cache_size = $((2**($ramBase+2)))M
thread_cache_size = $((2**($ramBase+2)))
table_cache = $((2**($ramBase+7)))
tmp_table_size = $((2**($ramBase+3)))M
max_heap_table_size = $((2**($ramBase+3)))M
join_buffer_size = ${ramBase}M
key_buffer_size = $((2**($ramBase+4)))M
max_connections = $((100 + (($ramBase-1) * 50)))
wait_timeout = 300

# Innodb settings
innodb_buffer_pool_size = $((2**($ramBase+3)))M
innodb_additional_mem_pool_size = ${ramBase}M
innodb_log_buffer_size = ${ramBase}M
innodb_thread_concurrency = $((2**$ramBase))

[mysqld_safe]
# Basic safe settings
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
EOF

When phpMyAdmin is installed, the default session timeout value is too low for many users, making your phpMyAdmin session expire too soon. One could argue that a low session timeout value is a good idea from a security perspective. If you do not think this is an issue, here are a few simple steps that’ll let you change how long phpMyAdmin will keep your session(s) alive.

Open config.inc.php in the phpMyAdmin “root” directory. Look for a line that contains this: $cfg[‘LoginCookieValidity’]. Set the value to the desired number of seconds you want the session to stay alive (3600 = one hour, which is reasonable for most users). If you do not have that line in your config.inc.php file, add it like this:

$cfg[‘LoginCookieValidity’] = 3600;

Don’t forget to save the file, and then login again to phpMyAdmin. You may need to close the browser and re-open your phpMyAdmin URL.

This also assumes that the PHP session garbage collection is set-up accordingly. This can be done in a number of ways:

• php.ini; add a line (or change an existing) that contains session.gc_maxlifetime = <seconds>
• Apache configuration; add a line to the appropriate <Directory> block that says “php_admin_value session.gc_maxlifetime <seconds>”
• config.inc.php (phpMyAdmin); after the previously edited line, add a line with “ini_set(‘session.gc_maxlifetime’, <seconds>);”

The <seconds> above is the same value that you set your variable to in config.inc.php at the beginning of this post, “3600” (sans quotes) in my case. (Some of these methods may or may not work on the server you’re using.)

This isn’t the only way to circumvent phpMyAdmin sessions expiring on you in the middle of that important work; you can, of course, configure phpMyAdmin to have appropriate access directly, thus allowing you to access your MySQL database(s) without entering a username and a password. You’ll find more information about this on the phpMyAdminwebsite.

Neat tricks with iptables: The past few months have seen me digging deep into the world of TCP/IP and firewalls. It has been a fascinating journey into packet queueing and TCP headers, three-way handshakes and ICMP broadcasts.

The result of this research has been the ongoing creation of a firewall to protect my laptop against open networks, and my Internet server from port scanning and DoS attacks. I’m pretty certain I haven’t even scratched the surface yet, but I have found some settings to protect against the most common attacks. Below I’ll summarize the major pieces of my new firewall, and the logic behind it.

Address spoofing: win with iptables

The easiest way to fool a server is to construct a packet that whose source address is faked, or spoofed. This is surprisingly easy to do. To craft packets, I use a very powerful network analysis tool called Scapy. Scapy will allow you to create packets on the fly, transmit them, and scan your network for any response.

For example, let’s say I’m on my local network (which I am right now, as I write this), connected via wireless as192.168.15.113. I’m going to interact with the router, which is at 192.168.15.1. For the purposes of analysis, I’ve also setup a virtual machine running on 192.168.15.114, so I can see what happens when I spoof the packet.

So, let’s say I spoof an ICMP echo-request packet, sent to .1 (router) from .113 (me) but spoofed as if it had come from .114 (virtual machine). In Scapy this is quite easy to do. I run two scapy session in two terminal windows. In the first I type:

>>> send(IP(src="192.168.15.114", dst="192.168.15.1")/ICMP())
.
Sent 1 packets.

Although my machine is at .113, I’m telling scapy to set the source address for the ICMP echo-request packet to.114, which is the host I want to attack. I’m sending this “ping” to the router, which should now send its response back to .114 instead of me.

In my other terminal window, I run scapy again, this time in promiscuous mode as a packet sniffer. Promiscuous mode means that it will capture all packets seen on the network, not just those destined for my own machine. Here’s what I see:

>>> sniff(filter="icmp")
^C
>>> _.show()
0000 Ether / IP / ICMP 192.168.15.114 > 192.168.15.1 echo-request 0
0001 Ether / IP / ICMP 192.168.15.1 > 192.168.15.114 echo-reply 0

I ran the sniffer, then did the ping, then stopped the sniffer by pressing Control-C. I can see that two ICMP packets were seen during the sniff. By showing the contents of these packets, I can see both the packet that I transmitted, and the response – which came back to .114!

That’s a spoof. How can it be used to attack someone? Read on in the next section, since what we just did forms the basis for a smurf attack.

Some packet spoofs, however, are more obvious. For example, a packet coming from the Internet bound for a private IP address or certain broadcast addresses, such as address beginning with 192.168 or 224. These are never valid, so it’s a good idea to drop such packets immediately upon receipt. Here are the iptables rules to do this:

# Reject packets from RFC1918 class networks (i.e., spoofed)
iptables -A INPUT -s 10.0.0.0/8     -j DROP
iptables -A INPUT -s 169.254.0.0/16 -j DROP
iptables -A INPUT -s 172.16.0.0/12  -j DROP
iptables -A INPUT -s 127.0.0.0/8    -j DROP

iptables -A INPUT -s 224.0.0.0/4      -j DROP
iptables -A INPUT -d 224.0.0.0/4      -j DROP
iptables -A INPUT -s 240.0.0.0/5      -j DROP
iptables -A INPUT -d 240.0.0.0/5      -j DROP
iptables -A INPUT -s 0.0.0.0/8        -j DROP
iptables -A INPUT -d 0.0.0.0/8        -j DROP
iptables -A INPUT -d 239.255.255.0/24 -j DROP
iptables -A INPUT -d 255.255.255.255  -j DROP

Here’s the same thing, now for ipfw users:

# Verify the reverse path to help avoid spoofed packets.  This means any
# packet coming from a particular interface must have an address matching the
# netmask for that interface.
ipfw add 100 deny all from any to any not verrevpath in

# Deny all inbound traffic from RFC1918 address spaces (spoof!)
ipfw add 110 deny all from 192.168.0.0/16 to any in
ipfw add 120 deny all from 172.16.0.0/12 to any in
ipfw add 130 deny all from 10.0.0.0/8 to any in
ipfw add 140 deny all from 127.0.0.0/8 to any in

ipfw add 150 deny all from 224.0.0.0/4 to any in
ipfw add 160 deny all from any to 224.0.0.0/4 in
ipfw add 170 deny all from 240.0.0.0/5 to any in
ipfw add 180 deny all from any to 240.0.0.0/5 in
ipfw add 190 deny all from 0.0.0.0/8 to any in
ipfw add 200 deny all from any to 0.0.0.0/8 in
ipfw add 210 deny all from any to 239.255.255.0/24 in
ipfw add 220 deny all from any to 255.255.255.255 in

Lire la suite…

Source: Per Linde, Martynas Pumputis and Guillermo Rodr ́ıguez iptables revisited: a not so ordinary ‘firewall’

iptables revisited: Abstract

At the present time, security on the internet, and networks in general have evolved, and become an issue that should not be disregarded. It is well known that many experts recommend Linux as the main operating system for the machines that have to be in charge of security (also for a desktop computer). Linux included a basic firewall tool called ipchains in the series of its kernel until 2.4 version, though after that version it switched to iptables. iptables is known for its efficiency and functionality, but the enormous functionality means a more complex tool to be configured. This paper will overview some mechanisms to do advanced configuration of iptables based on two main scenarios. The different configurations presented will try to prove the remarkable power of iptables as an independent firewall and also as a tool that can work in conjuntion with other tools usually incorporated when included this one.

Introduction

iptables is a firewall developed by the Netfilter Project 1 . Presently, this firewall is becoming more and more popular (both among end users and network administrators). The popularity of this firewall it closely related to Linux operating system, because iptables works with Linux kernels 2.4 and 2.6 and almost every major Linux distribution comes with pre-installed iptables firewall. This firewall is also known as a stateful packet filter. It is a main difference between iptables and ipchains (an ancestor of iptables that was used with Linux kernel versions up to 2.4). The firewall supports not only a packet filtering, but it is also able to log, forward packets and it could be used together with such tools as psad, snort, etc. In this paper we will look a bit deeper into more advance iptables configurations, but first of all, we would like to introduce a reader to a basic usage of iptables. The core of the firewall consists of four parts [7]: tables, chains, matches and targets. A system administrator is able to define an iptables policy, i.e. tables of chains, which describe how a kernel should react against different groups of packets. Rules are used to create a chain – collection of rules that is applied to every packet. There are five predefined chains:

• INPUT: used for incoming packets before routing.
• OUTPUT: used for packets coming into the box itself.
• FORWARD: used for packets being routed through the box.
• PREROUTING: used for locally-generated packets before routing.
• POSTROUTING: used for packets as they about to leave iptables.

Every rule should have a set of matches, that helps to filter packets (e.g. -d 10.100.1.1 matches a destination IP 10.100.1.1), and it also has to have a target – an action which should be performed when a rule matches on a packet (e.g. ACCEPT, DROP, LOG…). All the examples presented in this paper (excluding the one used at section 6) will be based on the scenario presented in the figure 1. In the following scenario, WebServer#1 (10.100.1.101 assigned to its ‘eth0’ interface), WebServer#2 (10.100.1.102 assigned to its ‘eth0’ interface) and PC (10.100.1.33 assigned to its ‘eth0’ interface) belong to a local network and every packet that comes from the Internet/LAN is filtered by the firewall (‘eth1’ interface for LAN’s traffic, ‘eth0’ for Internet’s traffic). PC (212.59.0.1 assigned to ‘eth0’ interface) is reachable directly through the Internet. The listing 1 shows a couple of rules, the first one appends a rule to the end of the INPUT chain and it specifies that every packet from the source with IP address 212.59.0.1 will beDROPed and the second rule logs all outgoing connections from eth1 interface.

iptables -A INPUT -s 212.59.0.1 -j DROP
iptables -A OUTPUT -o eth1 -j LOG

Lire la suite…

Last week during a casual conversation I overheard a colleague saying: « The Linux network stack is slow! You can’t expect it to do more than 50 thousand packets per second per core! »

That got me thinking. While I agree that 50kpps per core is probably the limit for any practical application, what is the Linux networking stack capable of? Let’s rephrase that to make it more fun:

On Linux, how hard is it to write a program that receives 1 million UDP packets per second?

Hopefully, answering this question will be a good lesson about the design of a modern networking stack.

First, let us assume:

• Measuring packets per second (pps) is much more interesting than measuring bytes per second (Bps). You can achieve high Bps by better pipelining and sending longer packets. Improving pps is much harder.
• Since we’re interested in pps, our experiments will use short UDP messages. To be precise: 32 bytes of UDP payload. That means 74 bytes on the Ethernet layer.
• For the experiments we will use two physical servers: « receiver » and « sender ».
• They both have two six core 2GHz Xeon processors. With hyperthreading (HT) enabled that counts to 24 processors on each box. The boxes have a multi-queue 10G network card by Solarflare, with 11 receive queues configured. More on that later.
• The source code of the test programs is available here: udpsender, udpreceiver.

Prerequisites

Let’s use port 4321 for our UDP packets. Before we start we must ensure the traffic won’t be interfered with by the iptables:

receiver$ iptables -I INPUT 1 -p udp --dport 4321 -j ACCEPT  
receiver$ iptables -t raw -I PREROUTING 1 -p udp --dport 4321 -j NOTRACK  

A couple of explicitly defined IP addresses will later become handy:

receiver$ for i in `seq 1 20`; do   
              ip addr add 192.168.254.$i/24 dev eth2; 
          done
sender$ ip addr add 192.168.254.30/24 dev eth3  

1. The naive approach

To start let’s do the simplest experiment. How many packets will be delivered for a naive send and receive?

The sender pseudo code:

fd = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)  
fd.bind(("0.0.0.0", 65400)) # select source port to reduce nondeterminism  
fd.connect(("192.168.254.1", 4321))  
while True:  
    fd.sendmmsg(["x00" * 32] * 1024)

While we could have used the usual send syscall, it wouldn’t be efficient. Context switches to the kernel have a cost and it is be better to avoid it. Fortunately a handy syscall was recently added to Linux: sendmmsg. It allows us to send many packets in one go. Let’s do 1,024 packets at once.

The receiver pseudo code:

fd = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)  
fd.bind(("0.0.0.0", 4321))  
while True:  
    packets = [None] * 1024
    fd.recvmmsg(packets, MSG_WAITFORONE)

Similarly, recvmmsg is a more efficient version of the common recv syscall.

Let’s try it out:

sender$ ./udpsender 192.168.254.1:4321  
receiver$ ./udpreceiver1 0.0.0.0:4321  
  0.352M pps  10.730MiB /  90.010Mb
  0.284M pps   8.655MiB /  72.603Mb
  0.262M pps   7.991MiB /  67.033Mb
  0.199M pps   6.081MiB /  51.013Mb
  0.195M pps   5.956MiB /  49.966Mb
  0.199M pps   6.060MiB /  50.836Mb
  0.200M pps   6.097MiB /  51.147Mb
  0.197M pps   6.021MiB /  50.509Mb

With the naive approach we can do between 197k and 350k pps. Not too bad. Unfortunately there is quite a bit of variability. It is caused by the kernel shuffling our programs between cores. Pinning the processes to CPUs will help:

sender$ taskset -c 1 ./udpsender 192.168.254.1:4321  
receiver$ taskset -c 1 ./udpreceiver1 0.0.0.0:4321  
  0.362M pps  11.058MiB /  92.760Mb
  0.374M pps  11.411MiB /  95.723Mb
  0.369M pps  11.252MiB /  94.389Mb
  0.370M pps  11.289MiB /  94.696Mb
  0.365M pps  11.152MiB /  93.552Mb
  0.360M pps  10.971MiB /  92.033Mb

Now, the kernel scheduler keeps the processes on the defined CPUs. This improves processor cache locality and makes the numbers more consistent, just what we wanted.

Lire la suite…