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 'ha_archive.so';
Query OK, 0 rows affected (0.02 sec)

And then

mysql> show engines \G

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


Voila!

The following plugins are installed into the OS but not into MySQL:


ha_archive.so - ARCHIVE
ha_blackhole.so - BLACKHOLE
ha_example.so - EXAMPLE
ha_innodb_plugin.so - InnoDB Plugin


Enjoy!

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 //burnz.blog)

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 //burnz.blog

Some myths about H.264 vs Theora and their use in HTML5 video tag.

I was planning to write this post some time ago, now I’ve got the last piece of puzzle [7].

Myth: H.264 – is proprietary codec, Theora – is open.
Facts: H.264 – is open international standard ISO/IEC 14496-10 [8], there exist open/free reference code and several open source implementations. Theora – is nonstandard codec with unpredictable roadmap, distributed as open source [5].

Myth: One have to pay royalties to use H.264 on web site
Facts: No if your video content is free (i.e. you are not selling videos on title-by-title or subscription basis), at least till the end of 2015 [10]. This date has been moved several times, chances are it will be moved yet again. After that date one will have to pay $2500/year if service has less than half a million users; $5K – if less than 1M users; $10K – over 1M users. The bill very reasonable even for startup. Additional protection is that license terms are fixed for five years, and royalties can not be increased by more than 10% at renewal. [4]

Myth: One have to pay for H.264 encoding and decoding applications.One have to pay if using browser with H.264.
Facts: Yes, each developer of H.264 encoding/decoding application or library has to pay $0.20 for each copy sold. If you are an end user there is no grounds to worry, it is application developer/distributor responsibility to pay the royalties. Paid application have royalties already included. Free applications like IE, Safari, iTunes, Flash will obviously remain free, as corporations will wave those royalties for users. Moreover some of the SW companies are among H.264 licensors, so they probably have special conditions agreements and pay much less if anything. It is not clear, but companies like Mozilla, Firefox maker, may be among losers, as they don’t have a way to pay royalties if they are found to have to. [4]

Myth: Theora – open source codec, therefore it could be no problems with patent infringement.
Facts: Video codecs (and audio too) is a knowledge area with extremely high patent density. Everything one can imagine has already been patented, that’s what millions bucks research budgets are spent for. So probability of Theora being patent-clear is zero.

Myth: On2 donated original (VP3) code which Theora is based on, and warranted no legal actions against anybody using it or its modifications. So no patent worries.
Facts: A) There is only protection from On2 legal actions [6] B) Development of Theora went quite a bit from original VP3 code, so it now may infringe some patents unintentionally. C) There is no warranties that original VP3 technology does not violate third party patents including ones in H.264 patent pool. D) Another fact to keep in mind, even obtaining MPEG-LA license does not warrant one is all covered, there could be other panets out of MPEG-LA pool [3]. E) We’ve got first confirmation that legal actions against Theora are possible – by Steve Jobs [7].

Myth: With broad support for Video tag in browsers Flash Video will die.
Facts: Not that soon. Video tag provides way fewer controls to developer – e.g. no caching and buffer size control. Live streaming is not supported yet, and is not in roadmaps.

Who is in which camp among SW giants?
Fact: Apple, Microsoft are among H.264 licensors pool; Adobe, Google are not. [2] Google seems to be trying to build its own video patent portfolio by acquiring On2 company [9].

Why industry seems to like H.264 codec more than free open source solutions?
This codec is now supported by Adobe Flash, MS Silverlight, Apple iTunes/QuickTime, it is supported by a lot of mobile phones, camcoders, digital television etc. And its support will become only wider. For all industry players it is very beneficial to have single universal standard codec, it allows for great technology reuse, simplifies and makes development cheaper, it benefits technology convergence. And after all it is just a very good codec delivering very good quality in wide bitrate ranges.

Conclusion:
We will see major perturbations if Theora success starts threaten business of big guys.

Update
1. Changed spelling to Firefox, as was required
2. Actually, H.264 use for free internet broadcast will remain royalty-free till the end of 2015 [10], as pointed out in
3. One more good article: http://www.engadget.com/2010/05/04/know-your-rights-h-264-patent-licensing-and-you/

Update 2
Another very good article on On2-Google VP8 now: http://x264dev.multimedia.cx/?p=377 It nicely describes overall quality of VP8 code, and patent issues with VP8 and MS’s VC-1. The later was also thought patent-free initially.

References
1. H.264 page at MPEG LA site: http://www.mpegla.com/main/programs/AVC/Pages/Intro.aspx
2. List of H.264 patent pool licensors – http://www.mpegla.com/main/programs/AVC/Pages/Licensors.aspx
3. MPEG LA FAQ http://www.mpegla.com/main/programs/AVC/Pages/FAQ.aspx
4. Summary of H.264 license terms by MPEG LA – http://www.mpegla.com/main/programs/AVC/Documents/AVC_TermsSummary.pdf
5. Theora codec page: http://www.theora.org
6. On2 statement about VP3 license: http://svn.xiph.org/trunk/theora/LICENSE
7. Apple prepares to go after open source codecs: http://blogs.fsfe.org/hugo/2010/04/open-letter-to-steve-jobs/
8. Official H.264 standard http://www.iso.org/iso/catalogue_detail.htm?csnumber=52974
9. Google acquires On2 Technologies – http://www.google.com/intl/en/press/pressrel/ir_20090805.html
10. H.264 will remain free for internet broadcast till end of 2015 http://www.mpegla.com/Lists/MPEG%20LA%20News%20List/Attachments/226/n-10-02-02.pdf

Fast IP->location resolution in SQL

Abstract
Many web services nowadays rely on users’ geo location information. It can be required purely for statistics purposes, or to add value to provided service itself. In most cases the only possible way to detect user location is to use IP address of originating request. IP addresses are not hierarchical in a geographical sense, large block of IP addresses is reserved to an ISP, and then broken down for smaller companies or individuals. There is no much order in the system. So a quick database is required in order to be able to make millions of lookups.

Geo location data
Surely enough companies exist which provide such IP-based geo location data. Maxmind is one of them. Fortunately they provide free version of their database under some legal constraints. There are other providers too, I’ve recently came across IpInfoDB. So far so good, now we need a system to make use of the data.

Physical database design
Data consists of (startIp, endIp, country) tuples. If IP address is in the range, it is originated from that country. There blind unassigned ranges, there are private ranges, and as said above multiple (very many) ranges map to a Country. In my table there is > 100K distinct IP ranges.

Physical design in MySQL:
CREATE TABLE LookupIpToCountry (
startIp int(10) unsigned NOT NULL,
endIp int(10) unsigned NOT NULL,
countryId tinyint(3) unsigned NOT NULL,
PRIMARY KEY (startIp),
KEY fk_countryId (countryId),
CONSTRAINT LookupIpToCountry_ibfk_1 FOREIGN KEY (countryId) REFERENCES Countries (id)
) ENGINE=InnoDB;

Other database or engine can be used, provided that startIp is clustering index, i.e. data in the table is physically sorted according to startIp in ascending order.

Fast lookup query
mysql> select * from LookupIpToCountry where startIp<=INET_ATON('74.125.19.99') and endIp>=INET_ATON('74.125.19.99') order by startIp desc limit 1;
+------------+------------+-----------+
| startIp | endIp | countryId |
+------------+------------+-----------+
| 1249693272 | 1249773023 | 230 |
+------------+------------+-----------+
1 row in set (0.00 sec)

The idea here is to find the first region into which IP of interest falls. SELECT ... WHERE startIp<= X ... order by startIp desc will navigate through B-tree index to the last row satisfying startIp criterion and setup scan iterator in descending order. (Because it can only validate second condition endIp>= X by scanning). But the iteration ends immediately because of LIMIT 1 if second condition is satisfied. So effectively whole query is reduced to B-tree lookup of one value. Of course, in case of the miss (IP address does not fall to any of known ranges), there is a performance hit:

mysql> select * from LookupIpToCountry where startIp<=INET_ATON('255.255.255.255') and endIp>=INET_ATON('255.255.255.255') order by startIp desc limit 1;
Empty set (0.39 sec)

In order to fix it you can simplify the query to select * from LookupIpToCountry where startIp<=INET_ATON('255.255.255.255') order by startIp desc limit 1; and check second condition endIp>=INET_ATON('255.255.255.255') at application level, if you think it is worth.

Solving performance problems in pub-sub erlang server

Intro
So I wrote (see some prehistory here) a kind of notification server where clients can subscribe to events and be notified when event of interest happens. Clients use HTTP long poll method to get notifications delivered, and one of the application of the server is chat room.

Symptoms
In my case there were two problems:

1. general lack of performance (I started optimizing somewhere from 200-300 messages per second)
2. under high load server would lock up, sometimes for extended period of time – it does not deliver any messages
3. even under moderate load performance is not stable and would drop eventually for some period of time (sometimes to complete lock up, sometimes not, sometimes for a couple of seconds, sometimes for longer time).
4. In-depth investigation with tcpdump has shown that server can not even accept connections.

Side note on server not accepting tcp connections
That is interesting how it happens though. I don’t know if it is specific Linux 2.6 behavior of it is a norm, but the sequence is:

1. client sends SYN
2. server responds with ACK, but acknowledgment number set to some arbitrary big number
3. client drops connection by sending RST

So should you see similar behavior note that this is just a symptom, not disease. The problem is somewhere dipper. In my case increasing TCP listen backlog from mochiweb’s default 30 helped a bit, fewer timeouts were observed, but still performance sucked.

Fixing Root Cause
So how pub-sub servers in Erlang are generally built? You get a message being processed in some process, it might have been received from somewhere else or originates from this process . And then you have bunch of waiting processes representing connected subscribed clients waiting for a message be delivered. Each of these processes normally represents TCP connection or in my case HTTP long polling connection. And delivering message to this process releases it from wait state and allows message to be delivered to end user. There is of course some router module or process which determines a subset of processes (PIDs) to which the message should be delivered. How to do efficiently is very interesting topic but not for this post. Then you do something like
lists:foreach(fun(Pid) -> send_message(Pid, Message) end, PidList)

The result of this is that each of processes from the target group (selected by router) becomes immediately available for execution. And if the group size is big, chances are that current process broadcasting this notifications will be preempted. And the thing is that this actually causes context switching storm. I’m not 100% sure how Erlang runtime is implemented, but it seems that if process receives a message it gets some kind of priority and is scheduled for execution, like it happens in some OSes. So the message sending loop may take quite awhile.

Now, if the message broadcast loop is more complex, say it consists of two nested loops, and inside it you do some non trivial operations followed by sending the message for one particular PID, then the things become very bad. Context switching overhead no matter how light it is in Erlang kills your performance.

Recipe

1. If you have complex loops to calculate to which PID send the message, and you do message sending inside that loop – rewrite the code. First prepare list of PIDs using whatever complex procedures you have, then send messages to those PIDs in one shot.
2. Then sending messages to bunch of PIDs do temporarily boost performance of your thread so it won’t be preempted.

Example:

PidList = generate_pid_list(WhatEver),
OldPri = process_flag(priority, high), % Raise priority, save old one
lists:foreach(fun({Pid, Msg}) -> send_message(Pid, Msg) end, PidList),
process_flag(priority, OldPri)


That’s basically it. The result is that now I’m reliably achieving about 1.5k messages/sec with all stuff like HTTP/JSON parsing, ETS operations, logging, etc. I would like to pump this number few times higher, but at the moment that’s what I can get. I will come back when learn something new

PS. You may also find this discussion followed $1000 code challenge useful:
http://groups.google.com/group/erlang-programming/browse_thread/thread/1931368998000836/b325e869a3eea26a

Fix Eclipse Config to Start with Broken Perspective

I happened to switch to Ruby Browsing Perspective in Eclipse eventually, for whatever reason it crashed Eclipse with Out of Memory exception. When I try to restart it won’t, because it tries to load all the same Perspective. The solution is to edit one of Eclipse config files. The file of interest is "workspace/.metadata/.plugins/org.eclipse.ui.workbench/workbench.xml" (in my case on Mac it is ~Documents/workspace/.metadata/.plugins/org.eclipse.ui.workbench/workbench.xml)

The default perspective is configured by the following directive:
<perspectives activePart="org.rubypeople.rdt.ui.EditorRubyFile" activePerspective="org.rubypeople.rdt.ui.RubyBrowsingPerspective">

So what I had to do is replace org.rubypeople.rdt.ui.RubyBrowsingPerspective with org.eclipse.jdt.ui.JavaPerspective

You can find for exact class names of available perspectives in the same file. Perspective declaration looks like:

<perspective editorAreaTrimState="2" editorAreaVisible="1" fixed="0" version="0.016">
<descriptor class="org.eclipse.jdt.internal.ui.JavaPerspectiveFactory" id="org.eclipse.jdt.ui.JavaPerspective" label="Java"/>
...

First line is opening of perspective declaration, and <descriptor> tag contains id attribute which is what you want. If you search trough workbench.xml you will find all available perspective names.

Tuning Linux firewall connection tracker ip_conntrack

Overview
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
131072

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
32768


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.

Get Unix timestamp in Java, Python, Erlang

Mostly a note for myself. To Get Unix timestamp value in seconds

java:

long timestamp = System.currentTimeMillis()/1000


Python:

import time
timestamp = int(time.time())


Erlang:

{Mega, Secs, _} = now(),
Timestamp = Mega*1000000 + Secs,


Working with timestamps in MySQL

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00 |
+---------------------------+


Solution for Erlide hangs Eclipse problem

If you are using Erlide to develop Erlang applications you may sooner or later find yourself in a situation than IDE just hangs completely whenever you try to access any Erlide functions. You can not even go to Erlide section of Eclipse Preferences ! This seems to be a kind of mystery, at least googling didn’t reveal any helpful information except hinting that problem could be due to backend connection issues. As far as I understand, backend is erlang compiler/toolchain used by Erlide. I can imagine it is used when compiling, or when doing syntax check, but why it hangs in editor and preferences dialog?!

I was able to find Erlide logfile (~/Document/workspace_erlide.log under MacOS) And it suggested that Erlide was running in dead loop trying to invoke external Erlang. In particualr it complained about that the following command were failing with result code 126:
/opt/local/var/macports/software/erlang/R12B-3_2/opt/local/lib/erlang//bin/erl -name f0a30ba_erlide@alexey-timanovskys-macbook-pro.local -setcookie erlide
. When I tried to execute it from command line I got errors from erlang. Then I discovered that I have two installations of erlang – R12B 3.2 and 5.0, obviously I used macports to upgrade to most recent one, but it kept previous version too. And erlide was configured to use old erlang setup which didn’t work for whatever reason. So the next step was to find where it is configured, I grepped and found it in
~Documents/workspace/.metadata/.plugins/org.eclipse.core.runtime/.settings/org.erlide.core.prefs
I replaced line

runtimes/erlang/homeDir=/opt/local/var/macports/software/erlang/R12B-3_2/opt/local/lib/erlang/
with

runtimes/erlang/homeDir=/opt/local/lib/erlang/

and restarted Eclipse. It worked!

So be careful, breaking anything in erlang configuration will not allow you to use Erlide, you can’t even go to preferences and change erlang home directory to correct one.

I’m using Eclipse 3.4.1 and tried Erlide versions 0.5.0 and 0.5.1.

How to produce hex array from binary data

Suppose you have a binary file you want to include as byte array into C/C++/Java program. Here how you can generate it (just add opening and closing braces and clean some garbage at the end)

hexdump -v -e " 16/1 \"0x%02X, \" \"\n\""

Something like the following will be produced:


0x7F, 0xFE, 0xF9, 0xE7, 0x9F, 0x7F, 0xFE, 0xF9, 0xE7, 0x9F, 0x7F, 0xFE, 0xF9, 0xE7, 0x9F, 0x7F,
0xFE, 0xF9, 0xE7, 0x9F, 0x7F, 0xFE, 0x0F, 0xFD, 0x3F, 0x4F, 0xAC, 0x0A, 0xA2, 0x00, 0xB0, 0x94,
0x01, 0x , 0x , 0x , 0x , 0x , 0x , 0x , 0x , 0x , 0x , 0x , 0x , 0x , 0x , 0x ,