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/ _______________________________________________ luv-main mailing list [email protected] http://lists.luv.asn.au/listinfo/luv-main
