«Trang web chậm» gần như luôn dẫn tới cơ sở dữ liệu. Và nếu kiểm tra theo đúng thứ tự thì mất vài phút: trước hết là những gì thấy được ngay trong lúc chậm, rồi mới đến những gì đã tích tụ hàng tuần.

Hãy xem trong lúc vấn đề đang diễn ra

Quy tắc nền tảng: thông tin giá trị nhất chỉ có được đúng lúc mọi thứ đang chậm. Lệnh đầu tiên cho MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Cho 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;"

Bạn sẽ thấy ngay lúc này cái gì đang chạy và đã bao lâu. Và câu trả lời thường đến ngay: một truy vấn nặng nào đó đang chặn tất cả những cái còn lại, hoặc một trăm truy vấn giống nhau đang chờ một khóa được mở.

Kết nối

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

Nếu Max_used_connections tiến sát max_connections thì ứng dụng thỉnh thoảng nhận về lời từ chối «too many connections». Và việc nâng giới hạn không nên là việc đầu tiên: mỗi kết nối chiếm bộ nhớ, và trên một máy chủ vốn đã thiếu bộ nhớ thì việc nâng nó chỉ đẩy nhanh hành trình tới swap. Trước hết hãy tìm hiểu vì sao các kết nối không được giải phóng: thường là một truy vấn dài nào đó hoặc việc thiếu connection pool ở phía ứng dụng.

Và cũng nên nhìn từ phía kia: việc kết nối tăng vọt có thể đến không phải từ độ phổ biến tăng lên mà từ các bot đang nện vào một trang nặng nào đó — một trang tìm kiếm, một bộ lọc danh sách, một trình tạo báo cáo.

Truy vấn chậm

Không có nhật ký truy vấn chậm thì chẳng đi đâu được. MySQL:

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

Một ngày sau:

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

PostgreSQL, trong postgresql.conf:

log_min_duration_statement = 1000

Sau đó là phần mở rộng pg_stat_statements, thứ hiển thị trực tiếp tổng thời gian cho từng truy vấn:

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

Hãy chú ý: cái quan trọng không phải là truy vấn dài nhất mà là cái có tổng thời gian lớn nhất. Một truy vấn 50 mili giây chạy một nghìn lần mỗi phút gây hại nhiều hơn một báo cáo mười giây chạy mỗi ngày một lần.

Cấu hình bị quên nhiều nhất

Với MySQL và MariaDB đó là kích thước buffer pool của InnoDB. Giá trị mặc định là 128 megabyte và đã không đổi suốt nhiều thập kỷ. Và trên một máy chủ mà cơ sở dữ liệu chiếm vài gigabyte thì điều đó nghĩa là liên tục đọc từ đĩa thay vì từ bộ nhớ.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Thước đo hợp lý cho một máy chủ cơ sở dữ liệu chuyên dụng là một nửa RAM; còn cho máy chủ có cả máy chủ web và PHP chạy cùng thì là một phần tư, tính sao cho không rơi vào swap. Và chỉ một cấu hình này thường mang lại nhiều hơn cả một tuần tối ưu truy vấn.

Hai thứ lặng lẽ ngốn đĩa

Nhật ký nhị phân của MySQL. Chúng cần cho việc nhân bản và khôi phục về một thời điểm, liên tục lớn lên, và theo mặc định đôi khi chẳng bao giờ bị xóa. Để kiểm tra:

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

Nếu bạn không có nhân bản và không dùng việc khôi phục về một thời điểm thì hãy đặt thời hạn lưu là vài ngày hợp lý.

Các tệp WAL của PostgreSQL với một slot nhân bản bị bỏ quên. Tình huống ít được biết hơn và nguy hiểm hơn: nếu tồn tại một slot nhân bản mà bên tiêu thụ của nó không còn kết nối nữa thì PostgreSQL buộc phải giữ mọi nhật ký kể từ thời điểm mất kết nối — và sẽ giữ cho tới khi hết đĩa.

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

Một slot không hoạt động mà chẳng ai cần như vậy thì nên xóa đi. Và đây là một trong số ít tình huống mà cơ sở dữ liệu chắc chắn đưa đĩa về không.

Kích thước và tăng trưởng

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;

Và cái hữu ích không phải con số hiện tại mà là tốc độ thay đổi của nó. Một cơ sở dữ liệu tăng gấp đôi trong một tháng mà lưu lượng không tăng tương ứng thường nghĩa là có một bảng ghi nhận nào đó đang được ghi vào và chẳng bao giờ được dọn — phiên, lịch sử tác vụ, nhật ký sự kiện của một plugin nào đó.

An toàn trong hai dòng

Và khi console cơ sở dữ liệu đã mở sẵn thì có hai thứ đáng kiểm tra. Thứ nhất, cơ sở dữ liệu có với tới được từ bên ngoài không (ss -tulpn | grep 3306): nó gần như không bao giờ nên hướng ra ngoài. Và thứ hai, các tài khoản có thể kết nối từ bất kỳ địa chỉ nào:

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

Một bản ghi với host = '%' nghĩa là «kết nối từ mọi nơi». Và nếu cổng cũng đang mở thì giữa cơ sở dữ liệu và Internet chỉ còn đúng một mật khẩu.

Sao lưu

Điều cuối cùng. Việc có một tệp sao lưu chẳng bảo đảm gì; chỉ một lần khôi phục đã được thử mới bảo đảm. Một bản sao chưa từng được khôi phục có xác suất ngang nhau là bị cắt ngang, được lấy kèm lỗi khóa, hoặc chứa nhầm cơ sở dữ liệu. Và điều đó phải kiểm tra trước, trên một máy riêng, chứ không phải vào cái ngày cần đến bản sao.

Với việc theo dõi hằng ngày thì ba con số là đủ: số kết nối, số truy vấn chậm mỗi ngày, và kích thước cơ sở dữ liệu. Cả ba đều thay đổi chậm và dự đoán được, và một chuyển động nhanh của bất kỳ cái nào cũng đáng để liếc nhìn. Trên một trang thì chúng trông ra sao — xem ở demo bên dưới.