Tìm hiểu và cấu hình 2 giá trị “wait_timeout”, ”interactive_timeout” trong MySQL
Trong một số trường hợp, các ứng dụng không thể đóng kết nối tới Cơ Sở Dữ Liệu mà ứng dụng đã không còn sử dụng kết nối đó nữa. Lúc đó các kết nối như vậy sẽ dễ rơi vào trạng thái “Sleeping idle connections”. Như vậy trình tự cơ bản phổ biến nhất dẫn đến trạng thái “Sleeping Idle Connection” như sau :
- PHP script thực hiện kết nối tới MySQL.
- Một vài query được thực thi nhưng sau đó script PHP phải làm các tác vụ khác tiêu tốn nhiều thời gian hơn mà không ngắt kết nối tới CSDL.
- PHP script thực hiện hoàn thành và sau đấy kết thúc hoạt động script, lúc này mới ngừng kết nối tới CSDL MySQL.
Như vậy nếu code PHP không thể thực hiện đóng kết nối tới CSDL sẽ có thể dẫn đến nhiều kết nối “Sleep Idle” khi mà rất nhiều tiến trình PHP vẫn còn giữ kết nối nhưng không làm gì cả.
Trên hệ thống dịch vụ MySQL thường sẽ thấy hiện tượng gì ?
- Load hệ thống tăng cao.
- Nhiều tiến trình nằm trong trạng thái idle của MySQL.
Ví dụ: Liệt kê các kết nối đang ở trạng thái “Sleep Idle”
mysql> show full processlist; +-------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------------------------------------------------------------------------------+ | 398780 | dbuser | localhost | database | Sleep | 169 | | NULL | | 399056 | dbuser | localhost | database | Sleep | 129 | | NULL | | 399491 | dbuser | localhost | database | Sleep | 87 | | NULL | +-------------------------------------------------------------------------------+
Một số cách giải quyết cơ bản như sau :
– Giảm thời gian mà query phải chạy để lấy được kết quả, hãy tối ưu query có lệnh dài nhất chẳng hạn, tối ưu dữ liệu trả về,..
– Hệ thống dịch vụ MySQL có hỗ trợ cấu hình quy định thời gian dừng kết nối đang chế độ rỗi bằng 2 giá trị “wait_timeout” và “interactive_timeout” mà ta sẽ tìm hiểu sau đây.
1.Tìm hiểu về 2 biến cấu hình “wait_timeout” và “interactive_timeout”
Thông tin biến cấu hình “wait_timeout“
– Loại : integer
– Giá trị mặc định : 28800 (8 giờ)
– Giá trị nhỏ nhất có thể quy định : 1
– Giá trị lớn nhất có thể quy định : 31536000
– Phân loại nhóm : Global, Session
Ý nghĩa của giá trị “wait_timeout“
– Là giá trị đại diện cho khoảng thời gian mà dịch vụ MySQL sẽ chờ đợi trước khi ngắt các kết nối đang ở chế độ rảnh rỗi (idle connections) được dùng trong phiên kết nối chế độ “non-interactive” . Tức là kết nối không có tương tác trực tiếp từ người dùng cụ thể là bàn phím (terminal shell).
– Khi mà một thread xử lý kết nối từ người dùng được khởi tạo, thì giá trị “wait_timeout” của 1 phiên hoạt động (session) sẽ được khởi tạo dựa trên giá trị Global wait_timeout”
Thông tin biến cấu hình “interactive_timeout“
– Loại : integer
– Giá trị mặc định : 28800 (8 giờ)
– Giá trị nhỏ nhất có thể quy định : 1
– Phân loại nhóm : Global, Session
Ý nghĩa của giá trị “interactive_timeout“
– Khi bạn sử dụng terminal shell, dùng chương trình lệnh “mysql” và thực thi lệnh query trên terminal gõ nhập từ bàn phím. Đó sẽ là phiên kết nối “interactive”.
– Nếu query thực hiện trên shell thì sau khoảng thời gian “interactive_timeout” thì MySQL sẽ tự động ngắt kết nối.
Khi nào nên set giá trị “wait_timeout” cao hay thấp ?
Bạn nên xem xét và phân loại website của mình thuộc dạng nào sau đây :
– Website có mã nguồn sử dụng query để lấy/cập nhật dữ liệu ít không lâu.
– Website có mã nguồn yêu cầu hoạt động core cần giữ kết nối tới CSDL lâu để hoàn thành nội dung của chức năng ứng dụng.
2. Hướng dẫn set giá trị “wait_timeout” và “interactive_timeout”
Gán giá trị trong file cấu hình dịch vụ MySQL
– Bạn không biết file “my.cnf” quy định cấu hình dịch vụ MySQL, thì bạn sẽ làm bước này trước.
# mysql --help | grep “my.cnf" order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
– Trên hệ thống CentOS của mình là “/etc/my.cnf”.
# vi /etc/my.cnf wait_timeout=300 interactive_timeout=300
– Khởi động lại dịch vụ MySQL
# /etc/init.d/mysqld restart
Kiểm tra các giá trị đã gán
mysql> show global variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | interactive_timeout | 300 | | wait_timeout | 300 | +----------------------------+----------+
Như vậy bạn đã thay đổi cấu hình “wait_timeout” và “interactive_timeout” thành công. Nên nhớ cần phân loại vấn đề website bạn có hoạt động yêu cầu thời gian kết nối tới Cơ Sở Dữ Liệu lâu như source Magento, Cs-Cart chẳng hạn.
Chúc các bạn thành công !
Tham khảo: Tìm hiểu và cấu hình 2 giá trị “wait_timeout” và ”interactive_timeout” trong MySQL
