بررسی Processlist در MySQL

وقتی تعداد Connectionهای دیتابیس افزایش پیدا می‌کند، Query طولانی اجرا می‌شود یا مصرف CPU و منابع MariaDB بالا می‌رود، یکی از اولین ابزارهای عیب‌یابی Processlist است.
Processlist نشان می‌دهد در همان لحظه چه Connectionها و Threadهایی روی Database Server وجود دارند، هر Connection متعلق به چه User و Hostی است، چه مدت در وضعیت فعلی قرار داشته و چه Queryای اجرا می‌کند. در این آموزش Processlist را روی MariaDB بررسی می‌کنیم. در محیط‌هایی مانند MariaDB 10.6 می‌توان همچنان از Client سازگار mysql استفاده کرد.

Processlist چیست؟

هر Connection به MariaDB توسط یک Thread مدیریت می‌شود. دستور:

SHOW PROCESSLIST;

فهرست Threadهای موجود را نمایش می‌دهد. خروجی معمولاً شامل ستون‌هایی مانند موارد زیر است:

Id
User
Host
db
Command
Time
State
Info
Progress

MariaDB این اطلاعات را هم از طریق SHOW PROCESSLIST و هم از طریق جدول INFORMATION_SCHEMA.PROCESSLIST در اختیار Administrator قرار می‌دهد.

مرحله اول: ورود به MariaDB

ابتدا وارد Database Server شوید:

mysql -u root -p

پس از ورود باید Prompt مشابه زیر را مشاهده کنید:

MariaDB [(none)]>

مرحله دوم: مشاهده Processlist

دستور زیر را اجرا کنید:

SHOW PROCESSLIST;

خروجی می‌تواند مشابه این باشد:

+----+------+--------------------+----------+---------+------+--------------+------------------+
| Id | User | Host               | db       | Command | Time | State        | Info             |
+----+------+--------------------+----------+---------+------+--------------+------------------+
| 12 | app  | 192.168.1.20:5523 | shop_db  | Sleep   |  145 |              | NULL             |
| 18 | root | localhost          | NULL     | Query   |    0 | starting     | SHOW PROCESSLIST |
| 24 | app  | 192.168.1.20:5531 | shop_db  | Query   |   32 | Sending data | SELECT ...       |
+----+------+--------------------+----------+---------+------+--------------+------------------+

اگر هدفتان صرفاً پیدا کردن Queryهای در حال اجرا است، مقاله پیدا کردن Query های در حال اجرا در MySQL را نیز بررسی کنید. در این مقاله تمرکز اصلی روی تحلیل Processlist است.

تفاوت SHOW PROCESSLIST و SHOW FULL PROCESSLIST

دستور معمولی:

SHOW PROCESSLIST;

متن Query را در ستون Info حداکثر تا 100 کاراکتر نمایش می‌دهد. برای مشاهده متن کامل‌تر Query از:

SHOW FULL PROCESSLIST;

استفاده کنید. برای خروجی خواناتر در Terminal نیز می‌توانید بنویسید:

SHOW FULL PROCESSLIST\G

این روش مخصوصاً زمانی مفید است که Query طولانی باشد.

ستون Id چیست؟

ستون id شناسه Connection یا Thread را نشان می‌دهد. برای مثال:

Id: 24

همین عدد برای عملیاتی مانند:

KILL 24;

استفاده می‌شود. MariaDB امکان متوقف کردن Connection یا فقط Query مربوط به یک Thread را فراهم می‌کند.

ستون User

ستون User نشان می‌دهد Connection با کدام Database User ایجاد شده است. اگر تعداد بسیار زیادی Connection از یک User خاص مشاهده می‌کنید، می‌توانید مشخص کنید کدام Application یا Website از آن Account استفاده می‌کند.

ستون Host

ستون Host منبع Connection را نشان می‌دهد. برای Connection محلی ممکن است ببینید:

localhost

و برای اتصال Network:

192.168.1.20:55321

MariaDB برای Connectionهای TCP/IP علاوه بر Host می‌تواند Port سمت Client را نیز در این ستون نمایش دهد. این ستون هنگام بررسی تعداد زیاد Connection از یک Application Server بسیار کاربردی است.

ستون db

ستون db Database پیش‌فرض Connection را نشان می‌دهد. مثلاً:

wordpress_db

اگر Connection هنوز Database خاصی انتخاب نکرده باشد ممکن است مقدار:

NULL

نمایش داده شود.

ستون Command

یکی از مهم‌ترین ستون‌ها Command است. مقادیر رایج می‌توانند شامل موارد زیر باشند:

مقدار Command نوع فعالیت Thread را نشان می‌دهد.

ستون Time

ستون Time مدت زمانی را برحسب ثانیه نشان می‌دهد که Thread در Command فعلی قرار داشته است. برای مثال:

Command: Query
Time: 120

یعنی Query فعلی تقریباً 120 ثانیه در این وضعیت بوده است.

ستون State چیست؟

ستون State مرحله یا وضعیت فعلی Thread را نشان می‌دهد. برای مثال ممکن است مقادیری مانند:

Sending data
Creating sort index
Opening tables
Waiting for table metadata lock
Updating
Statistics

مشاهده کنید. State برای Troubleshooting بسیار مهم است؛ زیرا نشان می‌دهد Query دقیقاً در چه مرحله‌ای قرار دارد. اما یک State را نباید بدون بررسی Query و مدت زمان آن به‌عنوان Error در نظر گرفت.

ستون Info

این ستون Statement فعلی Thread را نمایش می‌دهد. مثلاً:

SELECT * FROM orders WHERE customer_id = 100;

اگر Connection در وضعیت Sleep باشد معمولاً:

NULL

نمایش داده می‌شود.

بررسی مصرف Memory هر Process

در MariaDB می‌توان از ستون:

MEMORY_USED

در INFORMATION_SCHEMA.PROCESSLIST استفاده کرد. برای مثال:

SELECT
    ID,
    USER,
    COMMAND,
    TIME,
    ROUND(MEMORY_USED / 1024 / 1024, 2) AS Memory_MB,
    INFO
FROM information_schema.PROCESSLIST
ORDER BY MEMORY_USED DESC;

ستون‌های MEMORY_USED و MAX_MEMORY_USED در Processlist جزئیات مصرف Memory هر Thread را ارائه می‌دهند. این اطلاعات می‌تواند هنگام بررسی مصرف غیرعادی منابع مفید باشد.

مرحله سوم: متوقف کردن یک Query مشکل‌دار

اگر بعد از بررسی مطمئن شدید یک Query باید متوقف شود، ابتدا Id آن را پیدا کنید. فرض کنیم:

Id = 245

اگر فقط Query جاری را می‌خواهید متوقف کنید:

KILL QUERY 245;

در این حالت Query متوقف می‌شود اما Connection باقی می‌ماند.

قبل از Kill کردن Query چه چیزی را بررسی کنیم؟

صرفاً به دلیل اینکه:

Time = 500

است Query را Kill نکنید. ابتدا بررسی کنید:

• Query متعلق به چه Application است؟
• آیا Backup در حال اجراست؟
• آیا Migration اجرا می‌شود؟
• آیا عملیات Maintenance است؟
• Transaction باز دارد؟
• Query دیگری منتظر آن است؟
• Kill کردن آن چه اثری روی Application دارد؟

روی Server Production، Kill کردن اشتباه Process می‌تواند باعث Error در Website یا Rollback Transaction شود.

جمع‌بندی

برای مشاهده سریع Connectionها و Queryهای MariaDB ابتدا اجرا کنید:

SHOW FULL PROCESSLIST;

سپس ستون‌های:

Id
User
Host
db
Command
Time
State
Info

را بررسی کنید. برای تحلیل دقیق‌تر و Filter کردن Processها از:

SELECT *
FROM information_schema.PROCESSLIST;

استفاده کنید. MariaDB در این جدول اطلاعات بیشتری مانند TIME_MS، Memory مصرفی و Query ID نیز ارائه می‌دهد. اگر بعد از بررسی مشخص شد Query خاصی باید متوقف شود، می‌توانید فقط Statement را با:

KILL QUERY <ID>;

متوقف کنید. Processlist باید بیشتر به‌عنوان ابزار مشاهده و تشخیص زنده استفاده شود؛ برای Queryهای کند، Lockها و Deadlockها از مقالات تخصصی مربوط به همان موضوع استفاده کنید.