So what does this all mean? Is it a regression in Linux 3?

Or were previous versions not actually blocking while sync was called?

I think the latter. Probably the filesystems didn't sync as expected in previous versions of Linux. I was surprised by the numbers, but according to Wikipedia a modern 7200RPM SATA disk maxes out at around 75-100 IOPS. An average run of my dd script is: # dd if=/dev/zero of=test.bin bs=512 count=128 oflag=sync 128+0 records in 128+0 records out 65536 bytes (66 kB) copied, 4.19463 s, 15.6 kB/s So that's 128 sync writes in 4 seconds, or 32 writes per second. Every time you write the 512 byte chunk to the test.bin file, there will also be a metadata update, and even if that was only a single write operation we are now at 64 IOPS, which is within the same magnitude as the 75-100 figure given by Wikipedia. Small synchronous writes suck. I think the suckiness increases even more with 512 byte writes on 4k sectors because of the read-modify-write situation. I'm hoping that ceph with the journal on an SSD will improve my situation somewhat... my SSD's arrive tomorrow. James

On 30/04/13 11:09, Jason White wrote:

James Harper <james.harper@bendigoit.com.au> wrote:

...

I'm hoping that ceph with the journal on an SSD will improve my situation somewhat... my SSD's arrive tomorrow.

I found out that to take full advantage of a modern SSD, it is necessary to have a machine with a SATA 3.0 controller capable of a 6 GBPS transfer rate.

this rules out my current desktop and laptop systems.

Not really.. You'll still reap all the benefits of instant seek times and huge iops performance. You just will cap out at ~300mbyte/sec transfers instead of ~600mbyte/sec, but honestly, how often does that matter?

Hi all In my work (Open Query - MySQL and MariaDB database backed infrastructure) we tend to find that it's not data transfer rate or RPMs that's the biggest hindrance. On a disk or array, it's seektime, and with SANs it's latency. The disk story is because seektime is still counted in milliseconds, and rarely do you transfer a lot of data in one chunk. More commonly you'll read or write smaller chunks in different locations. Thus you're bound by the seek speed. A SAN is only fast when you request big chunks of data, or request smaller chunks that have in some recent past already been accessed (by you or another host). Otherwise you're not using its cache and thus it suffers from the same issue as any disk or array. (FYI, MySQL/MariaDB of course keep lots of data/index info in memory so when they read disk data it won't be in the cache - we tend to make SAN people cry as the *effective* performance for these tasks is just so dismal). SSD is of course very nice as it gets rid of the seeks. You can also use SSD as an intermediate caching mechanism, as it's persistent. There are now some RAID controllers available that implement this, using SSD as well as RAM in multiple layers of caching. This might come in handy when you need more storage. We sometimes play with SATA RAID rather than SAS for fast yet cost-efficient storage. SAS is freaking expensive for less space. SAS has a longer command queue but when you stick a RAID controller in front of it that becomes irrelevant as the controller will work that out for you. No matter which physical device you use, or filesystem, you'll find that setting 'noatime' (in /etc/fstab) helps. You generally don't need to know the last access time (unless it's for some strict read-access security auditing requirement) and it'll prevent at least one and possibly two seeks. Cheers, Arjen. -- Exec.Director @ Open Query (http://openquery.com) MariaDB/MySQL services Sane business strategy explorations at http://upstarta.com.au Personal blog at http://lentz.com.au/blog/

Craig Sanders <cas@taz.net.au> writes:

it's only safe to turn barriers off if disable any write-caching in the drive OR if you have a non-volatile write cache (e.g. hardware raid, something like bcache with an SSD, or ZFS with an SSD for ZIL), ^^^^

Not just hw raid, but hardware raid with a BBU or equivalent. I've seen at least one case where the BBU was an "optional extra" to make the base price look more reasonable, and $manager didn't notice it was a separate line item until it was too late...

Thanks everyone. You've confirmed that what I'm seeing isn't something strange with my servers, even if it isn't exactly what I expected. James

Hi James

My strace of mysql showed the behaviour:

write(512 bytes) fsync() write(1024 bytes) fsync() and so on

Indeed, that'll be writes to the ib_logfile* and/or binlog. Essentially sequential writes (but that's only useful if they're on separate spindles). For any commit (or single write command in autocommit mode), MySQL (or MariaDB) have to fsync to the InnoDB transaction logfile as well as the binary log (if enabled - for replication, and point-in-time recovery). So that's two fsyncs per transaction commit.

and using dd to write 512 byte chunks with O_SYNC is a good approximation of this and very consistent with the behaviour I was seeing from mysql. Initially I was looking for general performance problems but in the end it is this very specific case, and I have now proven it's not specific to this server.

The reason why mysql suddenly started doing these these very small sync writes is still a mystery though. Other backup servers don't show this behaviour.

You can approximate MySQL's different access methods (sequential for logs, random access for tablespace) using the hdlatency tool (on Launchpad). It's a bit of C without any external dependencies. Run with --quick. It'll test the different access methods similar to the way MySQL/MariaDB use, and also check with direct I/O which is generally what you'd use with InnoDB (flush-method=O_DIRECT). Direct I/O tends to be faster, except on some SANs and other attached storage. Again we use hdlatency to just see which one is faster and configure accordingly. hdlatency is also known as "the tool that makes SAN operators cry" ;-) Generally a desktop SATA comes up with better numbers than the average SAN... I kid you not. Cheers, Arjen. -- Exec.Director @ Open Query (http://openquery.com) MariaDB/MySQL services Sane business strategy explorations at http://upstarta.com.au Personal blog at http://lentz.com.au/blog/