Πώς κάνεις tune έναν MySQL server: buffer pool, slow query log και connections;

Question

IT Interview Practice · Accepted Answer

Το tuning ενός server ακολουθεί έναν βρόχο: **μέτρα, βρες το bottleneck, διόρθωσε ένα πράγμα, ξαναμέτρα.** Οι τρεις περιοχές με τη μεγαλύτερη μόχλευση είναι το InnoDB buffer pool, το slow query log και ο χειρισμός των connections.

## InnoDB buffer pool — το κουμπί #1

Το buffer pool κρατά στη cache data και index pages στη RAM· όταν το working set σου χωράει σε αυτό, τα reads είναι σε ταχύτητα μνήμης και το disk I/O σχεδόν εξαφανίζεται. Όρισε το μέγεθός του στο ~50–75% της RAM σε έναν αποκλειστικό DB server.

```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.
```

## Connections

Κάθε connection κοστίζει μνήμη και threads. Μη βάζεις το `max_connections` στα ύψη για να κρύψεις ένα leak — ένα ξέσπασμα χιλιάδων connections οδηγεί τον server σε thrashing. Χρησιμοποίησε ένα **connection pool** στην εφαρμογή (ή έναν proxy όπως το 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';
```

## Σειρά επίθεσης

Διόρθωσε πρώτα τα κορυφαία slow queries (indexes) — αυτό συνήθως υπερτερεί κάθε αλλαγής config. Μετά όρισε το σωστό μέγεθος για το buffer pool. Μόνο τότε κάνε tune τα `max_connections`, `innodb_log_file_size` και τις ρυθμίσεις flush. Άλλαζε μία μεταβλητή τη φορά και ξαναμέτρα.

## Γιατί έχει σημασία

Τα περισσότερα περιστατικά "η MySQL είναι αργή" είναι μια χούφτα queries χωρίς index ή ένα buffer pool πολύ μικρό για να χωρέσει το working set — όχι εξωτικό tuning. Ένας senior engineer καταφεύγει πρώτα στο slow log και στο buffer-pool hit rate, διορθώνοντας το πραγματικό bottleneck αντί να στρίβει στα τυφλά κουμπιά config.

InnoDB buffer pool — το κουμπί #1

Slow query log — βρες τι να διορθώσεις

Connections

Σειρά επίθεσης

Γιατί έχει σημασία

Σχετικές ερωτήσεις