Sử dụng mysqltuner tối ưu database MySQL trên Server Linux.

Công cụ mysqltuner tối ưu hóa database đơn giản mà hiệu quả, với việc tối ưu MySQL sẽ giúp server hoạt động hiệu quả và việc truy xuất database cũng nhanh hơn

Để xác định xem cơ sở dữ liệu MySQL của bạn có cần được cấu hình lại hay không, cách tốt nhất là xem các tài nguyên của bạn hiện đang hoạt động như thế nào. Điều này có thể được thực hiện bằng lệnh top

hoặc lệnh dưới đây

echo [PID] [MEM] [PATH] && ps aux | awk ‘{print $2, $4, $11}’ | sort -k2rn | head -n 20 ps -eo pcpu,pid,user,args | sort -k 1 -r | head -20

Với MySQLTuner sẽ đánh giá cài đặt MySQL của bạn, sau đó đưa ra các đề xuất để tăng hiệu suất và độ ổn định của server

1. Tải MySQLTuner:

   wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
   

2. Thay đổi các quyền của script để có thể thực thi:

   chmod +x mysqltuner.pl
   

3. Chạy mysqltuner.pl. Bạn sẽ được yêu cầu nhập thông tin đăng nhập và mật khẩu quản trị MySQL của mình:

   ./mysqltuner.pl
   

4. Tập lệnh sẽ trả về kết quả tương tự như kết quả bên dưới:

        -------- Performance Metrics -------------------------------------------------
        [--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K)
        [--] Reads / Writes: 100% / 0%
        [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
        [OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
        [OK] Slow queries: 0% (0/113)
        [OK] Highest usage of available connections: 0% (1/151)
        [OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
        [!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
        [OK] Query cache prunes per day: 0
        [OK] Temporary tables created on disk: 25% (54 on disk / 213 total)
        [OK] Thread cache hit rate: 97% (1 created / 42 connections)
        [OK] Table cache hit rate: 24% (52 open / 215 opened)
        [OK] Open file limit used: 4% (48/1K)
        [OK] Table locks acquired immediately: 100% (62 immediate / 62 locks)
        [OK] InnoDB buffer pool / data size: 128.0M/1.2M
        [OK] InnoDB log waits: 0
        -------- Recommendations -----------------------------------------------------
        General recommendations:
            Run OPTIMIZE TABLE to defragment tables for better performance
            Enable the slow query log to troubleshoot bad queries
        Variables to adjust:
            query_cache_limit (> 1M, or use smaller result sets)
       

MySQLTuner đưa ra các đề xuất về cách cải thiện hiệu suất của cơ sở dữ liệu.  Nếu bạn lo lắng về việc tự cập nhật cơ sở dữ liệu của mình, thì việc làm theo các đề xuất của MySQLTuner là một trong những cách an toàn hơn để cải thiện hiệu suất cơ sở dữ liệu của bạn.

Điều chỉnh MySQL

Khi thay đổi cấu hình MySQL, hãy cảnh giác với những thay đổi và cách chúng ảnh hưởng đến cơ sở dữ liệu của bạn. Ngay cả khi làm theo hướng dẫn của các chương trình như MySQLTuner , tốt nhất là bạn nên hiểu rõ về quy trình.

Các tập tin cấu hình MySQL lưu trữ trong các vị trí sau: /etc/my.cnf.

Ghi chú

Trước khi cập nhật cấu hình MySQL của bạn, hãy tạo một bản sao lưu của my.cnf:

cp /etc/my.cnf ~/my.cnf.backup

Phương pháp hay nhất đề xuất rằng bạn thực hiện các thay đổi nhỏ, lần lượt và sau đó theo dõi máy chủ sau mỗi lần thay đổi. Bạn nên khởi động lại MySQL sau mỗi lần thay đổi:

Đối với các bản phân phối sử dụng systemd:

systemctl restart mysqld

Đối với các bản phân phối với các hệ thống init khác nhau:

service mysql restart

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

Biến	Ý nghĩa
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 ngưỡng này thì các kết nối sau sẽ nhận phản hồi “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 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 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. 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.

Tham khảo các bài viết khác tại đây

Các khuyến mãi tại đây