Posts tagged mysql

Installing MySQL and SSD/ZFS Benchmarks

Jun29th
2012 avatar Written by Rob

Here we go again. This post walks through installing MySQL and running through some benchmarks to discover how best to utilise the SSDs and ZFS.

Installing MySQL

Naturally, the first thing you must do is install MySQL. There has been a lot of activity with Percona and MariaDB lately but we’ll stick with vanilla MySQL for the moment due to time constraints. We can drop in one of the other two later as required.

Advice for utilising an SSD in your MySQL deployment is pretty scattered and confusing. The best resource I’ve been able to find is this set of slides from Sun/MySQL.

Installing MySQL is a simple procedure using ports:

Easy as that. You can enable MySQL (and leave the data directory in the default /var/db/mysql location) just by adding the following line to /etc/rc.conf.

mysql_enable="YES"

Then start it up:

Benchmarking

For benchmarking MySQL we’ll use sysbench. In line with the PostgreSQL testing we did, we’ll create /mydbtmp and /mylogtmp drives in ZFS as they are required.

We’ll test the following scenarios, both with MyISAM and InnoDB (the most common engines).

  1. Running everything on zroot (Mirrored HDDs)
  2. Running everything on zflash (Mirrored SSDs)
  3. Running everything on zapps (Mirrored HDDs with SSD ZIL/L2ARC)
  • Splitting the data and logs

We’ll look at ZFS optimisations after.

Setup

We repeat these steps for each benchmark. For our first three benchmarks we’ll only need /mydbtmp. Create it using ZFS:

Substituting <pool> for zroot, zapps, and zflash as required.

To tell MySQL to use those data directories, add the following to your /etc/rc.conf:

mysql_datadir="/mydbtmp"

We will also need to create the mysql database and username:

Then we tell sysbench to prepare the database for testing.

Here we’re creating a table sbtest and populating it with a million records. Then we can run the benchmark:

All done! Jump into MySQL to drop the sbtest table, then we run sysbench again but this time with –mysql-table-engine=innodb.

Then we can destroy the /mydbtmp filesystem and create it again on a different drive ready for the next test.

Results

The results here are interesting, mostly because of how they contradict the advice linked above.

Volume Transactions Per Second
MyISAM InnoDB
zroot (Mirrored HDDs) 193.98 884.76
zapps (Mirrored HDDs with ZIL/L2ARC) 199.11 804.81
zflash (Mirrored SSDs) 199.03 1007.80
zroot logs 196.44 764.76
zflash logs 186.94 912.90

Graphically:

MySQL Benchmark Results

Conclusion

It seems the simplest way to improve performance with MySQL is to throw everything on to the SSDs. It is noted though that the InnoDB gains much more performance benefit than MyISAM. If you’re stuck with MyISAM tables it might not be worth investing in a SSD.

For now, I’ll create /var/mysql/data on zflash and store my databases there.

Server Build    , , , , ,

Server Build: Shana’s Role

Nov2nd
2011 avatar Written by Rob

Typically when you’re building a new server, you need to know what you’re going to use it for. As mentioned in a previous post, Shana will be replacing the existing Tyrande server. So she will need to take on all the roles that Tyrande already fulfils. After that Tyrande will be rebuilt as Taiga, then the two will share the roles, with a bias towards Shana as the more powerful server.

The current roles running on Tyrande:

  • Web Server (Apache)
  • Database Server (PostgreSQL)
  • Database Server (MySQL)
  • SMTP Mail Server (Postfix)
  • POP/IMAP Mail Server (Dovecot)
  • Domain Name Server (DNS – Bind)

In addition to typical services that I run on all my servers:

  • Time Server (NTPD)
  • Firewall (pf)
  • Secure Remote Shell (OpenSSH)
  • Custom Server Monitoring (Meidon)
  • Nightly backups using zfs snapshots and rsync
  • Nightly security updates and vulnerability checks

And the synchronisation, load balancing and failover between Shana and Taiga.

These are the things I’ll be dedicating the following blog posts to. Some are obviously going to take multiple posts. Lets give a bit of an overview here though:

Web Server

I use Apache as my web server. I always have. It hosts blogs and custom projects, and everything in between. Typically I have a preference for PHP over perl, python or ruby. Again as with my other posts, these blogs aren’t to debate my software preferences. You can fight that elsewhere :)

I prefer the OpenBSD Apache layout too, hence the /var/www/ filesystems in the ZFS setup post. I’ll go into detail on installing Apache, PHP, Subversion and its dependencies. We’ll be running the web root of Apache mainly on the SSD, so we’ll need to look at keeping a copy on the normal disk too.

Database Server (PostgreSQL)

PostgreSQL is my SQL database of choice. All of my projects use it for the SQL data storage. I wouldn’t touch MySQL with a 50 foot poll. I’ll go into detail installing it too, and putting its data in /var/pgsql. As a bonus we’ll be running PostgreSQL mainly on the SSD and configuring asynchronous replication to a second instance of PostgreSQL running on Shana that writes to the HDD. This way we can keep a constant backup on less volatile disk that we can manually failover to in an instant.

Database Server (MySQL)

I run MySQL only because of WordPress. That is all.

SMTP Mail Server / POP3/IMAP Mail Server

I use Postfix as my SMTP server of choice and Dovecot for POP3/IMAP. Its fairly lightly utilised but I prefer the flexibility of running my own mail server. Note: I always use my ISPs server for outgoing SMTP, so I don’t have to deal with the difficulties of running my own server – namely blacklists. I’ll detail installing and configuring them to use a SQL database (PostgreSQL) as its list of valid user accounts with aliasing that works at the account level (so an alias can login, even), catch-alls and a bunch of fun stuff.

Domain Name Server

I host my own DNS too using BIND. I have accounts over at easyDNS to have global redundant DNS servers, but they all slave off my primary server. Then I can use normal zone files to manage my domains instead of a web interface (I like the flexibility). I’ll cover setting all that up too.

Time Server

I configure the built in ntpdate and ntpd to keep my local machine time synchronised with an appropriate time server.

Firewall

I use OpenBSD’s Packet Filter (pf) for my firewalling. It is above and beyond the best open source firewall package I’ve found. I think even OS X Lion incorporates pf now. I don’t do too much fancy stuff in pf, just some normalisation, default block, country-based blacklists and bruteforce blacklisting on SSH. (You try to connect to port 22 on my server more than 5 times in 60 seconds and you’re blacklisted).

Secure Remote Shell

Standard config of the built in OpenSSH sshd.

Custom Server Monitoring

I wrote a small PHP-based package a few years ago that runs some commands every minute and saves the output to a PostgreSQL database. It can alert via push notification if something goes wrong, and display a little dashboard thing:

Meidon Dashboard

Nightly Backups using ZFS and Rsync

I set this up a while ago. Its an awesome set of scripts called rsbackup that basically take a list of servers with individual configurations, connects to them, runs any pre-backup commands (like taking ZFS snapshots), then rsyncs any changes down and runs some post-backup commands.

So nightly, my media centre Mac Mini calls out to Tyrande (just as it will do for Shana and Taiga just by adding a configuration file), asks her to take snapshots of the MySQL database, run a pg_dump of the PostgreSQL database and then copies it all back to my Drobo. Neat eh?

I initially did ZFS snapshots for PostgreSQL too but stopped for reasons that I can’t remember. I’ll investigate whether PostgreSQL 9.1 plays more friendly with snapshots and re-evaluate the best way forward for Shana.

Nightly security updates and vulnerability checks

How to configure the FreeBSD to check for security/operating system updates nightly, and ensure that the built in nightly security checks are emailed to you! Can’t stress how important this is.

Sychronisation, Load Balancing and Failover

As mentioned in the original server overview post, I’ll be using CARP for failover and load balancing between Shana and Taiga. So I’ll cover off how to configure all of that too.

So that’s all of the stuff that I’ll be setting up for Shana, and then for Taiga. Should be fun!

Server Build    , , , , , , , , , , , ,