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 Get SHOW VARIABLES from localhost:
11 mk-variable-advisor localhost
13 Get SHOW VARIABLES output saved in vars.txt:
15 mk-variable-advisor --source-of-variables vars.txt
17
19 The following section is included to inform users about the potential
20 risks, whether known or unknown, of using this tool. The two main
21 categories of risks are those created by the nature of the tool (e.g.
22 read-only tools vs. read-write tools) and those created by bugs.
23 mk-variable-advisor reads MySQL's configuration and examines it and is
25 thus very low risk.
26 At the time of this release, we know of no bugs that could cause
28 serious harm to users.
29 The authoritative source for updated information is always the online
31 issue tracking system. Issues that affect this tool will be marked as
32 such. You can see a list of such issues at the following URL:
33 http://www.maatkit.org/bugs/mk-variable-advisor
34 <http://www.maatkit.org/bugs/mk-variable-advisor>.
35 See also "BUGS" for more information on filing bugs and getting help.
37
39 mk-variable-advisor examines "SHOW VARIABLES" for bad values and
40 settings according to the "RULES" described below. It reports on
41 variables that match the rules, so you can find bad settings in your
42 MySQL server.
43 At the time of this release, mk-variable-advisor only examples "SHOW
45 VARIABLES", but other input sources are planned like "SHOW STATUS" and
46 "SHOW SLAVE STATUS".
47
49 These are the rules that mk-variable-advisor will apply to SHOW
50 VARIABLES. Each rule has three parts: an ID, a severity, and a
51 description.
52 The rule's ID is a short, unique name for the rule. It usually relates
54 to the variable that the rule examines. If a variable is examined by
55 several rules, then the rules' IDs are numbered like "-1", "-2", "-N".
56 The rule's severity is an indication of how important it is that this
58 rule matched a query. We use NOTE, WARN, and CRIT to denote these
59 levels.
60 The rule's description is a textual, human-readable explanation of what
62 it means when a variable matches this rule. Depending on the verbosity
63 of the report you generate, you will see more of the text in the
64 description. By default, you'll see only the first sentence, which is
65 sort of a terse synopsis of the rule's meaning. At a higher verbosity,
66 you'll see subsequent sentences.
67 auto_increment
69 severity: note
70 Are you trying to write to more than one server in a dual-master or
72 ring replication configuration? This is potentially very dangerous
73 and in most cases is a serious mistake. Most people's reasons for
74 doing this are actually not valid at all.
75 concurrent_insert
77 severity: note
78 Holes (spaces left by deletes) in MyISAM tables might never be
80 reused.
81 connect_timeout
83 severity: note
84 A large value of this setting can create a denial of service
86 vulnerability.
87 debug
89 severity: crit
90 Servers built with debugging capability should not be used in
92 production because of the large performance impact.
93 delay_key_write
95 severity: warn
96 MyISAM index blocks are never flushed until necessary. If there is
98 a server crash, data corruption on MyISAM tables can be much worse
99 than usual.
100 flush
102 severity: warn
103 This option might decrease performance greatly.
105 flush_time
107 severity: warn
108 This option might decrease performance greatly.
110 have_bdb
112 severity: note
113 The BDB engine is deprecated. If you aren't using it, you should
115 disable it with the skip_bdb option.
116 init_connect
118 severity: note
119 The init_connect option is enabled on this server.
121 init_file
123 severity: note
124 The init_file option is enabled on this server.
126 init_slave
128 severity: note
129 The init_slave option is enabled on this server.
131 innodb_additional_mem_pool_size
133 severity: warn
134 This variable generally doesn't need to be larger than 20MB.
136 innodb_buffer_pool_size
138 severity: warn
139 The InnoDB buffer pool size is unconfigured. In a production
141 environment it should always be configured explicitly, and the
142 default 10MB size is not good.
143 innodb_checksums
145 severity: warn
146 InnoDB checksums are disabled. Your data is not protected from
148 hardware corruption or other errors!
149 innodb_doublewrite
151 severity: warn
152 InnoDB doublewrite is disabled. Unless you use a filesystem that
154 protects against partial page writes, your data is not safe!
155 innodb_fast_shutdown
157 severity: warn
158 InnoDB's shutdown behavior is not the default. This can lead to
160 poor performance, or the need to perform crash recovery upon
161 startup.
162 innodb_flush_log_at_trx_commit-1
164 severity: warn
165 InnoDB is not configured in strictly ACID mode. If there is a
167 crash, some transactions can be lost.
168 innodb_flush_log_at_trx_commit-2
170 severity: warn
171 Setting innodb_flush_log_at_trx_commit to 0 has no performance
173 benefits over setting it to 2, and more types of data loss are
174 possible. If you are trying to change it from 1 for performance
175 reasons, you should set it to 2 instead of 0.
176 innodb_force_recovery
178 severity: warn
179 InnoDB is in forced recovery mode! This should be used only
181 temporarily when recovering from data corruption or other bugs, not
182 for normal usage.
183 innodb_lock_wait_timeout
185 severity: warn
186 This option has an unusually long value, which can cause system
188 overload if locks are not being released.
189 innodb_log_buffer_size
191 severity: warn
192 The InnoDB log buffer size generally should not be set larger than
194 16MB. If you are doing large BLOB operations, InnoDB is not really
195 a good choice of engines anyway.
196 innodb_log_file_size
198 severity: warn
199 The InnoDB log file size is set to its default value, which is not
201 usable on production systems.
202 innodb_max_dirty_pages_pct
204 severity: note
205 The innodb_max_dirty_pages_pct is lower than the default. This can
207 cause overly aggressive flushing and add load to the I/O system.
208 flush_time
210 severity: warn
211 This setting is likely to cause very bad performance every
213 flush_time seconds.
214 key_buffer_size
216 severity: warn
217 The key buffer size is unconfigured. In a production environment
219 it should always be configured explicitly, and the default 8MB size
220 is not good.
221 large_pages
223 severity: note
224 Large pages are enabled.
226 locked_in_memory
228 severity: note
229 The server is locked in memory with --memlock.
231 log_warnings-1
233 severity: note
234 Log_warnings is disabled, so unusual events such as statements
236 unsafe for replication and aborted connections will not be logged
237 to the error log.
238 log_warnings-2
240 severity: note
241 Log_warnings must be set greater than 1 to log unusual events such
243 as aborted connections.
244 low_priority_updates
246 severity: note
247 The server is running with non-default lock priority for updates.
249 This could cause update queries to wait unexpectedly for read
250 queries.
251 max_binlog_size
253 severity: note
254 The max_binlog_size is smaller than the default of 1GB.
256 max_connect_errors
258 severity: note
259 max_connect_errors should probably be set as large as your platform
261 allows.
262 max_connections
264 severity: warn
265 If the server ever really has more than a thousand threads running,
267 then the system is likely to spend more time scheduling threads
268 than really doing useful work. This variable's value should be
269 considered in light of your workload.
270 myisam_repair_threads
272 severity: note
273 myisam_repair_threads > 1 enables multi-threaded repair, which is
275 relatively untested and is still listed as beta-quality code in the
276 official documentation.
277 old_passwords
279 severity: warn
280 Old-style passwords are insecure. They are sent in plain text
282 across the wire.
283 optimizer_prune_level
285 severity: warn
286 The optimizer will use an exhaustive search when planning complex
288 queries, which can cause the planning process to take a long time.
289 port
291 severity: note
292 The server is listening on a non-default port.
294 query_cache_size-1
296 severity: note
297 The query cache does not scale to large sizes and can cause
299 unstable performance when larger than 128MB, especially on multi-
300 core machines.
301 query_cache_size-2
303 severity: warn
304 The query cache can cause severe performance problems when it is
306 larger than 256MB, especially on multi-core machines.
307 read_buffer_size-1
309 severity: note
310 The read_buffer_size variable should generally be left at its
312 default unless an expert determines it is necessary to change it.
313 read_buffer_size-2
315 severity: warn
316 The read_buffer_size variable should not be larger than 8MB. It
318 should generally be left at its default unless an expert determines
319 it is necessary to change it. Making it larger than 2MB can hurt
320 performance significantly, and can make the server crash, swap to
321 death, or just become extremely unstable.
322 read_rnd_buffer_size-1
324 severity: note
325 The read_rnd_buffer_size variable should generally be left at its
327 default unless an expert determines it is necessary to change it.
328 read_rnd_buffer_size-2
330 severity: warn
331 The read_rnd_buffer_size variable should not be larger than 4M. It
333 should generally be left at its default unless an expert determines
334 it is necessary to change it.
335 relay_log_space_limit
337 severity: warn
338 Setting relay_log_space_limit is relatively rare, and could cause
340 an increased risk of previously unknown bugs in replication.
341 slave_net_timeout
343 severity: warn
344 This variable is set too high. This is too long to wait before
346 noticing that the connection to the master has failed and retrying.
347 This should probably be set to 60 seconds or less. It is also a
348 good idea to use mk-heartbeat to ensure that the connection does
349 not appear to time out when the master is simply idle.
350 slave_skip_errors
352 severity: crit
353 You should not set this option. If replication is having errors,
355 you need to find and resolve the cause of that; it is likely that
356 your slave's data is different from the master. You can find out
357 with mk-table-checksum.
358 sort_buffer_size-1
360 severity: note
361 The sort_buffer_size variable should generally be left at its
363 default unless an expert determines it is necessary to change it.
364 sort_buffer_size-2
366 severity: note
367 The sort_buffer_size variable should generally be left at its
369 default unless an expert determines it is necessary to change it.
370 Making it larger than a few MB can hurt performance significantly,
371 and can make the server crash, swap to death, or just become
372 extremely unstable.
373 sql_notes
375 severity: note
376 This server is configured not to log Note level warnings to the
378 error log.
379 sync_frm
381 severity: warn
382 It is best to set sync_frm so that .frm files are flushed safely to
384 disk in case of a server crash.
385 tx_isolation-1
387 severity: note
388 This server's transaction isolation level is non-default.
390 tx_isolation-2
392 severity: warn
393 Most applications should use the default REPEATABLE-READ
395 transaction isolation level, or in a few cases READ-COMMITTED.
396 expire_log_days
398 severity: warn
399 Binary logs are enabled, but automatic purging is not enabled. If
401 you do not purge binary logs, your disk will fill up. If you
402 delete binary logs externally to MySQL, you will cause unwanted
403 behaviors. Always ask MySQL to purge obsolete logs, never delete
404 them externally.
405 innodb_file_io_threads
407 severity: note
408 This option is useless except on Windows.
410 innodb_data_file_path
412 severity: note
413 Auto-extending InnoDB files can consume a lot of disk space that is
415 very difficult to reclaim later. Some people prefer to set
416 innodb_file_per_table and allocate a fixed-size file for ibdata1.
417 innodb_flush_method
419 severity: note
420 Most production database servers that use InnoDB should set
422 innodb_flush_method to O_DIRECT to avoid double-buffering, unless
423 the I/O system is very low performance.
424 innodb_locks_unsafe_for_binlog
426 severity: warn
427 This option makes point-in-time recovery from binary logs, and
429 replication, untrustworthy if statement-based logging is used.
430 innodb_support_xa
432 severity: warn
433 MySQL's internal XA transaction support between InnoDB and the
435 binary log is disabled. The binary log might not match InnoDB's
436 state after crash recovery.
437 log_bin
439 severity: warn
440 Binary logging is disabled, so point-in-time recovery and
442 replication are not possible.
443 log_output
445 severity: warn
446 Directing log output to tables has a high performance impact.
448 max_relay_log_size
450 severity: note
451 A custom max_relay_log_size is defined.
453 myisam_recover_options
455 severity: warn
456 myisam_recover_options should be set to some value such as
458 BACKUP,FORCE to ensure that table corruption is noticed.
459 storage_engine
461 severity: note
462 The server is using a non-standard storage engine as default.
464 sync_binlog
466 severity: warn
467 Binary logging is enabled, but sync_binlog isn't configured so that
469 every transaction is flushed to the binary log for durability.
470 tmp_table_size
472 severity: note
473 The effective minimum size of in-memory implicit temporary tables
475 used internally during query execution is min(tmp_table_size,
476 max_heap_table_size), so max_heap_table_size should be at least as
477 large as tmp_table_size.
478 old mysql version
480 severity: warn
481 These are the recommended minimum version for each major release:
483 3.23, 4.1.20, 5.0.37, 5.1.30.
484 end-of-life mysql version
486 severity: note
487 Every release older than 5.1 is now officially end-of-life.
489
491 --ask-pass
492 Prompt for a password when connecting to MySQL.
493 --charset
495 short form: -A; type: string
496 Default character set. If the value is utf8, sets Perl's binmode
498 on STDOUT to utf8, passes the mysql_enable_utf8 option to
499 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
500 other value sets binmode on STDOUT without the utf8 layer, and runs
501 SET NAMES after connecting to MySQL.
502 --config
504 type: Array
505 Read this comma-separated list of config files; if specified, this
507 must be the first option on the command line.
508 --[no]continue-on-error
510 default: yes
511 Continue working even if there is an error.
513 --daemonize
515 Fork to the background and detach from the shell. POSIX operating
516 systems only.
517 --defaults-file
519 short form: -F; type: string
520 Only read mysql options from the given file. You must give an
522 absolute pathname.
523 --help
525 Show help and exit.
526 --host
528 short form: -h; type: string
529 Connect to host.
531 --ignore-rules
533 type: hash
534 Ignore these rule IDs.
536 Specify a comma-separated list of rule IDs (e.g.
538 LIT.001,RES.002,etc.) to ignore.
539 --password
541 short form: -p; type: string
542 Password to use when connecting.
544 --pid
546 type: string
547 Create the given PID file when daemonized. The file contains the
549 process ID of the daemonized instance. The PID file is removed
550 when the daemonized instance exits. The program checks for the
551 existence of the PID file when starting; if it exists and the
552 process with the matching PID exists, the program exits.
553 --port
555 short form: -P; type: int
556 Port number to use for connection.
558 --set-vars
560 type: string; default: wait_timeout=10000
561 Set these MySQL variables. Immediately after connecting to MySQL,
563 this string will be appended to SET and executed.
564 --socket
566 short form: -S; type: string
567 Socket file to use for connection.
569 --source-of-variables
571 type: string; default: mysql
572 Read "SHOW VARIABLES" from this source. Possible values are
574 "mysql", "none" or a file name. If "mysql" is specified then you
575 must also specify a DSN on the command line.
576 --user
578 short form: -u; type: string
579 User for login if not current user.
581 --verbose
583 short form: -v; cumulative: yes; default: 1
584 Increase verbosity of output. At the default level of verbosity,
586 the program prints only the first sentence of each rule's
587 description. At higher levels, the program prints more of the
588 description.
589 --version
591 Show version and exit.
592
594 These DSN options are used to create a DSN. Each option is given like
595 "option=value". The options are case-sensitive, so P and p are not the
596 same option. There cannot be whitespace before or after the "=" and if
597 the value contains whitespace it must be quoted. DSN options are
598 comma-separated. See the maatkit manpage for full details.
599 · A
601 dsn: charset; copy: yes
603 Default character set.
605 · D
607 dsn: database; copy: yes
609 Default database.
611 · F
613 dsn: mysql_read_default_file; copy: yes
615 Only read default options from the given file
617 · h
619 dsn: host; copy: yes
621 Connect to host.
623 · p
625 dsn: password; copy: yes
627 Password to use when connecting.
629 · P
631 dsn: port; copy: yes
633 Port number to use for connection.
635 · S
637 dsn: mysql_socket; copy: yes
639 Socket file to use for connection.
641 · u
643 dsn: user; copy: yes
645 User for login if not current user.
647
649 You can download Maatkit from Google Code at
650 <http://code.google.com/p/maatkit/>, or you can get any of the tools
651 easily with a command like the following:
652 wget http://www.maatkit.org/get/toolname
654 or
655 wget http://www.maatkit.org/trunk/toolname
656 Where "toolname" can be replaced with the name (or fragment of a name)
658 of any of the Maatkit tools. Once downloaded, they're ready to run; no
659 installation is needed. The first URL gets the latest released version
660 of the tool, and the second gets the latest trunk code from Subversion.
661
663 The environment variable "MKDEBUG" enables verbose debugging output in
664 all of the Maatkit tools:
665 MKDEBUG=1 mk-....
667
669 You need the following Perl modules: DBI and DBD::mysql.
670
672 For list of known bugs see
673 http://www.maatkit.org/bugs/mk-variable-advisor
674 <http://www.maatkit.org/bugs/mk-variable-advisor>.
675 Please use Google Code Issues and Groups to report bugs or request
677 support: <http://code.google.com/p/maatkit/>. You can also join
678 #maatkit on Freenode to discuss Maatkit.
679 Please include the complete command-line used to reproduce the problem
681 you are seeing, the version of all MySQL servers involved, the complete
682 output of the tool when run with "--version", and if possible,
683 debugging output produced by running with the "MKDEBUG=1" environment
684 variable.
685
687 This program is copyright 2009-2010 Percona Inc. Feedback and
688 improvements are welcome.
689 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
691 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
692 MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
693 This program is free software; you can redistribute it and/or modify it
695 under the terms of the GNU General Public License as published by the
696 Free Software Foundation, version 2; OR the Perl Artistic License. On
697 UNIX and similar systems, you can issue `man perlgpl' or `man
698 perlartistic' to read these licenses.
699 You should have received a copy of the GNU General Public License along
701 with this program; if not, write to the Free Software Foundation, Inc.,
702 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
703
705 Baron Schwartz, Daniel Nichter
706
708 This tool is part of Maatkit, a toolkit for power users of MySQL.
709 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
710 primary code contributors. Both are employed by Percona. Financial
711 support for Maatkit development is primarily provided by Percona and
712 its clients.
713
715 This manual page documents Ver 1.0.0 Distrib 6839 $Revision: 6831 $.
716perl v5.12.1 2010-08-01 MK-VARIABLE-ADVISOR(1)