MySQL performance in Go compared to Java [Benchmark]

[Julien Schmidt]

I wrote a simple (synthetic) benchmark to keep track how the MySQL performance in Go (database/sql package) improves by time.

Right now the benchmark is very simple but I'll keep extending it.

I compared the performance of the two MySQL drivers "Go-MySQL-Driver" ( https://github.com/Go-SQL-Driver/MySQL ) and the "godrv" of "mymysql" ( https://github.com/ziutek/mymysql ).

But I also wrote an equivalent (at least I tried) benchmark in Java7 to compare the performance to a mature library like JDBC and a highly optimized driver like "MySQL Connector/J".

Be invited to post your own results, extend / improve the benchmark or even help improving the performance in go.

Especially for multiple row queries (SimpleQuery / PreparedQuery) i wonder how they could be improved. I'm not sure why go performs so bad compared to Java there.

https://github.com/Go-SQL-Driver/SQL-Benchmark

[John Nagle]

Hm. Did you ever commit after all those SELECTs? When you do
a SELECT with MySQL using InnoDb, you're within a transaction, and
the database will not visibly change underneath you. "The default
transaction level for MySQL InnoDB is REPEATABLE READ".
Each time you do another SELECT within a transaction, the
database either has to lock the result set or make a temporary
copy of the rows you just read. So when you do 10,000 SELECT
operations, each of which returns 100 rows, you've forced
MySQL to make temporary copies of 10,000,000 rows.
That can slow down the database engine.

I think Java' driver has autocommit=true by default, and Go's
driver has it set to false. Set it explicitly in both test
cases and see what happens.

John Nagle

[Julien Schmidt]

autocommit is on by default, but you are right, this should be set explicitly.

The multi-row-mystery seems to be solved: While Go-MySQL-Driver reads just one row on every Rows.Next() from the buffered stream, MySQL Connector/J seems to use some sort of prefetching. If you turn this behavior off by adding "useReadAheadInput=false” to the DSN the performance drops to roughly 2100 queries/second. I'll try so add a similar (of course configurable) behavior to Go-MySQL-Driver.

[Julien Schmidt]

Mh no, no prefetching. I just browsed the source. "ReadAheadInputStream" is just a buffered stream like Go's bufio, but it claims to be non-blocking.

[ziutek]

Thanks for your work!

Your benchmark has clearly showed that I need to optimize mymysql. The
optimization was:
1. Reducing a number of allocations in protocol handling code.
2. Remove using of reflection in godrv.
3. Implement driver.Execer

After that Go-MySQL-Driver and mymysql/godrv benchmarks are more
similar.

There are my results (queries/second):

SimpleQuery
godrv: 2017 Go-MySQL: 1764

PreparedQuery
godrv: 2527 Go-MySQL: 1989

AutoQueryRow
godrv: 5514 Go-MySQL: 5802

SimpleQueryRow
godrv: 5668 Go-MySQL: 5910

PreparedQueryRow
godrv: 12491 Go-MySQL: 13201

SimpleExec
godrv: 16346 Go-MySQL: 16120

PreparedExec:
godrv: 17337 Go-MySQL: 16459

[Julien Schmidt]

Wow definitely improved!

Still not beating Go-MySQL-Driver on my setup though (usual best-of-2 mode). The results are below.

My turn to optimize now ;)

The JDBC results for (Simple|Prepared)Query made me think. I'm also reworking my stream I/O right now.

I'll run another benchmark-round soon. Feel free to contribute to the benchmark suite.

SimpleQuery:

Go-MySQl-Driver 3923 | mymysql 3216 queries/second

PreparedQuery:

Go-MySQl-Driver 3716 | mymysql 4122 queries/second <- wow!

AutoQueryRow:

Go-MySQl-Driver 7748 | mymysql 6855 queries/second

SimpleQueryRow:

Go-MySQl-Driver 15075 | mymysql 6970 queries/second

PreparedQueryRow:

Go-MySQl-Driver 15539 | mymysql 14238 queries/second

SimpleExec:

Go-MySQl-Driver 29238 | mymysql 27306 queries/second

PreparedExec:

Go-MySQl-Driver 30384 | mymysql 28295 queries/second

Log: 

*************************************************************

   BENCHMARKING mymysql godrv [run 1]

*************************************************************

-------------------------------------------------------------

   [10000 * Query 100 Rows]

-------------------------------------------------------------

SimpleQuery: 3.2581864s [ 3069 queries/second ]

PreparedQuery: 2.4971428s [ 4005 queries/second ]

-------------------------------------------------------------

   [100 * QueryRow] * 1000

-------------------------------------------------------------

AutoQueryRow: 14.5888344s [ 6855 queries/second ]

SimpleQueryRow: 14.3458205s [ 6971 queries/second ]

PreparedQueryRow: 7.0234017s [ 14238 queries/second ]

-------------------------------------------------------------

   [100000 * Exec]

-------------------------------------------------------------

SimpleExec: 3.6622094s [ 27306 queries/second ]

PreparedExec: 3.5452028s [ 28207 queries/second ]

*************************************************************

   BENCHMARKING mymysql godrv [run 2]

*************************************************************

-------------------------------------------------------------

   [10000 * Query 100 Rows]

-------------------------------------------------------------

SimpleQuery: 3.1091778s [ 3216 queries/second ]

PreparedQuery: 2.4261387s [ 4122 queries/second ]

-------------------------------------------------------------

   [100 * QueryRow] * 1000

-------------------------------------------------------------

AutoQueryRow: 14.7788453s [ 6766 queries/second ]

SimpleQueryRow: 14.3478206s [ 6970 queries/second ]

PreparedQueryRow: 7.1074065s [ 14070 queries/second ]

-------------------------------------------------------------

   [100000 * Exec]

-------------------------------------------------------------

SimpleExec: 3.776216s [ 26482 queries/second ]

PreparedExec: 3.5342021s [ 28295 queries/second ]

[Julien Schmidt]

sooooo... I did my homework :D

Current results of March 02, 2013 are online: https://github.com/Go-SQL-Driver/SQL-Benchmark

[gauravk]

It will be interesting to know memory usage of Go and Java code.

[ziutek]

I've tried run benchmarks with go version devel +1d5a80b07916 but your driver returns an error:

*************************************************************
BENCHMARKING Go-MySQL-Driver [run 1]

*************************************************************

-------------------------------------------------------------
[10000 * Query 100 Rows]
-------------------------------------------------------------

SimpleQuery: 2.245029758s [ 4454 queries/second ]
PreparedQuery: 2.040614795s [ 4900 queries/second ]

-------------------------------------------------------------
[100 * QueryRow] * 1000
-------------------------------------------------------------

[MySQL] 2013/03/04 21:11:08 packets.go:43: Malformed Packet
panic: sql: no rows in result set

Is it problem with your driver or bug known in this version of Go? Which Go version do you use for tip benchmarks?

[Julien Schmidt]

Right before the results chart:

Go tip

• reverted changeset ddb9e6365e57 because of Issue #4902 
  
• go version devel +646803253bb4 Tue Feb 26 09:51:33 2013
  

;)

Did you use the latest version of the driver?

[ziutek]

I tried wit this version:

commit 447c64a6f28a51227ceec3a057f38bba5fc416eb
Author: Julien Schmidt <git...@julienschmidt.com>
Date: Mon Mar 4 12:37:43 2013 +0100

On 4 Mar, 21:53, Julien Schmidt <g...@julienschmidt.com> wrote:
> Right before the results chart:
>

> *Go tip*
>
>
>
> >    - reverted changeset ddb9e6365e57<http://code.google.com/p/go/source/detail?r=ddb9e6365e570c2619d884271...> because
> >    of Issue #4902 <http://golang.org/issue/4902>
> >    - go version devel +646803253bb4 Tue Feb 26 09:51:33 2013

[ziutek]

The same problem with go version devel +646803253bb4 Tue Feb 26
09:51:33 2013 -0800 linux/386.

I will try om linux/amd64.

[Julien Schmidt]

Did you undo http://code.google.com/p/go/source/detail?r=ddb9e6365e570c2619d88427176a465e8b76b4aa ? Prepared statements are currently broken in go tip.

Also I pushed another driver update. There might be an error on writing a packet which was masked:

https://github.com/Go-SQL-Driver/MySQL/commit/5e8c2012e30b99afd4e6b82542c14938bdfdc170#L0R69

[Sugu Sougoumarane]

There is a series of CLs in the works by Dmitry (on scheduler and netpoller). I think they'll have a big impact on some of these numbers.

[steve wang]

Schmidt,

I plan to use go-sql-driver in my production environment in a few weeks.

Is there a stable release avaible now?

As the product is due to be dilivered to my clients, so I hope that the version of go-sql-driver is as stable as possible.

Could you please give me some pieces of advice?

Thanks in advance.  

[Julien Schmidt]

Schmidt,

Julien please, Schmidt is my surname ;) 

I rewrote a lot code in the last days. Although it passed my usual test stack I would recommend to wait some time ("a few weeks" should be enough) to feel certain that no bug slipped in.
In general I consider the current code base as safe for production use. I use it personally in production without any problems. But it's still tagged as "beta" because it's not feature complete yet.

[steve wang]

Julien，

Sorry, I thought it is polite to call you by your family name.

I will wait until everything is ready.
Thanks for your.advice.