Lab Notes: Running a basic sysbench MySQL benchmark

Hello. It is 2 PM. This morning, I took Eamon to the transport museum. And now I am sat in the garden shed, tinkering. My goal for today is just to get sysbench working. I’ve been screwing around without much success, so far. Just now I remembered that I work best when I write. It prevents my thinking from getting all cluttered. And so that is what I will do. So, hello.

What I want to do is run a sysbench MySQL test. I want to establish baseline performance. This way, when later I run an experiment, I can see how it affected performance.

A good experiment needs to be repeatable, and so I think it makes sense to define exactly what I’m installing and running. A solid way to do this would be via Docker. The only issue is that I’m working on an M1 MacBook, and while there /is/ an ARM package for MySQL, I’m not sure about sysbench. Well, that’s the first thing to figure out, right? And if that doesn’t exist, maybe I can look into somehow running it with x64 emulation, if that even is a thing.

I do have an x64 computer lying around, but working on a remote machine is so annoying. I’d like to make it work on my local machine if possible.

Alright, so there is a sysbench image on Docker Hub. I pulled it and was able to run a file IO test: docker run severalnines/sysbench:latest sysbench --test=fileio --file-test-mode=seqwr run. The benchmark ran. The output doesn’t matter for now, since right now I’m more interested in why this worked at all. That image was built for x64. This warning made clear that something is happening to make the compatibility work: WARNING: The requested image's platform (linux/amd64) does not match the detected host platform (linux/arm64/v8) and no specific platform was requested. Clearly, there must be /some/ compatibility mode running.

Ah, yeah, Docker uses Rosetta 2 for x64 emulation. Well, that’s good.

Alright, now I want to try setting up a test against MySQL. I’ll do it in a docker-compose file.

For actually running MySQL, I already configured some Docker compose services a while ago. It’s not a secure configuration, but for local experiments it doesn’t matter.

I define an actual mysql service (I added a health check because I learned Docker compose supported them only recently, and wanted to play with them), as well as a shell.

version: '3'
services:
  mysql:
    image: mysql:8
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: mydb
    volumes:
      - ~/dev/labs/sysbench-mysql-basic/data:/var/lib/mysql
    ports:
      - 3306
    networks:
      - mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "mysql", "-u", "root", "-ppassword"]
      interval: 5s
      timeout: 1s
      retries: 5
      start_period: 15s

  shell:
    image: mysql:8
    command: ["mysql", "-h", "mysql", "-u", "root", "-ppassword"]
    depends_on:
      - mysql
    volumes:
      - ~/dev/labs/sysbench-mysql-basic/data:/var/lib/mysql
    networks:
      - mysql

The first time you run the mysql service against an empty data dir, MySQL will automatically do some init. This isn’t a MySQL feature; the Docker image (by default, but it can be turned off) runs a shell script that does some init.

Now I can try to add sysbench. But how do I use it? I haven’t found great documentation online, but the help dialog isn’t so bad.

docker run severalnines/sysbench:latest sysbench --help
Usage:
  sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help

...

The biggest reason I pasted the above output is to show that, if you find that you are running something in Docker, but its documentation is in the executable itself, well, nothing is stopping you from running it to print the documentation!

I think the test I want to run is called mysql. Sounds like I can get more details about this test thusly.

-> % docker run severalnines/sysbench:latest sysbench mysql help
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

qemu: uncaught target signal 11 (Segmentation fault) - core dumped

Welp. And I can’t even exit out of this process. I’ll have to kill it.

What the hell is happening here? The advice from Docker is to use arm64 images when possible 🤷‍♀️️. Great. I know that people have compiled sysbench for arm64 (example, for Ubuntu so maybe I can try using that in a custom Dockerfile.

This feels pretty silly, though. My goal is to run sysbench, not to figure out how to cross-compile things or how to deal with arm64 transition pains. Maybe I should just use that x64 computer after all.

And that’s exactly what I did. Thanks to that docker-compose file, and given how easy it is to connect VSCode to a remote instance, I was able to get up and running again quickly.

The next question is, how do I actually run a MySQL benchmark? After a little bit of searching, I learned that the database tests are Lua scripts in their own directory. These scripts can be invoked directly by name (e.g. sysbench oltp_read_write ...). You can always pass help to any test to get more info. (Note that this is different from --help long flag, which always prints the same info).

I’m running a shell in the sysbench container just to make this exploration faster. Looks like for the rw test, there are defaults for every value. So can I just run it? Well, of course not. I have to tell sysbench which user to use, etc.

  sysbench:
    image: severalnines/sysbench:latest
    command: ["sysbench", "--mysql-host=mysql", "--mysql-user=root", "--mysql-password=password", "oltp_read_write", "run"]
    networks:
      - mysql
    depends_on:
      - mysql

This doesn’t actually work 😥. FATAL: unable to connect to MySQL server on host 'mysql', port 3306, aborting...

Well, let me just get into a shell for the container and see if I can connect another way.

  sysbench-shell:
    image: severalnines/sysbench:latest
    command: ["bash"]
    networks:
      - mysql
    depends_on:
      - mysql

I’m not doing this via a Docker command because I’m too lazy to remember how to specify a network 😛.

I’m able to ping the MySQL container no problem.

root@acbca23a0e67:/# ping mysql
PING mysql (172.20.0.2) 56(84) bytes of data.
64 bytes from sysbench-mysql-basic-mysql-1.sysbench-mysql-basic_mysql (172.20.0.2): icmp_seq=1 ttl=64 time=0.492 ms

I’ll install mysql-client (which will let me open a MySQL shell without installing a bunch of other mysql stuff) and try to connect as a client, then.

root@acbca23a0e67:/# mysql -h mysql -u root -ppassword
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/x86_64-linux-gnu/mariadb18/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

Interesting. Well, that’s certainly something I just have to look up.

Ah, it seems that maybe the password plugin used by MySQL 8 is one that I have to install some extra stuff to support. Easy enough, then: I will manually create a test user for sysbench that uses a simpler plugin (by the way, it’s definitely a best practice to create users for your different applications – you can’t run out of users, and it makes it possible to tune permissions and figure out the source of various transactions).

So, I created the user:

CREATE USER IF NOT EXISTS 'sbtest'@'%' IDENTIFIED BY 'password';

But, I’m still unable to log in with the same error. Alright, let me go back and see what this error means. Actually, I am catching myself in a pattern I sometimes get caught in: briefly read about an issue without truly understanding what’s going on. So… what’s going on? To the CREATE USER documentation. What is this sha2 thing?

Alright, so caching_sha2_password is an authentication plugin. The docs say that when you don’t specify the authentication plugin, the default one is used. caching_sha2_password must be the default. How can I verify this? Ah, by looking at default_authentication_plugin variable, which is, indeed, by default caching_sha2_password in MySQL 8. So I need to use a different plugin for the sbtest user.

Ah, still get the same error. But it turns out that there’s an entire page dedicated to caching_sha2_password compatibility. Specifically, it mentions that this error can be thrown even if the user is not authenticated using caching_sha2_password.

The issue is that the sysbench image I’m using uses a sysbench package which is in turn statically linked to an outdated MySQL client library. (Reference). Once again, I am stuck with compilation crap! In this case, I think what I will try to do is not use this authentication plugin by default. But, it looks like sooner or later I’m going to need to learn how to compile things for myself.

For now, I will force MySQL not to use caching_sha2_password by default, as recommended by the docs.

[mysqld]
default_authentication_plugin=mysql_native_password

Ooh, cool. Learned something from the my.cnf from the image. If you do this:

!includedir /etc/mysql/conf.d/

Then you can stack extra config on top by defining it in that directory. Very useful in this specific case as it allows me to set default_authentication_plugin without wiping out the rest of my.cnf defined in the image.

OK, now I’m no longer getting that error. And yet, I’m still seeing an issue.

FATAL: unable to connect to MySQL server on host 'mysql', port 3306, aborting...
FATAL: error 1044: Access denied for user 'sbtest'@'%' to database 'sbtest'

OK, I’ll create that database.

No, still the same issue. I’m guessing the the sbtest user needs some privileges.

I think this should work.

GRANT ALL ON sbtest.* TO 'sbtest'@'%';

Yay!

FATAL: mysql_stmt_prepare() failed
FATAL: MySQL error: 1146 "Table 'sbtest.sbtest1' doesn't exist"

I’m doing sysbench ... run. I think I need to first do sysbench ... prepare.

Yay! After preparing, the tests run.

Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            13006
        write:                           3716
        other:                           1858
        total:                           18580
    transactions:                        929    (92.76 per sec.)
    queries:                             18580  (1855.13 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0125s
    total number of events:              929

Latency (ms):
         min:                                    6.07
         avg:                                   10.76
         max:                                   40.59
         95th percentile:                       16.41
         sum:                                 9994.04

Threads fairness:
    events (avg/stddev):           929.0000/0.00
    execution time (avg/stddev):   9.9940/0.00

These numbers don’t mean too much to me – I think it’d be more interesting to see these if I set specific cpu and memory limit for MySQL, so that I can say “given such-and-such resource limitations, this is the baseline performance”. That said, I’m curious what the consistency between the tests is. Most importantly, I’ll look at the 95th percentile.

Upon second run, p95 didn’t change at all! Nice. I suppose that makes sense, because I’m running this in a VPC with almost nothing else running (I lied earlier when I said I was going to use an x86 computer lying around).

Well, that’s enough for today.

Dmitry Mazin

"cyberdemon.org is a cool domain"

Lab Notes: Running a basic sysbench MySQL benchmark