Last active
March 16, 2023 02:21
-
-
Save jayjanssen/8e74bc4c5bdefc880ffd to your computer and use it in GitHub Desktop.
Golang MySQL connection with multiple IPs and connection timeouts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package main | |
import ( | |
"database/sql" | |
"fmt" | |
"github.com/go-sql-driver/mysql" | |
"log" | |
"net/http" | |
"strings" | |
"time" | |
"net" | |
"math/rand" | |
) | |
const ( | |
pxc_hosts string = `pxc.service.consul:3306` | |
user = `test` | |
pass = `test` | |
schema = `sakila` | |
options = `timeout=500ms` | |
max_idle int = 0 // Can't keep idle conns open b/c of: https://github.com/go-sql-driver/mysql/issues/257 | |
concurrency = 10 // Keep this script from blasting the database | |
retry_delay = 500 * time.Millisecond | |
lag = 50 * time.Millisecond | |
) | |
// Our global writer DB handle | |
var writer *sql.DB | |
var hits int | |
func main() { | |
var err error | |
// Setup our SQL pool | |
writer, err = sql.Open(`mysql`, | |
fmt.Sprintf("%s:%s@tcp(%s)/%s?%s", user, pass, pxc_hosts, | |
schema, options)) | |
if err != nil { | |
log.Fatal("Got ", err, "trying to connect to writer") | |
} | |
writer.SetMaxIdleConns(max_idle) | |
writer.SetMaxOpenConns(concurrency) | |
mysql.RegisterDial( `tcp`, func( addr string ) (net.Conn, error) { | |
host, port, err := net.SplitHostPort( addr ) // MUST have the port component | |
if err != nil { | |
return nil, &net.OpError{Op: "dial", Net:`tcp`, Addr: nil, Err: err} | |
} | |
addrs, err := net.LookupHost( host ) | |
if err != nil { | |
return nil, &net.OpError{Op: "dial", Net:`tcp`, Addr: nil, Err: err} | |
} | |
selected := addrs[rand.Intn(len(addrs))] | |
fmt.Println( "Selected: ", selected ) | |
timeout, _ := time.ParseDuration(`500ms`) | |
nd := net.Dialer{Timeout: timeout } | |
conn, err := nd.Dial(`tcp`, net.JoinHostPort( selected, port )) | |
if err != nil { | |
return nil, &net.OpError{Op: "dial", Net:`tcp`, Addr: nil, Err: err} | |
} | |
// Close the connection after 10 seconds to force the pool to reconnect | |
// this isn't necessarily graceful, but it seems mostly ok. | |
go func() { | |
time.Sleep( 10 * time.Second ) | |
conn.Close() | |
}() | |
return conn, nil | |
}) | |
defer writer.Close() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Does this caches connections from different databases?