Description:
I have a simple script that just simply call prepare statement and close them. when the script is done I expect db to release memory allocated because of this prepared statement but allocated memory won't release.
After running script i get following result as part of running show global status like '%com_stmt%':
Com_stmt_prepare: 300
Com_stmt_close: 300
and memory usage reports following output:
query:
SELECT SUBSTRING_INDEX(event_name,'/',2) AS
code_area, FORMAT_BYTES(SUM(current_alloc))
AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name,'/',2)
ORDER BY SUM(current_alloc) DESC;
output:
memory/performance_schema,220.66 MiB
memory/innodb,192.54 MiB
memory/sql,9.67 MiB
memory/mysys,8.60 MiB
memory/temptable,1.00 MiB
memory/mysqld_openssl,121.38 KiB
memory/mysqlx,3.38 KiB
memory/vio,1.16 KiB
memory/myisam, 728 bytes
memory/csv, 120 bytes
memory/blackhole, 120 bytes
while allocated memory on mysql process is more than 6G
As far as I know in proxysql we have by default 20 prepare statement per connection and if we exceed that the connection will be reset and we expect mysql to release that memory but seems like this is not happening and memory stays allocated.
Example code
package main
import (
"database/sql"
"fmt"
"log"
"strings"
_ "github.com/go-sql-driver/mysql"
)
func main() {
dsn := "root:root@tcp(localhost:3306)/tes_db"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalf("Failed to connect to the database: %v", err)
}
defer db.Close()
question := "? ,"
qlen := 0
for i := 0; i < 300; i++ {
if i < 40 {
for i := 0; i < 1000; i++ {
question += "? ,"
}
}
query := "SELECT " + strings.TrimRight(question, ",")
qlen += len(query)
_, err := db.Prepare(query)
if err != nil {
log.Fatalf("Failed to prepare statement %d: %v", i, err)
}
fmt.Printf("Prepared statement %d\n", i)
}
fmt.Println("Finished preparing statements", qlen/1024/1024)
}
Configuration
Go version: run go version 1.22
Server OS: E.g. macos (mysql was on docker)
How to repeat:
By running the script you can see that memory is allocated and it's not released.
Description: I have a simple script that just simply call prepare statement and close them. when the script is done I expect db to release memory allocated because of this prepared statement but allocated memory won't release. After running script i get following result as part of running show global status like '%com_stmt%': Com_stmt_prepare: 300 Com_stmt_close: 300 and memory usage reports following output: query: SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, FORMAT_BYTES(SUM(current_alloc)) AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC; output: memory/performance_schema,220.66 MiB memory/innodb,192.54 MiB memory/sql,9.67 MiB memory/mysys,8.60 MiB memory/temptable,1.00 MiB memory/mysqld_openssl,121.38 KiB memory/mysqlx,3.38 KiB memory/vio,1.16 KiB memory/myisam, 728 bytes memory/csv, 120 bytes memory/blackhole, 120 bytes while allocated memory on mysql process is more than 6G As far as I know in proxysql we have by default 20 prepare statement per connection and if we exceed that the connection will be reset and we expect mysql to release that memory but seems like this is not happening and memory stays allocated. Example code package main import ( "database/sql" "fmt" "log" "strings" _ "github.com/go-sql-driver/mysql" ) func main() { dsn := "root:root@tcp(localhost:3306)/tes_db" db, err := sql.Open("mysql", dsn) if err != nil { log.Fatalf("Failed to connect to the database: %v", err) } defer db.Close() question := "? ," qlen := 0 for i := 0; i < 300; i++ { if i < 40 { for i := 0; i < 1000; i++ { question += "? ," } } query := "SELECT " + strings.TrimRight(question, ",") qlen += len(query) _, err := db.Prepare(query) if err != nil { log.Fatalf("Failed to prepare statement %d: %v", i, err) } fmt.Printf("Prepared statement %d\n", i) } fmt.Println("Finished preparing statements", qlen/1024/1024) } Configuration Go version: run go version 1.22 Server OS: E.g. macos (mysql was on docker) How to repeat: By running the script you can see that memory is allocated and it's not released.