Khi dùng mysql rất hay bị lỗi chiến hết CPU khi để mặc định, nhất là xampp, VPS, VMware Workstation Pro, ... Bài này sẽ hướng dẫn các bạn tối ưu hóa mysql

• Trên Windows >>

• Trên Linux >>

Tối Ưu Database MySQL trên Windows, chống mysql chiếm CPU

1. Cách tối ưu database MySQL

Mở file my.ini thường trong thư mụ bin ở thư mục cài đặt mysql

- Bật chức năng InnoDB file-per-table

innodb_file_per_table=1

Tại sao lại tối ưu thông số trên vì MySQL mặc định sử dụng InnoDB (storage engine). InnoDB cung cấp cho chung ta khả năng xử lý linh hoạt database những thông tin được lưu bên trong file .IDB.

Một lợi ít khác đó là nó cải thiện được tốc độ I/O trên VPS hoặc server của bạn.

Mặt định innodb_file_per_table được bật trên version 5.6

- Tối ưu InnoDB buffer pool

InnoDB engine dùng buffer pool used cho caching data và index trên memory. Thông số này giúp cho MySQL sẽ thực hiện queries nhanh hơn.

- Loại bỏ Swappiness in MySQL

Swapping là tiến trình xuất hiện khi hệ thống phải sử dụng swap disk khi hết Ram. Hệ thống sẽ đưa các thông tin vào ổ đĩa. Mà ổ đĩa thì read chậm hơn ram rất nhiều.

Mặc định :

Bạn disable bằng command sau :

Lưu ý : trường hợp này bạn không lưu vào file my.ini mà thực hiện trực tiếp trên hệ thống. Nếu bạn không thực hiện được là do VPS của bạn bị hạn chế, hãy liên hệ với nhà cung cấp để được hỗ trợ.

- Tham số Max connections

max_connections sẽ thông báo với server bao nhiêu kết nối được cho phép. Kết nối chỉ được mở trong thời gian MySQL thực hiện tiến trình queries, sau khi thực hiện xong tiến trình sẽ đóng và tạo một kết nối khác.

Quá nhiều kết nối thì sẽ làm cho Ram load cao, và nếu vượt quá thì MySQL sẽ treo tiến trình. Đối với những website vừa và nhỏ thì ta chỉ cần để max = 80 đến 200. Còn website có lượng kết nối lớn thì khoản 200 đến 600 hoặc hơn tùy thuộc vào resource của server.

max_connections = 200

- Tham số MySQL thread_cache_size

MySQL thread_cache_size cho chúng ta biết số lượng threads trên server sẽ được cache.

Để tính được thông số ta thực hiện như sau :

mysql> show status like ‘Threads_created’;
mysql> show status like ‘Connections’;

-> Sau khi có 2 thông số thì tính ra được Cache hit:

100 – ((Threads_created / Connections) * 100))

-> Đưa vào file my.ini

thread_cache_size = [Thông số tìm được]

- Tắt MySQL reverse DNS lookups

skip-name-resolve

Mặc định thì MySQL thực hiện DNS lookup những IP connect tới. Với mỏi Client connect thì địa chỉ IP sẽ được kiểm tra và phân giải. Sau đó Hostname sẽ được phân giải và trả về IP. Điều này sẽ làm cho quá trình bị chậm trê khi DNS có vấn đề . Vậy ta nên tắt chức năng này để tăng khả năng phản hồi cho VPS.

- Cấu hình MySQL query_cache_size

Nếu bạn có nhiều truy vấn được lập đi lập lại data mà dữ liệu của bạn không thay đổi thường xuyên thì dùng query cache.Thường thì đa phần sẽ không hiểu được khái niệm này. và sẽ set giái trị gigabytes.

Đối với website lớn thì cần từ 200 đến 500 MB, còn những website nhỏ thì chỉ cần khoản 64-128 MB

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k

query_cache_size = 64M

- Cấu hình tmp_table_size và max_heap_table_size

Cả 2 thông số trên nhàm hạn chế việc ghi vào ổ đĩa. tmp_table_size là thông số tối đa kích cở bên trong của một memory tables.Trong trường hợp vượt quá giới hạn bảng ghi sẽ được chuyển qua Disk myISAM. Để database có hiệu suất tốt thì đề nghị nên để giá trị khoản 64M cho mỏi GB ram.

tmp_table_size= 64M

max_heap_table_size= 64M

- Bât MySQL slow query logs

Logging query chậm có thể giúp bạn xác định các cơ sỡ dữ liệu và và debug.

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

- Kiểm tra MySQL idle connetions

Idle kết nối tiêu hao resource và có thể sẽ bị gián đoạn hoặc phải frefreshed. Giống như nhiều connect trong tình trạng “sleep” và trong thời gian dài. Để kiểm tra các kết nối trên ta thực hiện như sau :

command sẽ cho chúng ta thấy được các trạng thái Sleep, Khi php thực hiện truy vấn vào mysql mở kết nối sau đó querry , loại bỏ xác thực và mở kết nối. Điều này sẽ làm được lưu trên memory cho đến khi thread không hoạt động.

Đối với vấn đề này bạn cần kiểm tra lại source code và fix. Nếu bạn không fix thì bạn có thể thay đổi source code hoặc không biết cách sửa lỗi, vậy cách tạm thời hãy thay đổi thông số mặc định của nó xuong khoản 60.

wait_timeout=60

- Cấu hình MySQL max_allowed_packet

MySQL chia data trong một gói. Theo thông thường thì 1 package được cho là 1 hàng gửi đến client. max_allowed_packet sẽ định nghĩ giá trị lớn nhất được gửi đi.

Bạn có thể set giá trị này với website nhỏ từ 128M đến 256M. Đối với website lớn thì khoản 512 đến 1024M.

- Thường xuyên optimize và repair MySQL

Thường thì đôi lúc ta cần phải thực hiện tối ưu và repair database để tránh tình trạng bị crashed.

Tối Ưu Database MySQL trên Linux, chống mysql chiếm CPU

1. Cách tối ưu database MySQL

Bước 1: Login ssh vào hệ thống Linux
Bước 2: Mở file my.cnf

vi /etc/my.cnf

- Bật chức năng InnoDB file-per-table

innodb_file_per_table=1

Tại sao lại tối ưu thông số trên vì MySQL mặc định sử dụng InnoDB (storage engine). InnoDB cung cấp cho chung ta khả năng xử lý linh hoạt database những thông tin được lưu bên trong file .IDB.

Một lợi ít khác đó là nó cải thiện được tốc độ I/O trên VPS hoặc server của bạn.

Mặt định innodb_file_per_table được bật trên version 5.6

- Tối ưu InnoDB buffer pool

InnoDB engine dùng buffer pool used cho caching data và index trên memory. Thông số này giúp cho MySQL sẽ thực hiện queries nhanh hơn.

- Loại bỏ Swappiness in MySQL

Swapping là tiến trình xuất hiện khi hệ thống phải sử dụng swap disk khi hết Ram. Hệ thống sẽ đưa các thông tin vào ổ đĩa. Mà ổ đĩa thì read chậm hơn ram rất nhiều.

Mặc định :

Bạn disable bằng command sau :

Lưu ý : trường hợp này bạn không lưu vào file my.cnf mà thực hiện trực tiếp trên hệ thống. Nếu bạn không thực hiện được là do VPS của bạn bị hạn chế, hãy liên hệ với nhà cung cấp để được hỗ trợ.

- Tham số Max connections

max_connections sẽ thông báo với server bao nhiêu kết nối được cho phép. Kết nối chỉ được mở trong thời gian MySQL thực hiện tiến trình queries, sau khi thực hiện xong tiến trình sẽ đóng và tạo một kết nối khác.

Quá nhiều kết nối thì sẽ làm cho Ram load cao, và nếu vượt quá thì MySQL sẽ treo tiến trình. Đối với những website vừa và nhỏ thì ta chỉ cần để max = 80 đến 200. Còn website có lượng kết nối lớn thì khoản 200 đến 600 hoặc hơn tùy thuộc vào resource của server.

max_connections = 200

- Tham số MySQL thread_cache_size

MySQL thread_cache_size cho chúng ta biết số lượng threads trên server sẽ được cache.

Để tính được thông số ta thực hiện như sau :

mysql> show status like ‘Threads_created’;
mysql> show status like ‘Connections’;

-> Sau khi có 2 thông số thì tính ra được Cache hit:

100 – ((Threads_created / Connections) * 100))

-> Đưa vào file my.cnf

thread_cache_size = [Thông số tìm được]

- Tắt MySQL reverse DNS lookups

skip-name-resolve

Mặc định thì MySQL thực hiện DNS lookup những IP connect tới. Với mỏi Client connect thì địa chỉ IP sẽ được kiểm tra và phân giải. Sau đó Hostname sẽ được phân giải và trả về IP. Điều này sẽ làm cho quá trình bị chậm trê khi DNS có vấn đề . Vậy ta nên tắt chức năng này để tăng khả năng phản hồi cho VPS.

- Cấu hình MySQL query_cache_size

Nếu bạn có nhiều truy vấn được lập đi lập lại data mà dữ liệu của bạn không thay đổi thường xuyên thì dùng query cache.Thường thì đa phần sẽ không hiểu được khái niệm này. và sẽ set giái trị gigabytes.

Đối với website lớn thì cần từ 200 đến 500 MB, còn những website nhỏ thì chỉ cần khoản 64-128 MB

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k

query_cache_size = 64M

- Cấu hình tmp_table_size và max_heap_table_size

Cả 2 thông số trên nhàm hạn chế việc ghi vào ổ đĩa. tmp_table_size là thông số tối đa kích cở bên trong của một memory tables.Trong trường hợp vượt quá giới hạn bảng ghi sẽ được chuyển qua Disk myISAM. Để database có hiệu suất tốt thì đề nghị nên để giá trị khoản 64M cho mỏi GB ram.

tmp_table_size= 64M

max_heap_table_size= 64M

- Bât MySQL slow query logs

Logging query chậm có thể giúp bạn xác định các cơ sỡ dữ liệu và và debug.

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

- Kiểm tra MySQL idle connetions

Idle kết nối tiêu hao resource và có thể sẽ bị gián đoạn hoặc phải frefreshed. Giống như nhiều connect trong tình trạng “sleep” và trong thời gian dài. Để kiểm tra các kết nối trên ta thực hiện như sau :

command sẽ cho chúng ta thấy được các trạng thái Sleep, Khi php thực hiện truy vấn vào mysql mở kết nối sau đó querry , loại bỏ xác thực và mở kết nối. Điều này sẽ làm được lưu trên memory cho đến khi thread không hoạt động.

Đối với vấn đề này bạn cần kiểm tra lại source code và fix. Nếu bạn không fix thì bạn có thể thay đổi source code hoặc không biết cách sửa lỗi, vậy cách tạm thời hãy thay đổi thông số mặc định của nó xuong khoản 60.

wait_timeout=60

- Cấu hình MySQL max_allowed_packet

MySQL chia data trong một gói. Theo thông thường thì 1 package được cho là 1 hàng gửi đến client. max_allowed_packet sẽ định nghĩ giá trị lớn nhất được gửi đi.

Bạn có thể set giá trị này với website nhỏ từ 128M đến 256M. Đối với website lớn thì khoản 512 đến 1024M.

- Thường xuyên optimize và repair MySQL

Thường thì đôi lúc ta cần phải thực hiện tối ưu và repair database để tránh tình trạng bị crashed.