Ngăn chặn MySQL Downtime: Thiết lập max_user_connections

Một trong những nguyên nhân phổ biến gây ra downtime ở MySQL là chạy ra khỏi các kết nối. Bạn đã bao giờ  gặp lỗi “ERROR 1040 (00000): Too many connections.” Nếu bạn làm việc với MySQL đủ lâu bạn chắc chắn gặp lỗi này. Đây là một lỗi khó chiu vì nó có thể gây ra downtime hoàn toàn.Các lỗi thoáng qua này với các giao dịch thành công lẫn thất bại cũng như là một vài process dừng lại gây ra các hiệu ứng khác nhau nếu không được giám sát hợp lý.
Có nhiều nguyên nhân gây ra out of connections.Một trong những nguyên nhân phổ biến nhất là khi App/Web Server bất ngờ tạo ra một số lượng lớn các kết nối do lỗi cấu hình hoặc các script/application bị rò rỉ or tạo kết nối quá nhiều lỗi.
Các giải pháp tôi thấy mọi người thường sử dụng là tăng max_connections đến một số rất cao đẻ MySQL không bao giơ  "run out of connections".Tuy nhiên điều này có thể gây ra các vấn đề về sử dụng tài nguyên.Nếu một số lượng lớn kết nối trở nên thực sự hoạt động nó có thể sử dụng nhiều bộ nhớ và làm cho MySQL Server swap or be killed by OOM killer process.hoặc gây ra hiệu năng rất kém do cạnh tranh cao.
Có một giải pháp tốt hơn là sử dụng các tài khoản khác nhau cho các kịch bản khác nhau và thi hành giới hạn tài nguyên cho chúng. Cụ thể thiết lập max_user_connections.

Mysql> GRANT USAGE ON *.* TO 'batchjob1'@'localhost'

    ->     WITH MAX_USER_CONNECTIONS 10;
Cách tiếp cận này (có sẵn từ MySQL 5.0) có nhiều lợi ích:

Security - các tài khoản người dùng khác nhau với chỉ quyền cần làm cho hệ thống của bạn an toàn hơn khỏi những lỗi phát triển và an toàn hơn từ những kẻ xâm nhập
Ngăn chặn chạy ra khỏi Connections - nếu có một lỗi hoặc miss-cấu hình tất nhiên các ứng dụng / script sẽ chạy ra khỏi các kết nối nhưng nó sẽ là phần duy nhất của hệ thống bị ảnh hưởng và tất cả các ứng dụng khác sẽ có thể sử dụng cơ sở dữ liệu thông thường.
Overload Protection -. Số  các kết nối bổ sung giới hạn bao nhiêu query bạn có thể chạy đồng thời, quá nhiều đồng thời thường là nguyên nhân gây ra thời gian chết(Downtime) và hạn chế nó có thể làm giảm tác động của các truy vấn dài hơn mong đợi hoạt động đồng thời của các ứng dụng.

Ngoài cấu hình max_user_connections cho các tài khoản cho bạn có thể thiết lập nó tổng thể trong my.cnf như này là quá thô mặc dù theo ý kiến ​​của tôi "Max_user_connections = 20." - Bạn hầu như sẽ cần một số lượng khác nhau cho các ứng dụng khác nhau / script. Trường max_user_connections là hữu ích nhất là trong môi trường multi-tenant với nhiều người sử dụng tương đương chia sẻ hệ thống.

*Refer: https://www.percona.com/blog/2014/07/29/prevent-mysql-downtime-set-max_user_connections/