ZFS and ext4 Configuration

ZFS_and_ext4_Configuration.txt

# Create ext4 RAID

sudo mdadm --create --verbose /dev/md0 --level=10 --raid-devices=4 \
   --name=mysql /dev/sdb /dev/sdc /dev/sdd /dev/sde
sudo mkdir /mysql-ext4
sudo mkfs.ext4 /dev/md0
sudo mount -o rw,relatime /dev/md0 /mysql-ext4
sudo mkdir /mysql-ext4/msb
sudo chown revin.revin /mysql-ext4/msb

# Create Percona Server 5.7.19 standalone on ext4
env SANDBOX_BINARY=/home/revin/mysql make_sandbox 5.7.190 -- \
  --upper_directory=/mysql-ext4/msb --my_clause=innodb_buffer_pool_size=1G \
  --my_clause=innodb_log_file_size=8G --my_clause=innodb_flush_method=O_DIRECT \
  --my_clause=bind-address=0.0.0.0
  
# Create RAID10 ZFS pool  
sudo zpool create -f mysql-zfs -o ashift=12 mirror /dev/sdb /dev/sdc mirror /dev/sdd /dev/sde
sudo zfs set recordsize=16k mysql-zfs
sudo zfs set atime=off mysql-zfs
sudo zfs set logbias=throughput mysql-zfs
sudo zfs set primarycache=metadata mysql-zfs
sudo zfs set compression=lz4 mysql-zfs
sudo zpool add -f mysql-zfs log mirror /dev/nvme0n1 /dev/nvme0n2
sudo zfs create -o recordsize=128K mysql-zfs/logs
sudo zfs create -o recordsize=16K mysql-zfs/data
sudo mkdir -p /mysql-zfs/logs/innodb
sudo mkdir -p /mysql-zfs/data/msb
sudo chown revin.revin /mysql-zfs/logs/innodb
sudo chown revin.revin /mysql-zfs/data/msb

# Create Percona Server 5.7.19 standalone on ZFS
env SANDBOX_BINARY=/home/revin/mysql make_sandbox 5.7.190 -- \
  --upper_directory=/mysql-zfs/data/msb --my_clause=innodb_buffer_pool_size=1G \
  --my_clause=innodb_log_file_size=8G --my_clause=innodb_flush_method=O_DYSNC \
  --my_clause=innodb_log_group_home_dir=/mysql-zfs/logs/innodb \
  --my_clause=innodb_doublewrite=0 --my_clause=bind-address=0.0.0.0 
  
# sysbench tests
time sysbench --db-driver=mysql --mysql-user=msandbox --mysql-password=msandbox \
	--mysql-db=test --mysql-host=127.0.0.1 --mysql-port=57190 --tables=8 \
	--table-size=20000000 --auto-inc=off --threads=8 --time=0 \
	--rand-type=pareto oltp_read_only prepare && \
sleep 300 && \
time sysbench --db-driver=mysql --mysql-user=msandbox --mysql-password=msandbox \
	--mysql-db=test --mysql-host=127.0.0.1 --mysql-port=57190 --tables=8 \
	--table-size=20000000 --auto-inc=off --threads=8 --time=1800 \
	--rand-type=pareto oltp_read_write run && \
sleep 300 && \
time sysbench --db-driver=mysql --mysql-user=msandbox --mysql-password=msandbox \
	--mysql-db=test --mysql-host=127.0.0.1 --mysql-port=57190 --tables=8 \
	--table-size=20000000 --auto-inc=off --threads=8 --time=1800 \
	--rand-type=pareto oltp_update_index run && \
sleep 300 && \
time sysbench --db-driver=mysql --mysql-user=msandbox --mysql-password=msandbox \
	--mysql-db=test --mysql-host=127.0.0.1 --mysql-port=57190 --tables=8 \
	--table-size=20000000 --auto-inc=off --threads=8 --time=1800 \
	--rand-type=pareto oltp_read_only run && \
sleep 300 && \
time sysbench --db-driver=mysql --mysql-user=msandbox --mysql-password=msandbox \
	--mysql-db=test --mysql-host=127.0.0.1 --mysql-port=57190 --tables=8 \
	--table-size=20000000 --auto-inc=off --threads=8 --time=1800 \
	--rand-type=pareto oltp_read_only cleanup