gophers riding elephants: writing postgresql tools in go
TRANSCRIPT
Gophers Riding Elephants: Writing PostgreSQL Tools in Go
by AJ Bahnken, Senior Engineer @ Procore
Who am I?Senior Engineer @ ProcoreWork on availability, performance, and (now mostly)securityBeen writing Go code actively for 2 years
Twitter:
Email: [email protected]
@ajvbahnken
Who is this talk for?
Overview of Go
Go was created by Google
It was built for Google as well
ReliableGood for teamsGood for building the kind of things they need to build
"Less is exponentially more" by Rob Pike
Go is purposefully lacking certain features.
Link: https://commandcenter.blogspot.com/2012/06/less-is-exponentially-more.html
Statically TypedCompiledGarbage Collected
package main
import "fmt"
func main() fmt.Println("Hello, 世界")
Why Go with Postgres?
1. Performance
It's pretty fast
Garbage collector is pretty solid
Concurrency is a breezepackage main
import ( "fmt" "time" )
func say(s string) for i := 0; i < 5; i++ time.Sleep(100 * time.Millisecond) fmt.Println(s)
func main() go say("world") say("hello") // Allows for the goroutine to run, by blocking.
$ go run main.go world hello hello world world hello hello world world hello
2. Reliability
Statically Typed (yay!)bool
string
int int8 int16 int32 int64 uint uint8 uint16 uint32 uint64 uintptr
byte // alias for uint8
rune // alias for int32 // represents a Unicode code point
float32 float64
complex64 complex128
Simpletype EventProcessor struct eventQueue chan Event
func (ep *EventProcessor) Add(event Event) ep.eventQueue < event
func (ep *EventProcessor) Start() for event := <ep.eventQueue go event.Process()
Testing is simple and built in + race detector$ ls processing.go processing_test.go utils.go
$ go test PASS ok ~/pgnetdetective/processing 0.165s
$ go test race PASS ok ~/pgnetdetective/processing 2.133s
Error handling instead of exceptionsfunc MyFunc() (string, error) str, err := run() if err != nil return "", err return str, nil
func MustMyFunc() string str, err := run() if err != nil panic("run() returned an err: "+err.String()) return str
3. Ease of Use
Tooling
(Gofmt, testing, godocs, go build/run, vim-go)
Familiarity
Library support and ease of installation$ go get github.com/urfave/cli
Distribute a single binary anywhere$ go build $ file dbduke dbduke: MachO 64bit executable x86_64
$ GOOS=linux go build $ file dbduke dbduke: ELF 64bit LSB executable, x8664, version 1 (SYSV), \ statically linked, not stripped
$ GOOS=linux GOARCH=386 go build $ file dbduke dbduke: ELF 32bit LSB executable, Intel 80386, version 1 \ (SYSV), statically linked, not stripped
PerformanceReliability
Ease of Use
Interacting withPostgres in Go
Provides core interface for interacting with SQL databases
Open / CloseBegin / Rollback / CommitExec / Query / QueryRowPing / Connection Pooling
go get github.com/lib/pq
package main
import ( "database/sql" "fmt" _ "github.com/lib/pq" )
func main() dbUrl := "postgres://postgres@localhost:5432/postgres" db, err := sql.Open("postgres", dbUrl) if err != nil panic(err.String())
var result int err = db.QueryRow('SELECT 1').Scan(&result) if err != nil panic(err.String())
fmt.Printf("1 == %d", result)
Example #1pgnetdetective
https://github.com/procore/pgnetdetective
?????
tcpdump n w ~/pg.cap i any port 5432
~1GB every 10 seconds
We needed something faster,
so I decided to rewrite it in Go
https://github.com/google/gopacket
Provides packet processing capabilities for Go
// If the destination port is 5432... if tcp.DstPort == 5432 // And the packet payload starts with P or Q... raw = fmt.Sprintf("%s", tcp.Payload) if strings.HasPrefix(raw, "P") || strings.HasPrefix(raw, "Q") // It is a Parse or Query packet, therefore it contains a Query combinedQueryMetrics.Add( metrics.New( NormalizeQuery(raw), 1, ip.SrcIP, tcp.Seq, ), ) else if tcp.SrcPort == 5432 && tcp.ACK responses = append(responses, &ResponsePacket DstIP: ip.DstIP, Ack: tcp.Ack, Size: uint64(len(tcp.Payload)), )
So I got some output like this:******* Query ******* Query: SELECT attr.attname FROM pg_attribute attr INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = any(cons.conkey) WHERE cons.contype = p AND cons.conrelid = "drawing_log_imports"::regclass TotalNetBytes: 170 MB TotalResponsePackets: 64041 TotalQueryPackets: 63
ummm, catalog queries?
Introducing: Resquehttps://github.com/resque/resque
http://resque.github.io/
~10,000 jobs per hour1-8 tables being touched per jobaverage of 20 columns per table.
During spikes, this can get up to 120MB per second.
On to Sidekiq we go...
Where Go won with pgnetdetective:Performance
Community (Ease of Use)
Example #2 dbduke(not open source + still under active development)
Context:
1. We restore staging/qa/testing databases frequently2. It's important that they successfully restore
Problem:
1. When restores fail, productivity dies2. The process of kicking restores off by hand is faulty
Further Context for a Solution:
1. Restores sometimes fail from easily recoverable errors
A tool for making restores of Postgresdatabases manageable and fault tolerant.
A tool for making restores of Postgresdatabases manageable and fault tolerant.
ManageableRun dbduke as a daemon with jobs
$ dbduke jobs DBDuke * restore 35e1ca93936b4c738812b1a69d708791 database: postgres dumpfile: /data/litedump.dmp started: 17:19:59 Tue Oct 11, 2016 0700 flags: nobigtables maintenance
A tool for making restores of Postgresdatabases manageable and fault tolerant.
Fault ToleranceTreat restores as a state machine
and recover from failure states
Error handling in practice:
1. Error out2. Log a warning3. Retry with timeout (with or without backoff)
Error outdb, err := sql.Open("postgres", dbUrl) if err != nil log.Fatalf("Could not open postgres db @ `%s`", dbUrl)
Log warningquery := "DROP SCHEMA IF EXISTS _migration CASCADE" _, err = db.Exec(query) if err != nil log.Warnf("Query `%s` failed with err: %v", query, err)
Retry with timeout (without backoff)func (r *Restorer) BlockTillNotInUse() if r.State == state.InUse log.Warn("State is currently InUse. Going into retry loop.") for time.Sleep(time.Second * 15)
r.QuitIfTimeout()
currentState, err := state.GetCurrentState() if err != nil log.Errorf( "Error getting current state. Err: %v", err, ) break
if currentState != state.InUse r.State = currentState break
Manageability + Fault Tolerance
Go makes it easy! ™
Where Go won with dbduke:Error Handling (Reliability)
Concurrency (Performance/Ease of Use)
In ConclusionIn the context of tool building
Go = Reliability, Performance, and Ease ofUse
Further Resources
Tour of GoEffective Go (required reading)Great intoduction to using SQL within GoWhy we import drivers with '_'
Sources
Performance Graph