https://www.linode.com/docs/guides/how-to-optimize-mysql-performance-using-mysqltuner/
- Download the MySQLTuner script:
wget http://mysqltuner.com/mysqltuner.pl - Change the scripts permissions to be executable:
chmod +x mysqltuner.pl - Run the mysqltuner.pl script. You will be prompted to enter in your MySQL administrative login and password:
./mysqltuner.pl
https://nguyenhuuhoang.com/10-cach-toi-uu-database-mysql-tren-vps-linux/
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.
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 là: (chạy lệnh: # sysctl vm.swappiness)
vm.swappiness = 60
Bạn disable bằng command sau :
# sysctl -w vm.swappiness=0
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á 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
query_cache_type=1
query_cache_limit=256K
query_cache_min_res_unit=2k
query_cache_size = 64M
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
innodb_file_per_table=1
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.
innodb_buffer_pool_size = [Thông số ram]
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
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 -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:
100 – ((Threads_created / Connections) * 100))
– Đưa vào file my.cnf
thread_cache_size = [Thông số tìm được]
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.
skip-name-resolve
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 | 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
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