[go-nuts] MySQL performance in Go compared to Java [Benchmark]

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

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.

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.

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

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 ]

sooooo... I did my homework :D

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

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

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?

Right before the results chart:

*Go tip*

I tried wit this version:

commit 447c64a6f28a51227ceec3a057f38bba5fc416eb
Author: Julien Schmidt <[email protected]>
Date: Mon Mar 4 12:37:43 2013 +0100

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

I will try om linux/amd64.

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

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.

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 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.

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.