Trung tâm hỗ trợ

Tối ưu hóa hiệu suất MySQL với mysqltuner

MySQLTuner là gì? Cách tối ưu MySQL

MySQLTuner là một Script viết bằng Perl, giúp bạn chỉnh cấu hình và đề xuất những giải pháp tăng hiệu quả trong hoạt động, độ ổn định máy chủ cơ sở dữ liệu dùng MySQL của bạn. Trong khi chạy MySQLTuner (MT), bạn sẽ nhanh chóng nắm bắt được các thông số về cài đặt MySQL và các số liệu quan trọng, cần lưu ý và cần thiết khác.

Vì sao ta nên sử dụng MySQLTuner? MT được viết để hỗ trợ bạn cấu hình, cải thiện tốc độ, tối ưu hoá những cài đặt quan trọng, giúp máy chủ cơ sở dữ liệu hoạt động tốt trên định mức nhất có thể. MT không viết ra để đưa ra những lời khuyên, cũng như thông số sai lầm làm cho máy chủ của bạn hoạt động kém hiệu xuất hơn lúc ban đầu.

Một quản trị hệ thống cơ sở có kinh nghiệm luôn biết cách tốt nhất để tối ưu hoá hiệu xuất hoạt động của cơ sở dữ liệu là kiểm tra quá trình truy vấn từ máy chủ, MT được viết ra với một tập lệnh dành riêng cho quá trình này. Sau quá trình kiểm tra, bạn hoàn toàn có thể tự đưa ra đánh giá về hiệu xuất của máy chủ cơ sở dữ liệu của mình.

MT ứng dụng những câu truy vấn và giám sát dựa trên các kĩ thuât đa dạng, nhằm tìm ra cách tối ưu nhất để giúp người quản trị hoàn thiện cấu hình cơ sở dữ liệu. Sau quá trình kiểm tra, ngoài việc chỉnh sửa lại cấu hình hệ thống cho phù hợp, có thể bạn cũng cần nghĩ đến các giải pháp nâng cấp phần cứng, nếu thực sự cần thiết.

Hướng dẫn tối ưu hiệu suất MySQL với mysqltuner

Hướng dẫn này mô tả các bước để tối ưu hóa hiệu suất của cơ sở dữ liệu MySQL với mysqltuner.

Đăng nhập vào máy chủ của bạn với shell command, sau đó thực hiện các lệnh sau:

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

Sau đó nhập user và password root của mysql. Bạn sẽ nhận được thông tin như sau:

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)

Script yêu cầu điều chỉnh hoặc bổ sung các biến sau trong file my.cnf mysql. Vị trí của my.cnf bình thường là /etc/my.cnf hoặc /etc/mysql/my.cnf tùy thuộc vào bản Linux được cài đặt trên máy chủ của bạn.

Mở file my.cnf

Mã:

 vi /etc/mysql/my.cnf

Và tăng hoặc thiết lập các biến trong phần [mysqld]. Giống 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

Sau đó lưu lại và khởi động lại mysql. Đợi sau đó khoảng một vài giờ, chạy lại mysqltuner và kiểm tra lại nếu các giá trị được tốt hơn chưa, hoặc có thể tăng lên một giá trị cao hơn.

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 tùy chỉnh số lượng kết nối tối đa tại cùng một thời điểm. Khi số lượng kết nối vào mysql đạt đến ngưỡng này thì các kết nối sau sẽ nhận phản hồi là “Too many connections”
max_allowed_packet=[values] Tham số này là kích thước tối đa của gói tin truy vấn mà server có thể bắt được. Tham số này được mặc định là khoảng 4MB, tuy nhiên nếu những gói tin chứa câu truy vấn quá lớn thì ta cần phải tăng tham số này lên để server có thể handle được.

Values được set trong khoảng Min và Max.

[Min: 1KB, Default: 4MB, Maximum: 1GB]

Không như innodb_buffer_pool_size phần bộ nhớ được cấp cho buffer_pool không dùng cho mục đích khác, còn max_allowed_packet giá trị được cấp chỉ dùng khi server cần, bình thường thì khoảng trống này có thể được server sử dụng cho các việc khác.

thread_cache_size Kích thước hàng đợi chứa thread cache, khi người dùng ngưng kết nối thì thread hiện tại sẽ được đưa vào để cache lại cho tới khi người dùng sử dụng tiếp thì sẽ được lấy ra lại. Nếu được set 0 hoặc hàng đợi chạm mức giá trị này thì các kết nối mới tới mysql sẽ được thực hiện tạo một thread mới để xử lý.
table_open_cache Số lượng tối đa bảng có thể mở cho tất cả các thread. Việc tăng giá trị này sẽ gia tăng số lượng file descriptors mà mysql yêu cầu. (mọi đối tượng trên linux đều quy ra 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à giá trị chứa số bảng đang mở hiện tại
open_files_limit Điều chỉnh số lượng file tối đa mà mysqld có thể mở. Giới trị tối đa của biến này phụ thuộc vào nền tảng của hệ thống.
innodb_buffer_pool_size Đây là tham số quan trọng khi sử dụng innodb. InnoDB luôn duy trì một vùng lưu trữ được gọi là buffer pool để cache lại dữ liệu và chỉ mục bên trong memory. Với tham số này thì bộ nhớ đã cấp cho buffer pool server sẽ không sử dụng cho mục đích khác.Giá trị này cao thì khả năng chứa bộ nhớ đệm cho dữ liệu trong quá trình truy xuất dữ liệu database càng nhiều (truy xuất trên RAM thay vì trên ổ cứng với những giá trị được truy cập thường xuyên).

Tùy vào lượng RAM hiện tại của server và các ứng dụng khác dùng trên server mà ta có thể tinh chỉnh thông số này cho phù hợp cho MySQL.

Trên một server vật lý thì giá trị này nên bằng khoảng 70% lượng RAM của server vật lý

innodb_log_file_size Kích thước file log cho innodb, việc tùy chỉnh kích thước lớn phù hợp sẽ tăng hiệu suất xử lý. Nó còn phụ thuộc vào số lượng công việc hoạt động và phiên bản của máy chủ.Các phiên bản cũ thường khôi phục crash rất chậm với file log lớn. Thường sử dụng khoảng 128M hoặc 256MB là đủ.
innodb_flush_method Định nghĩa 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 dùng O_DIRECT cho GNU/Linux versions, FreeBSD, Solaris.
innodb_file_per_table Mặc định giá trị này đã tắt. Nếu tắt thì InnoDB sẽ tạo bảng trong khoảng không gian chia sẽ dùng chung của các bảng. Nếu bật cờ này (=1) thì khi tạo mỗi bảng sẽ sở hữu một file có đuôi là .idb để lưu trữ dữ liệu và chỉ mục.

Hy vọng với hướng dẫn trên của IMS, bạn có thể dễ dàng thực hiện kích hoạt cũng như tắt tính năng. Cảm ơn bạn đã theo dõi và hẹn gặp lại ở những bài viết sau.

Hơn +10,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á