I. Giới Thiệu

MySQL là một chương trình dùng để quản lý hệ thống cơ sở dữ liệu (CSDL), CSDL là một hệ thống lưu trữ thông tin cần thiết để bạn 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.

Nói đơn giản hơn là 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.

Nói một cách khoa học MySQL là hệ quản trị cơ sở dữ liệu phổ biến nhất thế giới và được các nhà phát triển rất ưa chuộng trong quá trình phát triển ứng dụng. Vì MySQL là 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. Với tốc độ và tính bảo mật cao, MySQL rất thích hợp cho các ứng dụng có truy cập CSDL trên internet. MySQL miễn phí hoàn toàn cho nên bạn có thể tải về MySQL từ trang chủ. Tham khảo thêm : https://vi.wikipedia.org/wiki/MySQL

Thông thường thì ta không cần tối ưu database nếu website hay dữ liệu của bạn hoạt động ổn. Nhưng khi dữ liệu phìng to thi ta cần phải tối ưu database để hệ thống chạy ổn định hơn.

Sau đây là các cách để bạn có thể tự tối ưu database mysql trên VPS hoặc Server của mình. Nếu bạn đang đang sử dụng hosting mà có nhu cầu chuyển qua VPS hoặc server thì bạn có thể tham khảo mua VPS/server tại đây với giá khuyến mãi tại Hostvn

LƯU Ý: Bạn nên cân nhắc trước những tham số khi tối ưu database MySQL, Nếu bạn không rõ những tham số trên thì bạn không nên cấu hình.

II. Hướng dẫn

Sau đây là các thông tin để bạn có thể tối ưu :

Bước 1: Login ssh vào hệ thống linux

Bước 2: Mở file my.cnf

vi /etc/my.cnf

Các tham số :

• 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_buffer_pool_size = [Thông số ram]

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. Vậy để có thể đưa ra thông số ram ta cần thực hiện như sau :

1. Mỏi processes chiếm bao nhiêu ram, bạn có thể tham khảo thông số trên thông qua lệnh top.

• 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ặt đị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.

Bước 3: Sau khi đã tối ưu xong bạn thực hiện restart lại dịch vụ để kiểm tra tổng thể Mysql

Lưu ý :  Đây chỉ là những cách cơ bản để bạn có thể tối ưu database của mình, còn nhiều cách khác bạn có thể nghiên cứu thêm, hoặc có thể chia sẽ để mình viết bài được tốt hơn. Đồng thời những thông số trên bạn nên cân nhắc trước khi được vào file hoặc thực hiện.