Question about CPU load.

[MakselPr]

I have written a test script m2 that simply executes db.Exec("UPDATE user SET count=count+ 1 WHERE id = '1'") in a loop, and I observe a CPU load of up to 25%. The same operation in Perl results in a CPU load of up to 11%, while in PHP, it reaches up to 9%.

package main import ( "fmt" "database/sql" _ "github.com/go-sql-driver/mysql" ) func main(){username := "root" password := "" host := "unix(/run/mysqld/mysqld.sock)" dbName := "test?loc=Local&allowNativePasswords=true" connectionString := fmt.Sprintf("%s:%s@%s/%s", username, password, host, dbName) db, _ := sql.Open("mysql", connectionString) defer db.Close() var count int db.QueryRow("SELECT count FROM user").Scan(&count) fmt.Printf("Count: %d\n", count) for i := 1; i <= 100000; i++{db.Exec("UPDATE `user` SET `count` = `count` + 1 WHERE `id` = '1'") } //#3 db.QueryRow("SELECT count FROM user").Scan(&count) fmt.Printf("Count: %d\n", count) } 

perf report readings on the screenshot
perf record -g -a sleep 25
perf report

I want to understand for myself who is to blame, whether it's the Go architecture or go-sql-driver. Or maybe there are optimization solutions. Google search didn't help me. So, I decided to ask here.

Thanks.

[methane]

Does PHP have same query/sec?

[MakselPr]

Does PHP have same query/sec?

Yes.
100,000 updates take approximately the same amount of time. But PHP is the fastest.

PHP 7.4.15

 time php ./test.php count=9755820 count=9855820 real 0m15,289s user 0m0,281s sys 0m1,034s 

Perl (v5.36.0)

time ./test.pl count: 9655820 count: 9755820 real 0m16,461s user 0m0,438s sys 0m1,001s 

Go 1.21.6

time ./m2 count: 9855820 count: 9955820 real 0m19,002s user 0m1,266s sys 0m2,301s 

test.php

<?php $mysqli = new mysqli("localhost", "root", "", "test"); #1 $res = $mysqli->query("SELECT count FROM user where id=1"); $t= $res->fetch_assoc(); print "count=$t[count]\n"; #2 for ($i=1;$i<=100000;$i++){$mysqli->query("UPDATE `user` SET `count` = `count` + 1 WHERE id = '1'")} #3 $res = $mysqli->query("SELECT count FROM user where id=1"); $t= $res->fetch_assoc(); print "count=$t[count]\n"; ?> 

test.pl

#!/usr/bin/perl -w use DBI(); my $dsn = "DBI:mysql:database=test;host=localhost"; my $dbh = DBI->connect($dsn, "root", "",{RaiseError => 1, PrintError => 1 }); # //1 $sql = "SELECT count FROM user"; my $sth = $dbh->prepare($sql); $sth->execute(); my ( $count); $sth->bind_columns(\$count); $sth->fetch(); print "count: $count\n"; # //2 for my $id (1..100000){$update_query = "UPDATE `user` SET `count` = `count` + 1 WHERE `id` = '1'"; $sth = $dbh->do($update_query)} # //3 $sql = "SELECT count FROM user"; $sth = $dbh->prepare($sql); $sth->execute(); $sth->bind_columns(\$count); $sth->fetch(); print "count: $count\n"; 

[MakselPr]

But I don't want to argue about whether PHP or Perl performed better in this task.
The question is why Go performs so poorly.
Is it an issue with Go Lang itself or the MySQL driver?

[methane]

You are using DB connection in PHP and Perl, but you are using DB connection pool in Go.
In each loop, you are checking out connection from the pool and return it to pool again.

Use db.Conn() instead.

[MakselPr]

Thank you for the response. However, I apologise, could you provide an example? Because what I was able to write didn't improve the situation.

package main import ( "database/sql" "fmt" "context" _ "github.com/go-sql-driver/mysql" ) func main(){username := "root" password := "" host := "unix(/run/mysqld/mysqld.sock)" dbName := "test?loc=Local&allowNativePasswords=true" connectionString := fmt.Sprintf("%s:%s@%s/%s", username, password, host, dbName) db, _ := sql.Open("mysql", connectionString) defer db.Close() var count int ctx := context.Background() conn, _ := db.Conn(ctx) defer conn.Close() rows, _ := conn.QueryContext(ctx, "SELECT `count` FROM `user` where `id`='1'") defer rows.Close() for rows.Next(){var count int rows.Scan(&count) fmt.Printf("count: %d\n", count) } for i := 1; i <= 100000; i++{_, _ = conn.ExecContext(ctx,"UPDATE `user` SET `count` = `count`+1 WHERE `id` ='1'") } fmt.Println("Update successful!") db.QueryRow("SELECT `count` FROM `user` where `id`='1'").Scan(&count) fmt.Printf("Count: %d\n",count) } 

Even worse, it takes about 1 second longer to complete.

[methane]

I have tried it. On my machine, PHP and Go run at the same speed.
Go has a small overhead due to its runtime, but it is negligible.