آموزش بهینه‌سازی MySQL برای سرور MTA؛ رفع کندی دیتابیس و Queryها

“`html

دیتابیس یکی از مهم‌ترین بخش‌های بسیاری از گیم‌مودهای MTA است. اطلاعات Accountها، شخصیت‌ها، Inventory، خودروها، Propertyها، Bank، Logها و بخش‌های مختلف یک گیم‌مود معمولاً در MySQL یا MariaDB ذخیره می‌شوند.

اگر Queryهای دیتابیس بهینه نباشند، ممکن است سرور با وجود CPU و RAM مناسب دچار تأخیر، Freeze کوتاه، کندی Login، دیر ذخیره شدن اطلاعات یا افزایش مصرف DB Thread شود.

در این آموزش ابتدا مشخص می‌کنیم آیا واقعاً دیتابیس عامل کندی است و سپس با ابزارهای MTA، MySQL و MariaDB Queryهای مشکل‌دار، Indexهای ناقص، درخواست‌های تکراری و روش‌های نامناسب اجرای Query را پیدا می‌کنیم.

نشانه‌های کند بودن دیتابیس در سرور MTA

• Login بازیکن با تأخیر انجام می‌شود.
• اطلاعات Character دیر Load می‌شوند.
• ذخیره Inventory یا Vehicle کند است.
• DB Thread در PerformanceBrowser بالا می‌رود.
• هم‌زمان با افزایش Player سرور لگ می‌گیرد.
• بعضی Resourceها هنگام Query مکث ایجاد می‌کنند.
• db.log تعداد زیادی Query مشابه نشان می‌دهد.
• MySQL یا MariaDB مصرف CPU بالایی دارد.
• Queryهای SELECT روی Tableهای بزرگ کند شده‌اند.

اول مطمئن شوید مشکل واقعاً از دیتابیس است

قبل از تغییر تنظیمات MySQL، وضعیت کلی VPS و MTA را بررسی کنید. CPU بالا همیشه به معنی مشکل Database نیست.

top

برای بررسی RAM:

free -h

برای مشاهده Processهای پرمصرف:

ps aux --sort=-%cpu | head

اگر Process دیتابیس یا DB Thread هم‌زمان با لگ بالا می‌رود، بررسی Database منطقی‌تر می‌شود.

برای بررسی کامل CPU، RAM، Network و Threadهای MTA مقاله آموزش مانیتورینگ سرور MTA در لینوکس را مطالعه کنید.

DB Thread در PerformanceBrowser چیست؟

PerformanceBrowser ابزار داخلی MTA برای بررسی عملکرد بخش‌های مختلف سرور است. اگر هنگام اجرای عملیات دیتابیس DB Thread به‌صورت محسوس بالا می‌رود، باید Queryهای مرتبط را بررسی کنید.

در Console سرور اجرا کنید:

start performancebrowser

مصرف DB Thread را در حالت عادی، هنگام Login بازیکنان و هنگام اجرای Resourceهای مشکوک مقایسه کنید.

فعال کردن Debug دیتابیس در MTA

برای ثبت جزئیات Queryهای دیتابیس در زمان عیب‌یابی، در Console سرور اجرا کنید:

debugdb 2

Log معمولاً در مسیر زیر قرار می‌گیرد:

mods/deathmatch/logs/db.log

برای مشاهده زنده فایل:

tail -f \
/home/mta/multitheftauto_linux_x64/mods/deathmatch/logs/db.log

بعد از پایان عیب‌یابی Logging اضافه را غیرفعال کنید:

debugdb 0

debugdb 2 را برای مدت طولانی روی سرور شلوغ فعال نگه ندارید؛ db.log می‌تواند سریع بزرگ شود و Logging اضافه نیز I/O ایجاد می‌کند.

پیدا کردن Queryهای پرتکرار در db.log

wc -l \
/home/mta/multitheftauto_linux_x64/mods/deathmatch/logs/db.log

برای مشاهده 200 خط آخر:

tail -n 200 \
/home/mta/multitheftauto_linux_x64/mods/deathmatch/logs/db.log

اگر یک Query در هر ثانیه یا برای هر Player چندین بار تکرار می‌شود، بررسی کنید آیا واقعاً اجرای آن با این تعداد دفعات ضروری است.

یک Connection دیتابیس بسازید، نه صدها Connection

ساختن dbConnect جدید قبل از هر Query می‌تواند سربار غیرضروری ایجاد کند. بهتر است Connection هنگام Start شدن Resource یک‌بار ساخته شود و در بخش‌های مختلف همان Resource استفاده شود.

local db

addEventHandler("onResourceStart", resourceRoot,
    function()
        db = dbConnect(
            "mysql",
            "dbname=mta;host=127.0.0.1;charset=utf8mb4",
            "mta_user",
            "PASSWORD"
        )

        if not db then
            outputDebugString("Database connection failed", 1)
        end
    end
)

Queryهای SELECT را Asynchronous اجرا کنید

برای Queryهایی که نتیجه برمی‌گردانند، استفاده از Callback باعث می‌شود مسیر عادی اجرای Server برای منتظر ماندن Database متوقف نشود.

dbQuery(
    function(qh)
        local result = dbPoll(qh, 0)

        if not result then
            return
        end

        for _, row in ipairs(result) do
            outputDebugString(
                "Player ID: " .. tostring(row.id)
            )
        end
    end,
    db,
    "SELECT `id`,`name` FROM `players` WHERE `serial`=? LIMIT 1",
    playerSerial
)

چرا dbPoll با مقدار -1 می‌تواند مشکل‌ساز شود؟

local qh = dbQuery(
    db,
    "SELECT * FROM players"
)

local result = dbPoll(qh, -1)

مقدار -1 باعث می‌شود MTA تا آماده شدن نتیجه منتظر بماند. اگر Query زمان‌بر باشد، این انتظار می‌تواند روی پاسخ‌گویی Server اثر بگذارد. در مسیرهای عادی گیم‌مود بهتر است از Callback و اجرای asynchronous استفاده شود.

برای INSERT و UPDATE همیشه به Result نیاز ندارید

وقتی نیازی به دریافت Result Set ندارید، dbExec گزینه مناسبی برای دستورهایی مانند UPDATE، INSERT و DELETE است.

dbExec(
    db,
    "UPDATE `players` SET `money`=? WHERE `id`=?",
    newMoney,
    playerId
)

پارامترها را مستقیم به SQL نچسبانید

برای داده‌های متغیر از Placeholderهای ? استفاده کنید.

روش نامناسب:

local sql =
    "SELECT * FROM players WHERE name='" ..
    playerName ..
    "'"

روش مناسب‌تر:

dbQuery(
    callback,
    db,
    "SELECT `id`,`name` FROM `players` WHERE `name`=? LIMIT 1",
    playerName
)

SELECT * را فقط زمانی استفاده کنید که لازم است

اگر فقط چند ستون مشخص نیاز دارید، تمام اطلاعات Row را دریافت نکنید.

به‌جای:

SELECT * FROM players WHERE id=25;

از:

SELECT id, money, level
FROM players
WHERE id=25
LIMIT 1;

Index چیست و چرا برای MTA مهم است؟

Index می‌تواند پیدا کردن Rowهای موردنظر را در بسیاری از Queryها سریع‌تر کند. ستون‌هایی که مرتب در WHERE، JOIN و بعضی ORDER BYها استفاده می‌شوند باید از نظر نیاز به Index بررسی شوند.

برای مثال اگر Player دائماً با serial جست‌وجو می‌شود:

SELECT id, name
FROM players
WHERE serial='ABC123';

باید بررسی شود آیا ستون serial Index مناسب دارد یا خیر.

مشاهده Indexهای یک Table

SHOW INDEX FROM players;

ساخت Index برای ستون پرتکرار

CREATE INDEX idx_players_serial
ON players(serial);

روی هر ستون بدون بررسی Index نسازید. Indexها فضا مصرف می‌کنند و عملیات INSERT، UPDATE و DELETE نیز باید آن‌ها را به‌روز کنند.

بررسی Query با EXPLAIN

قبل از حدس زدن درباره سرعت یک Query، Execution Plan آن را بررسی کنید.

EXPLAIN
SELECT id, name, money
FROM players
WHERE serial='ABC123';

در خروجی به مواردی مانند key، possible_keys، type و تعداد Rowهای تخمینی توجه کنید. اگر Query روی یک Table بزرگ تعداد زیادی Row را بررسی می‌کند، شرط‌ها، Indexها و ساختار Query باید بازبینی شوند.

Slow Query Log چیست؟

MySQL و MariaDB می‌توانند Queryهایی را که بیشتر از یک آستانه زمانی طول می‌کشند در Slow Query Log ثبت کنند. این Log یکی از ابزارهای اصلی پیدا کردن Queryهای کاندید بهینه‌سازی است.

برای مشاهده وضعیت فعلی:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

فعال‌سازی موقت Slow Query Log

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

عدد 1 در این مثال فقط یک نقطه شروع برای عیب‌یابی است. مقدار مناسب به نوع پروژه و Performance واقعی Database بستگی دارد.

مسیر Slow Query Log را از خود Database بگیرید

SHOW VARIABLES LIKE 'slow_query_log_file';

سپس همان فایل را در Linux بررسی کنید:

sudo tail -f /PATH/TO/SLOW-QUERY.LOG

خلاصه کردن Slow Queryها در MySQL

mysqldumpslow /PATH/TO/SLOW-QUERY.LOG

mysqldumpslow الگوهای مشابه را خلاصه می‌کند و برای پیدا کردن Queryهایی که مرتب تکرار می‌شوند مفید است.

Query داخل Loop را جدی بگیرید

فرض کنید برای 100 Player یک Loop دارید و برای هر Player چند Query جدا اجرا می‌کنید. با افزایش تعداد بازیکن، تعداد درخواست‌ها به Database می‌تواند به‌سرعت بالا برود.

قبل از اجرای Query داخل Loop بررسی کنید آیا امکان دریافت داده‌ها با یک Query، JOIN، Batch یا Cache کنترل‌شده وجود دارد.

Database را در Eventهای بسیار پرتکرار صدا نزنید

اطلاعاتی که دائماً برای Gameplay نیاز هستند نباید بدون دلیل در Eventهای بسیار پرتکرار دوباره از Database خوانده شوند.

• Money بازیکن
• Level
• Faction
• Inventory فعال
• تنظیمات Session

بسته به معماری گیم‌مود، بخشی از این اطلاعات می‌تواند هنگام Login Load شود، در حافظه مدیریت شود و در نقاط مشخص و امن به Database ذخیره شود.

Cache به معنی حذف ذخیره‌سازی نیست. باید مشخص باشد اطلاعات مهم چه زمانی ذخیره می‌شوند تا Crash یا Restart باعث از دست رفتن داده نشود.

N+1 Query چیست؟

یکی از الگوهای ناکارآمد این است که ابتدا یک لیست دریافت شود و سپس برای هر Row یک Query دیگر اجرا شود. در بسیاری از موارد می‌توان با JOIN یا طراحی بهتر Query تعداد Round Tripها به Database را کاهش داد.

نمونه JOIN به‌جای Queryهای متعدد

SELECT
    vehicles.id,
    vehicles.model,
    players.name AS owner_name
FROM vehicles
JOIN players
    ON players.id = vehicles.owner_id
WHERE vehicles.owner_id = 25;

نام Tableها و Columnها در این مثال باید با Database واقعی پروژه جایگزین شوند.

از LIMIT برای Queryهای تک‌نتیجه استفاده کنید

SELECT id, password
FROM accounts
WHERE username=?
LIMIT 1;

Tableهای Log را بدون محدودیت بزرگ نکنید

گیم‌مودها ممکن است Chatها، Transferها، Admin Actionها، Deathها و Transactionها را برای مدت طولانی ذخیره کنند. با رشد پروژه، Tableهای بسیار بزرگ می‌توانند Queryهای مدیریتی، Backup و Reportها را سنگین کنند.

• برای Logها Retention تعریف کنید.
• داده‌های قدیمی را در صورت نیاز Archive کنید.
• Indexهای مرتبط با زمان و Player را بررسی کنید.
• برای Admin Panel از Pagination استفاده کنید.
• تمام Logها را یک‌جا SELECT نکنید.

Pagination برای پنل مدیریتی

SELECT id, action, created_at
FROM admin_logs
ORDER BY id DESC
LIMIT 50 OFFSET 0;

در Datasetهای بسیار بزرگ، OFFSETهای بزرگ نیز می‌توانند هزینه‌بر شوند و Pagination مبتنی بر ID یا Key ممکن است انتخاب بهتری باشد.

بررسی مصرف MySQL در Linux

ps aux | grep -E 'mysqld|mariadbd'

همچنین با top می‌توانید مصرف CPU و RAM Process دیتابیس را در زمان لگ بررسی کنید.

بررسی Connectionهای Database

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

افزایش غیرعادی Connectionها می‌تواند نشانه ساخت Connectionهای بیش از حد یا مشکل در معماری Resourceها باشد.

بررسی Queryهای در حال اجرا

SHOW FULL PROCESSLIST;

این خروجی برای مشاهده Connectionها و Queryهایی که در همان لحظه فعال هستند مفید است.

MySQL روی همان VPS یا سرور جدا؟

اگر MTA و Database روی یک VPS باشند، تأخیر شبکه میان آن‌ها بسیار کم است اما هر دو Service از CPU، RAM و Disk همان VPS استفاده می‌کنند.

اگر Database روی سرور جداگانه قرار دارد، علاوه بر Performance دیتابیس باید Latency و کیفیت Network بین دو Server نیز بررسی شود.

آیا افزایش RAM همیشه مشکل دیتابیس را حل می‌کند؟

خیر. Query بدون Index، طراحی نامناسب Table، Queryهای Blocking یا صدها Query تکراری با اضافه کردن RAM الزاماً اصلاح نمی‌شوند.

قبل از ارتقای VPS مشخص کنید Bottleneck واقعاً CPU، RAM، Disk I/O، Database Design یا Resource Code است.

برای انتخاب سخت‌افزار مناسب مقاله VPS مناسب سرور MTA چه مشخصاتی باید داشته باشد؟ را مطالعه کنید.

Backup قبل از تغییر Index یا ساختار Table

قبل از ALTER TABLE، حذف Index یا تغییر گسترده Schema از Database Backup بگیرید. روی Database بزرگ، عملیات تغییر ساختار ممکن است زمان‌بر باشد و بهتر است در Maintenance Window انجام شود.

امنیت Database را فراموش نکنید

MTA را با User اختصاصی Database متصل کنید و اطلاعات حساب root دیتابیس را داخل Resource قرار ندهید. اگر MySQL روی همان VPS است و اتصال خارجی نیاز ندارید، پورت Database را بدون دلیل برای کل اینترنت باز نکنید.

برای تنظیمات امنیتی بیشتر مقاله آموزش افزایش امنیت سرور MTA در لینوکس را مطالعه کنید.

اشتباهات رایج دیتابیس در Resourceهای MTA

• ساخت dbConnect برای هر Query
• استفاده زیاد از dbPoll با timeout برابر -1
• اجرای Query داخل Loopهای بزرگ
• SELECT * بدون نیاز
• نبود Index روی ستون‌های پرتکرار
• ساخت Index بدون بررسی
• Query دیتابیس در Eventهای بسیار پرتکرار
• نداشتن Cache مناسب
• دریافت هزاران Row بدون LIMIT
• نداشتن Pagination
• چسباندن مستقیم داده‌های Player داخل SQL
• فعال نگه داشتن debugdb 2 برای مدت طولانی
• نداشتن Slow Query Log هنگام عیب‌یابی
• ارتقای VPS بدون پیدا کردن Query مشکل‌دار

ترتیب پیشنهادی عیب‌یابی DB Thread بالا

1. PerformanceBrowser را باز کنید.
2. مطمئن شوید DB Thread واقعاً بالا است.
3. زمان بروز مشکل را ثبت کنید.
4. debugdb 2 را موقتاً فعال کنید.
5. Resource مربوط به Queryهای پرتکرار را پیدا کنید.
6. استفاده از dbPoll -1 را بررسی کنید.
7. تعداد dbConnectها را بررسی کنید.
8. Slow Query Log را بررسی کنید.
9. Queryهای کند را با EXPLAIN تحلیل کنید.
10. Indexهای موجود را بررسی کنید.
11. Queryهای داخل Loop را پیدا کنید.
12. SELECTهای غیرضروری را کاهش دهید.
13. بعد از هر تغییر دوباره اندازه‌گیری کنید.

چک‌لیست بهینه‌سازی MySQL برای MTA

• Connection دیتابیس یک‌بار ساخته می‌شود.
• Queryهای SELECT به‌صورت asynchronous اجرا می‌شوند.
• dbPoll -1 در مسیرهای حساس استفاده نشده است.
• برای UPDATE و INSERT مناسب از dbExec استفاده شده است.
• پارامترها با Placeholder ارسال می‌شوند.
• SELECT * غیرضروری حذف شده است.
• Queryهای پرتکرار شناسایی شده‌اند.
• Query داخل Loop بررسی شده است.
• Indexهای Table بررسی شده‌اند.
• Queryهای مهم با EXPLAIN بررسی شده‌اند.
• Slow Query Log هنگام عیب‌یابی بررسی شده است.
• Tableهای Log دارای Retention هستند.
• Pagination برای لیست‌های بزرگ فعال است.
• DB Thread قبل و بعد از تغییر مقایسه شده است.
• Backup قبل از تغییر Schema گرفته شده است.

جمع‌بندی

برای بهینه‌سازی MySQL در سرور MTA نباید فقط تنظیمات خود Database را تغییر داد. بخش مهمی از Performance به نحوه نوشته شدن Resourceهای Lua و تعداد Queryهایی که گیم‌مود ایجاد می‌کند وابسته است.

ایجاد یک Connection پایدار، استفاده از Queryهای asynchronous، جلوگیری از Queryهای Blocking، بررسی db.log، استفاده از Slow Query Log، تحلیل Queryها با EXPLAIN و ایجاد Indexهای صحیح می‌تواند بسیاری از مشکلات رایج Database را مشخص کند.

اگر هنوز اتصال اولیه Database را انجام نداده‌اید، ابتدا مقاله آموزش اتصال سرور MTA به MySQL و رفع خطاهای دیتابیس را مطالعه کنید.

اگر مشکل اصلی Server لگ عمومی است، مقاله علت لگ سرور MTA و روش‌های رفع آن و راهنمای مانیتورینگ سرور MTA در لینوکس را نیز بررسی کنید.

برای بررسی تخصصی گیم‌مود، Database و Resourceهای پرمصرف می‌توانید از خدمات پشتیبانی فنی گیم‌سرور و راه‌اندازی و کانفیگ سرور MTA کیمیا گیم استفاده کنید.

پرسش‌های متداول

چگونه بفهمیم دیتابیس باعث لگ MTA شده است؟

DB Thread را در PerformanceBrowser بررسی کنید و هم‌زمان db.log، مصرف Process دیتابیس و Slow Query Log را در زمان بروز مشکل مقایسه کنید.

debugdb 2 در MTA چه کاری انجام می‌دهد؟

این دستور اطلاعات Verbose مربوط به Queryهای دیتابیس را برای عیب‌یابی در db.log ثبت می‌کند.

چرا dbPoll -1 ممکن است باعث لگ شود؟

زیرا Server تا آماده شدن نتیجه Query منتظر می‌ماند. برای Queryهای عادی بهتر است از Callback و اجرای asynchronous استفاده شود.

آیا هر ستون Database باید Index داشته باشد؟

خیر. Index باید براساس Queryهای واقعی پروژه و ستون‌هایی که مرتب در جست‌وجو، JOIN یا Sort استفاده می‌شوند طراحی شود.

Slow Query Log چه کاربردی دارد؟

Queryهایی را که بیش از آستانه زمانی تعیین‌شده طول می‌کشند ثبت می‌کند و برای پیدا کردن Queryهای کاندید بهینه‌سازی مفید است.

برای بررسی یک Query کند از چه ابزاری استفاده کنیم؟

Query را با EXPLAIN بررسی کنید و خروجی آن را همراه با Indexهای موجود Table، تعداد Rowها و الگوی واقعی استفاده تحلیل کنید.

منابع رسمی مرتبط

• مستندات رسمی dbConnect در MTA
• مستندات رسمی dbQuery در MTA
• مستندات رسمی dbPoll در MTA
• مستندات رسمی dbExec در MTA
• مستندات Slow Query Log در MySQL
• مستندات Slow Query Log در MariaDB
• مستندات EXPLAIN در MariaDB

“`