Posts tagged ssd

Installing MySQL and SSD/ZFS Benchmarks

Jun29th
2012

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:

#[root@shana /]$ cd /usr/ports/databases/mysql55-server/

[root@shana /usr/ports/databases/mysql55-server]$ make install clean
# ===&gt; Found saved configuration for mysql-server-5.5.25
# =&gt; mysql-5.5.25.tar.gz doesn't seem to exist in /usr/ports/distfiles/.
#
# ===&gt; Compressing manual pages for mysql-server-5.5.25
# ===&gt; Registering installation for mysql-server-5.5.25
# ===&gt; SECURITY REPORT:
# This port has installed the following files which may act as network
# servers and may therefore pose a remote security risk to the system.
# /usr/local/libexec/mysqld
#
# This port has installed the following startup scripts which may cause
# these network services to be started at boot time.
# /usr/local/etc/rc.d/mysql-server
#
# If there are vulnerabilities in these programs there may be a security
# risk to the system. FreeBSD makes no guarantee about the security of
# ports included in the Ports Collection. Please type 'make deinstall'
# to deinstall the port if this is a concern.
#
# For more information, and contact details about the security
# status of this software, see the following webpage:
# http://www.mysql.com/
# ===&gt; Cleaning for cmake-2.8.8
# ===&gt; Cleaning for mysql-client-5.5.25
# ===&gt; Cleaning for mysql-server-5.5.25

#[root@shana /]$ cd /usr/ports/databases/mysql55-server/

[root@shana /usr/ports/databases/mysql55-server]$ make install clean

# ===> Found saved configuration for mysql-server-5.5.25

# => mysql-5.5.25.tar.gz doesn't seem to exist in /usr/ports/distfiles/.

# ===> Compressing manual pages for mysql-server-5.5.25

# ===> Registering installation for mysql-server-5.5.25

# ===> SECURITY REPORT:

# This port has installed the following files which may act as network

# servers and may therefore pose a remote security risk to the system.

# /usr/local/libexec/mysqld

# This port has installed the following startup scripts which may cause

# these network services to be started at boot time.

# /usr/local/etc/rc.d/mysql-server

# If there are vulnerabilities in these programs there may be a security

# risk to the system. FreeBSD makes no guarantee about the security of

# ports included in the Ports Collection. Please type 'make deinstall'

# to deinstall the port if this is a concern.

# For more information, and contact details about the security

# status of this software, see the following webpage:

# http://www.mysql.com/

# ===> Cleaning for cmake-2.8.8

# ===> Cleaning for mysql-client-5.5.25

# ===> Cleaning for mysql-server-5.5.25

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:

[root@shana /]$ /usr/local/etc/rc.d/mysql start

1	[root@shana /]$ /usr/local/etc/rc.d/mysql start

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).

Running everything on zroot (Mirrored HDDs)
Running everything on zflash (Mirrored SSDs)
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:

[root@shana /]$ zfs create -o mountpoint=/mydbtmp/mydbtmp

1	[root@shana /]$ zfs create -o mountpoint=/mydbtmp/mydbtmp

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:

[root@shana /]$ mysqladmin create sbtest

[root@shana /]$ mysql
#
# Welcome to the MySQL monitor. Commands end with ; or \g.
# Your MySQL connection id is 12
# Server version: 5.5.25 Source distribution
#
# Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
#
# Oracle is a registered trademark of Oracle Corporation and/or its
# affiliates. Other names may be trademarks of their respective
# owners.
#
# Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql&gt; create user 'sbtest'@'localhost';
# Query OK, 0 rows affected (0.00 sec)

mysql&gt; grant all privileges on sbtest.* to 'sbtest'@'localhost' with grant option;
# Query OK, 0 rows affected (0.00 sec)

[root@shana /]$ mysqladmin create sbtest

[root@shana /]$ mysql

# Welcome to the MySQL monitor. Commands end with ; or \g.

# Your MySQL connection id is 12

# Server version: 5.5.25 Source distribution

# Oracle is a registered trademark of Oracle Corporation and/or its

# affiliates. Other names may be trademarks of their respective

# owners.

# Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'sbtest'@'localhost';

# Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on sbtest.* to 'sbtest'@'localhost' with grant option;

# Query OK, 0 rows affected (0.00 sec)

Then we tell sysbench to prepare the database for testing.

[root@shana /]$ sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=sbtest prepare
# sysbench 0.4.12: multi-threaded system evaluation benchmark
#
# No DB drivers specified, using mysql
# Creating table 'sbtest'...
# Creating 1000000 records in table 'sbtest'...

[root@shana /]$ sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=sbtest prepare

# sysbench 0.4.12: multi-threaded system evaluation benchmark

# No DB drivers specified, using mysql

# Creating table 'sbtest'...

# Creating 1000000 records in table 'sbtest'...

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

[root@shana /]$ sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=sbtest run
# sysbench 0.4.12: multi-threaded system evaluation benchmark
#
# No DB drivers specified, using mysql
# Running the test with following options:
# Number of threads: 16
#
# Doing OLTP test.
# Running mixed OLTP test
# Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
# Using "LOCK TABLES WRITE" for starting transactions
# Using auto_inc on the id column
# Maximum number of requests for OLTP test is limited to 100000
# Threads started!
# Done.
#
# OLTP test statistics:
# queries performed:
# read: 1400000
# write: 500000
# other: 200000
# total: 2100000
# transactions: 100000 (193.98 per sec.)
# deadlocks: 0 (0.00 per sec.)
# read/write requests: 1900000 (3685.55 per sec.)
# other operations: 200000 (387.95 per sec.)
#
# Test execution summary:
# total time: 515.5274s
# total number of events: 100000
# total time taken by event execution: 8247.0597
# per-request statistics:
# min: 6.97ms
# avg: 82.47ms
# max: 785.73ms
# approx. 95 percentile: 79.73ms
#
# Threads fairness:
# events (avg/stddev): 6250.0000/0.00
# execution time (avg/stddev): 515.4412/0.02

[root@shana /]$ sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=sbtest run

# sysbench 0.4.12: multi-threaded system evaluation benchmark

# No DB drivers specified, using mysql

# Running the test with following options:

# Number of threads: 16

# Doing OLTP test.

# Running mixed OLTP test

# Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)

# Using "LOCK TABLES WRITE" for starting transactions

# Using auto_inc on the id column

# Maximum number of requests for OLTP test is limited to 100000

# Threads started!

# Done.

# OLTP test statistics:

# queries performed:

# read: 1400000

# write: 500000

# other: 200000

# total: 2100000

# transactions: 100000 (193.98 per sec.)

# deadlocks: 0 (0.00 per sec.)

# read/write requests: 1900000 (3685.55 per sec.)

# other operations: 200000 (387.95 per sec.)

# Test execution summary:

# total time: 515.5274s

# total number of events: 100000

# total time taken by event execution: 8247.0597

# per-request statistics:

# min: 6.97ms

# avg: 82.47ms

# max: 785.73ms

# approx. 95 percentile: 79.73ms

# Threads fairness:

# events (avg/stddev): 6250.0000/0.00

# execution time (avg/stddev): 515.4412/0.02

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

mysql&gt; drop table sbtest;
# Query OK, 0 rows affected (0.05 sec)

1 2	mysql> drop table sbtest; # Query OK, 0 rows affected (0.05 sec)

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
Volume	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 freebsd, hdd, mysql, server build, shana, zfs

Replacing a failed disk

Jun28th
2012

Written by Rob

As I mentioned previously with in Catching up with the Server Build, I had stolen one of the SSDs from Shana for use in my Macbook. Now it gets put back, and the GEOM and ZFS mirrors re-established.

Repartitioning the disk

The first step is to partition the disk to be the same as before. This is where blogging what you did is helpful, all I needed to do was check the post where the SSDs were setup for a guide. I did something silly though it seems. When I pulled the drives out I must have swapped them over. It seems I took /dev/da2, and now the old /dev/da3 thinks it is /dev/da2.

This means the devices will be a bit mixed up, in the original article swap0 resided on da2, now it will be on da3.

[root@shana /]$ gpart create -s GPT da3
# da3 created

[root@shana /]$ gpart add -s 4g -t freebsd-swap -l swap0 da3
# da3p1 added

[root@shana /]$ gpart add -s 2g -t freebsd-zfs -l zil0 da3
# da3p2 added

[root@shana /]$ gpart add -s 8g -t freebsd-zfs -l zl2arc0 da3
# da3p3 added

[root@shana /]$ gpart add -t freebsd-zfs -l flashdisk0 da3
# da3p4 added

[root@shana /]$ gpart create -s GPT da3

# da3 created

[root@shana /]$ gpart add -s 4g -t freebsd-swap -l swap0 da3

# da3p1 added

[root@shana /]$ gpart add -s 2g -t freebsd-zfs -l zil0 da3

# da3p2 added

[root@shana /]$ gpart add -s 8g -t freebsd-zfs -l zl2arc0 da3

# da3p3 added

[root@shana /]$ gpart add -t freebsd-zfs -l flashdisk0 da3

# da3p4 added

Alright, so our partitions are setup. Time to add it back to the mirrors.

Adding the disk back into the swap mirror

If you read the original article, you’ll remember that I setup the swap for the drive to be mirrored across the SSDs. For this we used gmirror. It took me a couple of minutes to work this out, but you need to tell the mirror to forget the missing drive first, then insert the new drive into the mirror.

You can see here the mirror is quite sad:

[root@shana /home/bok]$ gmirror status
# Name Status Components
# mirror/swap DEGRADED gpt/swap1 (ACTIVE)

[root@shana /home/bok]$ gmirror status

# Name Status Components

# mirror/swap DEGRADED gpt/swap1 (ACTIVE)

But if we tell it to forget its lost friend, it cheers up:

[root@shana /home/bok]$ gmirror forget swap

[root@shana /home/bok]$ gmirror status
# Name Status Components
# mirror/swap COMPLETE gpt/swap1 (ACTIVE)

[root@shana /home/bok]$ gmirror forget swap

[root@shana /home/bok]$ gmirror status

# Name Status Components

# mirror/swap COMPLETE gpt/swap1 (ACTIVE)

If only life was that simple..

Anyway, lets give it a new friend by inserting the new disk into the mirror:

[root@shana /home/bok]$ gmirror insert -h -p 0 swap /dev/gpt/swap0

[root@shana /home/bok]$ gmirror status
# Name Status Components
# mirror/swap DEGRADED gpt/swap1 (ACTIVE)
# gpt/swap0 (SYNCHRONIZING, 8%)

[root@shana /home/bok]$ gmirror insert -h -p 0 swap /dev/gpt/swap0

[root@shana /home/bok]$ gmirror status

# Name Status Components

# mirror/swap DEGRADED gpt/swap1 (ACTIVE)

# gpt/swap0 (SYNCHRONIZING, 8%)

Patience, Rob.

[root@shana /home/bok]$ gmirror status
# Name Status Components
# mirror/swap COMPLETE gpt/swap1 (ACTIVE)
# gpt/swap0 (ACTIVE)

[root@shana /home/bok]$ gmirror status

# Name Status Components

# mirror/swap COMPLETE gpt/swap1 (ACTIVE)

# gpt/swap0 (ACTIVE)

There we go, all back to normal.

Adding the disk back into the zpool

Adding disks back into the zpools is even easier, just use the zpool replace command. But first, lets look at the status of the pools.

[root@shana /home/bok]$ zpool status zflash
# pool: zflash
# state: DEGRADED
# status: One or more devices could not be opened. Sufficient replicas exist for
# the pool to continue functioning in a degraded state.
# action: Attach the missing device and online it using 'zpool online'.
# see: http://www.sun.com/msg/ZFS-8000-2Q
# scan: resilvered 95.5K in 0h0m with 0 errors on Fri Nov 4 07:26:29 2011
# config:
#
# NAME STATE READ WRITE CKSUM
# zflash DEGRADED 0 0 0
# mirror-0 DEGRADED 0 0 0
# 1572948128388092590 UNAVAIL 0 0 0 was /dev/da2p4
# gptid/e8694dd9-05a5-11e1-aed5-001cc4429878 ONLINE 0 0 0
#
#errors: No known data errors

[root@shana /home/bok]$ zpool status zflash

# pool: zflash

# state: DEGRADED

# status: One or more devices could not be opened. Sufficient replicas exist for

# the pool to continue functioning in a degraded state.

# action: Attach the missing device and online it using 'zpool online'.

# see: http://www.sun.com/msg/ZFS-8000-2Q

# scan: resilvered 95.5K in 0h0m with 0 errors on Fri Nov 4 07:26:29 2011

# config:

# NAME STATE READ WRITE CKSUM

# zflash DEGRADED 0 0 0

# mirror-0 DEGRADED 0 0 0

# 1572948128388092590 UNAVAIL 0 0 0 was /dev/da2p4

# gptid/e8694dd9-05a5-11e1-aed5-001cc4429878 ONLINE 0 0 0

#errors: No known data errors

So you can see the zflash pool is in a bad way, all we need to do is tell it to replace the UNAVAIL disk (1572948128388092590) with the new flashdisk0:

[root@shana /home/bok]$ zpool replace zflash 1572948128388092590 /dev/gpt/flashdisk0
[root@shana /home/bok]$ zpool status zflash
# pool: zflash
# state: ONLINE
# scan: resilvered 121M in 0h0m with 0 errors on Thu Jun 28 23:22:49 2012
# config:
#
# NAME STATE READ WRITE CKSUM
# zflash ONLINE 0 0 0
# mirror-0 ONLINE 0 0 0
# gpt/flashdisk0 ONLINE 0 0 0
# gptid/e8694dd9-05a5-11e1-aed5-001cc4429878 ONLINE 0 0 0
#
# errors: No known data errors

[root@shana /home/bok]$ zpool replace zflash 1572948128388092590 /dev/gpt/flashdisk0

[root@shana /home/bok]$ zpool status zflash

# pool: zflash

# state: ONLINE

# scan: resilvered 121M in 0h0m with 0 errors on Thu Jun 28 23:22:49 2012

# config:

# NAME STATE READ WRITE CKSUM

# zflash ONLINE 0 0 0

# mirror-0 ONLINE 0 0 0

# gpt/flashdisk0 ONLINE 0 0 0

# gptid/e8694dd9-05a5-11e1-aed5-001cc4429878 ONLINE 0 0 0

# errors: No known data errors

And it is happy, lets check zapps.

[root@shana /home/bok]$ zpool status zapps
# pool: zapps
# state: DEGRADED
# status: One or more devices could not be opened. Sufficient replicas exist for
# the pool to continue functioning in a degraded state.
# action: Attach the missing device and online it using 'zpool online'.
# see: http://www.sun.com/msg/ZFS-8000-2Q
# scan: resilvered 94K in 0h0m with 0 errors on Fri Nov 4 07:01:33 2011
# config:
#
# NAME STATE READ WRITE CKSUM
# zapps DEGRADED 0 0 0
# mirror-0 ONLINE 0 0 0
# da0p3 ONLINE 0 0 0
# da1p3 ONLINE 0 0 0
# logs
# mirror-1 DEGRADED 0 0 0
# 1946587440960367696 UNAVAIL 0 0 0 was /dev/da2p2
# gptid/d92ab0db-05a5-11e1-aed5-001cc4429878 ONLINE 0 0 0
cache
# 3418458909976513027 UNAVAIL 0 0 0 was /dev/gpt/zl2arc0
# gpt/zl2arc1 ONLINE 0 0 0

# errors: No known data errors

[root@shana /home/bok]$ zpool status zapps

# pool: zapps

# state: DEGRADED

# status: One or more devices could not be opened. Sufficient replicas exist for

# the pool to continue functioning in a degraded state.

# action: Attach the missing device and online it using 'zpool online'.

# see: http://www.sun.com/msg/ZFS-8000-2Q

# scan: resilvered 94K in 0h0m with 0 errors on Fri Nov 4 07:01:33 2011

# config:

# NAME STATE READ WRITE CKSUM

# zapps DEGRADED 0 0 0

# mirror-0 ONLINE 0 0 0

# da0p3 ONLINE 0 0 0

# da1p3 ONLINE 0 0 0

# logs

# mirror-1 DEGRADED 0 0 0

# 1946587440960367696 UNAVAIL 0 0 0 was /dev/da2p2

# gptid/d92ab0db-05a5-11e1-aed5-001cc4429878 ONLINE 0 0 0

cache

# 3418458909976513027 UNAVAIL 0 0 0 was /dev/gpt/zl2arc0

# gpt/zl2arc1 ONLINE 0 0 0

# errors: No known data errors

So we have two devices missing this time in the logs and the cache. Lets replace them:

[root@shana /home/bok]$ zpool replace zapps 1946587440960367696 /dev/gpt/zil0

[root@shana /home/bok]$ zpool replace zapps 3418458909976513027 /dev/gpt/zl2arc0
# cannot replace 3418458909976513027 with /dev/gpt/zl2arc0: device is in use as a cache

[root@shana /home/bok]$ zpool replace zapps 1946587440960367696 /dev/gpt/zil0

[root@shana /home/bok]$ zpool replace zapps 3418458909976513027 /dev/gpt/zl2arc0

# cannot replace 3418458909976513027 with /dev/gpt/zl2arc0: device is in use as a cache

Ah bummer, looks like you can’t just replace cache drives. Over in the docs (Example 4-4) it looks like you need to add and remove cache devices, as they’re not mirrored, just part of a cache pool.

[root@shana /home/bok]$ zpool remove zapps 3418458909976513027

[root@shana /home/bok]$ zpool add zapps cache /dev/gpt/zl2arc0

[root@shana /home/bok]$ zpool status zapps
# pool: zapps
# state: ONLINE
# scan: resilvered 0 in 0h0m with 0 errors on Thu Jun 28 23:29:02 2012
# config:
#
# NAME STATE READ WRITE CKSUM
# zapps ONLINE 0 0 0
# mirror-0 ONLINE 0 0 0
# da0p3 ONLINE 0 0 0
# da1p3 ONLINE 0 0 0
# logs
# mirror-1 ONLINE 0 0 0
# gpt/zil0 ONLINE 0 0 0
# gptid/d92ab0db-05a5-11e1-aed5-001cc4429878 ONLINE 0 0 0
# cache
# gpt/zl2arc1 ONLINE 0 0 0
# gpt/zl2arc0 ONLINE 0 0 0
#
# errors: No known data errors

[root@shana /home/bok]$ zpool remove zapps 3418458909976513027

[root@shana /home/bok]$ zpool add zapps cache /dev/gpt/zl2arc0

[root@shana /home/bok]$ zpool status zapps

# pool: zapps

# state: ONLINE

# scan: resilvered 0 in 0h0m with 0 errors on Thu Jun 28 23:29:02 2012

# config:

# NAME STATE READ WRITE CKSUM

# zapps ONLINE 0 0 0

# mirror-0 ONLINE 0 0 0

# da0p3 ONLINE 0 0 0

# da1p3 ONLINE 0 0 0

# logs

# mirror-1 ONLINE 0 0 0

# gpt/zil0 ONLINE 0 0 0

# gptid/d92ab0db-05a5-11e1-aed5-001cc4429878 ONLINE 0 0 0

# cache

# gpt/zl2arc1 ONLINE 0 0 0

# gpt/zl2arc0 ONLINE 0 0 0

# errors: No known data errors

Looks like everything is happy again. Mission complete!

Server Build freebsd, gpt, zfs

Server Build: PostgreSQL Installation and SSD Benchmarking

Jun27th
2012

Written by Rob

The guts of this post was written back in November 2011. It covers installing and configuring PostgreSQL for FreeBSD, and then runs through some benchmarks to evaluate how best to use the SSD.

Installing PostgreSQL 9.1

The installation process is as simple as ever, just build the port:

[root@shana /home]$ cd /usr/ports/databases/postgresql91-server/
[root@shana /usr/ports/databases/postgresql91-server]$ make install clean
#
# ======================================================================
#
# To initialize the database, run
#
# /usr/local/etc/rc.d/postgresql initdb
#
# You can then start PostgreSQL by running:
#
# /usr/local/etc/rc.d/postgresql start
#
# For postmaster settings, see ~pgsql/data/postgresql.conf
#
# NB. FreeBSD's PostgreSQL port logs to syslog by default
# See ~pgsql/data/postgresql.conf for more info
#
# ======================================================================
#
# To run PostgreSQL at startup, add
# 'postgresql_enable="YES"' to /etc/rc.conf
#
# ======================================================================
# ===&gt; Installing rc.d startup script(s)
# ===&gt; Correct pkg-plist sequence to create group(s) and user(s)
# ===&gt; Registering installation for postgresql-server-9.1.1_1
# ===&gt; SECURITY REPORT:
# This port has installed the following files which may act as network
# servers and may therefore pose a remote security risk to the system.
# /usr/local/bin/postgres
#
# This port has installed the following startup scripts which may cause
# these network services to be started at boot time.
# /usr/local/etc/rc.d/postgresql
#
# If there are vulnerabilities in these programs there may be a security
# risk to the system. FreeBSD makes no guarantee about the security of
# ports included in the Ports Collection. Please type 'make deinstall'
# to deinstall the port if this is a concern.
#
# For more information, and contact details about the security
# status of this software, see the following webpage:
# http://www.postgresql.org/
# ===&gt; Cleaning for libxml2-2.7.8_1
# ===&gt; Cleaning for postgresql-client-9.1.1_1
# ===&gt; Cleaning for postgresql-server-9.1.1_1

[root@shana /home]$ cd /usr/ports/databases/postgresql91-server/

[root@shana /usr/ports/databases/postgresql91-server]$ make install clean

# ======================================================================

# To initialize the database, run

# /usr/local/etc/rc.d/postgresql initdb

# You can then start PostgreSQL by running:

# /usr/local/etc/rc.d/postgresql start

# For postmaster settings, see ~pgsql/data/postgresql.conf

# NB. FreeBSD's PostgreSQL port logs to syslog by default

# See ~pgsql/data/postgresql.conf for more info

# ======================================================================

# To run PostgreSQL at startup, add

# 'postgresql_enable="YES"' to /etc/rc.conf

# ======================================================================

# ===> Installing rc.d startup script(s)

# ===> Correct pkg-plist sequence to create group(s) and user(s)

# ===> Registering installation for postgresql-server-9.1.1_1

# ===> SECURITY REPORT:

# This port has installed the following files which may act as network

# servers and may therefore pose a remote security risk to the system.

# /usr/local/bin/postgres

# This port has installed the following startup scripts which may cause

# these network services to be started at boot time.

# /usr/local/etc/rc.d/postgresql

# If there are vulnerabilities in these programs there may be a security

# risk to the system. FreeBSD makes no guarantee about the security of

# ports included in the Ports Collection. Please type 'make deinstall'

# to deinstall the port if this is a concern.

# For more information, and contact details about the security

# status of this software, see the following webpage:

# http://www.postgresql.org/

# ===> Cleaning for libxml2-2.7.8_1

# ===> Cleaning for postgresql-client-9.1.1_1

# ===> Cleaning for postgresql-server-9.1.1_1

I’m installing the postgresql-contrib package also, mainly for benchmarking using pgbench below.

[root@shana /usr/ports/databases]$ cd postgresql91-contrib/
[root@shana /usr/ports/databases/postgresql91-contrib]$ make install clean

1 2	[root@shana /usr/ports/databases]$ cd postgresql91-contrib/ [root@shana /usr/ports/databases/postgresql91-contrib]$ make install clean

Configuring and Starting PostgreSQL

To enable PostgreSQL to start, just add the following lines to your /etc/rc.conf.

postgresql_enable="YES"
postgresql_data="/path/to/your/db"

then to start it.

[root@shana /]$ /usr/local/etc/rc.d/postgresql initdb
[root@shana /]$ /usr/local/etc/rc.d/postgresql start

1 2	[root@shana /]$ /usr/local/etc/rc.d/postgresql initdb [root@shana /]$ /usr/local/etc/rc.d/postgresql start

Benchmarking

I’m using pgbench for the benchmarking process.

Setup

The process of setting up the directories and configuring the PostgreSQL for each benchmark is:

Stop any running instances of PostgreSQL.

Shell

$ /usr/local/etc/rc.d/postgresql stop

1

$ /usr/local/etc/rc.d/postgresql stop
Create the target directory that you would like to store the database in, make sure you set owner of that directory correctly.

Shell

[root@shana /]$ mkdir /pgdbtmp [root@shana /]$ chown pgsql:pgsql /pgdbtmp

1
2

[root@shana /]$ mkdir /pgdbtmp
[root@shana /]$ chown pgsql:pgsql /pgdbtmp
Update the /etc/rc.conf file to set the data directory.
```
postgresql_data="/pgdbtmp"
```
Now init the database and start PostgreSQL again.

Shell

[root@shana /]$ /usr/local/etc/rc.d/postgresql initdb [root@shana /]$ /usr/local/etc/rc.d/postgresql start

1
2

[root@shana /]$ /usr/local/etc/rc.d/postgresql initdb
[root@shana /]$ /usr/local/etc/rc.d/postgresql start

The process of running pgbench is pretty straight forward. Its best to run it as the PostgreSQL superuser.

Switch to the PostgreSQL superuser account pgsql.

Shell

[root@shana /]$ sudo -u pgsql -s

1

[root@shana /]$ sudo -u pgsql -s
Create the pgbench database. It doesn’t have to be called pgbench, you can call it anything.

Shell

[pgsql@shana /]$ createdb pgbench

1

[pgsql@shana /]$ createdb pgbench
Initialise the pgbench database, this populates the data and schema for testing.

Shell

[pgsql@shana /]$ pgbench -i pgbench

1

[pgsql@shana /]$ pgbench -i pgbench

Run pgbench.

[pgsql@shana /]$ pgbench -c 10 -s 10 -t 10000 pgbench
# Scale option ignored, using pgbench_branches table count = 1
# starting vacuum...end.
# transaction type: TPC-B (sort of)
# scaling factor: 1
# query mode: simple
# number of clients: 10
# number of threads: 1
# number of transactions per client: 10000
# number of transactions actually processed: 100000/100000
# tps = 1469.204180 (including connections establishing)
# tps = 1470.132408 (excluding connections establishing)

[pgsql@shana /]$ pgbench -c 10 -s 10 -t 10000 pgbench

# Scale option ignored, using pgbench_branches table count = 1

# starting vacuum...end.

# transaction type: TPC-B (sort of)

# scaling factor: 1

# query mode: simple

# number of clients: 10

# number of threads: 1

# number of transactions per client: 10000

# number of transactions actually processed: 100000/100000

# tps = 1469.204180 (including connections establishing)

# tps = 1470.132408 (excluding connections establishing)

For the purposes of these benchmarks, I’ll keep it simple and run 10 concurrent clients (-c 10), and 10,000 transactions for each client (-t 10000) which is a total of 100,000 transactions. The tps value at the end is what we’re interested in. We’ll also exclude the connection overhead, its transaction execution that we’re interested in.

Setups to be tested

We’re after a few different configuration options to test here, based on the recommendations of some websites. A ZFS record size of 8k is recommended by other PostgreSQL SSD optimisation posts, as is setting the primarycache to metadata. I used these slides as part of my research for this. The configurations are:

Mirrored HDDs (zroot) with no ZFS optimisations (this is our baseline)
Mirrored HDDs (zroot) with ZFS recordsize=8k, primarycache=metadata
Mirrored HDDs with SSD ZIL and L2ARC cache (zapps) with no ZFS optimisations
Mirrored HDDs with SSD ZIL and L2ARC cache (zapps) with ZFS recordsize=8k, primarycache=metadata
Mirrored SSDs (zflash) with no ZFS optimisations
Mirrored SSDs (zflash) with ZFS recordsize=8k, primarycache=metadata

These tests were all completed with the data and transaction logs on the same volume. Lets try offloading the transaction logs to the SSD as well. In all three of these the transaction logs are on zflash.

You can move the transaction logs to a different directory by using the postgresql_initdb_flags option in /etc/rc.conf like so:

postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C -X /pglogtmp"

Mirrored HDDs (zroot) with no ZFS optimisations.
Mirrored HDDs (zroot) with ZFS recordsize=8k, primarycache=metadata.
Mirrored HDDs with SSD ZIL and L2ARC cache (zapps) and no ZFS optimisations
Mirrored HDDs with SSD ZIL and L2ARC cache (zapps) and ZFS recordsize=8k, primarycache=metadata.

Results

The results are rather interesting, the best result in bold.

Volume	ZFS Optimised?	Transactions Per Second
Volume	ZFS Optimised?	One Volume	SSD Transaction Logs
zroot (Mirrored HDDs)	No	1443.13	1664.21
zroot (Mirrored HDDs)	Yes	1444.58	1521.68
zapps (Mirrored HDDs with ZIL/L2ARC)	No	1033.59	1660.72
zapps (Mirrored HDDs with ZIL/L2ARC)	Yes	1062.12	1670.07
zflash (Mirrored SSDs)	No	1667.84	-
zflash (Mirrored SSDs)	Yes	1647.74	-

And graphically (green is with the SSD Transaction Logs).

PostgreSQL Optimisation Results

Conclusions

We can draw a few small conclusions from these benchmarks:

The biggest improvement came from the SSDs, this is perhaps unsurprising
The ZFS optimisations were not a huge improvement, and in the case of the ZFS optimised HDDs with SSD Transaction Logs it actually made performance worse.
The ZIL and L2ARC make PostgreSQL performance significantly worse when the transaction logs reside on that volume.
If you haven’t setup with a ZIL and L2ARC, don’t bother. Just offloading the Transaction Logs to the SSDs is a fantastic gain.

So what setup am I going with? I was never considering storing everything on the SSDs due to their volatility, so I was pleased at the end when the Mirrored HDDs with ZIL/L2ARC with ZFS Optimisations and SSD Transaction Logs came out on top, if only just barely. It also lines up with the way I setup the volumes originally for space, which is fortunate for me.

Server Build benchmarks, hdd, pgbench, postgresql, server build, shana, zfs

Server Build: SSDs are in!

Nov4th
2011

Written by Rob

I got a little tired of waiting for the caddies to arrive so I installed the SSDs in a temporary kind of way. By that I mean I pulled out the SATA brackets from the slots and have connected them all manually for the moment

Anyhow, so I now have two SSDs that are all ready for configuration! In this post I’ll partition them, get some mirrored swap setup and the ZFS mirror.
READ MORE »

Server Build flash, freebsd, gpt, swap, zfs