Category Archives: System Administration

Enabling ARCHIVE storage engine in IUS MySQL 5.1

IUS is great repo which allows seamless integration of MySQL 5.1 and Python 2.6 into CentOS systems (which have 5.0 and 2.4 versions). The only issue is that if you run ‘SHOW ENGINES’ it will only show you MRG_MYISAM, CSV, FEDERATED, InnoDB, MEMORY, MyISAM engines. I wanted to experiment with ARCHIVE storage engine for storing raw input BI events, which is basically JSON. ARCHIVE engine seems to be a good hit for this – it supports compression (of our highly redundant data) and auto increment, which is necessary to implement queue-like processing, how it goes should be a topic for the separate post. So I was puzzled when I didn’t see archive storage engine in MySQL by IUS. Initial googling only suggested that ARCHIVE storage engine is enabled at compile time, which was pretty sad and I couldn’t understand why on the Earth had they omitted it. Later I found this post suggesting that ARCHIVE storage engine can be installed as plugin, and I need to install separate YUM packages. Finding for those packages in the current repo gave no results. So I finally found this bugreport revealing taht a few plugins are actually installed as a part of mysql51-server package and you only need to enable it! So I went a head and

mysql> INSTALL PLUGIN archive SONAME '';
Query OK, 0 rows affected (0.02 sec)

And then

mysql> show engines \G

*************************** 7. row ***************************
Support: YES
Comment: Archive storage engine
Transactions: NO
Savepoints: NO
7 rows in set (0.00 sec)


The following plugins are installed into the OS but not into MySQL: - ARCHIVE - BLACKHOLE - EXAMPLE - InnoDB Plugin


Update: Experiment on 24M rows shows 11X compression ratio! From 437 bytes/row in InnoDB (no indexes) down to 38 bytes per row.

SSH: Convert OpenSSH to SSH2 and vise versa (via //

SSH2 format is used by windows PuTTY. If you need to migrate your SSH keys from windows to linux/ mac os, then this article is useful.

Connecting two server running different type of SSH can be nightmare if you does not know how to convert the key. In this tutorial, I will try to explain on how to convert the public key from OpenSSH to SSH2 and SSH2 to OpenSSH. To convert the key, it must be done in OpenSSH server. Convert OpenSSH key to SSH2 key Run the OpenSSH version of ssh-keygen on your OpenSSH public key to convert it into the format needed by SSH2 on the remote machine. T … Read More

via //

Tuning Linux firewall connection tracker ip_conntrack

If your Linux server should handle lots of connections, you can get into the problem with ip_conntrack iptables module. It limits number of simultaneous connections your system can have. Default value (in CentOS and most other distros) is 65536.

To check how many entries in the conntrack table are occupied at the moment:

cat /proc/sys/net/ipv4/netfilter/ip_conntrack_count

Or you can dump whole table :

cat /proc/net/ip_conntrack

Conntrack table is hash table (hash map) of fixed size (8192 entries by default), which is used for primary lookup. When the slot in the table is found it points to list of conntrack structures, so secondary lookup is done using list traversal. 65536/8192 gives 8 – the average list length. You may want to experiment with this value on heavily loaded systems.

Modifying conntrack capacity
To see the current conntrack capacity:

cat /proc/sys/net/ipv4/netfilter/ip_conntrack_max

You can modify it by echoing new value there:

# echo 131072 > /proc/sys/net/ipv4/netfilter/ip_conntrack_max
# cat /proc/sys/net/ipv4/netfilter/ip_conntrack_max

Changes are immediate, but temporary – will not survive reboot.

Modifying number of buckets in the hash table
As mentioned above just changing this parameter will give you some relief, if your server was at the cap, but it is not ideal setup. For 1M connections average list becomes 1048576 / 8192 = 128, which is a bit too much.

To see current size of hash table:

cat /proc/sys/net/ipv4/netfilter/ip_conntrack_buckets

which is read-only aliase for module parameter:

cat /sys/module/ip_conntrack/parameters/hashsize

You can change it on the fly as well:

#echo 32768 > /sys/module/ip_conntrack/parameters/hashsize
# cat /sys/module/ip_conntrack/parameters/hashsize

Persisting the changes
Making these changes persistent is a bit tricky.
For total number of connection just edit /etc/sysctl.conf (CentOs, Redhat etc) and you are done:

# conntrack limits
net.ipv4.netfilter.ip_conntrack_max = 131072

Not so easy with hashtable size. You need to pass parameters to kerenl module at boot time. Edit add to /etc/modprobe.conf:

options ip_conntrack hashsize=32768

Memory usage
You can find how much kernel memory each conntrack entry occupies by grepping /var/log/messages :

ip_conntrack version 2.4 (8192 buckets, 65536 max) - 304 bytes per conntrack

1M connections would require 304MB of kernel memory.