Skip to content

Instantly share code, notes, and snippets.

@dpifke
Last active June 3, 2023 03:16
Show Gist options
  • Save dpifke/4ac0db9b28aa1f66fec0eb256c4ab0b8 to your computer and use it in GitHub Desktop.
Save dpifke/4ac0db9b28aa1f66fec0eb256c4ab0b8 to your computer and use it in GitHub Desktop.
// To run, create a empty main.go, then run: go test -bench=.
//
// Copyright (C) 2023 by Dave Pifke.
//
// Permission to use, copy, modify, and/or distribute this software for any
// purpose with or without fee is hereby granted.
//
// THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
// WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
// MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY
// SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
// WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
// ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR
// IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
package main
import (
"database/sql"
"fmt"
"math/rand"
"os"
"strconv"
"testing"
"time"
_ "github.com/lib/pq"
)
func createTables(db *sql.DB, tables int) (err error) {
for i := 0; i < tables; i++ {
if _, err = db.Exec(fmt.Sprintf(
"CREATE TABLE table%d (col1 INT PRIMARY KEY, col2 TEXT, col3 INT)",
i,
)); err != nil {
break
}
}
return
}
func populateTables(db *sql.DB, tables, rowsPerTable int) (err error) {
for i := 0; i < tables; i++ {
for j := 0; j < rowsPerTable; j++ {
if _, err = db.Exec(
fmt.Sprintf("INSERT INTO table%d VALUES ($1, $2, $3)", i),
rand.Int31(),
strconv.Itoa(int(rand.Int31())),
rand.Int31(),
); err != nil {
return
}
}
}
return
}
func truncateTables(db *sql.DB, tables int) (err error) {
for i := 0; i < tables; i++ {
if _, err = db.Exec(
fmt.Sprintf("TRUNCATE TABLE table%d", i),
); err != nil {
break
}
}
return
}
func deleteFromTables(db *sql.DB, tables int) (err error) {
for i := 0; i < tables; i++ {
if _, err = db.Exec(
fmt.Sprintf("DELETE FROM table%d", i),
); err != nil {
break
}
}
return
}
const (
tables = 1500
rowsPerTable = 10
dbhost = "/var/run/postgresql"
)
func BenchmarkTemplate(b *testing.B) {
var db *sql.DB
var err error
if db, err = sql.Open(
"postgres",
"postgres:///postgres?host="+dbhost,
); err != nil {
b.Fatal(err)
}
templatedb := fmt.Sprintf("template_%s%d", b.Name(), os.Getpid())
if _, err = db.Exec(`DROP DATABASE IF EXISTS "` + templatedb + `"`); err != nil {
b.Fatal(err)
}
if _, err = db.Exec(`CREATE DATABASE "` + templatedb + `"`); err != nil {
b.Fatal(err)
}
if err = db.Close(); err != nil {
b.Fatal(err)
}
if db, err = sql.Open(
"postgres",
"postgres:///"+templatedb+"?host="+dbhost,
); err != nil {
b.Fatal(err)
}
if err = createTables(db, tables); err != nil {
b.Fatal(err)
}
if err = db.Close(); err != nil {
b.Fatal(err)
}
var createElapsed, reconnectElapsed, insertElapsed time.Duration
b.ResetTimer()
for i := 0; i < b.N; i++ {
start := time.Now()
if db, err = sql.Open(
"postgres",
"postgres:///postgres?host="+dbhost,
); err != nil {
b.Fatal(err)
}
testdb := fmt.Sprintf("%s%d_%d", b.Name(), os.Getpid(), i)
if _, err = db.Exec(`DROP DATABASE IF EXISTS "` + testdb + `"`); err != nil {
b.Fatal(err)
}
if _, err = db.Exec(
`CREATE DATABASE "` + testdb + `" WITH TEMPLATE "` + templatedb + `"`,
); err != nil {
b.Fatal(err)
}
if err = db.Close(); err != nil {
b.Fatal(err)
}
createElapsed += time.Since(start)
start = time.Now()
if db, err = sql.Open(
"postgres", "postgres:///"+testdb+"?host="+dbhost,
); err != nil {
b.Fatal(err)
}
reconnectElapsed += time.Since(start)
start = time.Now()
if err = populateTables(db, tables, rowsPerTable); err != nil {
b.Fatal(err)
}
if err = db.Close(); err != nil {
b.Fatal(err)
}
insertElapsed += time.Since(start)
}
b.ReportMetric(createElapsed.Seconds()/float64(b.N), "create-sec/op")
b.ReportMetric(reconnectElapsed.Seconds()/float64(b.N), "reconnect-sec/op")
b.ReportMetric(insertElapsed.Seconds()/float64(b.N), "insert-sec/op")
}
func BenchmarkTruncate(b *testing.B) {
var db *sql.DB
var err error
if db, err = sql.Open(
"postgres", "postgres:///postgres?host="+dbhost,
); err != nil {
b.Fatal(err)
}
testdb := fmt.Sprintf("%s%d", b.Name(), os.Getpid())
if _, err = db.Exec(`DROP DATABASE IF EXISTS "` + testdb + `"`); err != nil {
b.Fatal(err)
}
if _, err = db.Exec(`CREATE DATABASE "` + testdb + `"`); err != nil {
b.Fatal(err)
}
if err = db.Close(); err != nil {
b.Fatal(err)
}
if db, err = sql.Open(
"postgres",
"postgres:///"+testdb+"?host="+dbhost,
); err != nil {
b.Fatal(err)
}
if err = createTables(db, tables); err != nil {
b.Fatal(err)
}
var insertElapsed, truncElapsed time.Duration
b.ResetTimer()
for i := 0; i < b.N; i++ {
start := time.Now()
if err = populateTables(db, tables, rowsPerTable); err != nil {
b.Fatal(err)
}
insertElapsed += time.Since(start)
start = time.Now()
if err = truncateTables(db, tables); err != nil {
b.Fatal(err)
}
truncElapsed += time.Since(start)
}
if err = db.Close(); err != nil {
b.Fatal(err)
}
b.ReportMetric(insertElapsed.Seconds()/float64(b.N), "insert-sec/op")
b.ReportMetric(truncElapsed.Seconds()/float64(b.N), "truncate-sec/op")
}
func BenchmarkDelete(b *testing.B) {
var db *sql.DB
var err error
if db, err = sql.Open(
"postgres",
"postgres:///postgres?host="+dbhost,
); err != nil {
b.Fatal(err)
}
testdb := fmt.Sprintf("%s%d", b.Name(), os.Getpid())
if _, err = db.Exec(`DROP DATABASE IF EXISTS "` + testdb + `"`); err != nil {
b.Fatal(err)
}
if _, err = db.Exec(`CREATE DATABASE "` + testdb + `"`); err != nil {
b.Fatal(err)
}
if err = db.Close(); err != nil {
b.Fatal(err)
}
if db, err = sql.Open(
"postgres",
"postgres:///"+testdb+"?host="+dbhost,
); err != nil {
b.Fatal(err)
}
if err = createTables(db, tables); err != nil {
b.Fatal(err)
}
var insertElapsed, deleteElapsed time.Duration
b.ResetTimer()
for i := 0; i < b.N; i++ {
start := time.Now()
if err = populateTables(db, tables, rowsPerTable); err != nil {
b.Fatal(err)
}
insertElapsed += time.Since(start)
start = time.Now()
if err = deleteFromTables(db, tables); err != nil {
b.Fatal(err)
}
deleteElapsed += time.Since(start)
}
if err = db.Close(); err != nil {
b.Fatal(err)
}
b.ReportMetric(insertElapsed.Seconds()/float64(b.N), "insert-sec/op")
b.ReportMetric(deleteElapsed.Seconds()/float64(b.N), "delete-sec/op")
}
@dpifke
Copy link
Author

dpifke commented Jun 3, 2023

goos: linux
goarch: amd64
pkg: pifke.org/pgbench
cpu: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz
BenchmarkTemplate-8 1 31195306814 ns/op 3.651 create-sec/op 27.54 insert-sec/op 0.0000176 reconnect-sec/op
BenchmarkTruncate-8 1 49918523039 ns/op 32.13 insert-sec/op 17.79 truncate-sec/op
BenchmarkDelete-8 1 32439070678 ns/op 2.779 delete-sec/op 29.66 insert-sec/op
PASS
ok pifke.org/pgbench 172.183s

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment