I had a very annoying problem with proftpd, which seems a common one at first sight: slow login and the problem, that a lot of ftp clients out there have a low timeout setting configured. The problem is that googling "slow connection" or "slow login" in combination with "proftpd" led me in a totally wrong direction. A lot of people seem to have a problem with DNS lookups, which can be easily fixed by adding ...

UseReverseDNS off
IdentLookups  off

... to the configuration file, to turn of any DNS lookups. But this did not change anything for me. Running a ftp client in debug mode it turned out, that the authorization itself took a very long time, which led to a timeout with most ftp clients:

air:~ harald$ ftp -d ftp.xxxxxxxxxx.de
Connected to ftp.xxxxxxxxxx.de.
220 xxxxxxxxxx FTP Server
ftp_login: user `' pass `' host `ftp.xxxxxxxxxx.de'
Name (ftp.xxxxxxxxxx.de:harald): 
---> USER harald
331 Password required for harald
Password: 
---> PASS XXXX 
...

The password was send, and than the ftp client had to wait 10 seconds and longer for a respone. Lot's of ftp clients have a timeout of less than 10 seconds, which results in a timed out connection for such a long response time.

After googling for quite some time without finding anything useful on this topic -- besides the DNS lookup problem -- i delved deeper into to the proftpd documentation and found a howto which gave me some hints of how to speed up ftp login.

As it turned out the problem was my SQLAuthenticate directive, which i just copied from the example configuration file of mod_sql. The configuration was set to:

SQLAuthenticate users userset

The problem with this configuration is, that the userset switch seems to be very, very expensive. I still don't know, why this switch is set in the configuration -- the documentation contains no useful examples of when to use / when to avoid this switch, but eventually i found a forum post of a proftpd maintainer, where he tells, that the userset switch is not necessary to be configured. After changing above configuration to ...

SQLAuthenticate users

... login is fast as hell. I'm still curious why the switch was there ...

In a previous blog entry i described a method of how to setup master-slave replication with mysql. In steps #4 and #5 i used mysqldump and mysql-client for creating a database dump on the master and importing it on the slave. The problem with this approach is, that the database tables are locked, as long as the dump is running. For small databases this might not be a problem, but as data grows, the time to create a dump takes longer and longer. @work we apparently reached some critical level -- mysqldump ran hours and hours and would probably still run, if i had not stopped it already.

Luckily there are more suitable tools for large databases available. Innodb hot backup and xtrabackup. I've decided to go with xtrabackup, because it's open-source, free and actively developed. Innodb hot backup is closed-source and not for free .

The following steps are ment to replace steps #4 and #5 of my previous blog post.

1. building xtrabackup

For Linux i had to build xtrabackup from the source package, because there was no binary package available for my architecture -- it's very easy, though:

harald@master:~/xtrabackup-0.9.5rc$ automake -a -c
...
harald@master:~/xtrabackup-0.9.5rc$ ./configure
...
harald@master:~/xtrabackup-0.9.5rc$ make
...
harald@master:~/xtrabackup-0.9.5rc$ cd innobase/xtrabackup
harald@master:~/xtrabackup-0.9.5rc/innobase/xtrabackup$ make
...
harald@master:~/xtrabackup-0.9.5rc/innobase/xtrabackup$ sudo cp \
    innobackupex-1.5.1 /usr/local/bin
harald@master:~/xtrabackup-0.9.5rc/innobase/xtrabackup$ sudo cp \ 
    xtrabackup /usr/local/bin

Needless to say, that xtrabackup needs to be deployed on every database server.

2. creating a database dump

After successfully building and installing xtrabackup, taking a database dump is very easy:

root@master:~# innobackupex-1.5.1 --user=... --password=... \
    --defaults-file=... --databases="..." .

The command innobackupex-1.5.1 takes the following parameters:

--user

username to use for database connection

--password

password to use for database connection

--defaults-file

this parameter is required, if the my.cnf configuration file is not located at /etc/my.cnf

--dabases

space-separated list of databases to backup

.

destination directory to save dump to

Dumping the database with xtrabackup is incredible fast compared to mysqldump. With xtrabackup it's just a matter of minutes:

real    4m15.614s
user    0m11.710s
sys     0m14.960s

If xtrabackup was successful, it should have created a subdirectory which name is the current date/time, with all required files in it. The directory can now be copied to the slave:

root@master:~# scp -r 2010-03-02_15-02-24 root@xx.xx.xx.xx:~

3. Setting up the slave

The first thing to do on the slave is applying the binary log files to the database dump:

root@dbslave1:~# innobackupex-1.5.1 --apply-log 2010-03-02_15-02-24

...

100302 14:29:56  innobackupex: innobackup completed OK!

Innobackupex will show the message above, if everything was OK. Next task is to copy the database dump to it's new location on the slave. innobackupex is doing everything for you:

root@dbslave1:~# innobackupex-1.5.1 --copy-back 2010-03-02_15-02-24

...

100302 14:29:56  innobackupex: innobackup completed OK!

xtrabackup should now have copied the dump to the mysql data directory. It's a good idea to check the user and owner of the copied files and adjust them, when needed.

Last step is to start the replication. All information required to do so ist stored in the file xtrabackup_binlog_info:

root@dbslave1:~# cat 2010-03-02_15-02-24/xtrabackup_binlog_info
mysql-bin.000331	54249842

With this information available the replication can be set up as described in step #6 of my previous blog post.