So, on a large IBM POWER8 system I was recently running the newly coined “yesmark” benchmark, which is best translated as this:

Benchmark (N for concurrency): for i in {1..N}; do yes "DO 0;" | mysql > /dev/null & done
Live results: mysqladmin -ri 1 extended-status | grep Questions

Which sounds all fun until you realize that it’s *amazingly* close in results to a sysbench point select benchmark these days (well, with MySQL 5.7.7).

Since yesmark doesn’t use InnoDB though, MariaDB is back in the game.

I don’t think it matters between MariaDB and MySQL at this point for yesbench. With MySQL in a KVM guest on a shared 2 socket POWER8 I could get 754kQPS and on a larger system, I could get 1.3 million / sec.

1.3 Million queries / sec is probably the highest number anybody has ever seen out of MySQL or MariaDB, so …

There was a point a few years ago where Sun could have had the next generation UNIX filesystem. It was in Solaris (and people were excited), there was a port to MacOS X (that was quite exciting for people) and there was a couple of ways to run it on linux (and people were excited). So… instead of the fractured landscape of ext3, HFS+ and (the various variations of) UFS we could have had one file system that was common between all of the commonly used UNIX-like variants. Think of being able to use a file system on a removable drive that isn’t FAT and being able to take it from machine to machine (well… Windows would be a problem, but it always is).

There was some really great work done in OpenSolaris with integration between the file manager and ZFS snapshots (a slider bar to browse the history of a directory, an idea I’ve championed for over a decade now, although the Sun implementation was likely completely independently developed). …

In an email disscussion for pre-allocating binlogs for MySQL (something we’ll likely have to do for Drizzle and replication), Yoshinori brought up the excellent point of that in some situations you don’t want to be doing zero-fill as getting up and running quickly is the most important thing.

So what does Linux do? Does it zero-fill, or behave sensibly and pre-allocate quickly?

Let’s look at hte kernel:

Inside the fallocate implementation (fs/open.c):

if (inode->i_op->fallocate)
ret = inode->i_op->fallocate(inode, mode, offset, len);
else
ret = -EOPNOTSUPP;

and for ext4:
/*
* currently supporting (pre)allocate mode for extent-based
* files _only_
*/
if (!(EXT4_I(inode)->i_flags & EXT4_EXTENTS_FL))
return -EOPNOTSUPP;

XFS has always done fast pre-allocate, so it’s not a problem there and the only other …

I can’t remember the last time i used default mkfs or mount options… oh yeah, that’s right - by accident.

Anyway… I did a little experiment today.

The filesystem is my laptop /home - XFS, 100GB, 95% used (so 5-6GB free), rather aged. This is where a lot of my MySQL development is done. Mkfs options: 128MB log, version2 log. Mount options: logbufs=8, logbsize=256k. All of this geared towards increasing metadata performance.

Why metadata performance? well… source code trees are a lot of metadata :)

So, let’s try some things: cloning a repository and then removing the repository.

Two variables are being tested: mounting the file system with nobarrier (or barrier, the default). Write barriers tell the disk to ensure write order to the platter when write cache is in use. Also testing disabling (or enabling, the default) the disk write cache.

NOTE: the last option which …

* dchinner hands MacPlusG3 a bigger knife….

(on #xfs yesterday)

Inspired by PeterZ’s Opening Tables scalability post, I decided to try a little benchmark. This benchmark involved the following:

• Create 50,000 tables

• CREATE TABLE t{$i} (i int primary key)

I wanted to test file system impact on this benchmark. So, I created a new LVM volume, 10GB in size. I extracted a ‘make bin-dist’ of a recent MySQL 5.1 tree, did a “mysql-test-run.pl –start-and-exit” and ran my script, timing real time with time.

For a default ext3 file system creating MyISAM tables, the test took 15min 8sec.

For a default xfs file sytem creating MyISAM tables, the test took 7min 20sec.

For an XFS file system with a 100MB Version 2 log creating MyISAM tables, the test took 7min …

I’ve talked about disk space allocation previously, mainly revolving around XFS (namely because it’s what I use, a sensible choice for large file systems and large files and has a nice suite of tools for digging into what’s going on).Most people write software that just calls write(2) (or libc things like fwrite or fprintf) to do file IO - including space allocation. Probably 99% of file io is fine to do like this and the allocators for your file system get it mostly right (some more right than others). Remember, disk seeks are really really expensive so the less you have to do, the better (i.e. fragmentation==bad).

I recently (finally) wrote my patch to use the xfsctl to get better allocation for NDB disk data files (datafiles and undofiles).
patch at:
http://lists.mysql.com/commits/15088

Arjen’s MySQL Community Journal - HyperThreading? Not on a MySQL server…

I blame the Linux Process Scheduler. At least it’s better than the earlier 2.6 days where things would get shunted a lot from one “cpu” to the other “cpu” for no real reason.

Newer kernel verisons are probably better… but don’t even think of HT and pre-2.6 - that would be funny.

DaveM talks about Ingo’s new SMP lock validator for linux kernel

A note reminding me to go take a look and see what can be ripped out and placed into various bits of MySQL and NDB. Ideally, of course, it could be turned into a LD_PRELOAD for pthread mutexes.

Anybody who wants to look deeper into it before I wake up again is welcome to (and tell me what they find)

Beat Vontobel blogs about “fuþark: The silence of futhark and the state of the dolphin” which is basically about how he’s found that the 5.0.20 release of MySQL is when the 5.0 release is really starting to shine.

This confirms my theory (that I’ve had for quite a while now… like years) that a software release is never really mature until it hits about .20 (that’s dot twenty, not dot two).

When something reaches .10 (dot ten) it’s no longer going to be annoying for most uses, but .20 means that you’re going to be happy. Don’t ask me really why this is the case, but it is.

Think about the 2.6 kernel (yes, Linux Kernel - honestly, you think i was talking about something else?). At about …