DEV Community: dkelxldk

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

dkelxldk — Tue, 07 May 2024 05:52:39 +0000

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

Why Your Laravel Log File Owner Suddenly Changes (And How to Fix It)

dkelxldk — Sat, 05 Aug 2023 14:47:10 +0000

Have you ever encountered your Laravel log file's owner/group suddenly changing to root or some other unexpected user? This can cause permission errors that prevent your application from writing logs, potentially hiding critical errors from you.

Here are the most common causes — and how to fix them.

Cause 1: Supervisor Running Queues as Root

If you use Supervisor to manage your queue workers, check the user setting in your Supervisor config. Let's say your application's owner/group is nginx:nginx, but your Supervisor config looks like this:

[program:laravel-worker]
process_name=%(program_name)s_%(process_num)02d
command=php /var/www/html/laravel-app/artisan queue:work sqs --sleep=3 --tries=3 --max-time=3600
autostart=true
autorestart=true
stopasgroup=true
killasgroup=true
user=root
numprocs=8
redirect_stderr=true
stdout_logfile=/var/www/html/laravel-app/logs/worker.log
stopwaitsecs=3600

Since the queue worker is running as root, any error that gets logged will create or modify the log file as root. Once that happens, your application (running as nginx) can no longer write to it.

Fix: Change the user in your Supervisor config to match your application's owner:

user=nginx

Then restart Supervisor:

sudo supervisorctl reread
sudo supervisorctl update
sudo supervisorctl restart laravel-worker:*

Cause 2: Cron Scheduler Running as Root

If you set up your Laravel scheduler using sudo crontab -e, the cron job runs as root:

$ sudo crontab -e
# Your scheduler entry goes here

Just like with Supervisor, any scheduled task that triggers an error will write to the log file as root, changing its ownership.

Fix: Set up the scheduler under your application's user instead:

$ sudo --user=nginx crontab -e
# Your scheduler entry goes here

Summary

If your Laravel log file ownership is unexpectedly changing, check two things:

Supervisor config — make sure user matches your app's owner.
Cron scheduler — make sure the crontab belongs to your app's user, not root.

Matching the user across all processes that touch your application will save you from mysterious permission issues down the road.

Optimizing database query - In a loop

dkelxldk — Fri, 06 May 2022 08:13:02 +0000

Imagine you have an array of object
but in the array, some object can have multiple data
for example

const transactions = [
    {
        "player_id": 1,
        "transaction_id": "xxx",
        "amount": "100",
    },
    {
        "player_id": 2,
        "transaction_id": "xxx",
        "amount": "100",
    },
    {
        "player_id": 3,
        "transaction_id": "xxx",
        "amount": "100",
    },
    {
        "player_id": 1,
        "transaction_id": "xxx",
        "amount": "100",
    }
]

and then you need to get the player information from the database

for (let i = 0; i<= transactions.length; i++) {
    const player = PlayerModel.findByPk(transactions[i].player_id)
// equals to SELECT * FROM users WHERE player_id = 1

    const saveTransaction = TransactionModel.create({
        id: transactions[i].transaction_id,
        player_id: transactions[i].player_id,
        player_before_balance: player.balance,
        player_after_balance: player.balance - transactions[i].amount
    });
}

you'll be creating database tcp connection in each loop, and that would be bad for the performance (more bad if your database is on another host)
because on every call you make, the flow goes like

open the db connection -> execute the query -> close the connection

so instead of using that way, you can distinct the transactions array by the player_id

const distinctPlayers = [...new Set(transactions.map(transaction => transaction.player_id))] 
// result [1,2,3]

the you will just call the database query only 1 time, using where in clause and store it in a variable

const players = PlayerModel.findAll({
        where: {
            id: distinctPlayers
        }
    })
// equals to SELECT * FROM users WHERE player_id IN (1,2,3)

for (let i = 0; i<= transactions.length; i++) {
    const player = players.find(player => player.player_id === players.player_id);

    const saveTransaction = TransactionModel.create({
        id: transactions[i].transaction_id,
        player_id: transactions[i].player_id,
        player_before_balance: player.balance,
        player_after_balance: player.balance - transactions[i].amount
    });
}

but the downside is, if the array is too large
the memory will take too much

please tell me if you have better approach

Handling concurrent mysql transaction request with sequelize

dkelxldk — Mon, 07 Feb 2022 08:05:12 +0000

In this post, I just wanna share my experience on handling hundreds of concurrent request on sql transaction using sequelize.

At first, when i build my application. There was no problem during the test. The problem comes when the third party that I want to integrate with is testing my app with a stress test.

And in a result, my app can't handle hundreds of concurrent request. There was no problem with the nodejs. The problem was when the app trying to get a connection from a database pool, but there was no released connection on the pool. Because it was all in use.

My db pool config was :

pool: {
      max: 38, // maximum number of pool connection
      min: 0,
      acquire: 60000, // 60 seconds acquire time
      idle: 5000, // 5 seconds idle time
    },

And here's the example code :

await Promise.all([
  User.findByPk(1, { transaction }),
  UserActionLog.Create(userActionLog, { transaction }),
  TransactionLog.bulkCreate(transactionLogs, { transaction })
]);

The solution was quite simple, it was an issue from the sequelize itself. https://github.com/sequelize/sequelize/issues/11024

pool.max transactions can run simultaneously, and the (pool.max + 1) transaction will cause a deadlock which completely blocks new transactions.

So we can't use async/await on this scenario, rather we use promises.

sequelize.transaction(async transaction => {
  User.findByPk(1, { transaction });
  UserActionLog.Create(userAction, { transaction });
  TransactionLog.bulkCreate(transactionLogs, { transaction });
})
.then(() => {
  return true
})
.catch(err => return false)

Migrasi database dari Non-Relational ke Relational

dkelxldk — Fri, 09 Jul 2021 10:19:38 +0000

Tujuan menulis artikel ini kayaknya lebih ke curhat, bukan tutorial XD

Posisi saya saat itu sedang menjalani probation sebagai junior di suatu tech company, ekspektasi diawal paling dikasih task ringan. Awalnya sih iya, pas di bulan terakhir probation, ada masalah jadinya tasknya ditunda dulu dan diganti task lain dengan rayuan "Mas, saya mau ngasih tantangan. Bisa nggak migrasiin sistem berbasis mongodb ke sistem yang berbasis mysql?". Dengan alasan servernya yang ngejalanin ini app selalu out of memory (beberapa kali udah gedein size memory nodejsnya, kalo kata sysadminnya gegara mongodb-nya, cuman saya lebih curiganya logic di app sebelumnya memory managementnya ngaco)

Bermodal tiap hari ngulik-ngulik nyari artikel, awalnya dimulai dari apa sih buat migrasi data ini? akhirnya sampai pada kesimpulan.

Pahami model data yang lama
Bikin skema relational buat database SQL yang baru
Modifikasi data lama agar sesuai dengan skema SQL yang baru
Import ke database SQL yang baru

Untuk poin pertama dan kedua kayaknya nggak perlu diceritakan terlalu banyak ya. Pada intinya penyimpanan data di mongo kan nggak relational, kalo udah biasa dengan relational database design, nanti keliatan field-field mana yang mau dibikin relasi, yang mana perlu di normalisasi.

Masalah yang saya temukan :

Susah memastikan field-field apa saja yang dibutuhkan, karena data terbaru ada tambahan beberapa field yang nggak diupdate ke data sebelumnya. Misal di data yang baru mengandung field orang_baru, di data yang lama nggak ada field tersebut. Jadi harus tahu field-field apa saja yang ada di collection tersebut.
Di collection tertentu, ntah kenapa ada data yang duplicate. Ini harus dibersihkan buat migrasi ke SQL nanti.

Langsung ke poin ke - 3.
Nah ini yang lumayan lama prosesnya, soalnya sambil trial & error dengan poin 4, gimana modifikasinya? format apa yang mau saya jadikan output buat diimport ke sql nanti? datanya ada jutaan, pas di hari migrasi nanti, migrasinya mau secara live atau mau matikan server? kalau mau matikan server, prosesnya berapa jam?

Diawal-awal saya nyoba bikin dummy data aja, performa kalo nginput data satu-satu pake script dengan node js habis berapa lama.
Hasilnya data 100rb rows makan waktu 30-40 menit coy, gimana ntar yg jutaan. (saya lupa pas make ini saya ngirim requestnya secara paralel atau synchronous, soalnya bener-bener baru juga di nodejs)

Opsi lain yang saya temukan adalah, mysql bisa nampung rows value banyak dalam sekali input. Wogh okenih pas dicoba 100rb rows, jauh lebih cepet. Permasalahan lain muncul ketika data yang dicoba adalah 1jt. Memory nodenya nggak kuat nampung itu data ~_~. Mungkin hal tersebut nggak jadi masalah kalau kalian mengolahnya dengan bahasa yang lain.

Lalu saya menemukan sebuah artikel bahwa salah satu cara cepat (raw performance) buat import ke sql ya dengan import dengan format csv.
*tkp => Article 1, Article 2

Akhirnya saya cobalah format data2 mongo yang lama ke format csv.

Step pertama saya export dulu data dari mongonya ke bentuk csv.
Di step pertama itu saya menggunakan 2 cara.
Cara pertama adalah untuk data-data yang valuenya tidak mengandung koma. Langsung saja jalanin command

mongoexport --db=db_name --collection=collection_name --type=csv --fields=field1,field2,fieldn --noHeaderLine --out=D:\output_path_csvmu.csv

Cara kedua ini saya pakai kalau data yang mau di export, valuenya mengandung koma. misal di field alamat, valuenya "Jl. x no. x, RT X/RWX". Saya bikinkan script buat konekin dengan mongoclient dan valuenya nanti dipisahkan dengan semicolon. (bisa sedikit lebih lama dibanding cara yang pertama).

const fs = require("fs");
const writeStream = fs.createWriteStream(
  "./output_path_csvmu.csv"
);
const MongoClient = require("mongodb").MongoClient;

(async () => {
  const connect = await MongoClient.connect(
    "mongodb://username:password@host:port/?authSource=admin",
    {
      useUnifiedTopology: true,
    }
  );
  const db = await connect.db("db_name");
  const cur = await db
    .collection("collection_name")
    .find({});
  let obj;
  while (await cur.hasNext()) {
    obj = await cur.next();
    const newobj = `${obj.id};${obj.name};${obj.address}`;
    writeStream.write(newobj + "\r\n");
  }
})();

Nah kekurangan pakai cara yang pertama tadi adalah karena format datanya masih seperti mongo, saya bikin script lagi biar formatnya bisa sesuai dengan database yang sudah saya design. Kalau mau pakai langsung cara yang kedua juga bisa, dari select datanya ke db secara langsung sekalian formatin sesuai design database yang sudah dibuat.
Setelah semua data diformat sesuai kebutuhan masing-masing, saatnya import ke SQL nya dengan LOAD DATA LOCAL INFILE!!!!!
Cukup gampang sih, kalau main terminal, tinggal masuk ke SQL nya

- mysql -u root -p
- use db_name
- LOAD DATA LOCAL INFILE '/path_ke_file_csvmu_berada.csv' 
INTO TABLE table_name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

Untuk format yang valuenya sudah kalian pisahkan dengan semicolon, FIELDS TERMINATED BY ',' bisa diganti menjadi FIELDS TERMINATED BY ';'
Dari segi performanya, nginsert data 2 jutaan memakan waktu dibawah 1 menit

Semoga membantu.
Cara diatas bisa saja bukan merupakan cara yang paling optimal, barangkali ada developer lain yang ingin memberi masukan tentunya akan sangat membantu