PageRenderTime 46ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 0ms

/openarkkit/doc/html/oak-hook-general-log.html

http://openarkkit.googlecode.com/
HTML | 273 lines | 235 code | 38 blank | 0 comment | 0 complexity | ea203e545303070d9512e0ebe6a343da MD5 | raw file
Possible License(s): BSD-3-Clause 
    

  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    2. 

  2. <html xmlns="http://www.w3.org/1999/xhtml">
    3. 

  3. <head>
    4. 

  4. 	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    5. 

  5. 	<title>oak-hook-general-log: openark kit documentation</title>
    6. 

  6. 	<meta name="description" content="oak-hook-general-log: openark kit" />
    7. 

  7. 	<meta name="keywords" content="oak-hook-general-log: openark kit" />
    8. 

  8. 	<link rel="stylesheet" type="text/css" href="style.css" />
    9. 

  9. </head>
    10. 

  10. 

  11. <body>
    12. 

  12. 	<div id="main">
    13. 

  13. 		<div id="header">
    14. 

  14. 			<h1>openark kit documentation</h1>
    15. 

  15. 			<div class="subtitle">Common utilities for MySQL</div>
    16. 

  16. 		</div>
    17. 

  17. 		<div id="contentwrapper">
    18. 

  18. 			<div id="content">
    19. 

  19. 				<h2><a href="oak-hook-general-log.html">oak-hook-general-log</a></h2>	
    20. 

  20. <h3>NAME</h3>
    21. 

  21. oak-hook-general-log: hook up and filter general log entries based on entry type or execution plan criteria.
    22. 

  22. <h3>SYNOPSIS</h3>
    23. 

  23. Hook up the general log and dump everything:
    24. 

  24. <blockquote>oak-hook-general-log --user=root --socket=/tmp/mysql.sock</blockquote>
    25. 

  25. Only dump connect/disconnect events:
    26. 

  26. <blockquote>oak-hook-general-log --user=root --socket=/tmp/mysql.sock --filter-connection</blockquote>
    27. 

  27. Same as above, use defaults file:
    28. 

  28. <blockquote>oak-hook-general-log --defaults-file=/home/myuser/.my-oak.cnf --filter-connection</blockquote>
    29. 

  29. Only dump queries which make for a full table scan <i>and</i> use the City table:
    30. 

  30. <blockquote>oak-hook-general-log --defaults-file=/home/myuser/.my-oak.cnf --filter-explain-fullscan --filter-explain-table=City</blockquote>
    31. 

  31. Only dump queries which make for a filesort:
    32. 

  32. <blockquote>oak-hook-general-log --defaults-file=/home/myuser/.my-oak.cnf --filter-explain-filesort</blockquote>
    33. 

  33. Only dump queries which make for an implicit temporary table creation:
    34. 

  34. <blockquote>oak-hook-general-log --defaults-file=/home/myuser/.my-oak.cnf --filter-explain-temporary</blockquote>
    35. 

  35. Only dump queries where the execution plan expects at least <b>100,000</b> rows in any of the tables:
    36. 

  36. <blockquote>oak-hook-general-log --defaults-file=/home/myuser/.my-oak.cnf --filter-explain-rows-exceed=100000</blockquote>
    37. 

  37. Only dump queries where the execution plan expects at least <b>1,000,000</b> rows combined:
    38. 

  38. <blockquote>oak-hook-general-log --defaults-file=/home/myuser/.my-oak.cnf --filter-explain-total-rows-exceed=1000000</blockquote>
    39. 

  39. Only dump queries where the execution plan uses given index (`first_name` index on `authors` table):
    40. 

  40. <blockquote>oak-hook-general-log --defaults-file=/home/myuser/.my-oak.cnf --filter-explain-key=actor.first_name</blockquote>
    41. 

  41. Only dump queries where the execution plan contains given text anywhere (e.g. in key name, ref, etc.)
    42. 

  42. This serves as a <i>grep</i> on the execution plan.
    43. 

  43. <blockquote>oak-hook-general-log --defaults-file=/home/myuser/.my-oak.cnf --filter-explain-contains=my_column_name</blockquote>
    44. 

  44. 

  45. <h3>DESCRIPTION</h3>
    46. 

  46. <p><i>oak-hook-general-log</i> hooks up to a MySQL >= 5.1 server, and dumps general log to standard output, allowing for sophisticated filtering.</p>
    47. 

  47. <p>Query filtering relies not only on query text (for which <i>grep</i> or <i>awk</i> are good tools) but rather on query type and query execution plan.
    48. 

  48. It is possible to only dump connect/disconnect queries; queries which make for a table scan; queries which scan more than <b>100,000</b> rows; or queries which use a specific index.
    49. 

  49. </p>
    50. 

  50. <p>The tool activates the server's <i>general log</i>, and instructs it to write to log table (<b>mysql.general_log</b>). 
    51. 

  51. The <b>general_log</b> table is periodically polled for new entries and rotated. Tool's output corresponds to the <b>general_log</b> table schema: </p>
    52. 

  52. <blockquote><pre>mysql-5.1.51> DESC mysql.general_log;
    53. 

  53. +--------------+------------------+------+-----+-------------------+-----------------------------+
    54. 

  54. | Field        | Type             | Null | Key | Default           | Extra                       |
    55. 

  55. +--------------+------------------+------+-----+-------------------+-----------------------------+
    56. 

  56. | event_time   | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    57. 

  57. | user_host    | mediumtext       | NO   |     | NULL              |                             |
    58. 

  58. | thread_id    | int(11)          | NO   |     | NULL              |                             |
    59. 

  59. | server_id    | int(10) unsigned | NO   |     | NULL              |                             |
    60. 

  60. | command_type | varchar(64)      | NO   |     | NULL              |                             |
    61. 

  61. | argument     | mediumtext       | NO   |     | NULL              |                             |
    62. 

  62. +--------------+------------------+------+-----+-------------------+-----------------------------+
    63. 

  63. </pre></blockquote>
    64. 

  64. <p>A sample output may look like this:</p>
    65. 

  65. <blockquote><pre>2010-12-07 11:30:23     root[root] @ localhost []       8       1       Connect root@localhost on
    66. 

  66. 2010-12-07 11:30:23     root[root] @ localhost []       8       1       Query   select 1
    67. 

  67. 2010-12-07 11:30:39     root[root] @ localhost []       8       1       Query   show processlist
    68. 

  68. 2010-12-07 11:30:45     root[root] @ localhost []       8       1       Query   desc mysql.general_log</pre></blockquote>
    69. 

  69. 

  70. <p>Activation of the <i>general_log</i>, and in particular writing to the <b>general_log</b> table makes for considerable overhead. 
    71. 

  71. The tool is configured to automatically terminate after one minute from the time of execution, or as configured by <b>--timeout-minutes</b>.
    72. 

  72. Upon termination the tool restores original <i>general_log</i> settings.</p>
    73. 

  73. 

  74. <p>Interrupting the tool via Ctrl-C makes for a graceful termination, and original settings are restored. Any other means of termination
    75. 

  75. (e.g. via <i>kill -9</i> results with the <i>general_log</i> remaining active. Make sure to disable it!</p>
    76. 

  76. 

  77. <p>The reason the <b>general_log</b> table is used is that the <i>general_log</i> file is in inconsistent format, and lacks some information such as user & host, 
    78. 

  78. which must be cross referenced to previous entries. This makes it impossible to diagnose existing connections when no log entries are present.</p>
    79. 

  79. <p>Even so, the <b>general_log</b> table lacks the current database for the given connection, information which is critical to understanding the context of the query 
    80. 

  80. (this information <i>can</i> be found in the <b>slow_log</b> table). To overcome this limitation, <i>oak-hook-general-log</i> cross-references the general log
    81. 

  81. entries with PROCESSLIST entries. There may still be mismatches, but on most situations this should work well.</p>
    82. 

  82. 

  83. <p>When asked to filter by execution plan criteria, <i>oak-hook-general-log</i> invokes an <b>EXPLAIN</b> query for each <b>SELECT</b> query encountered.
    84. 

  84. This may further slow down your overall performance if execution plans are complicated. However, you should note it does not delay the execution of the query itself.
    85. 

  85. In fact, the execution plan is calculated after the query is invoked.
    86. 

  86. </p>
    87. 

  87. 

  88. <p>
    89. 

  89. It is possible to specify multiple filter criteria. For a query to be logged, it must answer for <i>all</i> specified filters.
    90. 

  90. </p>
    91. 

  91. 

  92. <h3>OPTIONS</h3>
    93. 

  93. --ask-pass
    94. 

  94. <p class="indent">Prompt for password.</p>
    95. 

  95. 

  96. --debug
    97. 

  97. <p class="indent">Print stack trace on error.</p>
    98. 

  98. 

  99. --defaults-file=DEFAULTS_FILE
    100. 

  100. <p class="indent">Read from MySQL configuration file. Overrides --user, --password, --socket, --port.</p>
    101. 

  101. <p class="indent">Configuration needs to be in the following format:</p>
    102. 

  102. 

  103. <p class="indent"><strong>[client]<br/>
    104. 

  104. user=my_user<br/>
    105. 

  105. password=my_pass<br/>
    106. 

  106. socket=/tmp/mysql.sock<br/>
    107. 

  107. port=3306</strong>
    108. 

  108. </p>
    109. 

  109. 

  110. --filter-connection   
    111. 

  111. <p class="indent">Only output connect/disconnect entries</p>
    112. 

  112. 

  113. --filter-explain-contains=FILTER_EXPLAIN_CONTAINS
    114. 

  114. <p class="indent">
    115. 

  115.                         Only output queries whose execution plan contains
    116. 

  116.                         given text
    117. 

  117. </p>
    118. 

  118. 

  119. --filter-explain-filesort
    120. 

  120. <p class="indent">
    121. 

  121.                         Only output queries where execution plan indicates
    122. 

  122.                         filesort
    123. 

  123. </p>
    124. 

  124. 

  125. --filter-explain-fulljoin
    126. 

  126. <p class="indent">
    127. 

  127.                         Only output queries where execution plan indicates
    128. 

  128.                         full join
    129. 

  129. </p>
    130. 

  130. 

  131. --filter-explain-fullscan
    132. 

  132. <p class="indent">
    133. 

  133.                         Only output queries where execution plan indicates
    134. 

  134. </p>
    135. 

  135. 

  136. --filter-explain-indexscan
    137. 

  137. <p class="indent">
    138. 

  138.                         Only output queries where execution plan indicates
    139. 

  139.                         full index scan
    140. 

  140. </p>
    141. 

  141. 

  142. --filter-explain-key=FILTER_EXPLAIN_KEY
    143. 

  143. <p class="indent">
    144. 

  144.                         Only output queries where given key is used (specify
    145. 

  145.                         key_name or table_name.key_name)
    146. 

  146. </p>
    147. 

  147.  
    148. 

  148. --filter-explain-rows-exceed=FILTER_EXPLAIN_ROWS_EXCEED
    149. 

  149. <p class="indent">
    150. 

  150.                         Only output queries where some path in the execution
    151. 

  151.                         plan expects more than given number of rows scanned
    152. 

  152. </p>
    153. 

  153. 

  154. --filter-explain-table=FILTER_EXPLAIN_TABLE
    155. 

  155. <p class="indent">
    156. 

  156.                         Only output queries where given table is used
    157. 

  157. </p>
    158. 

  158. 

  159. --filter-explain-temporary
    160. 

  160. <p class="indent">
    161. 

  161.                         Only output queries where execution plan indicates use
    162. 

  162.                         of temporary tables
    163. 

  163. </p>
    164. 

  164. 

  165. --filter-explain-total-rows-exceed=FILTER_EXPLAIN_TOTAL_ROWS_EXCEED
    166. 

  166. <p class="indent">
    167. 

  167.                         Only output queries where execution plan expects at least as given total
    168. 

  168.                         number of rows scanned
    169. 

  169. </p>
    170. 

  170. 

  171. --filter-query        
    172. 

  172. <p class="indent">
    173. 

  173. 						Only output queries
    174. 

  174. </p>
    175. 

  175. 

  176. --filter-query-contains=FILTER_QUERY_CONTAINS
    177. 

  177. <p class="indent">
    178. 

  178. 	Only consider queries containing given text
    179. 

  179. </p>
    180. 

  180. 

  181. -H HOST, --host=HOST
    182. 

  182. <p class="indent">MySQL host (default: localhost)</p>
    183. 

  183. 

  184. --include-existing    
    185. 

  185. <p class="indent">
    186. 

  186. 						Include possibly pre-existing entries in the general
    187. 

  187.                         log table (default: disabled)
    188. 

  188. </p>
    189. 

  189. 

  190. -p PASSWORD, --password=PASSWORD
    191. 

  191. <p class="indent">MySQL password</p>
    192. 

  192. 

  193. -P PORT, --port=PORT
    194. 

  194. <p class="indent">TCP/IP port (default: 3306)</p>
    195. 

  195. 

  196. -s SLEEP_TIME, --sleep-time=SLEEP_TIME
    197. 

  197. <p class="indent">Number of seconds between log polling (default: 1)</p>
    198. 

  198. 

  199. -S SOCKET, --socket=SOCKET
    200. 

  200. <p class="indent">MySQL socket file. Only applies when host is localhost</p>
    201. 

  201. 

  202. -t TIMEOUT_MINUTES, --timeout-minutes=TIMEOUT_MINUTES
    203. 

  203. <p class="indent">Auto disconnect after given number of minutes (default: 1)</p>
    204. 

  204. 

  205. -u USER, --user=USER
    206. 

  206. <p class="indent">MySQL user</p>
    207. 

  207. 

  208. -v, --verbose
    209. 

  209. <p class="indent">Print user friendly messages</p>
    210. 

  210. 

  211. <h3>ENVIRONMENT</h3>
    212. 

  212. Requires MySQL 5.1 or newer, python 2.3 or newer.
    213. 

  213. 

  214. python-mysqldb must be installed in order to use this tool. You can
    215. 

  215. <blockquote>apt-get install python-mysqldb</blockquote>
    216. 

  216. or
    217. 

  217. <blockquote>yum install mysql-python</blockquote>
    218. 

  218. <p>Please note that in MySQL versions &lt; <b>5.1.46</b> you may run into <a href="http://bugs.mysql.com/bug.php?id=49823">this bug</a>, in which MySQL 
    219. 

  219. allows for invalid <b>general_log</b> CSV tables. You may wish to either upgrade your MySQL version or issue:</p>
    220. 

  220. <blockquote>mysql&gt; ALTER TABLE mysql.general_log ENGINE=MyISAM;</blockquote>
    221. 

  221. 

  222. <h3>SEE ALSO</h3>
    223. 

  223. <h3>LICENSE</h3>
    224. 

  224. This tool is released under the BSD license.
    225. 

  225. <blockquote><pre>Copyright (c) 2008 - 2010, Shlomi Noach
    226. 

  226. All rights reserved.
    227. 

  227. 

  228. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
    229. 

  229. * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
    230. 

  230. * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
    231. 

  231. * Neither the name of the organization nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
    232. 

  232. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 
    233. 

  233. A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 
    234. 

  234. LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR 
    235. 

  235. TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.</pre>
    236. 

  236. </blockquote>
    237. 

  237. <h3>AUTHOR</h3>
    238. 

  238. Shlomi Noach
    239. 

  239. 				<br/>
    240. 

  240. 			</div>
    241. 

  241. 			<div id="sidebarwrapper">
    242. 

  242. 				<div id="menu">
    243. 

  243. 					<h3>openark kit tools</h3>
    244. 

  244. 					<ul>
    245. 

  245. 						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
    246. 

  246. 						<li><a title="Download" href="download.html">Download</a></li>
    247. 

  247. 						<li><a title="Install" href="install.html">Install</a></li>
    248. 

  248. 						<li><a title="oak-apply-ri" href="oak-apply-ri.html">oak-apply-ri</a></li>
    249. 

  249. 						<li><a title="oak-block-account" href="oak-block-account.html">oak-block-account</a></li>
    250. 

  250. 						<li><a title="oak-chunk-update" href="oak-chunk-update.html">oak-chunk-update</a></li>
    251. 

  251. 						<li><a title="oak-get-slave-lag" href="oak-get-slave-lag.html">oak-get-slave-lag</a></li>
    252. 

  252. 						<li><a title="oak-hook-general-log" href="oak-hook-general-log.html">oak-hook-general-log</a></li>
    253. 

  253. 						<li><a title="oak-kill-slow-queries" href="oak-kill-slow-queries.html">oak-kill-slow-queries</a></li>
    254. 

  254. 						<li><a title="oak-modify-charset" href="oak-modify-charset.html">oak-modify-charset</a></li>
    255. 

  255. 						<li><a title="oak-online-alter-table" href="oak-online-alter-table.html">oak-online-alter-table</a></li>
    256. 

  256. 						<li><a title="oak-prepare-shutdown" href="oak-prepare-shutdown.html">oak-prepare-shutdown</a></li>
    257. 

  257. 						<li><a title="oak-purge-master-logs" href="oak-purge-master-logs.html">oak-purge-master-logs</a></li>
    258. 

  258. 						<li><a title="oak-repeat-query" href="oak-repeat-query.html">oak-repeat-query</a></li>
    259. 

  259. 						<li><a title="oak-security-audit" href="oak-security-audit.html">oak-security-audit</a></li>
    260. 

  260. 						<li><a title="oak-show-limits" href="oak-show-limits.html">oak-show-limits</a></li>
    261. 

  261. 						<li><a title="oak-show-replication-status" href="oak-show-replication-status.html">oak-show-replication-status</a></li>
    262. 

  262. 					</ul>
    263. 

  263. 				</div>
    264. 

  264. 			</div>	
    265. 

  265. 			<div class="clear">&nbsp;</div>
    266. 

  266. 			
    267. 

  267. 			<div id="footnote" align="center">
    268. 

  268. 				<a href="">openark kit</a> documentation
    269. 

  269. 			</div>
    270. 

  270. 		</div>
    271. 

  271. 	</div>
    272. 

  272. </body>
    273. 

  273. </html>
    274. 

  274.