Speeding up MySQL Import on FreeBSD

Wed 28 September 2016 by feld

I was recently tasked with rebuilding a readonly slave database server which only slaves a couple of the available databases. The backup/dump is straightforward and fast, but the restore was being excruciatingly slow. I didn't want to wait a week for this thing to finish, so I had to compile a list of optimizations that would speed up the process. This is the best way to do it on FreeBSD, assuming you're working with InnoDB. Additional optimizations may be required if you're using a different database engine.

Please note this is assuming no other databases are running on this MySQL instance. Some of these are rather dangerous and you wouldn't want to put other live data at risk.


innodb_buffer_pool_size = 38G  # roughly 70-80% of your available memory
innodb_flush_method = O_DIRECT
sync_binlog = 0 # Don't keep this permanently
innodb_flush_log_at_trx_commit = 0 # Don't keep this permanently
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_write_io_threads = 16


# Only use this during imports

The actual import command in use:

# cat dump.sql.gz | { echo "set sql_log_bin=0; set autocommit=0; set
unique_checks=0; set foreign_key_checks=0;"; zcat; } | mysql -u root -p

And now I've gone from a tens of MBs imported per minute to several GBs imported per minute.

Monitoring FreeBSD Base System Vulnerabilities with pkg audit

Fri 12 August 2016 by feld

The FreeBSD base system has been difficult to monitor for published vulnerabilities for a long time. This will improve when we achieve a packaged base system, but that leaves users of currently supported -RELEASE systems without a standardized option.

The freebsd-version(1) utility has existed since FreeBSD 10.0. This ...

read more

Java KVM Troubles on OSX

Thu 30 June 2016 by feld

I was having troubles on OSX getting access to the KVM at work which is a Dell KVM 4322DS. The connection errors from Java about security settings were not resolved by whitelisting the site in the Java console. Turns out newer Java disables some SSL/TLS algorithms and settings that ...

read more

Fixing Time Machine / Netatalk (error (null))

Fri 19 February 2016 by feld

Recently I was setting up a new Time Machine backup on my wife's MacBook so it would use my FreeBSD/ZFS server. My own personal MacBook was already backing up to it successfully and has been for quite some time. When I attempted to start a new backup to ...

read more

Mono's DNS is broken

Fri 18 December 2015 by feld

I recently started playing around with Sonarr by porting it to FreeBSD. It's a pretty well designed app -- I'm not aware of an alternative with such a well thought-out interface and deep integration into the other programs it communicates with. I've never run program via mono before ...

read more

Routing a FreeBSD Jail through OpenVPN

Tue 30 June 2015 by feld

I decided I wanted to concoct a solution where I could force all applications in a jail or jails through a VPN connection without affecting the internet connectivity of other daemons on the system. After some headbanging I was able to make this work. The OS version being used in ...

read more

Braindead FreeBSD Backups with Tarsnap and ACTS

Thu 14 May 2015 by feld

Before I start this simple blog post you should be aware that:

Now that has been said, let's start with the super basics. Do you ...

read more

IPv6 via 6rd on FreeBSD

Wed 04 February 2015 by feld

My ISP is Charter and they support 6rd for IPv6. Unfortunately 6rd support does not exist in the stf(4) driver in FreeBSD yet. There is a work-in-progress implementation available from hrs in ports, net/stf-6rd-kmod. However, I haven't found very good documentation on exactly how to use it ...

read more

BSD License Audit

Mon 22 December 2014 by feld

I recently did an audit of the "BSD" licenses in the FreeBSD ports tree. This pertains strictly to those defined as LICENSE=BSD which could be one of several licenses. It was an extremely tedious process manually verifying the license of each port, and except for a dozen which are ...

read more

Outlook-compatible WebDav with Nginx

Mon 10 November 2014 by feld

Microsoft Outlook has a Publish Online feature for sharing specific calendar information by publishing iCal files to WebDav. I don't use Apache on my personal servers, so here's how to configure it on Nginx.

You first need to ensure that you have both Nginx WebDav modules installed. They ...

read more