- 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
|
1
2
3
4
5
|
# mysql --helpor# mysqld --help |
Trên Centos 6.5 mình tìm như sau.
|
1
2
3
|
# mysql --help | grep my.cnforder 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.
|
1
2
3
4
5
6
7
8
9
10
|
[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0skip-host-cacheskip-name-resolveback_log = 256max_connections = 1024key_buffer = 128Msort_buffer_size = 256Kread_buffer_size = 32Mjoin_buffer_size = 64Mread_rnd_buffer_size = 756Kthread_concurrency = 16read_buffer_size = 32Mtable_open_cache = 1536tmp_table_size = 1024Mmax_heap_table_size = 1024Mmyisam_sort_buffer_size = 32Mmax_allowed_packet = 512Mquery_cache_limit = 4Mquery_cache_size = 1024Mquery_cache_type = 1query_cache_min_res_unit = 1Kinteractive_timeout = 300wait_timeout = 600connect_timeout = 60thread_cache_size = 32[mysqld_safe]log-error=/var/log/mysqld.logpid-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ề.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
[root@vps ~]# perl /usr/src/mysqltuner.pl>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at https://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 performanceMySQL started within last 24 hours - recommendations may be inaccurateReduce your overall MySQL memory footprint for system stabilityEnable the slow query log to troubleshoot bad queriesTemporary table size is already large - reduce result set sizeReduce your SELECT DISTINCT queries without LIMIT clausesIncrease table_open_cache gradually to avoid file descriptor limitsRead this before increasing table_open_cache over 64: https://bit.ly/1mi7c4CVariables 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.
