1 | -- MYSQL PERFORMANCE TUNING PRIMER --
|
2 | - By: Matthew Montgomery -
|
3 |
|
4 | MySQL Version 4.1.22mysql4-log armv7l is EOL please upgrade to MySQL 4.1 or later
|
5 |
|
6 | Uptime = 0 days 0 hrs 5 min 58 sec
|
7 | Avg. qps = 4
|
8 | Total Questions = 1624
|
9 | Threads Connected = 1
|
10 |
|
11 | Warning: Server has not been running for at least 48hrs.
|
12 | It may not be safe to use these recommendations
|
13 |
|
14 | To find out more information on how each of these
|
15 | runtime variables effects performance visit:
|
16 | http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
|
17 | Visit http://www.mysql.com/products/enterprise/advisors.html
|
18 | for info about MySQL's Enterprise Monitoring and Advisory Service
|
19 |
|
20 | SLOW QUERIES
|
21 | The slow query log is NOT enabled.
|
22 | Current long_query_time = 10 sec.
|
23 | You have 0 out of 1638 that take longer than 10 sec. to complete
|
24 | Your long_query_time seems to be fine
|
25 |
|
26 | BINARY UPDATE LOG
|
27 | The binary update log is enabled
|
28 |
|
29 | WORKER THREADS
|
30 | Current thread_cache_size = 0
|
31 | Current threads_cached = 0
|
32 | Current threads_per_sec = 1
|
33 | Historic threads_per_sec = 0
|
34 | Your thread_cache_size is fine
|
35 |
|
36 | MAX CONNECTIONS
|
37 | Current max_connections = 8
|
38 | Current threads_connected = 1
|
39 | Historic max_used_connections = 2
|
40 | The number of used connections is 25% of the configured maximum.
|
41 | Your max_connections variable seems to be fine.
|
42 |
|
43 | INNODB STATUS
|
44 | Cannot parse InnoDB stats prior to 5.0.x
|
45 | ERROR 1227 (HY000) at line 1: Access denied; you need the SUPER privilege for this operation
|
46 | Current innodb_buffer_pool_size = 8 M
|
47 | Depending on how much space your innodb indexes take up it may be safe
|
48 | to increase this value to up to 2 / 3 of total system memory
|
49 |
|
50 | MEMORY USAGE
|
51 | Max Memory Ever Allocated : 157 M
|
52 | Configured Max Per-thread Buffers : 12 M
|
53 | Configured Max Global Buffers : 154 M
|
54 | Configured Max Memory Limit : 166 M
|
55 | Physical Memory : 925 M
|
56 | Max memory limit seem to be within acceptable norms
|
57 |
|
58 | KEY BUFFER
|
59 | Current MyISAM index space = 0 bytes
|
60 | Current key_buffer_size = 16 M
|
61 | Key cache miss rate is 1 : 2
|
62 | Key buffer free ratio = 58 %
|
63 | Your key_buffer_size seems to be fine
|
64 |
|
65 | QUERY CACHE
|
66 | Query cache is enabled
|
67 | Current query_cache_size = 128 M
|
68 | Current query_cache_used = 3 M
|
69 | Current query_cache_limit = 1 M
|
70 | Current Query cache Memory fill ratio = 3.04 %
|
71 | Current query_cache_min_res_unit = 4 K
|
72 | Your query_cache_size seems to be too high.
|
73 | Perhaps you can use these resources elsewhere
|
74 | MySQL won't cache query results that are larger than query_cache_limit in size
|
75 |
|
76 | SORT OPERATIONS
|
77 | Current sort_buffer_size = 512 K
|
78 | Current read_rnd_buffer_size = 508 K
|
79 | Sort buffer seems to be fine
|
80 |
|
81 | JOINS
|
82 | Current join_buffer_size = 132.00 K
|
83 | You have had 0 queries where a join could not use an index properly
|
84 | Your joins seem to be using indexes properly
|
85 |
|
86 | OPEN FILES LIMIT
|
87 | Current open_files_limit = 1024 files
|
88 | The open_files_limit should typically be set to at least 2x-3x
|
89 | that of table_cache if you have heavy MyISAM usage.
|
90 | Your open_files_limit value seems to be fine
|
91 |
|
92 | TABLE CACHE
|
93 | You are not '1002' or 'root'
|
94 | I am unable to determine the table_count!
|
95 | Current table_cache value = 256 tables
|
96 | You have 195 open tables.
|
97 | The table_cache value seems to be fine
|
98 |
|
99 | TEMP TABLES
|
100 | Current max_heap_table_size = 511 M
|
101 | Current tmp_table_size = 512 M
|
102 | Of 50 temp tables, 49% were created on disk
|
103 | Effective in-memory tmp_table_size is limited to max_heap_table_size.
|
104 | Perhaps you should increase your tmp_table_size and/or max_heap_table_size
|
105 | to reduce the number of disk-based temporary tables
|
106 | Note! BLOB and TEXT columns are not allow in memory tables.
|
107 | If you are using these columns raising these values might not impact your
|
108 | ratio of on disk temp tables.
|
109 |
|
110 | TABLE SCANS
|
111 | Current read_buffer_size = 252 K
|
112 | Current table scan ratio = 129 : 1
|
113 | read_buffer_size seems to be fine
|
114 |
|
115 | TABLE LOCKING
|
116 | Current Lock Wait ratio = 0 : 1790
|
117 | Your table locking seems to be fine
|
118 |
|
119 | >> MySQLTuner 1.6.12 - Major Hayden <major@mhtx.net>
|
120 | >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
|
121 | >> Run with '--help' for additional options and output filtering
|
122 | [--] Performing tests on 127.0.0.1:3047
|
123 | [!!] Successfully authenticated with no password - SECURITY RISK!
|
124 |
|
125 | [--] Skipped version check for MySQLTuner script
|
126 | [!!] failed to execute: SHOW GLOBAL STATUS
|
127 | [!!] FAIL Execute SQL / return code: 256
|
128 | [!!] failed to execute: SHOW SLAVE STATUS\G
|
129 | [!!] FAIL Execute SQL / return code: 256
|
130 | [!!] failed to execute: SHOW SLAVE HOSTS
|
131 | [!!] FAIL Execute SQL / return code: 256
|
132 | [!!] Your MySQL version 4.1.22mysql4-log is EOL software! Upgrade soon!
|
133 | [OK] Operating on 32-bit architecture with less than 2GB RAM
|
134 |
|
135 | -------- Storage Engine Statistics -----------------------------------------------------------------
|
136 | [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
|
137 | [!!] InnoDB is enabled but isn't being used
|
138 | [OK] Total fragmented tables: 0
|
139 |
|
140 | -------- Security Recommendations ------------------------------------------------------------------
|
141 | [!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE TRIM(USER) = '' OR USER IS NULL
|
142 | [!!] FAIL Execute SQL / return code: 256
|
143 | [OK] There are no anonymous accounts for any database users
|
144 | [!!] No more password checks for MySQL version <=5.1
|
145 | [!!] MySQL version <=5.1 are deprecated and end of support.
|
146 |
|
147 | -------- CVE Security Recommendations --------------------------------------------------------------
|
148 | [--] Skipped due to --cvefile option undefined
|
149 | Argument "du:" isn't numeric in addition (+) at /usr/local/sbin/mysqltuner.pl
|
150 | line 2103 (#1)
|
151 | (W numeric) The indicated string was fed as an argument to an operator
|
152 | that expected a numeric value instead. If you're fortunate the message
|
153 | will identify which operator was so unfortunate.
|
154 |
|
155 |
|
156 | -------- Performance Metrics -----------------------------------------------------------------------
|
157 | [--] Up for: 6m 4s (1K q [5.058 qps], 123 conn, TX: 8M, RX: 153K)
|
158 | [--] Reads / Writes: 97% / 3%
|
159 | [--] Binary logging is enabled (GTID MODE: OFF)
|
160 | [--] Physical Memory : 925.6M
|
161 | [--] Max MySQL memory : 678.4M
|
162 | [--] Other process memory: 61.2M
|
163 | [--] Total buffers: 666.0M global + 1.6M per thread (8 max threads)
|
164 | [--] P_S Max memory usage: 0B
|
165 | [--] Galera GCache Max memory usage: 0B
|
166 | [OK] Maximum reached memory usage: 669.1M (72.29% of installed RAM)
|
167 | [OK] Maximum possible memory usage: 678.4M (73.30% of installed RAM)
|
168 | [OK] Overall possible memory usage with other process is compatible with memory available
|
169 | [OK] Slow queries: 0% (0/1K)
|
170 | [OK] Highest usage of available connections: 25% (2/8)
|
171 | [OK] Aborted connections: 0.00% (0/123)
|
172 | [OK] Query cache efficiency: 26.9% (214 cached / 797 selects)
|
173 | [OK] Query cache prunes per day: 0
|
174 | [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 84 sorts)
|
175 | [OK] No joins without indexes
|
176 | [!!] Temporary tables created on disk: 98% (49 on disk / 50 total)
|
177 | [OK] Table cache hit rate: 97% (195 open / 201 opened)
|
178 | [OK] Open file limit used: 37% (386/1K)
|
179 | [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
|
180 | [OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)
|
181 |
|
182 | -------- ThreadPool Metrics ------------------------------------------------------------------------
|
183 | [--] ThreadPool stat is disabled.
|
184 |
|
185 | -------- Performance schema ------------------------------------------------------------------------
|
186 | [--] Performance schema is disabled.
|
187 |
|
188 | -------- MyISAM Metrics ----------------------------------------------------------------------------
|
189 | [!!] Key buffer used: 41.8% (7M used / 16M cache)
|
190 | [!!] Cannot calculate MyISAM index size - re-run script as root user
|
191 |
|
192 | -------- AriaDB Metrics ----------------------------------------------------------------------------
|
193 | [--] AriaDB is disabled.
|
194 |
|
195 | -------- InnoDB Metrics ----------------------------------------------------------------------------
|
196 | [--] InnoDB is disabled.
|
197 |
|
198 | -------- TokuDB Metrics ----------------------------------------------------------------------------
|
199 | [--] TokuDB is disabled.
|
200 |
|
201 | -------- Galera Metrics ----------------------------------------------------------------------------
|
202 | [--] Galera is disabled.
|
203 |
|
204 | -------- Replication Metrics -----------------------------------------------------------------------
|
205 | [--] Galera Synchronous replication: NO
|
206 | [--] No replication slave(s) for this server.
|
207 | [--] This is a standalone server.
|
208 |
|
209 | -------- Recommendations ---------------------------------------------------------------------------
|
210 | General recommendations:
|
211 | Add skip-innodb to MySQL configuration to disable InnoDB
|
212 | MySQL started within last 24 hours - recommendations may be inaccurate
|
213 | Enable the slow query log to troubleshoot bad queries
|
214 | Temporary table size is already large - reduce result set size
|
215 | Reduce your SELECT DISTINCT queries without LIMIT clauses
|
216 | Upgrade to MySQL 5.5+ to use asynchronous write
|