Tối ưu Mysql với MySQLTuner (8 cores cpu, 4GB ram cho 1500 đến 2000 truy cập )) | Toi uu Mysql voi MySQLTuner (8 cores cpu, 4GB ram cho 1500 den 2000 truy cap )) - Quản trị net diễn đàn chia sẻ thông tin các thủ thuật mạng, internet bảo mật thông tin dành cho giới IT VIệt hy vọng là nơi bổ ích cho cộng đồng

11-03-2015, 01:58 PM

Tối ưu Mysql với MySQLTuner (8 cores cpu, 4GB ram cho 1500 đến 2000 truy cập ))

Tham khảo: http://wikivps.net/toi-uu-cau-hinh-cho-mysql-phan-12/

Giới thiệu :

MySQL Database là một trong những Database được sử dụng rất phổ biến cùng với ngôn ngữ PHP. Bình thường thì chúng ta cài MySQL xong là để cấu hình mặt định của nó, điều này sẽ không tốt cho sự phát triển sau này của WebSite. Trong bài hướng dẫn này mình sẽ chú trọng vào tối ưu cấu hình cho Database MySQL này.

Làm sao để thay đổi cấu hình của MySQL :

MySQL lưu cấu hình trong file my.cnf được tạo mặt định và lưu ở đường dẫn /etc/my.cnf hoặc là /etc/mysql/my.cnf tùy vào cách cài đặt và Distro Linux.
Có thể tìm thấy file cấu hình bằng câu lệnh sau

# mysql --help

or

# mysqld --help

Trên Centos 6.5 mình tìm như sau.

# mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Cấu hình :

File cấu hình mặt định.

[mysqld]datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Các bạn dùng trình soạn thảo vi hoặc là vim hoặc nano gì củng được. mở file cấu hình MySQL lên, và sữa y hệt như bên dưới. rùi lưu lại và restart lại service MySQL.

[mysqld]datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

skip-host-cache
skip-name-resolve

back_log = 256
max_connections = 1024
key_buffer = 128M
sort_buffer_size = 256K
read_buffer_size = 32M
join_buffer_size = 64M
read_rnd_buffer_size = 756K
thread_concurrency = 16
read_buffer_size = 32M
table_open_cache = 1536
tmp_table_size = 1024M
max_heap_table_size = 1024M
myisam_sort_buffer_size = 32M
max_allowed_packet = 512M
query_cache_limit = 4M
query_cache_size = 1024M
query_cache_type = 1
query_cache_min_res_unit = 1K
interactive_timeout = 300
wait_timeout = 600
connect_timeout = 60
thread_cache_size = 32

[mysqld_safe]log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Đây là cấu hình mình đã áp dụng trên VPS 8 cores cpu, 4GB Ram ( VPS thường chứ không phải CLOUD VPS nhé, Datacenter đặt ở VietNam ). Mình thấy chạy khá ổn, cài đặt thêm Memcached, Opcode cache, Không dùng Proxy cache thì online tầm 1k5-2k là ổn. Còn kết hợp thêm Proxy Cache thì online > 10k . Đây là kết quả của riêng cá nhân mình muốn chia sẽ với các bạn. Chắc chắn sẽ còn nhiều người cấu hình tốt hơn nữa. Mong là sẽ được thảo luận với các bạn.

Benchmark ( Theo quan điểm của mình nó có nghĩa là mình test một hệ thống, chức năng, hoặc chương trình nào đó để tìm ra điểm chuẩn, Mục đích cuối cùng là đạt tới giới hạn, hoạt động ổn đinh, … )

Đối với MySQL, tối ưu nó để chạy ổn định trên một VPS, Server đặt biệt nào đó thì đó là cả một quá trình dài. Ở đây mình sẽ giới thiệu cho các bạn một tool nó gọi là MySQLTuner , Nó sẽ phân tích hiệu xuất của MySQL trên VPS, Server mình sau đó đưa ra những gợi ý thay đổi các thông số cho phù hợp.
_ Download MySQLTuner, các bạn chạy các câu lệnh sau.
1
# wget -O /usr/src/mysqltuner.pl mysqltuner.pl

_ Chạy script vừa Download về.

[root@vps ~]# perl /usr/src/mysqltuner.pl

>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.40
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 211M (Tables: 51)
[--] Data in InnoDB tables: 6M (Tables: 13)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Security Recommendations -------------------------------------------
[!!] User '@testing.vn' has no password set.
[!!] User 'root@testing.vn' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 19h 10m 55s (14M q [207.130 qps], 298K conn, TX: 37B, RX: 1B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 3.1G global + 97.2M per thread (1024 max threads)
[!!] Maximum possible memory usage: 100.4G (1254% of installed RAM)
[OK] Slow queries: 0% (10/14M)
[OK] Highest usage of available connections: 4% (45/1024)
[OK] Key buffer size / total MyISAM indexes: 128.0M/50.7M
[OK] Key buffer hit rate: 100.0% (940M cached / 272K reads)
[OK] Query cache efficiency: 74.6% (9M cached / 12M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (7K temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 32% (194K on disk / 593K total)
[OK] Thread cache hit rate: 99% (67 created / 298K connections)
[!!] Table cache hit rate: 11% (87 open / 760 opened)
[OK] Open file limit used: 1% (88/5K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB buffer pool / data size: 1.0G/6.2M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
table_open_cache (> 1024)

Các bạn chú ý ở dòng thứ 4 từ dưới đếm lên. nó sẽ liệt kê ra những biến nên tinh chỉnh lại để đạt được hiệu suất tốt hơn. Xong rùi đó các bạn, có thể cách vài ngày mình chạy lại script một lần, để xem cần thay đổi thông số nào thì thay đổi. để luôn luôn đạt được hiệu suất tốt nhất.

Kết Luận :

Đừng chỉ sử dụng Script để tối ưu MySQL, các bạn nên học từng Directive (Thông số cấu hình) để hiểu được nó là gì? và nó dùng làm gì? và cách dùng của nó như thế nào? Có như vậy thì các bạn mới xây dựng được một hệ thống Database tốt được.
Các bạn có thể comment bên dưới để trao đổi học tập với nhau. Các bạn nào sử dụng script, hay tool optimize khác thì giới thiệu thêm nhé.

11-03-2015, 01:58 PM	#1
hoctinhoc Guest Trả Lời: n/a	Tối ưu Mysql với MySQLTuner (8 cores cpu, 4GB ram cho 1500 đến 2000 truy cập )) Tối ưu Mysql với MySQLTuner (8 cores cpu, 4GB ram cho 1500 đến 2000 truy cập )) Tham khảo: http://wikivps.net/toi-uu-cau-hinh-cho-mysql-phan-12/ Giới thiệu : MySQL Database là một trong những Database được sử dụng rất phổ biến cùng với ngôn ngữ PHP. Bình thường thì chúng ta cài MySQL xong là để cấu hình mặt định của nó, điều này sẽ không tốt cho sự phát triển sau này của WebSite. Trong bài hướng dẫn này mình sẽ chú trọng vào tối ưu cấu hình cho Database MySQL này. Làm sao để thay đổi cấu hình của MySQL : MySQL lưu cấu hình trong file my.cnf được tạo mặt định và lưu ở đường dẫn /etc/my.cnf hoặc là /etc/mysql/my.cnf tùy vào cách cài đặt và Distro Linux. Có thể tìm thấy file cấu hình bằng câu lệnh sau # mysql --help or # mysqld --help Trên Centos 6.5 mình tìm như sau. # mysql --help \| grep my.cnf order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf Cấu hình : File cấu hình mặt định. [mysqld]datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe]log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Các bạn dùng trình soạn thảo vi hoặc là vim hoặc nano gì củng được. mở file cấu hình MySQL lên, và sữa y hệt như bên dưới. rùi lưu lại và restart lại service MySQL. [mysqld]datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 skip-host-cache skip-name-resolve back_log = 256 max_connections = 1024 key_buffer = 128M sort_buffer_size = 256K read_buffer_size = 32M join_buffer_size = 64M read_rnd_buffer_size = 756K thread_concurrency = 16 read_buffer_size = 32M table_open_cache = 1536 tmp_table_size = 1024M max_heap_table_size = 1024M myisam_sort_buffer_size = 32M max_allowed_packet = 512M query_cache_limit = 4M query_cache_size = 1024M query_cache_type = 1 query_cache_min_res_unit = 1K interactive_timeout = 300 wait_timeout = 600 connect_timeout = 60 thread_cache_size = 32 [mysqld_safe]log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Đây là cấu hình mình đã áp dụng trên VPS 8 cores cpu, 4GB Ram ( VPS thường chứ không phải CLOUD VPS nhé, Datacenter đặt ở VietNam ). Mình thấy chạy khá ổn, cài đặt thêm Memcached, Opcode cache, Không dùng Proxy cache thì online tầm 1k5-2k là ổn. Còn kết hợp thêm Proxy Cache thì online > 10k . Đây là kết quả của riêng cá nhân mình muốn chia sẽ với các bạn. Chắc chắn sẽ còn nhiều người cấu hình tốt hơn nữa. Mong là sẽ được thảo luận với các bạn. Benchmark ( Theo quan điểm của mình nó có nghĩa là mình test một hệ thống, chức năng, hoặc chương trình nào đó để tìm ra điểm chuẩn, Mục đích cuối cùng là đạt tới giới hạn, hoạt động ổn đinh, … ) Đối với MySQL, tối ưu nó để chạy ổn định trên một VPS, Server đặt biệt nào đó thì đó là cả một quá trình dài. Ở đây mình sẽ giới thiệu cho các bạn một tool nó gọi là MySQLTuner , Nó sẽ phân tích hiệu xuất của MySQL trên VPS, Server mình sau đó đưa ra những gợi ý thay đổi các thông số cho phù hợp. _ Download MySQLTuner, các bạn chạy các câu lệnh sau. 1 # wget -O /usr/src/mysqltuner.pl mysqltuner.pl _ Chạy script vừa Download về. [root@vps ~]# perl /usr/src/mysqltuner.pl >> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.5.40 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 211M (Tables: 51) [--] Data in InnoDB tables: 6M (Tables: 13) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 3 -------- Security Recommendations ------------------------------------------- [!!] User '@testing.vn' has no password set. [!!] User 'root@testing.vn' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 19h 10m 55s (14M q [207.130 qps], 298K conn, TX: 37B, RX: 1B) [--] Reads / Writes: 88% / 12% [--] Total buffers: 3.1G global + 97.2M per thread (1024 max threads) [!!] Maximum possible memory usage: 100.4G (1254% of installed RAM) [OK] Slow queries: 0% (10/14M) [OK] Highest usage of available connections: 4% (45/1024) [OK] Key buffer size / total MyISAM indexes: 128.0M/50.7M [OK] Key buffer hit rate: 100.0% (940M cached / 272K reads) [OK] Query cache efficiency: 74.6% (9M cached / 12M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (7K temp sorts / 1M sorts) [!!] Temporary tables created on disk: 32% (194K on disk / 593K total) [OK] Thread cache hit rate: 99% (67 created / 298K connections) [!!] Table cache hit rate: 11% (87 open / 760 opened) [OK] Open file limit used: 1% (88/5K) [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks) [OK] InnoDB buffer pool / data size: 1.0G/6.2M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: * MySQL's maximum memory usage is dangerously high * * Add RAM before increasing MySQL buffer variables * table_open_cache (> 1024) Các bạn chú ý ở dòng thứ 4 từ dưới đếm lên. nó sẽ liệt kê ra những biến nên tinh chỉnh lại để đạt được hiệu suất tốt hơn. Xong rùi đó các bạn, có thể cách vài ngày mình chạy lại script một lần, để xem cần thay đổi thông số nào thì thay đổi. để luôn luôn đạt được hiệu suất tốt nhất. Kết Luận : Đừng chỉ sử dụng Script để tối ưu MySQL, các bạn nên học từng Directive (Thông số cấu hình) để hiểu được nó là gì? và nó dùng làm gì? và cách dùng của nó như thế nào? Có như vậy thì các bạn mới xây dựng được một hệ thống Database tốt được. Các bạn có thể comment bên dưới để trao đổi học tập với nhau. Các bạn nào sử dụng script, hay tool optimize khác thì giới thiệu thêm nhé.

Chia Sẽ Kinh Nghiệm Về IT