1MK-VARIABLE-ADVISOR(1)User Contributed Perl DocumentationMK-VARIABLE-ADVISOR(1)
2
6 mk-variable-advisor - Analyze MySQL variables and advise on possible
7 problems.
8
10 Usage: mk-variable-advisor [OPTION...] [DSN]
11 mk-variable-advisor analyzes variables and advises on possible
13 problems.
14 Get SHOW VARIABLES from localhost:
16 mk-variable-advisor localhost
18 Get SHOW VARIABLES output saved in vars.txt:
20 mk-variable-advisor --source-of-variables vars.txt
22
24 The following section is included to inform users about the potential
25 risks, whether known or unknown, of using this tool. The two main
26 categories of risks are those created by the nature of the tool (e.g.
27 read-only tools vs. read-write tools) and those created by bugs.
28 mk-variable-advisor reads MySQL's configuration and examines it and is
30 thus very low risk.
31 At the time of this release, we know of no bugs that could cause
33 serious harm to users.
34 The authoritative source for updated information is always the online
36 issue tracking system. Issues that affect this tool will be marked as
37 such. You can see a list of such issues at the following URL:
38 <http://www.maatkit.org/bugs/mk-variable-advisor>.
39 See also "BUGS" for more information on filing bugs and getting help.
41
43 mk-variable-advisor examines "SHOW VARIABLES" for bad values and
44 settings according to the "RULES" described below. It reports on
45 variables that match the rules, so you can find bad settings in your
46 MySQL server.
47 At the time of this release, mk-variable-advisor only examples "SHOW
49 VARIABLES", but other input sources are planned like "SHOW STATUS" and
50 "SHOW SLAVE STATUS".
51
53 These are the rules that mk-variable-advisor will apply to SHOW
54 VARIABLES. Each rule has three parts: an ID, a severity, and a
55 description.
56 The rule's ID is a short, unique name for the rule. It usually relates
58 to the variable that the rule examines. If a variable is examined by
59 several rules, then the rules' IDs are numbered like "-1", "-2", "-N".
60 The rule's severity is an indication of how important it is that this
62 rule matched a query. We use NOTE, WARN, and CRIT to denote these
63 levels.
64 The rule's description is a textual, human-readable explanation of what
66 it means when a variable matches this rule. Depending on the verbosity
67 of the report you generate, you will see more of the text in the
68 description. By default, you'll see only the first sentence, which is
69 sort of a terse synopsis of the rule's meaning. At a higher verbosity,
70 you'll see subsequent sentences.
71 auto_increment
73 severity: note
74 Are you trying to write to more than one server in a dual-master or
76 ring replication configuration? This is potentially very dangerous
77 and in most cases is a serious mistake. Most people's reasons for
78 doing this are actually not valid at all.
79 concurrent_insert
81 severity: note
82 Holes (spaces left by deletes) in MyISAM tables might never be
84 reused.
85 connect_timeout
87 severity: note
88 A large value of this setting can create a denial of service
90 vulnerability.
91 debug
93 severity: crit
94 Servers built with debugging capability should not be used in
96 production because of the large performance impact.
97 delay_key_write
99 severity: warn
100 MyISAM index blocks are never flushed until necessary. If there is
102 a server crash, data corruption on MyISAM tables can be much worse
103 than usual.
104 flush
106 severity: warn
107 This option might decrease performance greatly.
109 flush_time
111 severity: warn
112 This option might decrease performance greatly.
114 have_bdb
116 severity: note
117 The BDB engine is deprecated. If you aren't using it, you should
119 disable it with the skip_bdb option.
120 init_connect
122 severity: note
123 The init_connect option is enabled on this server.
125 init_file
127 severity: note
128 The init_file option is enabled on this server.
130 init_slave
132 severity: note
133 The init_slave option is enabled on this server.
135 innodb_additional_mem_pool_size
137 severity: warn
138 This variable generally doesn't need to be larger than 20MB.
140 innodb_buffer_pool_size
142 severity: warn
143 The InnoDB buffer pool size is unconfigured. In a production
145 environment it should always be configured explicitly, and the
146 default 10MB size is not good.
147 innodb_checksums
149 severity: warn
150 InnoDB checksums are disabled. Your data is not protected from
152 hardware corruption or other errors!
153 innodb_doublewrite
155 severity: warn
156 InnoDB doublewrite is disabled. Unless you use a filesystem that
158 protects against partial page writes, your data is not safe!
159 innodb_fast_shutdown
161 severity: warn
162 InnoDB's shutdown behavior is not the default. This can lead to
164 poor performance, or the need to perform crash recovery upon
165 startup.
166 innodb_flush_log_at_trx_commit-1
168 severity: warn
169 InnoDB is not configured in strictly ACID mode. If there is a
171 crash, some transactions can be lost.
172 innodb_flush_log_at_trx_commit-2
174 severity: warn
175 Setting innodb_flush_log_at_trx_commit to 0 has no performance
177 benefits over setting it to 2, and more types of data loss are
178 possible. If you are trying to change it from 1 for performance
179 reasons, you should set it to 2 instead of 0.
180 innodb_force_recovery
182 severity: warn
183 InnoDB is in forced recovery mode! This should be used only
185 temporarily when recovering from data corruption or other bugs, not
186 for normal usage.
187 innodb_lock_wait_timeout
189 severity: warn
190 This option has an unusually long value, which can cause system
192 overload if locks are not being released.
193 innodb_log_buffer_size
195 severity: warn
196 The InnoDB log buffer size generally should not be set larger than
198 16MB. If you are doing large BLOB operations, InnoDB is not really
199 a good choice of engines anyway.
200 innodb_log_file_size
202 severity: warn
203 The InnoDB log file size is set to its default value, which is not
205 usable on production systems.
206 innodb_max_dirty_pages_pct
208 severity: note
209 The innodb_max_dirty_pages_pct is lower than the default. This can
211 cause overly aggressive flushing and add load to the I/O system.
212 flush_time
214 severity: warn
215 This setting is likely to cause very bad performance every
217 flush_time seconds.
218 key_buffer_size
220 severity: warn
221 The key buffer size is unconfigured. In a production environment
223 it should always be configured explicitly, and the default 8MB size
224 is not good.
225 large_pages
227 severity: note
228 Large pages are enabled.
230 locked_in_memory
232 severity: note
233 The server is locked in memory with --memlock.
235 log_warnings-1
237 severity: note
238 Log_warnings is disabled, so unusual events such as statements
240 unsafe for replication and aborted connections will not be logged
241 to the error log.
242 log_warnings-2
244 severity: note
245 Log_warnings must be set greater than 1 to log unusual events such
247 as aborted connections.
248 low_priority_updates
250 severity: note
251 The server is running with non-default lock priority for updates.
253 This could cause update queries to wait unexpectedly for read
254 queries.
255 max_binlog_size
257 severity: note
258 The max_binlog_size is smaller than the default of 1GB.
260 max_connect_errors
262 severity: note
263 max_connect_errors should probably be set as large as your platform
265 allows.
266 max_connections
268 severity: warn
269 If the server ever really has more than a thousand threads running,
271 then the system is likely to spend more time scheduling threads
272 than really doing useful work. This variable's value should be
273 considered in light of your workload.
274 myisam_repair_threads
276 severity: note
277 myisam_repair_threads > 1 enables multi-threaded repair, which is
279 relatively untested and is still listed as beta-quality code in the
280 official documentation.
281 old_passwords
283 severity: warn
284 Old-style passwords are insecure. They are sent in plain text
286 across the wire.
287 optimizer_prune_level
289 severity: warn
290 The optimizer will use an exhaustive search when planning complex
292 queries, which can cause the planning process to take a long time.
293 port
295 severity: note
296 The server is listening on a non-default port.
298 query_cache_size-1
300 severity: note
301 The query cache does not scale to large sizes and can cause
303 unstable performance when larger than 128MB, especially on multi-
304 core machines.
305 query_cache_size-2
307 severity: warn
308 The query cache can cause severe performance problems when it is
310 larger than 256MB, especially on multi-core machines.
311 read_buffer_size-1
313 severity: note
314 The read_buffer_size variable should generally be left at its
316 default unless an expert determines it is necessary to change it.
317 read_buffer_size-2
319 severity: warn
320 The read_buffer_size variable should not be larger than 8MB. It
322 should generally be left at its default unless an expert determines
323 it is necessary to change it. Making it larger than 2MB can hurt
324 performance significantly, and can make the server crash, swap to
325 death, or just become extremely unstable.
326 read_rnd_buffer_size-1
328 severity: note
329 The read_rnd_buffer_size variable should generally be left at its
331 default unless an expert determines it is necessary to change it.
332 read_rnd_buffer_size-2
334 severity: warn
335 The read_rnd_buffer_size variable should not be larger than 4M. It
337 should generally be left at its default unless an expert determines
338 it is necessary to change it.
339 relay_log_space_limit
341 severity: warn
342 Setting relay_log_space_limit is relatively rare, and could cause
344 an increased risk of previously unknown bugs in replication.
345 slave_net_timeout
347 severity: warn
348 This variable is set too high. This is too long to wait before
350 noticing that the connection to the master has failed and retrying.
351 This should probably be set to 60 seconds or less. It is also a
352 good idea to use mk-heartbeat to ensure that the connection does
353 not appear to time out when the master is simply idle.
354 slave_skip_errors
356 severity: crit
357 You should not set this option. If replication is having errors,
359 you need to find and resolve the cause of that; it is likely that
360 your slave's data is different from the master. You can find out
361 with mk-table-checksum.
362 sort_buffer_size-1
364 severity: note
365 The sort_buffer_size variable should generally be left at its
367 default unless an expert determines it is necessary to change it.
368 sort_buffer_size-2
370 severity: note
371 The sort_buffer_size variable should generally be left at its
373 default unless an expert determines it is necessary to change it.
374 Making it larger than a few MB can hurt performance significantly,
375 and can make the server crash, swap to death, or just become
376 extremely unstable.
377 sql_notes
379 severity: note
380 This server is configured not to log Note level warnings to the
382 error log.
383 sync_frm
385 severity: warn
386 It is best to set sync_frm so that .frm files are flushed safely to
388 disk in case of a server crash.
389 tx_isolation-1
391 severity: note
392 This server's transaction isolation level is non-default.
394 tx_isolation-2
396 severity: warn
397 Most applications should use the default REPEATABLE-READ
399 transaction isolation level, or in a few cases READ-COMMITTED.
400 expire_log_days
402 severity: warn
403 Binary logs are enabled, but automatic purging is not enabled. If
405 you do not purge binary logs, your disk will fill up. If you
406 delete binary logs externally to MySQL, you will cause unwanted
407 behaviors. Always ask MySQL to purge obsolete logs, never delete
408 them externally.
409 innodb_file_io_threads
411 severity: note
412 This option is useless except on Windows.
414 innodb_data_file_path
416 severity: note
417 Auto-extending InnoDB files can consume a lot of disk space that is
419 very difficult to reclaim later. Some people prefer to set
420 innodb_file_per_table and allocate a fixed-size file for ibdata1.
421 innodb_flush_method
423 severity: note
424 Most production database servers that use InnoDB should set
426 innodb_flush_method to O_DIRECT to avoid double-buffering, unless
427 the I/O system is very low performance.
428 innodb_locks_unsafe_for_binlog
430 severity: warn
431 This option makes point-in-time recovery from binary logs, and
433 replication, untrustworthy if statement-based logging is used.
434 innodb_support_xa
436 severity: warn
437 MySQL's internal XA transaction support between InnoDB and the
439 binary log is disabled. The binary log might not match InnoDB's
440 state after crash recovery, and replication might drift out of sync
441 due to out-of-order statements in the binary log.
442 log_bin
444 severity: warn
445 Binary logging is disabled, so point-in-time recovery and
447 replication are not possible.
448 log_output
450 severity: warn
451 Directing log output to tables has a high performance impact.
453 max_relay_log_size
455 severity: note
456 A custom max_relay_log_size is defined.
458 myisam_recover_options
460 severity: warn
461 myisam_recover_options should be set to some value such as
463 BACKUP,FORCE to ensure that table corruption is noticed.
464 storage_engine
466 severity: note
467 The server is using a non-standard storage engine as default.
469 sync_binlog
471 severity: warn
472 Binary logging is enabled, but sync_binlog isn't configured so that
474 every transaction is flushed to the binary log for durability.
475 tmp_table_size
477 severity: note
478 The effective minimum size of in-memory implicit temporary tables
480 used internally during query execution is min(tmp_table_size,
481 max_heap_table_size), so max_heap_table_size should be at least as
482 large as tmp_table_size.
483 old mysql version
485 severity: warn
486 These are the recommended minimum version for each major release:
488 3.23, 4.1.20, 5.0.37, 5.1.30.
489 end-of-life mysql version
491 severity: note
492 Every release older than 5.1 is now officially end-of-life.
494
496 This tool accepts additional command-line arguments. Refer to the
497 "SYNOPSIS" and usage information for details.
498 --ask-pass
500 Prompt for a password when connecting to MySQL.
501 --charset
503 short form: -A; type: string
504 Default character set. If the value is utf8, sets Perl's binmode
506 on STDOUT to utf8, passes the mysql_enable_utf8 option to
507 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
508 other value sets binmode on STDOUT without the utf8 layer, and runs
509 SET NAMES after connecting to MySQL.
510 --config
512 type: Array
513 Read this comma-separated list of config files; if specified, this
515 must be the first option on the command line.
516 --[no]continue-on-error
518 default: yes
519 Continue working even if there is an error.
521 --daemonize
523 Fork to the background and detach from the shell. POSIX operating
524 systems only.
525 --defaults-file
527 short form: -F; type: string
528 Only read mysql options from the given file. You must give an
530 absolute pathname.
531 --help
533 Show help and exit.
534 --host
536 short form: -h; type: string
537 Connect to host.
539 --ignore-rules
541 type: hash
542 Ignore these rule IDs.
544 Specify a comma-separated list of rule IDs (e.g.
546 LIT.001,RES.002,etc.) to ignore.
547 --password
549 short form: -p; type: string
550 Password to use when connecting.
552 --pid
554 type: string
555 Create the given PID file when daemonized. The file contains the
557 process ID of the daemonized instance. The PID file is removed
558 when the daemonized instance exits. The program checks for the
559 existence of the PID file when starting; if it exists and the
560 process with the matching PID exists, the program exits.
561 --port
563 short form: -P; type: int
564 Port number to use for connection.
566 --set-vars
568 type: string; default: wait_timeout=10000
569 Set these MySQL variables. Immediately after connecting to MySQL,
571 this string will be appended to SET and executed.
572 --socket
574 short form: -S; type: string
575 Socket file to use for connection.
577 --source-of-variables
579 type: string; default: mysql
580 Read "SHOW VARIABLES" from this source. Possible values are
582 "mysql", "none" or a file name. If "mysql" is specified then you
583 must also specify a DSN on the command line.
584 --user
586 short form: -u; type: string
587 User for login if not current user.
589 --verbose
591 short form: -v; cumulative: yes; default: 1
592 Increase verbosity of output. At the default level of verbosity,
594 the program prints only the first sentence of each rule's
595 description. At higher levels, the program prints more of the
596 description.
597 --version
599 Show version and exit.
600
602 These DSN options are used to create a DSN. Each option is given like
603 "option=value". The options are case-sensitive, so P and p are not the
604 same option. There cannot be whitespace before or after the "=" and if
605 the value contains whitespace it must be quoted. DSN options are
606 comma-separated. See the maatkit manpage for full details.
607 • A
609 dsn: charset; copy: yes
611 Default character set.
613 • D
615 dsn: database; copy: yes
617 Default database.
619 • F
621 dsn: mysql_read_default_file; copy: yes
623 Only read default options from the given file
625 • h
627 dsn: host; copy: yes
629 Connect to host.
631 • p
633 dsn: password; copy: yes
635 Password to use when connecting.
637 • P
639 dsn: port; copy: yes
641 Port number to use for connection.
643 • S
645 dsn: mysql_socket; copy: yes
647 Socket file to use for connection.
649 • u
651 dsn: user; copy: yes
653 User for login if not current user.
655
657 You can download Maatkit from Google Code at
658 <http://code.google.com/p/maatkit/>, or you can get any of the tools
659 easily with a command like the following:
660 wget http://www.maatkit.org/get/toolname
662 or
663 wget http://www.maatkit.org/trunk/toolname
664 Where "toolname" can be replaced with the name (or fragment of a name)
666 of any of the Maatkit tools. Once downloaded, they're ready to run; no
667 installation is needed. The first URL gets the latest released version
668 of the tool, and the second gets the latest trunk code from Subversion.
669
671 The environment variable "MKDEBUG" enables verbose debugging output in
672 all of the Maatkit tools:
673 MKDEBUG=1 mk-....
675
677 You need the following Perl modules: DBI and DBD::mysql.
678
680 For a list of known bugs see
681 <http://www.maatkit.org/bugs/mk-variable-advisor>.
682 Please use Google Code Issues and Groups to report bugs or request
684 support: <http://code.google.com/p/maatkit/>. You can also join
685 #maatkit on Freenode to discuss Maatkit.
686 Please include the complete command-line used to reproduce the problem
688 you are seeing, the version of all MySQL servers involved, the complete
689 output of the tool when run with "--version", and if possible,
690 debugging output produced by running with the "MKDEBUG=1" environment
691 variable.
692
694 This program is copyright 2009-2011 Percona Inc. Feedback and
695 improvements are welcome.
696 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
698 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
699 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
700 This program is free software; you can redistribute it and/or modify it
702 under the terms of the GNU General Public License as published by the
703 Free Software Foundation, version 2; OR the Perl Artistic License. On
704 UNIX and similar systems, you can issue `man perlgpl' or `man
705 perlartistic' to read these licenses.
706 You should have received a copy of the GNU General Public License along
708 with this program; if not, write to the Free Software Foundation, Inc.,
709 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
710
712 Baron Schwartz, Daniel Nichter
713
715 This tool is part of Maatkit, a toolkit for power users of MySQL.
716 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
717 primary code contributors. Both are employed by Percona. Financial
718 support for Maatkit development is primarily provided by Percona and
719 its clients.
720
722 This manual page documents Ver 1.0.2 Distrib 7540 $Revision: 7477 $.
723perl v5.38.0 2023-07-20 MK-VARIABLE-ADVISOR(1)