Load testing API (Vanilla PHP vs Laravel vs Go) with MySQL pessimistic locking

This is just a simple experiment for me, being curious how many request each of these code that is handling mysql pessimistic locking can handle load testing using k6.

The K6 Script will load testing with 10 virtual users with total duration of 30s

import http from "k6/http";

export const options = {
    // A number specifying the number of VUs to run concurrently.
    vus: 10,
    // A string specifying the total duration of the test run.
    duration: "30s",
};

// The function that defines VU logic.
export default function () {
    http.post(
        "http://localhost:8080/api/test",
        JSON.stringify({ username: "usertest" }),
        {
            headers: {
                "Content-Type": "application/json",
                Accept: "application/json",
            },
        }
    );
}

1. Vanilla PHP (8.2)

Source code index.php

<?php

function initDB() {
    try {
        // Create a new PDO instance
        $pdo = new PDO("mysql:host=localhost;dbname=dbname", 'root', '');

        // Set PDO to throw exceptions on error
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $pdo;
    } catch (PDOException $e) {
        // Display error message if connection fails
        echo "Connection failed: " . $e->getMessage();
        die(); // Terminate script
    }
}

function isRoute(string $method, string $route, array ...$handlers): int
{
    global $params;
    $uri = parse_url($_SERVER['REQUEST_URI'])['path'];
    $route_rgx = preg_replace('#:(\w+)#', '(?<$1>(\S+))', $route);
    return preg_match("#^$route_rgx$#", $uri, $params);
}

(match (1) {
    isRoute('POST', '/api/test') => function () {
        $request = json_decode(file_get_contents('php://input'), true);

        $maxRetries = 1; // Maximum number of retries
        $retryCount = 0;

        while (true) {
            // Retry logic
            if ($retryCount >= $maxRetries) {
                return json_encode(['error' => 'Failed to update user balance after maximum retries']);
            }

            try {
                $pdo = initDB();

                // Start a transaction
                $pdo->beginTransaction();
                $stmt = $pdo->prepare("SELECT id, balance, version FROM users WHERE username = ? FOR UPDATE");
                $stmt->execute([$request['username']]);
                $user = $stmt->fetch(PDO::FETCH_ASSOC);

                if (!$user) {
                    $pdo->rollBack();
                    return json_encode(['error' => 'user does not exist']);
                }

                // Save the user (update with optimistic locking)
                $stmt = $pdo->prepare("UPDATE users SET balance = balance - 1, version = version + 1 WHERE id = ? AND version = ?");
                $updated_rows = $stmt->execute([$user['id'], $user['version']]);

                if ($updated_rows !== 0) {
                    // Commit the transaction
                    $pdo->commit();
                    return json_encode(['balance' => $user['balance']]);
                }
            } catch (PDOException $e) {
                // Rollback the transaction if there is any error
                $pdo->rollBack();

                // Handle other query exceptions
                return json_encode(['error' => 'Failed to update user balance']);
            }

            $pdo->rollBack();
            $retryCount++;
        }
    }
})();

Served using Nginx+PHP-FPM

Result

2. Laravel 11 (PHP 8.2)

Source code routes/api.php

<?php

use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Route;

Route::post('/test', function (Request $request) {
    $max_retries = 1; // Maximum number of retries
    $retry_count = 0;

    while (true) {
        if ($retry_count >= $max_retries) {
            return response()->json(['error' => 'Retry count exceeded'], 500);
        }

        try {
            // Start a transaction
            DB::beginTransaction();

            $rows = DB::select('SELECT id, balance, version FROM users WHERE username = ? FOR UPDATE', [$request->username]);

            if (count($rows) === 0) {
                DB::rollBack();
                return response()->json(['error' => 'user not exists'], 404);
            }

            // Update the user balance (update with optimistic locking)
            $updated_rows = DB::affectingStatement('UPDATE users SET balance = balance - 1, version = version + 1 WHERE id = ? AND version = ?', [$rows[0]->id, $rows[0]->version]);

            if ($updated_rows !== 0) {
                // Commit the transaction
                DB::commit();

                return response()->json(['message' => $user->balance]);
            }
        } catch (\Throwable $th) {
            // Rollback the transaction if there is any error
            DB::rollBack();

            // Handle other query exceptions
            return response()->json(['error' => 'Failed to update user balance'], 500);
        }

        DB::rollBack();
        $retry_count++;
    }

    return response()->json(['error' => 'Failed to update user balance'], 500);
});

Served using Nginx+PHP-FPM

Result

3. Go 1.21.3 (Gin v1.9.1)

Source code main.go

package main

import (
    "database/sql"
    "errors"
    "fmt"
    "log"
    "net/http"
    "time"

    "github.com/gin-gonic/gin"
    "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func initDB() {
    // Capture connection properties
    cfg := mysql.Config{
        User:   "root",
        Passwd: "",
        Net:    "tcp",
        Addr:   "127.0.0.1:3306",
        DBName: "dbname",
    }
    // Get a database handle.
    var err error
    db, err = sql.Open("mysql", cfg.FormatDSN())
    if err != nil {
        log.Fatal(err)
    }

    pingErr := db.Ping()
    if pingErr != nil {
        log.Fatal(pingErr)
    }

    db.SetMaxIdleConns(10)
    db.SetMaxOpenConns(100)
    db.SetConnMaxIdleTime(5 * time.Minute)
    db.SetConnMaxLifetime(1 * time.Hour)
}

func init() {
    initDB()
}

func main() {
    router := gin.Default()
    router.POST("/api/test", test)

    router.Run("localhost:8080")
}

func test(c *gin.Context) {
    requestBody := struct {
        Username string `json:"username"`
    }{}

    if err := c.BindJSON(&requestBody); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"message": err.Error()})
        return
    }

    err := updateBalance(requestBody.Username, 0)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"code": 1, "msg": "error", "data": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"code": 0, "msg": "success", "data": 0})
}

var maxRetries = 1

func updateBalance(username string, retry int) error {
    // check retry count
    if retry >= maxRetries {
        return errors.New("max retries exceeded")
    }

    user := struct {
        ID uint64 `json:"id"`
        Balance float64 `json:"balance"`
        Version uint64 `json:"version"`
    }{}

    dbt, err := db.Begin()
    if err != nil {
        return err
    }

    row := dbt.QueryRow("SELECT id, balance, version FROM users WHERE username = ? FOR UPDATE", username)
    if err := row.Scan(&user.ID, &user.Balance, $user.Version); err != nil {
        dbt.Rollback()
        return err
    }

    res, err := dbt.Exec("UPDATE users SET balance = balance - 1, version = version + 1 WHERE id = ? AND version = ?", user.ID, user.Version)
    if err != nil {
        dbt.Rollback()
        return err
    }

    rowAffected, err := res.RowsAffected()
    if err != nil {
        dbt.Rollback()
        return err
    }

    if rowAffected == 0 {
        dbt.Rollback()
        return updateBalance(username, retry+1)
    }

    err = dbt.Commit()
    if err != nil {
        return err
    }

    return nil
}

Served using go webserver

go run main.go

Result

Summary

Tested using Macbook Pro M2 with 8-core CPU, 8GB RAM

	Req/s	Total Req
PHP	1,203	36,112
Laravel	346	10,414
Go	1,347	40,453