الموقع بطيء: إلى ماذا تنظر في MySQL و PostgreSQL

«الموقع بطيء» ينتهي دائمًا تقريبًا إلى قاعدة البيانات. والفحص يستغرق دقائق قليلة إن جرى بالترتيب الصحيح: أولًا ما يُرى أثناء البطء نفسه، ثم ما تراكم على مدى أسابيع.

انظر بينما المشكلة قائمة

القاعدة الأساسية: أثمن المعلومات متاح بالضبط بينما كل شيء بطيء. والأمر الأول لـ 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;

وانتبه: المهم ليس أطول استعلام بل صاحب أكبر زمن إجمالي. فاستعلام مدته 50 مللي ثانية ينفَّذ ألف مرة في الدقيقة يضر أكثر من تقرير مدته عشر ثوانٍ مرة في اليوم.

الإعداد الأكثر نسيانًا

بالنسبة إلى MySQL و MariaDB هو حجم مجمّع مخازن InnoDB المؤقتة. والقيمة الافتراضية 128 ميغابايت ولم تتغيّر منذ عقود. وعلى خادم تشغل فيه القاعدة عدة غيغابايتات يعني ذلك قراءة دائمة من القرص بدل الذاكرة.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

والمعيار المعقول لخادم قاعدة بيانات مخصص هو نصف الذاكرة؛ ولخادم يشغّل أيضًا خادم ويب و 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 = '%' تعني «اتصالات من كل مكان». وإن كان المنفذ مفتوحًا كذلك، فلا يقف بين القاعدة والإنترنت سوى كلمة مرور.

النسخ الاحتياطية

أمر أخير. وجود ملف نسخة احتياطية لا يضمن شيئًا؛ وحدها الاستعادة المختبَرة تضمن. فنسخة لم تُستعَد قط تحتمل بالقدر نفسه أن تكون مبتورة، أو مأخوذة مع خطأ قفل، أو تحتوي قاعدة خاطئة. وينبغي فحص ذلك مسبقًا وعلى آلة منفصلة، لا يوم الحاجة إلى النسخة.

وللمراقبة اليومية تكفي ثلاثة أرقام: عدد الاتصالات، وعدد الاستعلامات البطيئة يوميًا، وحجم القاعدة. وثلاثتها تتغيّر ببطء وعلى نحو متوقَّع، وأي حركة حادة في أحدها تستحق نظرة. وشكلها على صفحة واحدة يعرضه العرض التوضيحي أدناه.