Attaching file owner/permission information to the git commit

Sometimes it is useful to store more filesystem information in your Git commit, such as file/directory owner, directory permissions, etc. Not necessarily the same permissions git stores by default.

One case I found this useful for is storing our servers config in the Git repository. The idea is to have a master branch, which holds very basic system configuration, useful multiplatform (Linux/BSD/Solaris) shell scripts, a set of certificates, a Munin plugins submodule and so on, a number of child branches for each set of servers or OSes, and finally a Git branch per server.

In this scenario, useful information includes file and directory owner and permission information. Yes, Git stores file permissions bits, but when I clone our repository to my local machine I don’t need those restored; moreover I don’t want those to be overridden by my local ones.

Inspired by once found git-meta-fs script I decided to improve it to suit all our needs. The new shell script stores file metadata in the .gitmetafs directory. A file-per-file approach ensures minimal merging efforts between branches. The script also tries to solve various multiplatform issues, such as root:wheel on BSD vs root:root on Linux, nobody:nogroup differencies on Debian and CentOS and others.

The script uses Git hooks. The hooks should be installed on every machine having a Git repository; place the script into a permanent place and run path/to/git-meta-fs init (or path/to/git-meta-fs dev init on your dev machine). Some instructions you may find out by launching the script without arguments.

GitHub repository.

MySQL to PostgreSQL, PostgreSQL to MySQL

In attempts to optimize our relational database backend we were trying various RDBMS solutions including MySQL, Percona and PostgreSQL. It’s worth to say that we had a relatively large (25GB) database set with plenty of Unicode data, including special chars such as \n, \t and others. That made migration process even more sophisticated.

There are many articles all over the Internet explaining fine plans of migration from MySQL to PostgreSQL and only a few describe the backwards action. In this article I’ll try to only show the caveats we faced.

So our first migration was MySQL to Percona. That was amazingly simple: as easy as installing Percona (which in Debian 6.0 removes MySQL) and issuing service mysql restart. That’s it. Percona picked up database files and my.cnf flawlessly, just as they were built for it from the very beginning.

Though Percona proved to be much faster, we decided to take even more advantage and switch to PostgreSQL. Here is where challenges began.
Note: I will not explain schema and roles migration because we had schema for each RDBMS written from scratch; so this is out of scope of this article, as it only explains data migration.
First thing to do was to create a dump of existing data:

mysqldump --default-character-set=utf8 --skip-lock-tables --skip-opt --compatible=postgresql --no-create-info database_name

This prints the database dump to stdout, in some kind of postgre compatible format (please see MySQL 5.6 Documentation for details). This is true but quotes. Quotes are in MySQL, not ANSI SQL style. To make PostgreSQL understand this syntax, we had to prepend the dump with the following lines:

SET standard_conforming_strings = 'off';
SET backslash_quote = 'on';

You can find more information at Wikibooks: Converting MySQL to PostgreSQL.
Another issue was with Boolean fields. In MySQL these are represented with tinyint(1) and in PostgreSQL there’s a special type for it. But PostgreSQL did not want to understand 0 as false and 1 as true (only with quotes, which is kinda weird). So we had to make that columns INT then cast to boolean with CAST(column AS boolean) and save to a new column.

But staying with PostgreSQL did not show much benefit for us. The I/O rate has increased and the overall performance was mostly the same. There may be several reasons for it: we have so called “simple” queries (but much of them), PostgreSQL was not configured properly (though I spent quite a time with it) or even software bugs, but this is another story. So our task was to switch back to MySQL (Percona).
To dump the database:

pg_dump --data-only --inserts --no-privileges --no-tablespaces --schema=public database_name

This prints INSERTs to stdout. Again same problem with quotes: the output is in ANSI format, while MySQL expects it’s own by default. No problem, prepend the dump with MySQL directive:


Also we had no problem with booleans: MySQL was okay to translate true and false into tinyint(1). The only queries to worry about were calls to “pg_catalog.setval”, so we just grepped them out.

Human-readable du (file space usage) sorted by size

Ever wondered how much space is occupied by directories including files, recursively? I believe many of us use ‘du -s‘ and it’s human-readable form ‘du -sh‘.
But while ‘du -s‘ is easily sortable with ‘sort -n‘, a human-readable form of it isn’t. There are many ways to achieve the result though, the easiest ones invoke ‘du -s‘, sort it and then employ ‘du -sh‘ on each entry, separately. This is a good solution thanks to FS caching, but not so good for network shares and really large/often changed files and directories.

Here’s a small snippet in Perl which only calls du once and then sorts it. Wrapped in a shell function, which can be put in your .rc (.bashrc) for convenience:

function ds() {
        du -sh "$@" | perl -e '
                %byte_order = ( G => 0, M => 1, K => 2, k => 2, 0 => 3 );
                print map { $_->[0] } sort { $byte_order{$a->[1]} <=> $byte_order{$b->[1]} || $b->[2] <=> $a->[2] } map { [ $_, /^\s*[\d.]+([MGKk0])/, /^\s*([\d.]+)/ ] } <>'

(this is a changed version of


% ds .
195M .


% ds /boot/*
293M /boot/GENERIC
293M /boot/kernel
386k /boot/zfsloader
258k /boot/loader
258k /boot/pxeboot
65k /boot/zfsboot
38k /boot/gptzfsboot
35k /boot/support.4th
25k /boot/defaults
16k /boot/gptboot
15k /boot/
8.5k /boot/beastie.4th
8.5k /boot/boot
8.0k /boot/boot2
6.5k /boot/loader.4th
3.5k /boot/zfs
3.0k /boot/frames.4th
2.0k /boot/cdboot
1.5k /boot/device.hints
1.5k /boot/firmware
1.5k /boot/loader.conf
1.5k /boot/modules
1.5k /boot/screen.4th
1.0k /boot/boot0
1.0k /boot/boot0sio
1.0k /boot/boot1
1.0k /boot/loader.rc
1.0k /boot/mbr
1.0k /boot/pmbr

Port forwarding with iptables

The most common and easy solution for the plain TCP port forwarding in Linux is probably socat (as easy as ‘socat TCP-LISTEN:80,fork TCP:‘ to forward port 80 to Though very powerful (it can be used for many other purposes), it’s port forwarding is performed at the user-level, which means less performance and possibly less reliability, as well as relatively high resource usage (you’ll have a fork of socat per connection — 10-20 is good, but what about thousands?). A more robust solution could be needed if you want to share a single IP address across multiple services running on multiple servers (e.g. a website and an IM server).

One can try to use iptables’ DNAT/SNAT address translation capabilities to achieve this. Let’s be more concrete for our examples: say, you want to forward port 1234 on your host to port 4321 on the host First we should ensure our iptables do have a module for NATing (especially on VPS): modprobe iptable_nat (note iptable, singular form). If the command returned no error, you should be good to go.

Now having the NAT module with us, let’s set up the rule to forward the packets:

iptables -t nat -A PREROUTING -p tcp --dport 1234 -j DNAT --to-destination

Here we say: add a rule to the end of nat table, process it before the packet goes into kernel routing table (chain PREROUTING) filter by the TCP protocol (-p tcp) and destination port 1234 (--dport 1234), Translate the Destination Network Address (-j DNAT) to (Note: you don’t have to specify the port if it’s the same, i.e. --to-destination will be enough).

So far so good, but when the packets come back from they want their source address translated as, because otherwise they will not be correct, thus won’t be delivered. iptables has an ability of translating the source address, too:

iptables -t nat -A POSTROUTING -p tcp --dport 4321 -j SNAT --to-source

The description is mostly the same. Note that --to-source should not have it’s port specified: iptables will choose free port(s) for you.

You can test your new forwarding with netcat like

nc -vv 1234

or an external service like

Notes for the post:

  • The port forwarding described is actually used to redirect traffic to another Internet host, not your LAN. In the case of a LAN router, the things could be simplified by just using MASQUERADE

MySQL+InnoDB: on-line table optimization

Intense use of MySQL may lead to table and index fragmentation, which both affect performance. There’s however a simple solution for it, called ANALYZE TABLE. What’s wrong with it? As documentation says:
During the analysis, the table is locked with a read lock for InnoDB and MyISAM.
This means, effectively, that no other connection may read/write to the table during maintenance. For most of the services, this means downtime. One obvious solution for this is to perform optimization on the copy of a table, then replace the original table with the optimized one. Any updates made during maintenance will of course be lost, but this short ‘readonly’ period is much better than service downtime. Let’s see this in detail.

  1. Copy an existing table
    • This will copy the table definition, including columns and indexes, but not foreign keys:
      CREATE TABLE users_for_optimization LIKE users;
    • Before we proceed with copying data, let’s remove any non-clustered indexes, orelse every insertion will update them and data copying may take very long to complete. There’s only one clustered index for the table and it determines how the data is arranged. The clustered index is your PRIMARY KEY, if any, orelse UNIQUE INDEX, or if you don’t have any, the system creates one for you. Each time you modify the clustered index (e.g. create a PRIMARY KEY) the data in the table is rearranged. Thus we should leave our clustered index intact to avoid this rearrangement. Let’s see the list of indexes:
      SHOW INDEXES FROM users_for_optimization;
      Non_unique Key_name Seq_in_index Column_name
      0 PRIMARY 1 id
      0 external_id 1 ext_id_cell
      0 external_id 2 ext_id_broad
      1 delegated_id 1 delegated_id

      Here we have a PRIMARY KEY (Key_name = PRIMARY), one UNIQUE INDEX (Key_name = external_id) which has 2 columns and one non-unique index (Key_name = delegated_id). We would obviously use the PK as our clustered index and can thus drop the external_id and delegated_id indexes:

      ALTER TABLE users_for_optimization
        DROP INDEX external_id,
        DROP INDEX delegated_id;
    • And now we can copy the data (this may also keep clustered index optimized in the new table):
      INSERT INTO users_for_optimization SELECT * FROM users;
  2. We can use the following query to perform optimization:
    ANALYZE TABLE users_for_optimization;

    Note however that InnoDB only takes eight random dives into the key tree, so you may want to retry ANALYZE TABLE several times to achieve better results.

  3. After the data is copied, we should restore our indexes and foreign keys. Look at the query to create the initial table:

    PRIMARY KEY (`id`),
    UNIQUE KEY `external_id` (`ext_id_cell`,`ext_id_broad`),
    KEY `delegated_id` (`delegated_id`),
    CONSTRAINT `id_fk_accounts` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

    As you remember, our new table users_for_optimization already has a PRIMARY KEY, let’s create the rest:

    ALTER TABLE users_for_optimization
      ADD UNIQUE INDEX `external_id` (`ext_id_cell`,`ext_id_broad`),
      ADD INDEX `delegated_id` (`delegated_id`),
      ADD CONSTRAINT `id_fk_accounts`
        FOREIGN KEY (`account_id`)
        REFERENCES `accounts` (`id`)

    Note that it’s faster to create several indexes in one query because it only scans the clustered index once.

  4. Now replace the original table. Note that we first rename tables (this is done atomically) and only then perform dropping, as it can take some time:
    RENAME TABLE users TO users_for_removal, users_for_optimization TO users;
    DROP TABLE users_for_removal;

    Don’t forget to update the table-specific privileges for the new table, if any, since these are not migrated.

That’s it. Have a nice and fast database with no downtime!

Notes for the post:

  • I don’t know what happens with binlog, since we don’t use it. It could be worth disabling it for the time, unless you have a clustered configuration. Additionally you may have a chance to recover the data with binlog, thus even having no ‘readonly’ period, but this may require additional investigation.
  • You may want to doublecheck the triggers and foreign keys. We had no problems with them, but just in case.

Some links you may find useful:

You may also want to try out Percona, an enhanced version of MySQL that is fully compatible.

Linux-like stat in FreeBSD

The default output of the stat command in FreeBSD was completely different from what I got used to in Linux. For example:

linux% stat /boot/vmlinuz-linux 
  File: ‘/boot/vmlinuz-linux’
  Size: 3269840         Blocks: 6416       IO Block: 1024   regular file
Device: 801h/2049d      Inode: 12          Links: 1
Access: (0644/-rw-r--r--)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2012-08-28 23:35:55.000000000 +0300
Modify: 2012-08-26 11:19:01.000000000 +0300
Change: 2012-08-28 23:35:47.000000000 +0300
 Birth: -


freebsd% /usr/bin/stat /boot/boot0
1149243074 13 -r--r--r-- 1 root wheel 0 512 "Jul 12 18:52:22 2012" "Mar  2 10:39:17 2011" "Feb 11 16:24:47 2012" "Feb 11 16:24:47 2012" 4096 2 0 /boot/boot0

Found it hard to read, I made some use of the -f argument to get it uniformed. Here’s the alias I use and the new output:

freebsd% alias stat="/usr/bin/stat -f \"  File: '%N'%SY%n  Size: %-15z Blocks: %-10b IO Block: %-6k %HT%nDevice: %7Hr/%-7Lr Inode: %-11i Links: %l%nAccess: (%Mp%Lp/%Sp) Uid: (%5u/%8Su)  Gid: (%5g/%8Sg)%nAccess: %Sa%nModify: %Sm%nChange: %Sc%n Birth: %SB\" -t '%F %T %z'"

freebsd% stat /boot/boot0
  File: '/boot/boot0'
  Size: 512             Blocks: 2          IO Block: 4096   Regular File
Device:       0/0       Inode: 13          Links: 1
Access: (0444/-r--r--r--) Uid: (    0/    root)  Gid: (    0/   wheel)
Access: 2012-07-12 18:52:22 +0000
Modify: 2011-03-02 10:39:17 +0000
Change: 2012-02-11 16:24:47 +0000
 Birth: 2012-02-11 16:24:47 +0000

Looks much better huh?