TipsonbenchmarkingGo + MySQL_MySQL

TipsonbenchmarkingGo + MySQL We just released, as an open source release, our newpercona-agent Https://github.com/percona/percona-agent), The agent to work withPercona Cloud Tools. This agent is written inGo.

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 toregister nowand 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 schedine. 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 againstPercona Server. Of course it adds some complication as there are more moving parts(I. e. go scheddriver, go SQL driver, MySQL by itself), But it just makes the experiment more interesting.

Source code of my benchmark: Go-pk-example:
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 isGo-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 shoshould take into account it is a proper setting
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 commandruntime.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...

rows, err := db.Query("select k from sbtest"+strconv.Itoa(tab+1)+" where id = "+strconv.Itoa(i))

Rows, err: = db. Query ("select k from sbtest" + strconv. Itoa (tab + 1) + "where id =" + strconv. Itoa (I ))

... Very soon we ran out of TCP ports. Apparently "database/SQL" and Go-MySQL-Driver and its smart connection pool createsNew connection for each query. I can explain why this happens, but using the following statement:

'db.SetMaxIdleConns(10000)'

& Apos; db. SetMaxIdleConns (10000) & apos )'

Helps(I hope somebody with "database/SQL" knodge DGE 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 inPercona Cloud Tools)

MySQL Throughput(User threads are increasing from 1 to 64)

CPU Usage(User threads are increasing from 1 to 64)

I wocould 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)

CPU Usage(Sysbench, user threads are increasing from 1 to 64)

From the graphs(This is what I like them ),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:

MySQL Throughput, q/s (more is better)Threads| Go-MySQL | sysbench1|13,189|16,7652|26,837|33,5344|52,629|65,9438|95,553| 116,95316| 146,979| 182,78124| 169,739| 231,89532| 181,334| 245,93948| 198,238| 250,49764| 207,732| 251,972

MySQLThroughput, q/s (moreisbetter) Threads | Go-MySQL | sysbench 1 | 13,189 | 16,765 2 | 26,837 | 33,534 5 | 52,629 | 65,943 Eight | 95,553 | 116,953 16 | 146,979 | 182,781 24 | 169,739 | 231,895 32 | 181,334 | 245,939 8 | 198,238 | 250,497 64 | 207,732 | 251,972

(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 tive as plan raw C(Sysbench), As it seems it has ds some extra CPU time in system CILS.

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