1MK-VARIABLE-ADVISOR(1)User Contributed Perl DocumentationMK-VARIABLE-ADVISOR(1)
2
3
4

NAME

6       mk-variable-advisor - Analyze MySQL variables and advise on possible
7       problems.
8

SYNOPSIS

10       Usage: mk-variable-advisor [OPTION...] [DSN]
11
12       mk-variable-advisor analyzes variables and advises on possible
13       problems.
14
15       Get SHOW VARIABLES from localhost:
16
17         mk-variable-advisor localhost
18
19       Get SHOW VARIABLES output saved in vars.txt:
20
21         mk-variable-advisor --source-of-variables vars.txt
22

RISKS

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
29       mk-variable-advisor reads MySQL's configuration and examines it and is
30       thus very low risk.
31
32       At the time of this release, we know of no bugs that could cause
33       serious harm to users.
34
35       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
40       See also "BUGS" for more information on filing bugs and getting help.
41

DESCRIPTION

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
48       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

RULES

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
57       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
61       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
65       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
72       auto_increment
73           severity: note
74
75           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
80       concurrent_insert
81           severity: note
82
83           Holes (spaces left by deletes) in MyISAM tables might never be
84           reused.
85
86       connect_timeout
87           severity: note
88
89           A large value of this setting can create a denial of service
90           vulnerability.
91
92       debug
93           severity: crit
94
95           Servers built with debugging capability should not be used in
96           production because of the large performance impact.
97
98       delay_key_write
99           severity: warn
100
101           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
105       flush
106           severity: warn
107
108           This option might decrease performance greatly.
109
110       flush_time
111           severity: warn
112
113           This option might decrease performance greatly.
114
115       have_bdb
116           severity: note
117
118           The BDB engine is deprecated.  If you aren't using it, you should
119           disable it with the skip_bdb option.
120
121       init_connect
122           severity: note
123
124           The init_connect option is enabled on this server.
125
126       init_file
127           severity: note
128
129           The init_file option is enabled on this server.
130
131       init_slave
132           severity: note
133
134           The init_slave option is enabled on this server.
135
136       innodb_additional_mem_pool_size
137           severity: warn
138
139           This variable generally doesn't need to be larger than 20MB.
140
141       innodb_buffer_pool_size
142           severity: warn
143
144           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
148       innodb_checksums
149           severity: warn
150
151           InnoDB checksums are disabled.  Your data is not protected from
152           hardware corruption or other errors!
153
154       innodb_doublewrite
155           severity: warn
156
157           InnoDB doublewrite is disabled.  Unless you use a filesystem that
158           protects against partial page writes, your data is not safe!
159
160       innodb_fast_shutdown
161           severity: warn
162
163           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
167       innodb_flush_log_at_trx_commit-1
168           severity: warn
169
170           InnoDB is not configured in strictly ACID mode.  If there is a
171           crash, some transactions can be lost.
172
173       innodb_flush_log_at_trx_commit-2
174           severity: warn
175
176           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
181       innodb_force_recovery
182           severity: warn
183
184           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
188       innodb_lock_wait_timeout
189           severity: warn
190
191           This option has an unusually long value, which can cause system
192           overload if locks are not being released.
193
194       innodb_log_buffer_size
195           severity: warn
196
197           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
201       innodb_log_file_size
202           severity: warn
203
204           The InnoDB log file size is set to its default value, which is not
205           usable on production systems.
206
207       innodb_max_dirty_pages_pct
208           severity: note
209
210           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
213       flush_time
214           severity: warn
215
216           This setting is likely to cause very bad performance every
217           flush_time seconds.
218
219       key_buffer_size
220           severity: warn
221
222           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
226       large_pages
227           severity: note
228
229           Large pages are enabled.
230
231       locked_in_memory
232           severity: note
233
234           The server is locked in memory with --memlock.
235
236       log_warnings-1
237           severity: note
238
239           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
243       log_warnings-2
244           severity: note
245
246           Log_warnings must be set greater than 1 to log unusual events such
247           as aborted connections.
248
249       low_priority_updates
250           severity: note
251
252           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
256       max_binlog_size
257           severity: note
258
259           The max_binlog_size is smaller than the default of 1GB.
260
261       max_connect_errors
262           severity: note
263
264           max_connect_errors should probably be set as large as your platform
265           allows.
266
267       max_connections
268           severity: warn
269
270           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
275       myisam_repair_threads
276           severity: note
277
278           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
282       old_passwords
283           severity: warn
284
285           Old-style passwords are insecure.  They are sent in plain text
286           across the wire.
287
288       optimizer_prune_level
289           severity: warn
290
291           The optimizer will use an exhaustive search when planning complex
292           queries, which can cause the planning process to take a long time.
293
294       port
295           severity: note
296
297           The server is listening on a non-default port.
298
299       query_cache_size-1
300           severity: note
301
302           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
306       query_cache_size-2
307           severity: warn
308
309           The query cache can cause severe performance problems when it is
310           larger than 256MB, especially on multi-core machines.
311
312       read_buffer_size-1
313           severity: note
314
315           The read_buffer_size variable should generally be left at its
316           default unless an expert determines it is necessary to change it.
317
318       read_buffer_size-2
319           severity: warn
320
321           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
327       read_rnd_buffer_size-1
328           severity: note
329
330           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
333       read_rnd_buffer_size-2
334           severity: warn
335
336           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
340       relay_log_space_limit
341           severity: warn
342
343           Setting relay_log_space_limit is relatively rare, and could cause
344           an increased risk of previously unknown bugs in replication.
345
346       slave_net_timeout
347           severity: warn
348
349           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
355       slave_skip_errors
356           severity: crit
357
358           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
363       sort_buffer_size-1
364           severity: note
365
366           The sort_buffer_size variable should generally be left at its
367           default unless an expert determines it is necessary to change it.
368
369       sort_buffer_size-2
370           severity: note
371
372           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
378       sql_notes
379           severity: note
380
381           This server is configured not to log Note level warnings to the
382           error log.
383
384       sync_frm
385           severity: warn
386
387           It is best to set sync_frm so that .frm files are flushed safely to
388           disk in case of a server crash.
389
390       tx_isolation-1
391           severity: note
392
393           This server's transaction isolation level is non-default.
394
395       tx_isolation-2
396           severity: warn
397
398           Most applications should use the default REPEATABLE-READ
399           transaction isolation level, or in a few cases READ-COMMITTED.
400
401       expire_log_days
402           severity: warn
403
404           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
410       innodb_file_io_threads
411           severity: note
412
413           This option is useless except on Windows.
414
415       innodb_data_file_path
416           severity: note
417
418           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
422       innodb_flush_method
423           severity: note
424
425           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
429       innodb_locks_unsafe_for_binlog
430           severity: warn
431
432           This option makes point-in-time recovery from binary logs, and
433           replication, untrustworthy if statement-based logging is used.
434
435       innodb_support_xa
436           severity: warn
437
438           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
443       log_bin
444           severity: warn
445
446           Binary logging is disabled, so point-in-time recovery and
447           replication are not possible.
448
449       log_output
450           severity: warn
451
452           Directing log output to tables has a high performance impact.
453
454       max_relay_log_size
455           severity: note
456
457           A custom max_relay_log_size is defined.
458
459       myisam_recover_options
460           severity: warn
461
462           myisam_recover_options should be set to some value such as
463           BACKUP,FORCE to ensure that table corruption is noticed.
464
465       storage_engine
466           severity: note
467
468           The server is using a non-standard storage engine as default.
469
470       sync_binlog
471           severity: warn
472
473           Binary logging is enabled, but sync_binlog isn't configured so that
474           every transaction is flushed to the binary log for durability.
475
476       tmp_table_size
477           severity: note
478
479           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
484       old mysql version
485           severity: warn
486
487           These are the recommended minimum version for each major release:
488           3.23, 4.1.20, 5.0.37, 5.1.30.
489
490       end-of-life mysql version
491           severity: note
492
493           Every release older than 5.1 is now officially end-of-life.
494

OPTIONS

496       This tool accepts additional command-line arguments.  Refer to the
497       "SYNOPSIS" and usage information for details.
498
499       --ask-pass
500           Prompt for a password when connecting to MySQL.
501
502       --charset
503           short form: -A; type: string
504
505           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
511       --config
512           type: Array
513
514           Read this comma-separated list of config files; if specified, this
515           must be the first option on the command line.
516
517       --[no]continue-on-error
518           default: yes
519
520           Continue working even if there is an error.
521
522       --daemonize
523           Fork to the background and detach from the shell.  POSIX operating
524           systems only.
525
526       --defaults-file
527           short form: -F; type: string
528
529           Only read mysql options from the given file.  You must give an
530           absolute pathname.
531
532       --help
533           Show help and exit.
534
535       --host
536           short form: -h; type: string
537
538           Connect to host.
539
540       --ignore-rules
541           type: hash
542
543           Ignore these rule IDs.
544
545           Specify a comma-separated list of rule IDs (e.g.
546           LIT.001,RES.002,etc.)  to ignore.
547
548       --password
549           short form: -p; type: string
550
551           Password to use when connecting.
552
553       --pid
554           type: string
555
556           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
562       --port
563           short form: -P; type: int
564
565           Port number to use for connection.
566
567       --set-vars
568           type: string; default: wait_timeout=10000
569
570           Set these MySQL variables.  Immediately after connecting to MySQL,
571           this string will be appended to SET and executed.
572
573       --socket
574           short form: -S; type: string
575
576           Socket file to use for connection.
577
578       --source-of-variables
579           type: string; default: mysql
580
581           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
585       --user
586           short form: -u; type: string
587
588           User for login if not current user.
589
590       --verbose
591           short form: -v; cumulative: yes; default: 1
592
593           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
598       --version
599           Show version and exit.
600

DSN OPTIONS

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
608       •   A
609
610           dsn: charset; copy: yes
611
612           Default character set.
613
614       •   D
615
616           dsn: database; copy: yes
617
618           Default database.
619
620       •   F
621
622           dsn: mysql_read_default_file; copy: yes
623
624           Only read default options from the given file
625
626       •   h
627
628           dsn: host; copy: yes
629
630           Connect to host.
631
632       •   p
633
634           dsn: password; copy: yes
635
636           Password to use when connecting.
637
638       •   P
639
640           dsn: port; copy: yes
641
642           Port number to use for connection.
643
644       •   S
645
646           dsn: mysql_socket; copy: yes
647
648           Socket file to use for connection.
649
650       •   u
651
652           dsn: user; copy: yes
653
654           User for login if not current user.
655

DOWNLOADING

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
661          wget http://www.maatkit.org/get/toolname
662          or
663          wget http://www.maatkit.org/trunk/toolname
664
665       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

ENVIRONMENT

671       The environment variable "MKDEBUG" enables verbose debugging output in
672       all of the Maatkit tools:
673
674          MKDEBUG=1 mk-....
675

SYSTEM REQUIREMENTS

677       You need the following Perl modules: DBI and DBD::mysql.
678

BUGS

680       For a list of known bugs see
681       <http://www.maatkit.org/bugs/mk-variable-advisor>.
682
683       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
687       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

COPYRIGHT, LICENSE AND WARRANTY

694       This program is copyright 2009-2011 Percona Inc.  Feedback and
695       improvements are welcome.
696
697       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
701       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
707       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

AUTHOR

712       Baron Schwartz, Daniel Nichter
713

ABOUT MAATKIT

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

VERSION

722       This manual page documents Ver 1.0.2 Distrib 7540 $Revision: 7477 $.
723
724
725
726perl v5.38.0                      2023-07-20            MK-VARIABLE-ADVISOR(1)
Impressum