10 cách tối ưu database MYSQL trên VPS linux
I. Giới Thiệu MYSQL
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.
LƯU Ý:
Bạn nên cân nhắc trước những tham số khi tối ưu database MySQL và backup toàn bộ database trước khi cấu hình, Nếu bạn không rõ những tham số trên thì bạn không nên cấu hình.
Mọi thông số cấu hình MySQL đều nằm trong file /etc/my.cnf
Trước khi cấu hình bạn cần lưu lại file trên và nội dung bên trong, nếu bạn không lưu thì bạn cần biết chính xác mình cấu hình gì bên trong.
Sau khi cấu hình trong file /etc/my.cnf thì bạn cần restart lại MySQL để có thể áp dụng ngay.
II. Hướng dẫn tối ưu mysql
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. Bạn có thể tham khảo bài viết sau: Linux top command
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 :
sysctl vm.swappiness vm.swappiness = 60
Bạn disable bằng command sau :
sysctl -w vm.swappiness=0
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 M
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 :
mysqladmin processlist -u root -p | grep “Sleep”
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.
# mysqlcheck -u root -p --auto-repair --check --optimize --all-databases # mysqlcheck -u root -p --auto-repair --check --optimize [Tên database cần sửa]
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
service mysqld restart
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.