[MySQL] Tối ưu hóa MySQL - Tăng tốc độ truy vấn SQL
Điều Chỉnh Hiệu Suất Trong MySQL – Performance Tuning In Database MySQL
Structured Query Language (SQL) là ngôn ngữ truy vấn có cấu trúc được sử dụng để lưu trữ, thao tác và truy xuất dữ liệu từ cơ sở dữ liệu. Tất cả các hệ thống quản lý cơ sở dữ liệu quan hệ (RDMS) như MySQL, MS Access, Oracle, Sybase, Informix, Postgres và SQL Server đều sử dụng SQL làm ngôn ngữ cơ sở dữ liệu chuẩn. Bằng cách sử dụng các câu lệnh SQL, các developers dễ dàng create, update và delete dữ liệu. Khi khối lượng data tăng lên và technology ngày càng phức tạp, việc tối ưu hóa cơ sở dữ liệu MySQL đúng cách để tăng trải nghiệm người dùng và giảm chi phí cơ sở hạ tầng trở nên quan trọng hơn.
https://i.imgur.com/ekG69nS.png
Với sự phức tạp của khối data ngày càng tăng và các thao tác công việc thay đổi liên tục, thì việc điều chỉnh database performance và tối ưu hóa lệnh truy vấn MySQL rất cần thiết để tối đa hóa việc sử dụng resource và system performance.
MySQL là một ứng dụng cơ sở dữ liệu mã nguồn mở tạo ra cấu trúc có ý nghĩa và khả năng truy cập cho một lượng lớn dữ liệu. Tuy nhiên, với dữ liệu lớn đi kèm với các vấn đề về hiệu suất. Bài viết này sẽ cung cấp cho bạn các mẹo điều chỉnh hiệu suất trong MySQL để tăng hiệu suất của nó.
I - Đảm bảo bạn đang sử dụng phiên bản MYSQL mới nhất
- Nếu bạn là cơ sở dữ liệu cũ hoặc cũ hơn, điều này có thể không khả thi. Nhưng nếu có, hãy kiểm tra phiên bản MySQL mới nhất và nâng cấp lên phiên bản mới nhất ( cơ sở dữ liệu NoSQL gần đây nhất ).
- Các phiên bản mới hơn đi kèm với các cải tiến hiệu suất theo mặc định, giúp bạn không cần phải tìm thêm các giải pháp để điều chỉnh hiệu suất phổ biến trong các vấn đề MySQL. Thông thường, tốt nhất là sử dụng các cải tiến hiệu suất MySQL mặc định hoặc nguyên bản thay vì các tệp cấu hình hoặc tập lệnh.
II - Chọn InnoDB thay vì MyISAM
- MyISAM có ít tính năng nâng cao hơn InnoDB. Nhìn chung, MyISAM kém hiệu quả hơn, với ít cải tiến tối ưu hóa gốc hơn. Ví dụ, InnoDB có một chỉ mục theo nhóm, với dữ liệu trong các trang và các khối vật lý liên tiếp. Nếu một giá trị quá nhiều để vừa với một trang, InnoDB sẽ di chuyển nó đến một vị trí khác, lập chỉ mục giá trị của nó – dữ liệu thích hợp vẫn ở cùng một vị trí trên ổ cứng, rút ngắn thời gian truy xuất nó.
III - Performance Tuning in database MySQL
- 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.
- 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.
1. Disable 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. Nên tốt nhất là disable chức năng này
- Để disble swappiness, chúng ta chạy lệnh sau trên putty:
Mã:
echo 0 >/proc/sys/vm/swappiness
- hoặc sửa thông số trong file /proc/sys/vm/swappiness = 0
Mã:
/proc/sys/vm/swappiness
2. Mysql query cache
- 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ừ 256 MB đến 512 MB, còn những website nhỏ thì chỉ cần khoản 64-128 MB
Mã:
query_cache_type=1
query_cache_limit=256K
query_cache_min_res_unit=2k
query_cache_size = 500M
3. InnoDB file-per-table
- 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
- Nếu chưa bật ta thêm dòng sau
Mã:
innodb_file_per_table=1
4. InnoDB buffer pool
- Là vùng lưu trữ data và indexes trong bộ nhớ. Nó được sử dụng để lưu trữ dữ liệu thường xuyên truy cập trong bộ nhớ, và khi bạn đang chạy một máy chủ chuyên dụng hoặc máy ảo mà DB thường là bottleneck, và đây sẽ là phần chiếm nhiều ram nhất trong các ứng dụng mà bạn đang sử dụng. Do đó, chúng tôi cung cấp cho nó 50-70% của tất cả các RAM. Có một bộ tài liệu hướng dẫn định dạng butter pool có sẵn trong MySQL docs.
Mã:
innodb_buffer_pool_size = 1500M
5. 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.
Mã:
max_connections = 200
6. 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 :
Mã:
# mysql -e "show status like 'Threads_created';"
# mysql -e "show status like 'Connections';"
– Sau khi có 2 thông số thì tính ra được Cache hit:
Mã:
100 – ((Threads_created / Connections) * 100))
– Đưa vào file my.cnf
Mã:
thread_cache_size = [Thông số tìm được]
7. MySQL reverse DNS lookups
- 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.
8. 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 :
Mã:
# mysqladmin processlist | 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ó xuống khoảng 60.
9. 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
Mã:
slow-query-log=1
slow-query-log-file=/var/lib/mysql/mysql-slow.log
long_query_time = 5
10. Kiểm tra các thông số cache, buffer
- Kiểm tra các thông số cache, buffer sau khi cài đặt (thực hiện lệnh trong cửa sổ mysql, mariadb):
MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache%';
MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_buffer%';
MariaDB [(none)]> SHOW VARIABLES LIKE '%cache%';
MariaDB [(none)]> SHOW VARIABLES LIKE '%buffer%';
- cài đặt tổng cache + buffer = 1/2 tổng số ram của vps, server sao cho Available Memory còn lại dao động khoảng 20-30% tổng số ram của vps, server trở lên để phục vụ các tác vụ khác
