«سایت کند است» تقریباً همیشه به پایگاه داده می‌رسد. و بررسی اگر با ترتیب درست انجام شود چند دقیقه طول می‌کشد: نخست آنچه هنگام خود کندی دیده می‌شود، سپس آنچه هفته‌ها انباشته شده است.

وقتی مشکل در جریان است بنگرید

قاعده اصلی: ارزشمندترین آگاهی دقیقاً هنگامی در دسترس است که همه‌چیز کند است. نخستین فرمان برای MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

برای PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

خواهید دید هم‌اکنون چه چیزی اجرا می‌شود و چه مدت. و پاسخ معمولاً بی‌درنگ می‌آید: یک پرس‌وجوی سنگین بقیه را نگه داشته، یا صد پرس‌وجوی یکسان منتظر برداشته‌شدن قفلی‌اند.

اتصال‌ها

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

اگر Max_used_connections به max_connections نزدیک شود، برنامه گاه‌به‌گاه رد «too many connections» می‌گیرد. و بالابردن سقف نخستین کاری نیست که باید کرد: هر اتصال حافظه می‌گیرد، و بالابردنش روی سروری که از پیش حافظه کم دارد تنها راه رفتن به ناحیه مبادله را تندتر می‌کند. نخست دریابید چرا اتصال‌ها آزاد نمی‌شوند: معمولاً پرس‌وجویی طولانی یا نبودِ حوضچه اتصال در سمت برنامه.

و ارزش دارد از سوی دیگر هم بنگرید: افزایش ناگهانی اتصال‌ها ممکن است نه از رشد محبوبیت بلکه از ربات‌هایی بیاید که صفحه‌ای سنگین را می‌کوبند — جست‌وجویی، پالایه‌ای در فهرست، یا سازنده گزارش.

پرس‌وجوهای کند

بدون گزارش پرس‌وجوهای کند جایی برای رفتن نیست. MySQL:

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

یک شبانه‌روز بعد:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL، در postgresql.conf:

log_min_duration_statement = 1000

سپس افزونه pg_stat_statements که زمان کل هر پرس‌وجو را مستقیم نشان می‌دهد:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

توجه کنید: مهم طولانی‌ترین پرس‌وجو نیست بلکه آن است که بیشترین زمان کل را دارد. پرس‌وجویی ۵۰ میلی‌ثانیه‌ای که هزار بار در دقیقه اجرا می‌شود بیش از گزارشی ده‌ثانیه‌ای که روزی یک‌بار اجرا می‌شود زیان می‌رساند.

تنظیمی که بیش از همه فراموش می‌شود

برای MySQL و MariaDB این اندازه حوضچه بافر InnoDB است. مقدار پیش‌فرض ۱۲۸ مگابایت است و دهه‌هاست تغییر نکرده. و روی سروری که پایگاه چند گیگابایت جا می‌گیرد این یعنی خواندن پیوسته از دیسک به‌جای حافظه.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

معیار معقول برای سرور اختصاصی پایگاه داده نیمی از RAM است؛ و برای سروری که وب‌سرور و PHP هم روی آن است یک‌چهارم، با نگاه به اینکه به ناحیه مبادله نرود. و همین یک تنظیم معمولاً بیش از یک هفته بهینه‌سازی پرس‌وجوها می‌دهد.

دو چیزی که بی‌صدا دیسک را می‌خورند

گزارش‌های دودویی MySQL. برای همانندسازی و بازگردانی به نقطه‌ای در زمان لازم‌اند، پیوسته رشد می‌کنند، و به‌صورت پیش‌فرض گاهی هرگز حذف نمی‌شوند. برای بررسی:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

اگر همانندسازی ندارید و از بازگردانی به نقطه‌ای در زمان استفاده نمی‌کنید، نگهداری را روی چند روز معقول بگذارید.

فایل‌های WAL در PostgreSQL با شکاف همانندسازی فراموش‌شده. حالتی کمتر شناخته و خطرناک‌تر: اگر شکاف همانندسازی باشد اما مصرف‌کننده‌اش دیگر متصل نشود، PostgreSQL موظف است هر گزارشی را از لحظه قطع نگه دارد — و تا تمام‌شدن دیسک نگه خواهد داشت.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

شکاف غیرفعالی که کسی به آن نیاز ندارد باید حذف شود. و این یکی از معدود وضعیت‌هایی است که پایگاه داده دیسک را با اطمینان به صفر می‌رساند.

اندازه و رشد

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

و سودمند نه عدد کنونی بلکه سرعت تغییر آن است. پایگاهی که در یک ماه دو برابر شده بی‌آنکه ترافیک به همان اندازه رشد کرده باشد، معمولاً یعنی جدولی برای ثبت نوشته می‌شود و هرگز پاک نمی‌گردد — نشست‌ها، تاریخچه وظیفه‌ها، یا گزارش رویدادهای افزونه‌ای.

امنیت در دو سطر

و حالا که کنسول پایگاه باز است، دو چیز ارزش بررسی دارند. نخست، اینکه آیا پایگاه از بیرون دسترس‌پذیر است (ss -tulpn | grep 3306): تقریباً هرگز نباید بیرون را بنگرد. و دوم، حساب‌هایی که می‌توانند از هر نشانی متصل شوند:

mysql -e "SELECT user, host FROM mysql.user"

ثبت‌هایی با host = '%' یعنی «اتصال از هر جا». و اگر پورت هم باز باشد، تنها چیزی که میان پایگاه و اینترنت ایستاده یک گذرواژه است.

نسخه‌های پشتیبان

یک چیز آخر. وجود فایل پشتیبان چیزی را تضمین نمی‌کند؛ تنها بازگردانی آزموده تضمین می‌کند. نسخه‌ای که هرگز بازگردانده نشده به همان اندازه ممکن است بریده باشد، یا با خطای قفل گرفته شده باشد، یا پایگاه اشتباهی در آن باشد. و این را باید از پیش و روی ماشینی جداگانه بررسی کرد، نه روزی که نسخه لازم می‌شود.

برای پایش روزمره سه عدد بس است: شمار اتصال‌ها، شمار پرس‌وجوهای کند در شبانه‌روز، و اندازه پایگاه. هر سه کند و پیش‌بینی‌پذیر تغییر می‌کنند، و هر جهش تند در یکی از آن‌ها ارزش نگاه دارد. شکل این‌ها در یک صفحه را نمایش پایین نشان می‌دهد.