1NAME
2 mysql-genocide - Parallel operation on MySQL processlist
3
4SYNOPSIS
5 mysql-genocide [OPTIONS] [FILTER]
6
7 Options:
8 -h, --help Show help.
9
10 Connection:
11 -h, --host=name Connect to host.
12 -P, --port=# Port number to use for connection.
13 -D, --database=name Database to use.
14 -u, --user=name User for login if not current user.
15 -p, --password=name Password to use when connecting to server.
16
17 Filters:
18 -e, --exclude Exclude queries by different criteria
19 -s, --selects-only Exclude everything but selects
20 -t, --min-time=# Exclude queries with exec time lower than #
21 -T, --timeout Exclude queries with exec time lower than query time hint
22 -l, --limit=# Only take first # lines
23 -g, --placeholder Replace values in queries by placeholders
24 -d, --distinct Only keep the first occurence of the same query
25 --sort Sort result
26
27 Actions:
28 -L, --list Output list of queries
29 -K, --kill Kill every matched threads
30 -S, --stats Show some stats about processlist
31
32 -i, --interval[=#] Repeat the command at regular interval
33
34OPTIONS
35 --help Print a brief help message and exits.
36
37 --host, --port, --database, --user, --password
38 See mysql cli help for more information on those parameters.
39
40 --exclude=[!]name
41 Exclude from the running thread list queries matching the
42 argument. This parameter can be repeated several times to
43 exclude different kinds of queries. If you prefix the argument
44 with an exclamation mark (!), all thread not matching this
45 argument will be excluded. Allowed parameters are:
46
47 select, insert, replace, update, delete, create, drop, alter
48
49 Exclude SQL query which command is of the same name.
50
51 write
52
53 Exclude every SQL query doing write operation (insert, replace,
54 update, delete).
55
56 other
57
58 Exclude SQL query which are none of the above type.
59
60 sleep
61
62 Exclude all sleeping threads
63
64 system
65
66 Exclude thread ran by the system (often used for replication
67 threads)
68
69 user=<user>
70
71 Exclude threads ran by given mysql username.
72
73 db=<db>
74
75 Exclude threads using given database.
76
77 state=<state>
78
79 Exclude threads in given state.
80
81 See
82 <http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.ht
83 ml> for the list of possible states
84
85 command=<command>
86
87 Exclude threads using given command.
88
89 See
90 <http://dev.mysql.com/doc/refman/5.0/en/thread-commands.html>
91 for the list of possible commands.
92
93 --selects-only
94 This is equivalent to: --exclude=write --exclude=other
95 --exclude=sleep --exclude=system.
96
97 --min-time=#
98 Exclude queries with execution time lower than given parameter.
99
100 --timeout
101 Keep only queries with a timeout provided and with an execution
102 time which exceeded this timeout. The timeout can be provided
103 with the query in a comment like this:
104
105 SELECT /* timeout:40 */ * FROM table;
106
107 Timeout is expressed in second.
108
109 --limit=#
110 Take first # threads and excludes others.
111
112 --placeholder
113 Will try to make queries more generic by replacing all values by
114 either "i" for numbers or "s" for strings.
115
116 --distinct
117 Keep only the first query from group of identical queries and
118 exlude others.
119
120 Note: If you use this option with --placeholder, queries that
121 would differ only by values used will become identical. This is
122 useful to distinct big type of queries.
123
124 --sort[=name]
125 Sort matched queries by parameter given as argument.
126
127 Allowed parameters are as follow:
128
129 concurrency or c
130
131 Sort queries by most repeated ones.
132
133 time or t
134
135 Sort queries by longer execution time.
136
137 --list[=template]
138 Output the result to the terminal.
139
140 The default output format can be changed by supplying a template
141 with desired field.
142
143 Available fields are: Id, User, Command, State, Db, Host, Time,
144 Info, Group, Concurrency, QType, Timeout
145
146 Default template is: <Id> <User> <Db> <Time> <Info>
147
148 --kill Kill every threads that match the given filters.
149
150 --stats Show statistics about queries matched by filters.
151
152 --interval[=#]
153 Repeat the command at regular interval. Interval in second can
154 be specified as argument. If not specified, default interval is
155 5 seconds.
156
157DESCRIPTION
158 mysql-genocide helps you play with big MySQL processlists. It can filter
159 it using different criterias like execution time, query type, user or
160 regexp matching of the SQL query etc. Actions can then be peformed on
161 the result like killing, sorting or generating statistics.
162
163EXAMPLES
164 Kill all selects with execution time geater than 60 seconds:
165
166 mysql-genocide --selects-only --min-time 60 --kill
167
168 Same as before but limited on queries matching a pattern:
169
170 mysql-genocide -s -t 60 -K 'FROM user '
171
172 Kill all queries timed out:
173
174 mysql-genocide --timeout --kill
175
176 Replace queries values by placeholders and group identical queries
177 together, sort them by most concurrent and keep only the 10 most
178 concurent ones:
179
180 mysql-genocide --placeholder --group --sort concurrency --limit 10 --list
181
182SCRIPT CATEGORIES
183 Unix/System_administration
184
185PREREQUISITES
186 This script requires the "DBD::mysql", "Getopt::Long" and "Pod::Usage"
187 modules.
188
189OSNAMES
190 any
191
192AUTHOR
193 Olivier Poitrey <rs@dailymotion.com>
194
195