كيف تضبط خادم MySQL: buffer pool، وslow query log، والاتصالات؟

Question

IT Interview Practice · Accepted Answer

ضبط الخادم يتبع حلقة: **قِسْ، وجِد عنق الزجاجة، وأصلح شيئًا واحدًا، وأعد القياس.** والمجالات الثلاثة الأعلى فعالية هي InnoDB buffer pool، وslow query log، ومعالجة الاتصالات.

## InnoDB buffer pool — المقبض الأول

الـ buffer pool يخزّن صفحات البيانات والفهارس في الذاكرة (RAM)؛ وعندما تسع مجموعة عملك فيه، تكون القراءات بسرعة الذاكرة ويكاد يختفي إدخال/إخراج القرص. حدّد حجمه بنحو 50–75% من الذاكرة على خادم قاعدة بيانات مخصّص.

```sql
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;  -- 12 GB

-- Health check: hit rate should be ~99%+
SHOW ENGINE INNODB STATUS\G
--   Buffer pool hit rate 999 / 1000     <- good; near 1000/1000 = mostly RAM
```

## slow query log — اعثر على ما تُصلح

```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- log anything over 500 ms
-- Then aggregate with pt-query-digest / mysqldumpslow to rank the worst queries,
-- and EXPLAIN each one. Most wins are a missing index, not a server setting.
```

## الاتصالات

كل اتصال يكلّف ذاكرة وخيوطًا. لا تضبط `max_connections` عاليًا جدًا لإخفاء تسرّب — فتدفّق آلاف الاتصالات يُرهق الخادم. استخدم **connection pool** في التطبيق (أو وكيلًا مثل ProxySQL) للإبقاء على مجموعة صغيرة مُعاد استخدامها، وراقب `Threads_connected` مقابل `Threads_running`.

```sql
SHOW GLOBAL STATUS LIKE 'Threads_running';   -- how many are actually doing work
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```

## ترتيب الهجوم

أصلح أعلى الاستعلامات بطئًا (الفهارس) أولًا — فهذا عادةً يتفوّق على أي تغيير في الإعدادات. ثم اضبط حجم الـ buffer pool بشكل صحيح. وبعد ذلك فقط اضبط `max_connections` و`innodb_log_file_size` وإعدادات الإفراغ (flush). غيّر متغيّرًا واحدًا في كل مرة وأعد القياس.

## لماذا يهم

معظم حوادث "MySQL بطيء" هي حفنة من الاستعلامات غير المُفهرَسة أو buffer pool أصغر من أن يحتوي مجموعة العمل — لا ضبط غريب. المهندس الكبير (senior) يلجأ إلى الـ slow log ومعدّل إصابة الـ buffer pool أولًا، فيُصلح عنق الزجاجة الحقيقي بدل تدوير مقابض الإعدادات عشوائيًا.

InnoDB buffer pool — المقبض الأول

slow query log — اعثر على ما تُصلح

الاتصالات

ترتيب الهجوم

لماذا يهم

أسئلة ذات صلة