Tối ưu hiệu năng MySQL

Giới thiệu

Tối ưu hóa hiệu năng MySQL là một yếu tố quan trọng để đảm bảo cơ sở dữ liệu của bạn hoạt động mượt mà và hiệu quả. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn các phương pháp và kỹ thuật để cải thiện hiệu suất của MySQL, từ việc tối ưu hóa cấu hình hệ thống đến việc điều chỉnh các truy vấn và chỉ mục. Với những mẹo và công cụ hữu ích, bài viết này sẽ giúp bạn nâng cao hiệu quả hoạt động của MySQL và giảm thiểu thời gian phản hồi của hệ thống.

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

Default :vm.swappiness = 60
Disalbe by :
	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àyvà sẽ set giái trị gigabytes.
Websites lớn cần 256 -> 512MB . Còn nhỏ thì chỉ cần 64 – 128MB.

Chỉnh sửa trong my.cnf :

	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

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. 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, cách tính 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 đượ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 Server :

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 đó query , 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ả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

Việc tối ưu hóa hiệu năng MySQL là một quá trình liên tục đòi hỏi sự chú ý và điều chỉnh thường xuyên để đạt được kết quả tốt nhất. Bằng cách áp dụng các kỹ thuật và phương pháp đã được đề cập trong bài viết này, bạn có thể cải thiện đáng kể tốc độ và hiệu quả của cơ sở dữ liệu của mình. Hãy luôn theo dõi hiệu suất hệ thống, điều chỉnh các cấu hình và tối ưu hóa các truy vấn để đảm bảo cơ sở dữ liệu hoạt động ổn định và nhanh chóng.