Trung tâm hỗ trợ

Tối ưu hóa hiệu năng MySQL với công cụ MySQLTuner

MySQLTuner là một công cụ hữu ích giúp các quản trị viên cơ sở dữ liệu MySQL cải thiện hiệu năng và độ ổn định của hệ thống. Được Script viết bằng Perl, MySQLTuner có thể phân tích cấu hình MySQL hiện tại và đưa ra các khuyến nghị nhằm tối ưu hóa các thông số quan trọng.

Tại sao nên sử dụng MySQLTuner?

Mục tiêu chính của MySQLTuner là hỗ trợ việc điều chỉnh cấu hình MySQL một cách tối ưu, giúp cơ sở dữ liệu hoạt động với hiệu suất cao nhất có thể. Nó không đưa ra các đề xuất sai lầm có thể làm giảm hiệu năng. Thay vào đó, công cụ này giúp người quản trị đánh giá tình trạng hiện tại của hệ thống thông qua việc phân tích các truy vấn và giám sát hoạt động.

 

Quy trình tối ưu hóa với MySQLTuner bao gồm các bước sau:

Tải về và cài đặt MySQLTuner trên máy chủ MySQL bằng lệnh:

Download mysqltuner:

Mã:

 cd /usr/local/bin
wget http://mysqltuner.pl/mysqltuner.pl
chmod +x mysqltuner.pl

Chạy mysqltuner

Mã:

 /usr/local/bin/mysqltuner.pl

Nhập User và Password Root của MySQL. MySQLTuner sẽ phân tích và đưa ra báo cáo về hiệu năng cùng các đề xuất cải thiện.

Mã:
 root@v221:/usr/local/bin# /usr/local/bin/mysqltuner.pl

>>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-24+lenny2
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 26M (Tables: 215)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 33

-------- Performance Metrics -------------------------------------------------
[--] Up for: 96d 23h 3m 41s (10M q [1.239 qps], 686K conn, TX: 701M, RX: 1B)
[--] Reads / Writes: 44% / 56%
[--] Total buffers: 58.0M global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 320.5M (12% of installed RAM)
[OK] Slow queries: 0% (20/10M)
[OK] Highest usage of available connections: 33% (33/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/8.5M
[OK] Key buffer hit rate: 99.9% (57M cached / 30K reads)
[OK] Query cache efficiency: 78.6% (5M cached / 6M selects)
[!!] Query cache prunes per day: 483
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 408K sorts)
[!!] Temporary tables created on disk: 36% (269K on disk / 745K total)
[OK] Thread cache hit rate: 99% (427 created / 686K connections)
[!!] Table cache hit rate: 2% (64 open / 3K opened)
[OK] Open file limit used: 11% (120/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 16M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 64)

Dựa trên kết quả phân tích, điều chỉnh các tham số MySQL trong file cấu hình my.cnf mysql (thường nằm ở /etc/my.cnf hoặc /etc/mysql/my.cnf).

Mở file my.cnf

Mã:

 vi /etc/mysql/my.cnf

Tăng hoặc thiết lập các biến trong phần [mysqld] như sau:

Mã:
 [mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover          = BACKUP
#max_connections        = 100
table_cache            = 128
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M

query_cache_size        = 32M
tmp_table_sizee        = 64M
max_heap_table_sizee        = 32M

Tiếp theo, bạn hãy lưu các thay đổi và khởi động lại dịch vụ MySQL. Sau khoảng một đến hai giờ, bạn chạy lại công cụ MySQLTuner và kiểm tra xem các thông số đã được tối ưu hơn chưa. Nếu chưa đạt yêu cầu, bạn có thể thử điều chỉnh các giá trị lên cao hơn một chút và lặp lại quá trình kiểm tra cho đến khi đạt được hiệu năng tốt nhất.

 

Lưu thay đổi và khởi động lại MySQL. Sau vài giờ hoạt động, chạy lại MySQLTuner để kiểm tra hiệu quả cải thiện.

 

Ngoài ra, một số tham số khác cũng đóng vai trò quan trọng trong việc tăng hiệu năng MySQL:

 

Các thông số có thể tăng hiệu suất hoạt động của mysql

Biến

Ý nghĩa thông số

max_connections

Tham số này điều chỉnh số lượng kết nối tối đa đồng thời. Khi số lượng kết nối vào MySQL đạt ngưỡng này, các kết nối sau sẽ nhận phản hồi "Too many connections".

max_allowed_packet

Tham số này xác định kích thước tối đa của gói tin truy vấn mà Server có thể xử lý. Mặc định, tham số này được đặt khoảng 4MB. Tuy nhiên, nếu các gói tin chứa câu truy vấn quá lớn, cần tăng giá trị này để Server có thể xử lý được. 

Giá trị được đặt trong khoảng từ 1KB (tối thiểu) đến 1GB (tối đa), với giá trị mặc định là 4MB. 

Không giống như innodb_buffer_pool_size, phần bộ nhớ được cấp cho max_allowed_packet chỉ được sử dụng khi cần thiết. Khi không sử dụng, khoảng trống này có thể được Server dùng cho các mục đích khác.

thread_cache_size

Kích thước hàng đợi chứa bộ nhớ Cache cho các Thread. Khi người dùng ngừng kết nối, Thread hiện tại sẽ được đưa vào bộ nhớ Cache cho đến khi người dùng sử dụng lại. Nếu giá trị này được đặt là 0 hoặc hàng đợi đạt đến giá trị này, các kết nối mới đến MySQL sẽ tạo một Thread mới để xử lý.

table_open_cache

Số lượng bảng tối đa có thể mở cho tất cả các Thread. Tăng giá trị này sẽ làm tăng số lượng File Descriptors mà MySQL yêu cầu (trên Linux, mọi đối tượng đều được xem như một File, vì vậy mỗi bảng được mở tương ứng với một File). Giá trị này phải lớn hơn giá trị open_tables, là số bảng đang mở hiện tại.

open_files_limit

Tham số này cho phép thay đổi giới hạn về số lượng tập tin mà quá trình mysqld có thể truy cập và sử dụng cùng một lúc. Giá trị tối đa của biến này phụ thuộc vào nền tảng hệ thống.

innodb_buffer_pool_size

Tham số này giữ vai trò thiết yếu và có ảnh hưởng lớn khi sử dụng InnoDB. InnoDB luôn duy trì một vùng lưu trữ được gọi là Buffer Pool để Cache dữ liệu và chỉ mục trong bộ nhớ. Với tham số này, bộ nhớ đã cấp cho Buffer Pool sẽ không được Server sử dụng cho mục đích khác. 

Giá trị cao hơn cho phép lưu trữ nhiều dữ liệu trong bộ nhớ đệm trong quá trình truy xuất dữ liệu (truy xuất trên RAM thay vì trên ổ cứng cho các giá trị thường xuyên được truy cập). Tùy thuộc vào lượng RAM hiện có trên Server và các ứng dụng khác sử dụng trên Server, ta có thể tinh chỉnh thông số này cho phù hợp với MySQL. 

Trên một Server vật lý, giá trị này nên được đặt khoảng 70% lượng RAM của Server.

innodb_log_file_size

Tham số này quy định dung lượng của các tệp Log Files được sử dụng bởi Engine lưu trữ InnoDB.. Việc tùy chỉnh kích thước phù hợp sẽ tăng hiệu suất xử lý. Nó còn phụ thuộc vào khối lượng công việc và phiên bản của máy chủ. Các phiên bản cũ thường khôi phục sự cố rất chậm với File Log lớn. Thông thường, sử dụng khoảng 128MB hoặc 256MB là đủ.

innodb_flush_method

Xác định phương thức để làm sạch dữ liệu cho các File dữ liệu và Log của InnoDB. Có nhiều phương thức, nhưng người ta thường sử dụng O_DIRECT cho các phiên bản GNU/Linux, FreeBSD và Solaris.

innodb_file_per_table

Mặc định, giá trị này được tắt. Nếu tắt, InnoDB sẽ tạo bảng trong không gian chia sẻ dùng chung của các bảng. Nếu bật (=1), khi tạo mỗi bảng, InnoDB sẽ tạo một File có đuôi .idb để lưu trữ dữ liệu và chỉ mục của bảng đó.

Việc điều chỉnh tham số cần dựa trên cấu hình phần cứng, tải trọng hệ thống và phiên bản MySQL đang sử dụng. MySQLTuner là công cụ đắc lực giúp đánh giá và tối ưu hóa các thông số này một cách an toàn và hiệu quả, góp phần nâng cao hiệu năng tổng thể của cơ sở dữ liệu MySQL.

 

Nguồn: Thiết kế Website

Hơn 5.000+ doanh nghiệp và chủ shop đang bán hàng như thế nào ?
Bạn mong muốn có một website thương hiệu phù hợp với lĩnh vực hoạt động doanh nghiệp. Hãy liên hệ với chúng tôi, mọi mong muốn của bạn sẽ được lắng nghe.Chúng tôi mong muốn nhận thông tin yêu cầu của quý khách hàng qua đường dây nóng 0938 91 96 05 để chúng tôi có thể tư vấn một cách tốt nhất.
IMS luôn sẵn sàng phục vụ quý khách hàng 24/7, mọi thắc mắc xin hãy gọi điện với chúng tôi qua số hotline 0938 91 96 05 để được tư vấn trực tiếp Liên hệ báo giá