1MK-LOADAVG(1) User Contributed Perl Documentation MK-LOADAVG(1)
2
6 mk-loadavg - Watch MySQL load and take action when it gets too high.
7
9 Usage: mk-loadavg [OPTION...] [DSN]
10 mk-loadavg watches the load on a MySQL server and takes action if it is
12 too high.
13 Execute my_script.sh when Threads_running exceeds 10:
15 mk-loadavg --watch "Status:status:Threads_running:>:10" \
17 --execute-command my_script.sh
18
20 The following section is included to inform users about the potential
21 risks, whether known or unknown, of using this tool. The two main
22 categories of risks are those created by the nature of the tool (e.g.
23 read-only tools vs. read-write tools) and those created by bugs.
24 mk-loadavg merely reads and prints information by default, and is very
26 low-risk. The "--execute-command" option can execute user-specified
27 commands.
28 At the time of this release, we know of no bugs that could cause
30 serious harm to users.
31 The authoritative source for updated information is always the online
33 issue tracking system. Issues that affect this tool will be marked as
34 such. You can see a list of such issues at the following URL:
35 <http://www.maatkit.org/bugs/mk-loadavg>.
36 See also "BUGS" for more information on filing bugs and getting help.
38
40 mk-loadavg watches a MySQL server and takes action when a defined
41 threshold is exceeded. One or more items can be watched including
42 MySQL status values from SHOW STATUS, SHOW INNODB STATUS and SHOW SLAVE
43 STATUS, the three system load averages from "uptime", and values from
44 "vmstat". Watched items and their threshold values are specified by
45 "--watch". Every item is checked at intervals (see "--interval"). By
46 default, if any one item's check returns true (i.e. its threshold is
47 exceeded), then "--execute-command" is executed. Specifying "--and"
48 requires that every item has exceeded its threshold before
49 "--execute-command" is executed.
50
52 If you specify "--verbose", mk-loadavg prints information to STDOUT
53 about each check for each watched item. Else, it prints nothing and
54 "--execute-command" (if specified) is responsible for logging any
55 information you want.
56
58 An exit status of 0 (sometimes also called a return value or return
59 code) indicates success. Any other value represents the exit status of
60 the Perl process itself, or of the last forked process that exited if
61 there were multiple servers to monitor.
62
64 This tool accepts additional command-line arguments. Refer to the
65 "SYNOPSIS" and usage information for details.
66 --and
68 group: Action
69 Trigger the actions only when all "--watch" items exceed their
71 thresholds.
72 The default is to trigger the actions when any one of the watched
74 items exceeds its threshold. This option requires that all watched
75 items exceed their thresholds before any action is triggered.
76 --ask-pass
78 Prompt for a password when connecting to MySQL.
79 --charset
81 short form: -A; type: string
82 Default character set. If the value is utf8, sets Perl's binmode
84 on STDOUT to utf8, passes the mysql_enable_utf8 option to
85 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
86 other value sets binmode on STDOUT without the utf8 layer, and runs
87 SET NAMES after connecting to MySQL.
88 --config
90 type: Array
91 Read this comma-separated list of config files; if specified, this
93 must be the first option on the command line.
94 --daemonize
96 Fork to the background and detach from the shell. POSIX operating
97 systems only.
98 --database
100 short form: -D; type: string
101 Database to use.
103 --defaults-file
105 short form: -F; type: string
106 Only read mysql options from the given file. You must give an
108 absolute pathname.
109 --execute-command
111 type: string; group: Action
112 Execute this command when watched items exceed their threshold
114 values
115 This command will be executed every time a "--watch" item (or all
117 items if "--and" is specified) exceeds its threshold. For example,
118 if you specify "--watch "Server:vmstat:swpd:":0">, then this
119 command will be executed when the server begins to swap and it will
120 be executed again at each "--interval" so long as the server is
121 still swapping.
122 After the command is executed, mk-loadavg has no control over it,
124 so it is responsible for its own info gathering, logging, interval,
125 etc. Since the command is spawned from mk-loadavg, its STDOUT,
126 STDERR and STDIN are closed so it doesn't interfere with mk-
127 loadavg. Therefore, the command must redirect its output to files
128 or some other destination. For example, if you specify
129 "--execute-command 'echo Hello'", you will not see "Hello" printed
130 anywhere (neither to screen nor "--log") because STDOUT is closed
131 for the command.
132 No information from mk-loadavg is passed to the command.
134 See also "--and".
136 --help
138 Show help and exit.
139 --host
141 short form: -h; type: string
142 Connect to host.
144 --interval
146 type: time; default: 60s; group: Watch
147 How long to sleep between each check.
149 --log
151 type: string
152 Print all output to this file when daemonized.
154 Output from "--execute-command" is not printed to this file.
156 --password
158 short form: -p; type: string
159 Password to use when connecting.
161 --pid
163 type: string
164 Create the given PID file when daemonized. The file contains the
166 process ID of the daemonized instance. The PID file is removed
167 when the daemonized instance exits. The program checks for the
168 existence of the PID file when starting; if it exists and the
169 process with the matching PID exists, the program exits.
170 --port
172 short form: -P; type: int
173 Port number to use for connection.
175 --run-time
177 type: time
178 Time to run before exiting.
180 Causes "mk-loadavg" to stop after the specified time has elapsed.
182 Optional suffix: s=seconds, m=minutes, h=hours, d=days; if no
183 suffix, s is used.
184 --sentinel
186 type: string; default: /tmp/mk-loadavg-sentinel
187 Exit if this file exists.
189 --set-vars
191 type: string; default: wait_timeout=10000
192 Set these MySQL variables. Immediately after connecting to MySQL,
194 this string will be appended to SET and executed.
195 --socket
197 short form: -S; type: string
198 Socket file to use for connection.
200 --stop
202 Stop running instances by creating the "--sentinel" file.
203 --user
205 short form: -u; type: string
206 User for login if not current user.
208 --verbose
210 short form: -v
211 Print information to STDOUT about what is being done.
213 This can be used as a heartbeat to see that mk-loadavg is still
215 properly watching all its values. If "--log" is specified, this
216 information will be printed to that file instead.
217 --version
219 Show version and exit.
220 --vmstat
222 type: string; default: vmstat 1 2; group: Watch
223 vmstat command for "--watch" Server:vmstat:...
225 The vmstat output should look like:
227 procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
229 r b swpd free buff cache si so bi bo in cs us sy id wa
230 0 0 0 590380 143756 571852 0 0 6 9 228 340 4 1 94 1
231 0 0 0 590400 143764 571852 0 0 0 28 751 818 4 2 90 3
232 The second line from the top needs to be column headers for
234 subsequent lines. Values are taken from the last line.
235 The default, "vmstat 1 2", gets current values. Running just
237 "vmstat" would get average values since last reboot.
238 --wait
240 short form: -w; type: time; default: 60s
241 Wait this long to reconnect to MySQL.
243 If the MySQL server goes away between "--interval" checks, mk-
245 loadavg will attempt to reconnect to MySQL forever, sleeping this
246 amount of time in between attempts.
247 --watch
249 type: string; group: Watch
250 A comma-separated list of watched items and their thresholds
252 (required).
253 Each watched item is string of arguments separated by colons (like
255 arg:arg). Each argument defines the watch item: what particular
256 value is watched and how to compare the current value to a
257 threshold value (N). Multiple watched items can be given by
258 separating them with a comma, and the same watched item can be
259 given multiple times (but, of course, it only makes sense to do
260 this if the comparison and/or threshold values are different).
261 The first argument is the most important and is case-sensitive. It
263 defines the module responsible for watching the value. For
264 example,
265 --watch Status:...
267 causes the WatchStatus module to be loaded. The second and
269 subsequent arguments are passed to the WatchStatus module which
270 parses them. Each watch module requires different arguments. The
271 watch modules included in mk-loadavg and what arguments they
272 require are listed below.
273 This is a common error when specifying "--watch" on the command
275 line:
276 mk-loadavg --watch Server:vmstat:swpd:>:0
278 Failed to load --watch WatchServer: Error parsing parameters vmstat:swpd:: No comparison parameter; expected >, < or = at ./mk-loadavg line 3100.
280 The "--watch" values need to be quoted:
282 mk-loadavg --watch "Server:vmstat:swpd:>:0"
284 Status
286 Watch SHOW STATUS, SHOW INNODB STATUS, and SHOW SLAVE STATUS
287 values. The value argument is case-sensitive.
288 --watch Status:[status|innodb|slave]:value:[><=]:N
290 Examples:
292 --watch "Status:status:Threads_connected:>:16"
294 --watch "Status:innodb:Innodb_buffer_pool_hit_rate:<:0.98"
295 --watch "Status:slave:Seconds_behind_master:>:300"
296 You can easily see what values are available for SHOW STATUS
298 and SHOW SLAVE STATUS, but the values for SHOW INNODB STATUS
299 are not apparent. Some common values are:
300 Innodb_buffer_pool_hit_rate
302 Innodb_buffer_pool_pages_created_sec
303 Innodb_buffer_pool_pages_dirty
304 Innodb_buffer_pool_pages_read_sec
305 Innodb_buffer_pool_pages_written_sec
306 Innodb_buffer_pool_pending_data_writes
307 Innodb_buffer_pool_pending_dirty_writes
308 Innodb_buffer_pool_pending_fsyncs
309 Innodb_buffer_pool_pending_reads
310 Innodb_buffer_pool_pending_single_writes
311 Innodb_common_memory_allocated
312 Innodb_data_fsyncs_sec
313 Innodb_data_pending_fsyncs
314 Innodb_data_pending_preads
315 Innodb_data_pending_pwrites
316 Innodb_data_reads_sec
317 Innodb_data_writes_sec
318 Innodb_insert_buffer_pending_reads
319 Innodb_rows_read_sec
320 Innodb_rows_updated_sec
321 lock_wait_time
322 mysql_tables_locked
323 mysql_tables_used
324 row_locks
325 io_avg_wait
326 io_wait
327 max_io_wait
328 Several of those values can appear multiple times in the SHOW
330 INNODB STATUS output. The value used for comparison is always
331 the highest value. So the value for io_wait is the highest
332 io_wait value for all the IO threads.
333 Processlist
335 Watch aggregated SHOW PROCESSLIST values.
336 --watch Processlist:[db|user|host|state|command]:value:[count|time]:[><=]:N
338 Examples:
340 --watch "Processlist:state:Locked:count:>:5"
342 --watch "Processlist:command:Query:time:<:1"
343 Server
345 Watch server values.
346 --watch Server:loadavg:[1|5|15]:[><=]:N
348 --watch Server:vmstat:[r|b|swpd|free|buff|cache|si|so|bi|bo|in|cs|us|sy|id|wa]:[><=]:N
349 Examples:
351 --watch "Server:loadavg:5:>:4.00"
353 --watch "Server:vmstat:swpd:>:0"
354 --watch "Server:vmstat:free:=:0"
355 See "--vmstat".
357
359 These DSN options are used to create a DSN. Each option is given like
360 "option=value". The options are case-sensitive, so P and p are not the
361 same option. There cannot be whitespace before or after the "=" and if
362 the value contains whitespace it must be quoted. DSN options are
363 comma-separated. See the maatkit manpage for full details.
364 • A
366 dsn: charset; copy: yes
368 Default character set.
370 • D
372 dsn: database; copy: yes
374 Default database.
376 • F
378 dsn: mysql_read_default_file; copy: yes
380 Only read default options from the given file
382 • h
384 dsn: host; copy: yes
386 Connect to host.
388 • p
390 dsn: password; copy: yes
392 Password to use when connecting.
394 • P
396 dsn: port; copy: yes
398 Port number to use for connection.
400 • S
402 dsn: mysql_socket; copy: yes
404 Socket file to use for connection.
406 • u
408 dsn: user; copy: yes
410 User for login if not current user.
412
414 You can download Maatkit from Google Code at
415 <http://code.google.com/p/maatkit/>, or you can get any of the tools
416 easily with a command like the following:
417 wget http://www.maatkit.org/get/toolname
419 or
420 wget http://www.maatkit.org/trunk/toolname
421 Where "toolname" can be replaced with the name (or fragment of a name)
423 of any of the Maatkit tools. Once downloaded, they're ready to run; no
424 installation is needed. The first URL gets the latest released version
425 of the tool, and the second gets the latest trunk code from Subversion.
426
428 The environment variable "MKDEBUG" enables verbose debugging output in
429 all of the Maatkit tools:
430 MKDEBUG=1 mk-....
432
434 You need Perl, DBI, DBD::mysql, and some core packages that ought to be
435 installed in any reasonably new version of Perl.
436
438 For a list of known bugs see <http://www.maatkit.org/bugs/mk-loadavg>.
439 Please use Google Code Issues and Groups to report bugs or request
441 support: <http://code.google.com/p/maatkit/>. You can also join
442 #maatkit on Freenode to discuss Maatkit.
443 Please include the complete command-line used to reproduce the problem
445 you are seeing, the version of all MySQL servers involved, the complete
446 output of the tool when run with "--version", and if possible,
447 debugging output produced by running with the "MKDEBUG=1" environment
448 variable.
449
451 This program is copyright 2008-2011 Baron Schwartz. Feedback and
452 improvements are welcome.
453 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
455 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
456 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
457 This program is free software; you can redistribute it and/or modify it
459 under the terms of the GNU General Public License as published by the
460 Free Software Foundation, version 2; OR the Perl Artistic License. On
461 UNIX and similar systems, you can issue `man perlgpl' or `man
462 perlartistic' to read these licenses.
463 You should have received a copy of the GNU General Public License along
465 with this program; if not, write to the Free Software Foundation, Inc.,
466 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
467
469 Baron Schwartz, Daniel Nichter
470
472 This tool is part of Maatkit, a toolkit for power users of MySQL.
473 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
474 primary code contributors. Both are employed by Percona. Financial
475 support for Maatkit development is primarily provided by Percona and
476 its clients.
477
479 This manual page documents Ver 0.9.7 Distrib 7540 $Revision: 7460 $.
480perl v5.38.0 2023-07-20 MK-LOADAVG(1)