Load testing API (Vanilla PHP vs Laravel vs Go) with MySQL pessimistic locking
Published: 2024-05-07 05:52:39

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
```javascript
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
<?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

![k6 vanilla php load test](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/junqk749ri8gj1fanjha.jpg)

## 2. Laravel 11 (PHP 8.2)
Source code routes/api.php
```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
![k6 laravel load test](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fqc5p2450jjrfg6jmqqp.jpg)

## 3. Go 1.21.3 (Gin v1.9.1)
Source code main.go
```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
```bash
go run main.go
```

### Result

![k6 go load test](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ek17iuatfg34595l9vr3.jpg)

## 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     |