October 23, 2014

Tips on benchmarking Go + MySQL

We just released, as an open source release, our new percona-agent (https://github.com/percona/percona-agent), the agent to work with Percona Cloud Tools. This agent is written in Go.

I will give a webinar titled “Monitoring All MySQL Metrics with Percona Cloud Tools” on June 25 that will cover the new features in percona-agent and Percona Cloud Tools, where I will also explain how it works. You are welcome to register now and join me.

There will be more posts about percona-agent, but in the meantime I want to dedicate this one to Go, Go with MySQL and some performance topics.

I have had an interest in the Go programming language for a long time, but in initial versions I did not quite like the performance of the gorountine scheduler. See my report from more than two years ago on runtime: reduce scheduling contention for large $GOMAXPROCS.

Supposedly this performance issue was fixed in Go 1.1, so this is a good time to revisit my benchmark experiment.

A simple run of prime or fibonachi numbers calculation in N threas is quite boring, so I am going to run queries against Percona Server. Of course it adds some complication as there are more moving parts (i.e. go scheduler, go sql driver, MySQL by itself), but it just makes the experiment more interesting.

Source code of my benchmark: Go-pk-bench:
This is probably not the best example of how to code in Go, but that was not the point of this exercise. This post is really about some tips to take into account when writing an application in Go using a MySQL (Percona Server) database.

So, first, we will need a MySQL driver for Go. The one I used two years ago (https://github.com/Philio/GoMySQL) is quite outdated. It seems the most popular choice today is Go-MySQL-Driver, and this is the one we use for internal development. This driver is based on the standard Go “database/sql” package. This package kind of provides a standard Go-way to deal with SQL-like databases. “database/sql” seems to work out OK, with some questionable design decisions as for my taste. So using “database/sql” and Go-MySQL-Driver you will need to deal with some quirks like almost unmanageable connection pool.

The first thing you should take into account it is a proper setting of
runtime.GOMAXPROCS().

If you do not do that, Go scheduler will use the default, which is 1. That binary will use one and only 1 CPU (so much for a modern concurrent language).

The command runtime.GOMAXPROCS(runtime.NumCPU())
will prescribe to use all available CPUs. Always remember to use this if you care about multi-threaded performance.

The next problem I faced in the benchmark is that when I ran queries in a loop, i.e. to repeat as much possible…

… very soon we ran out of TCP ports. Apparently “database/sql” and Go-MySQL-Driver and its smart connection pool creates a NEW CONNECTION for each query. I can explain why this happens, but using the following statement:

helps (I hope somebody with “database/sql” knowledge will explain what it is doing).

So after these adjustments we now can run the benchmark, which by query you see is quite simple – run primary key lookups against Percona Server which we know scales perfectly in this scenario (I used sysbench to create 64 tables 1mln rows each, all this fits into memory). I am going to run this benchmark with 1, 2, 4, 8, 16, 24, 32, 48, 64 user threads.

Below you can see graphs for MySQL Throughput and CPU Usage (both graph are built using new metrics graphing in Percona Cloud Tools)

MySQL Throughput (user threads are increasing from 1 to 64)
mysql-go

CPU Usage (user threads are increasing from 1 to 64)
Cpu-go

I would say the result scales quite nicely, at least it is really much better than it was two years ago. It is interesting to compare with something, so there is a graph from an identical run, but now I will use sysbench + lua for main workload driver.

MySQL Throughput (sysbench, user threads are increasing from 1 to 64)
mysql-sysbench

CPU Usage (sysbench, user threads are increasing from 1 to 64)
cpu-sysbench

From the graphs (this is what I like them for), we can clearly see increases in User CPU utilization (and actually we are able to use CPUs on 100% in user+system usage) and it clearly corresponds to increased throughput.

And if you are a fan of raw numbers:

(one with a knowledge of Universal Scalability Law can draw a prediction till 1000 threads, I leave it as a homework)

So, in conclusion, I can say that Go+MySQL is able to show decent results, but it is still not as effective as plan raw C (sysbench), as it seems it spends some extra CPU time in system calls.

If you want to try these new graphs in Percona Cloud Tools and see how it works with your system – join the free beta!

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. Pascal says:

    “(so much for a modern concurrent language)”
    See http://blog.golang.org/concurrency-is-not-parallelism

  2. Pascal,

    I do not see a contradiction here with the link you provided.
    Excerpt: “..concurrency is the composition of independently executing processes..”
    If I have 24 independent CPU cores, why independently executing processes can’t use an individual core per process ?

  3. Pascal says:

    They can – if you enable it. Depending on the program using multiple cpu cores may actually slow it down. So I think using 1 core as a save default (and let the programmer think about what he wants/try out what works best) is a good decision.

  4. Erik St. Martin says:

    ” ‘db.SetMaxIdleConns(10000)’ (I hope somebody with “database/sql” knowledge will explain what it is doing).”

    The connection pool that database/sql maintains for you is based off the number of connections that sit idle within it, when a new connection is requested from the pool, it first checks the pool for any available connections, if one is found it’s removed from the pool and returned. If one is not found it creates a new connection and returns it.

    SetMaxIdleConns sets the maximum number of idle connections that can be sitting in the pool at any given time, so when your connection is handed back to the pool if this capacity is reached (I believe the default is 2), the connection is closed.

    The reason that it’s helping is that more connections are making their way back into the pool instead of the default 2, which make them available for subsequent requests rather than being dropped on the floor and ending up in a TIME_WAIT state.

    Go 1.2 introduced SetMaxOpenConns() which is to control the other side of the equation, capping the maximum number of connections that can be open at any given time, this will cause the database connection pool to block before opening a new connection if one isn’t in the pool, and this count has been reached.

  5. Pascal says:

    Slightly OT: For everyone who wants to get started with database/sql in go (like myself right now), http://go-database-sql.org/ seems to be a good resource to get started.

  6. Bob says:

    You are referring to threads. Concurrency is not based on threads in Go (hence http://blog.golang.org/concurrency-is-not-parallelism).

Speak Your Mind

*