Dean Ellis

I have been using Google’s Go programming language for a couple of years and basically fell in love with it. About a year ago I started using it for almost all of my MariaDB and MySQL development, and pretty much everything I do unless it involves the python pandas library (a subject for a separate blog, perhaps).

While I could gush endlessly about Go (golang), the documentation sums things up quite nicely: “Go is expressive, concise, clean, and efficient. Its concurrency mechanisms make it easy to write programs that get the most out of multicore and networked machines, while its novel type system enables flexible and modular program construction. Go compiles quickly to machine code yet has the convenience of garbage collection and the power of run-time reflection. It's a fast, statically typed, compiled language that feels like a dynamically typed, interpreted language.”

I won’t delve deeply into the language. Do please read more about it here.

For MariaDB and MySQL development, I currently use the Go-MySQL-Driver found here.

If you already have Go installed, you can easily fetch that library with “go get”:

go get github.com/go-sql-driver/mysql

Let’s look at a (very) simple program demonstrating usage, and find out whether the driver supports MariaDB 10. Pardon me for stripping out the error handling:

skysql:dellis$ cat gomaria.go

package main

import (
	"fmt"
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// Create the database handle, confirm driver is present
	db, _ := sql.Open("mysql", "dellis:@/shud")
	defer db.Close()

	// Connect and check the server version
	var version string
	db.QueryRow("SELECT VERSION()").Scan(&version)
	fmt.Println("Connected to:", version)
}

skysql:dellis$ go build gomaria.go
skysql:dellis$ ./gomaria
Connected to: 10.0.11-MariaDB

Success!

One thing of interest is that the variable “db” references a database handle, rather than an actual connection to the database server. In the example above, a connection to the database isn’t actually created until we call db.QueryRow().

I have not explicitly opened or closed an individual connection in this example, because Go’s SQL package automatically handles connection pooling. Let’s give that a (very silly) test, again stripping out the error handling:

skysql:dellis$ cat gomaria.go

package main

import (
	"sync"
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// Create the database handle, confirm driver is present
	db, _ := sql.Open("mysql", "dellis:@/shud")
	defer db.Close()

	// Test several connections
	var wg sync.WaitGroup
	for i := 0; i 

In theory, then, this will create ten goroutines (like and unlike threads, or, again quoting the documentation, “a function executing concurrently with other goroutines in the same address space”, which are multiplexed onto multiple system threads), each of which will try to connect to MariaDB and execute SELECT SLEEP(10). The main() function will wait until each of these has completed (via sync.WaitGroup), giving me time to check the processlist to see what happened.

Notice that I pass the database handle to my function but once again do not explicitly open or close connections.

Let’s try it:

skysql:dellis$ ./gomaria

MariaDB [shud]> SHOW PROCESSLIST;
+----+--------+-----------------+------+---------+------+------------+------------------+----------+
| Id | User   | Host            | db   | Command | Time | State      | Info             | Progress |
+----+--------+-----------------+------+---------+------+------------+------------------+----------+
|  3 | dellis | localhost:57547 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
|  4 | dellis | localhost:57548 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
|  5 | dellis | localhost:57549 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
|  6 | dellis | localhost:57550 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
|  7 | dellis | localhost:57551 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
|  8 | dellis | localhost:57552 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
|  9 | dellis | localhost:57553 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
| 10 | dellis | localhost:57554 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
| 11 | dellis | localhost:57555 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
| 12 | dellis | localhost:57556 | shud | Query   |    3 | User sleep | SELECT SLEEP(10) |    0.000 |
| 13 | dellis | localhost       | shud | Query   |    0 | init       | SHOW PROCESSLIST |    0.000 |
+----+--------+-----------------+------+---------+------+------------+------------------+----------+

And again: Success! We opened ten connections, and each of those issued their queries, with the main() function awaiting completion.

If you were to “sleep” this program with time.Sleep() after the goroutines complete, you might see some idle connections to the database remaining, held open by the connection pool. That can be controlled via db.SetMaxIdleConns() and db.SetMaxOpenConns().

There is a lot more to the topic of using MariaDB and MySQL with Go, and if you are interested I recommend visiting VividCortex’s tutorial available here. The site covers a lot of ground, including some of the limitations you may encounter. It is a great place to start.

With a language so very well designed for concurrency, it may be interesting to see whether the Go MySQL driver could take advantage of MariaDB’s non-blocking API as we see in the mariasql binding for Node.js, but that goes well beyond my subject today.

Thanks for reading.

Tags: 

About the Author

Dean Ellis's picture
Dean Ellis

Dean Ellis is Vice President of Technical Support at SkySQL.

Dean was hired by the original MySQL AB corporation as a support engineer and developer in 2003, after some years of providing MySQL-related help to the community via the freenode IRC network. He began using MySQL in 1999 while working as a software developer and DBA, eventually deploying MySQL for web and enterprise applications alongside Oracle, Microsoft SQL Server and Lucene.

He moved into management and took over responsibility for the global support organization after MySQL AB was acquired by Sun Microsystems, retaining that role after Oracle Corporation acquired Sun. Dean left Oracle to join SkySQL in 2010, with the goal of creating a new and better home for support customers and engineers.

Dean now leads the global SkySQL Support organization from Nashville, Tennessee.

zhifenghu

The source code is missing -------------------------------------- skysql:dellis$ cat gomaria.go package main import ( "sync" "database/sql" _ "github.com/go-sql-driver/mysql" ) func main() { // Create the database handle, confirm driver is present db, _ := sql.Open("mysql", "dellis:@/shud") defer db.Close() // Test several connections var wg sync.WaitGroup for i := 0; i --------------------------------------------- there should be more code here.

Newsletter Signup

Subscribe to get MariaDB tips, tricks and news updates in your inbox: