- Giới thiệu :
- Làm sao để thay đổi cấu hình của MySQL :
|
1
2
3
4
5 |
# mysql --helpor# mysqld --help |
|
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 :
|
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 |
|
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 |
- 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, … )
|
1 |
# wget -O /usr/src/mysqltuner.pl mysqltuner.pl |
|
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) |
- Kết Luận :
