Tối Ưu Database MySQL trên VPS Linux

MySQL là một phần mềm mã nguồn mở và hoàn toàn miễn phí được sử dụng để quản lý cơ sở dữ liệu (database) trên máy tính. Cơ sở dữ liệu là một hệ thống lưu trữ thông tin cần thiết để người dùng có thể quản lý các dữ liệu của mình một cách chuẩn xác và tối ưu thông tin. MySQL có thể được cài đặt trên nhiều nền tảng hệ điều hành khác nhau như Windows, Linux và Mac OS X. 

Thông thường, database không cần thiết phải tối ưu nếu website hay dữ liệu đang hoạt động ổn định. Tuy nhiên, khi dữ liệu phình to ra thì việc tối ưu database là cần thiết để giúp cho hệ thống chạy ổn định hơn. Bài viết dưới đây sẽ giới thiệu cách tối ưu cho MySQL trên VPS Linux.

1, Hiểu rõ hơn về MySQL

MySQL sẽ là nơi lưu trữ một đống bài thi và mõi bài thì là một bảng và trong các bản sẽ có những thông tin. Vậy thông tin của bài thi nào càng rõ ràng càng mạch lạc thì việc truy vẫn sẽ nhanh chóng còn bài thi nào viết nhăn viết cuội thì việc tìm và dữ liệu cực kì khó khăn. Lúc đó thì bạn cần phải tối ưu lại MySQL để MySQL trở nên thông minh hơn và tìm cách giải quyết các thông tin mà đã được lưu trong mysql và phản hồi lại khi có có nhu cầu.

"MySQL là hệ quản trị cơ sở dữ liệu phổ biến nhất thế giới"

- Được ưa chuộng trong quá trình phát triển ứng dụng.
- Cơ sở dữ liệu tốc độ cao, ổn định và dễ sử dụng.
- Có tính khả chuyển, hoạt động trên nhiều hệ điều hành cung cấp một hệ thống lớn các hàm tiện ích rất mạnh.
- Tốc độ - tính bảo mật cao.

2, 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.