Để quản trị hệ thống cơ sở dữ liệu là việc khó kể cả với người giàu kinh nghiệm, thế giới người ta có cả bộ chứng chỉ DBA cho chuyên ngành này từ MySQL, MSSQL đến Oracle… Với người không chuyên thì chỉ nguyên việc hiểu các thông số cơ bản đã thấy oải rồi nhất là với máy chủ VPS ít tài nguyên.
Hôm nay mình xin giới thiệu với các bạn công cụ mysqltuner tối ưu hóa database đơn giản mà hiệu quả. Với newbie thì dùng cung cụ này rất tốt, giúp bạn tránh được lỗi Error establishing a database connection hay những lỗi thiếu tài nguyên mysql tự stop khi cấu hình webserver.
Công cụ này được viết bằng Perl hỗ trợ tối ưu tốt trên phiên bản CSDL:
- MySQL 3.23, 4.0, 4.1, 5.0, 5.1, 5.5, 5.6, 5.7 (full support)
- MariaDB 5.5, 10.0, 10.1 (full support)
Cách sử dụng mysqltuner
Trước tiên cần tải chương trình tối ưu DB về máy
wget http://mysqltuner.pl/ -O mysqltuner.pl
Tải file basic_passwords, đây là danh sách mật khẩu dễ đoán thường được mọi người dùng nhiều nhất. Bạn có thể bổ sung thêm vào danh sách mỗi mật khẩu một dòng.
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
Tải file CVE vulnerabilities, đây là file chứa các lỗ hổng bảo mật trên Database, mysqltuner khi chạy sẽ sử dụng file này để kiểm tra CSDL của bạn có gặp các vấn đề bảo mật này không.
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
Đã đủ file cần thiết, bạn nhớ để chúng vào cùng một thư mục rồi chạy lệnh chmod file để có quyền thực thi.
chmod +x mysqltuner.pl
Tiếp theo chạy chương trình.
perl mysqltuner.pl
Chi tiết:
>> MySQLTuner 1.6.13 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Logged in using credentials from debian maintenance account. [OK] Currently running supported MySQL version 5.6.30-0ubuntu0.15.10.1 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My ISAM +PERFORMANCE_SCHEMA [--] Data in InnoDB tables: 4M (Tables: 47) [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [--] There are 605 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 17d 6h 52m 52s (195K q [0.131 qps], 18K conn, TX: 686M, RX: 25M) [--] Reads / Writes: 94% / 6% [--] Binary logging is disabled [--] Physical Memory : 740.8M [--] Max MySQL memory : 240.8M [--] Other process memory: 245.6M [--] Total buffers: 161.0M global + 1.0M per thread (30 max threads) [--] P_S Max memory usage: 48M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 213.6M (28.83% of installed RAM) [OK] Maximum possible memory usage: 240.8M (32.51% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/195K) [OK] Highest usage of available connections: 13% (4/30) [!!] Aborted connections: 45.21% (8366/18503) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9K sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 48% (6K on disk / 12K total) [!!] Table cache hit rate: 0% (4 open / 29K opened) [OK] Open file limit used: 0% (0/1K) [OK] Table locks acquired immediately: 100% (156K immediate / 156K locks) -------- Performance schema ---------------------------------------------------- [--] Performance schema is enabled. [--] Memory used by P_S: 48.4M [--] Sys schema isn't installed. -------- ThreadPool Metrics ---------------------------------------------------- [--] ThreadPool stat is disabled. -------- MyISAM Metrics -------------------------------------------------------- [!!] Key buffer used: 18.8% (3K used / 16K cache) [OK] Key buffer size / total MyISAM indexes: 16.0K/105.0K [OK] Read Key buffer hit rate: 99.5% (756 cached / 4 reads) [OK] Write Key buffer hit rate: 98.6% (579 cached / 8 writes) -------- AriaDB Metrics -------------------------------------------------------- [--] AriaDB is disabled. -------- InnoDB Metrics -------------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 128.0M/4.2M [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1). [!!] InnoDB Used buffer: 8.59% (704 used/ 8191 total) [OK] InnoDB Read buffer efficiency: 99.99% (9586131 hits/ 9586704 total) [!!] InnoDB Write Log efficiency: 75.07% (29036 hits/ 38676 total) [OK] InnoDB log waits: 0.00% (0 waits / 9640 writes) -------- TokuDB Metrics -------------------------------------------------------- [--] TokuDB is disabled. -------- Galera Metrics -------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics --------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server. -------- Recommendations ------------------------------------------------------- General recommendations: Restrict Host for user@% to user@SpecificDNSorIp Reduce or eliminate unclosed connections and network issues Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Beware that open_files_limit (1024) variable should be greater than table_open_cache ( 4) Variables to adjust: tmp_table_size (> 16M) max_heap_table_size (> 16M) table_open_cache (> 4) innodb_buffer_pool_instances (=1)
Bạn chú ý chỗ bôi đỏ Variables to adjust là các việc bạn cần phải làm để tối ưu DB. Giờ chỉ việc tìm file cấu hình Database mà thay đổi thông sô theo hướng dẫn, vơi mỗi hệ điều hành file cấu hình sẽ khác bạn nhé.
Trong bài này mình dùng cài MySQL trên Ubuntu thì file cấu hình MySQL có đường dẫn là:
/etc/mysql/my.cnf
Nếu cài MariaDB trên CentOS đường dẫn sẽ là:
/etc/my.cnf.d/server.inf
Mình thấy công cụ này khá hữu ích mà dễ dùng, bạn nào có cách làm hay hơn thì comment bên dưới để mọi người cùng tham khảo.
