Mysql

[orinoco]

Currently running several Vm's from my Napp-IT server, one of the machines is running mysql database is only 3gb although it does have 9 million rows, currently performance has been poor, I have tried off loading the DB to RAM, the Napp-IT box is an i5 with 16gb of RAM bare metal install not a VM, IBM m1015, all drives have a smart status of passed, 3 pools, VM's run from one pool, AFP from another pool and the DB sits on this pool, drives for this pool are Raidz1 Seagate 3TB ST3000DM001 x3 1 x WD WDC WD30EZRX-00M, VM pool is 4 x Samsung HD204UI, Vm's sit across 2 ESXi 5.1 servers both i7 2600 each with 32gb of RAM, I do have an SSD Crucial m4 256gb not currently deploed, I was going to install this in my macbook as was told it would quickly get destroyed using as cache, really looking for some ways to get performance back on track, any pointers greatly appreciated, drives in AFP remain largely idle apart from when it is serving movies and music


Thanks

 

[sotech]

Sorry, I'm finding your post a little difficult to follow - paragraphs may assist there.

To try and clarify:

Napp-it box: i5/16GB RAM with two pools:

4x3TB - database pool, AFP pool
4x2TB - VM pool

So the SQL DB is on the pool with 4x3TB.... that pool (like all pools in general terms) has the IOPS of the slowest single spinning disk in it (since it only has one vdev), which doesn't sound helpful for your database. I have to say that we have been using Crucial M4 drives as L2ARC drives for >12 months and have yet to kill one... though our usage case is different to yours.

 

[orinoco]

Thanks, apologies I should have split into paragraphs so using my m4 for l2arc should improve things?

 

[gea]

I supppose, you share your pool via NFS to ESXi as a shared datapool.
In this case ESXi requests sync write with a very bad write performance without a dedicated fast ZIL disk.

I would:
- disable sync property on the shared NFS filesystem and check performance
- avoid Raid-Z for datastores and prefer Raid-10 (with spindels)
or
- avoid spindels at all for a fast ESXi datastore (use SSD pools)
maybee a smaller but fast SSD mirror is a solution.

Using the m4 for l2arc readcache may help a little but mostly (sync) write is the problem


read http://napp-it.org/doc/manuals/benchmarks_5_2013.pdf
(especially the benchmarks with sync writes=always)

 

[orinoco]

Pools shared via iscsi, writeback cache is disabled, the other vms are happy just the ubuntu install running mysql percona that is performing poorly, will have a look at the benchmark docs shortly

 

[gea]

Pools shared via iscsi, writeback cache is disabled, the other vms are happy just the ubuntu install running mysql percona that is performing poorly, will have a look at the benchmark docs shortly

Enable write back cache.
If its much faster, you need a good ZIL

 

[dba]

When you say that performance is poor, what specific symptoms are you having? Poor select query performance, poor insert performance, or both?

Currently running several Vm's from my Napp-IT server, one of the machines is running mysql database is only 3gb although it does have 9 million rows, currently performance has been poor, I have tried off loading the DB to RAM, the Napp-IT box is an i5 with 16gb of RAM bare metal install not a VM, IBM m1015, all drives have a smart status of passed, 3 pools, VM's run from one pool, AFP from another pool and the DB sits on this pool, drives for this pool are Raidz1 Seagate 3TB ST3000DM001 x3 1 x WD WDC WD30EZRX-00M, VM pool is 4 x Samsung HD204UI, Vm's sit across 2 ESXi 5.1 servers both i7 2600 each with 32gb of RAM, I do have an SSD Crucial m4 256gb not currently deploed, I was going to install this in my macbook as was told it would quickly get destroyed using as cache, really looking for some ways to get performance back on track, any pointers greatly appreciated, drives in AFP remain largely idle apart from when it is serving movies and music


Thanks

 

[gea]

When you say that performance is poor, what specific symptoms are you having? Poor select query performance, poor insert performance, or both?

Every write.
On regular async writes, ZFS collects alle write requests for about 5s and writes them in a single large seqential fast write to disk. In case of a power failure these last 5s are lost.

If you disable the write back cache, you force sync write where each single write/transaction must be commited from disk until the next one can occur. Write performance can drop to 10% under this condition. You can lower this degration with a fast ZIL log device.

A easy way to check this behaviour on your pool
- create a volume based iSCSI target (create LU, target, target group and view)
- connect from Windows
- do a Crystalmark bench (free tool) with sync=always vs sync=disabled on your filesystem (or write back cache enabled/disabled)
check 4k QD32 (32 concurrent small writes) sync vs async