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.