A set of gorm plugins for providing more features. It is using prometheus metrics to monitor your SQLs.
I will frequently write gorm plugins in this project. Please star it if you like it.
go get github.com/changsongl/gorm-plugin
It is a plugin to monitor query execution time and slow query count
for tables. We can use prometheus client to expose metrics api, and
having a prometheus server to scrape data from it. Eventually, you can
use Alertmanager
or Grafana
to monitor your slow query and query running
time.
package main
import (
"net/http"
"time"
"github.com/changsongl/gorm-plugin/query"
"github.com/prometheus/client_golang/prometheus"
"github.com/prometheus/client_golang/prometheus/promhttp"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)
type test struct {
Id int64 `gorm:"column:id" json:"id"`
Test string `gorm:"column:test" json:"test"`
}
func (test) TableName() string {
return "test"
}
func main() {
// create gorm v2 db
dsn := "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
panic(err)
}
// create query plugin
plugin := query.New(
query.SlowQueryCallback(query.Config{
DBName: "my_test_db", // can be empty
NamePrefix: "myprefix", // can be empty
Namespace: "mynamespace", // can be empty
SlowThreshold: time.Millisecond, // slow query time threshold
}),
query.ErrorQueryCallback(query.Config{
DBName: "my_test_db", // can be empty
NamePrefix: "myprefix", // can be empty
Namespace: "mynamespace", // can be empty
}),
)
// using plugin
if err := db.Use(plugin); err != nil {
panic(err.Error())
}
// running sqls
db.Raw("SELECT id FROM test WHERE id = ?", 3).Scan(&test{})
db.Create(&test{Test: "hahaha"})
db.Where("id = 123232132").First(&test{}) // record not found
db.Raw("SELECT id FROM test2 WHERE id = ?", 3).Scan(&test{}) // error
// register query plugin collectors to prometheus
prometheus.MustRegister(plugin.MetricsCollectors()...)
// run prometheus server
http.Handle("/metrics", promhttp.Handler())
srv := &http.Server{
Addr: ":8080",
}
if err := srv.ListenAndServe(); err != nil {
panic(err.Error())
}
}
// http://127.0.0.1:8080/metrics
# HELP mynamespace_myprefix_error_count gorm-plugin: error counter
# TYPE mynamespace_myprefix_error_count counter
mynamespace_myprefix_error_count{callback="gorm:row",db_name="my_test_db",table_name=""} 1
# HELP mynamespace_myprefix_query_time gorm-plugin: slow query timeQuery histogram (unit: second)
# TYPE mynamespace_myprefix_query_time histogram
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="",le="0.05"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="",le="0.1"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="",le="0.25"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="",le="0.5"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="",le="1"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="",le="2.5"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="",le="5"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="",le="10"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="",le="+Inf"} 2
mynamespace_myprefix_query_time_sum{db_name="my_test_db",table_name=""} 0.018511481
mynamespace_myprefix_query_time_count{db_name="my_test_db",table_name=""} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="test",le="0.05"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="test",le="0.1"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="test",le="0.25"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="test",le="0.5"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="test",le="1"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="test",le="2.5"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="test",le="5"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="test",le="10"} 2
mynamespace_myprefix_query_time_bucket{db_name="my_test_db",table_name="test",le="+Inf"} 2
mynamespace_myprefix_query_time_sum{db_name="my_test_db",table_name="test"} 0.016793621
mynamespace_myprefix_query_time_count{db_name="my_test_db",table_name="test"} 2
# HELP mynamespace_myprefix_slow_query_count gorm-plugin: slow query counter
# TYPE mynamespace_myprefix_slow_query_count counter
mynamespace_myprefix_slow_query_count{callback="gorm:create",db_name="my_test_db",table_name="test"} 1
mynamespace_myprefix_slow_query_count{callback="gorm:query",db_name="my_test_db",table_name="test"} 1
mynamespace_myprefix_slow_query_count{callback="gorm:row",db_name="my_test_db",table_name=""} 2
It is a plugin for explain the sql you ran. You can set the requirement of explain result.
CREATE TABLE `test`.`explain_table` (
`id` INT NOT NULL AUTO_INCREMENT,
`room_id` INT UNSIGNED NOT NULL,
`room_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_room_id` (`room_id` ASC));
INSERT INTO `test`.`explain_table` (`id`, `room_id`, `room_name`) VALUES (NULL, '1', 'room_name_1');
INSERT INTO `test`.`explain_table` (`id`, `room_id`, `room_name`) VALUES (NULL, '2', 'room_name2');
INSERT INTO `test`.`explain_table` (`room_id`, `room_name`) VALUES ('3', 'room_name2');
INSERT INTO `test`.`explain_table` (`room_id`, `room_name`) VALUES ('4', 'room_name2');
INSERT INTO `test`.`explain_table` (`room_id`, `room_name`) VALUES ('5', 'room_name3');
INSERT INTO `test`.`explain_table` (`room_id`, `room_name`) VALUES ('5', 'room_name5');
INSERT INTO `test`.`explain_table` (`room_id`, `room_name`) VALUES ('6', 'room_name9');
INSERT INTO `test`.`explain_table` (`room_id`, `room_name`) VALUES ('7', 'room_name1');
package main
import (
"fmt"
"github.com/changsongl/gorm-plugin/explain"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)
type test struct {
ID int64 `gorm:"column:id" json:"id"`
RoomID int64 `gorm:"column:room_id" json:"room_id"`
RoomName string `gorm:"column:room_name" json:"room_name"`
}
func (test) TableName() string {
return "explain_table"
}
func main() {
// create gorm v2 db
dsn := "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
panic(err)
}
// create query plugin
plugin := explain.New(
explain.CallBackFuncOption(func(result explain.CallBackResult) {
fmt.Printf("%+v\n", result)
}),
explain.TypeLevelOption(explain.ResultTypeRange),
)
// using plugin
if err := db.Use(plugin); err != nil {
panic(err.Error())
}
db.Where("room_name = 'haha'").First(&test{})
}
{Err:index not pass type requirement (range) Results:[{Id:1 SelectType:SIMPLE Table:explain_table Type:index PossibleKey: Key:PRIMARY KeyLen:4 Ref: Rows:1 Extra:Using where}] SQL:SELECT * FROM `explain_table` WHERE room_name = 'haha' ORDER BY `explain_table`.`id` LIMIT 1}