| 1 |
""" |
| 2 |
The MIT License |
| 3 |
|
| 4 |
Copyright (c) 2008 Gilad Raphaelli <[email protected]> |
| 5 |
|
| 6 |
Permission is hereby granted, free of charge, to any person obtaining a copy |
| 7 |
of this software and associated documentation files (the "Software"), to deal |
| 8 |
in the Software without restriction, including without limitation the rights |
| 9 |
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell |
| 10 |
copies of the Software, and to permit persons to whom the Software is |
| 11 |
furnished to do so, subject to the following conditions: |
| 12 |
|
| 13 |
The above copyright notice and this permission notice shall be included in |
| 14 |
all copies or substantial portions of the Software. |
| 15 |
|
| 16 |
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR |
| 17 |
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, |
| 18 |
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE |
| 19 |
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER |
| 20 |
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, |
| 21 |
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN |
| 22 |
THE SOFTWARE. |
| 23 |
|
| 24 |
if using < python2.5, http://code.activestate.com/recipes/523034/ works as a |
| 25 |
pure python collections.defaultdict substitute |
| 26 |
""" |
| 27 |
|
| 28 |
#from collections import defaultdict |
| 29 |
try: |
| 30 |
from collections import defaultdict |
| 31 |
except: |
| 32 |
class defaultdict(dict): |
| 33 |
def __init__(self, default_factory=None, *a, **kw): |
| 34 |
if (default_factory is not None and |
| 35 |
not hasattr(default_factory, '__call__')): |
| 36 |
raise TypeError('first argument must be callable') |
| 37 |
dict.__init__(self, *a, **kw) |
| 38 |
self.default_factory = default_factory |
| 39 |
def __getitem__(self, key): |
| 40 |
try: |
| 41 |
return dict.__getitem__(self, key) |
| 42 |
except KeyError: |
| 43 |
return self.__missing__(key) |
| 44 |
def __missing__(self, key): |
| 45 |
if self.default_factory is None: |
| 46 |
raise KeyError(key) |
| 47 |
self[key] = value = self.default_factory() |
| 48 |
return value |
| 49 |
def __reduce__(self): |
| 50 |
if self.default_factory is None: |
| 51 |
args = tuple() |
| 52 |
else: |
| 53 |
args = self.default_factory, |
| 54 |
return type(self), args, None, None, self.items() |
| 55 |
def copy(self): |
| 56 |
return self.__copy__() |
| 57 |
def __copy__(self): |
| 58 |
return type(self)(self.default_factory, self) |
| 59 |
def __deepcopy__(self, memo): |
| 60 |
import copy |
| 61 |
return type(self)(self.default_factory, |
| 62 |
copy.deepcopy(self.items())) |
| 63 |
def __repr__(self): |
| 64 |
return 'defaultdict(%s, %s)' % (self.default_factory, |
| 65 |
dict.__repr__(self)) |
| 66 |
|
| 67 |
import MySQLdb |
| 68 |
|
| 69 |
def is_hex(s): |
| 70 |
try: |
| 71 |
int(s, 16) |
| 72 |
return True |
| 73 |
except ValueError: |
| 74 |
return False |
| 75 |
|
| 76 |
|
| 77 |
def longish(x): |
| 78 |
if len(x): |
| 79 |
try: |
| 80 |
return long(x) |
| 81 |
except ValueError: |
| 82 |
if(x.endswith(',')): |
| 83 |
return longish(x[:-1]) |
| 84 |
if(is_hex(x.lower()) == True): |
| 85 |
return hexlongish(x) |
| 86 |
#print "X==(%s)(%s)(%s)" %(x, x[:-1],hexlongish(x)), sys.exc_info()[0] |
| 87 |
return longish(x[:-1]) |
| 88 |
else: |
| 89 |
raise ValueError |
| 90 |
|
| 91 |
|
| 92 |
def hexlongish(x): |
| 93 |
if len(x): |
| 94 |
try: |
| 95 |
return long(str(x), 16) |
| 96 |
except ValueError: |
| 97 |
return longish(x[:-1]) |
| 98 |
else: |
| 99 |
raise ValueError |
| 100 |
|
| 101 |
def parse_innodb_status(innodb_status_raw, innodb_version="1.0"): |
| 102 |
def sumof(status): |
| 103 |
def new(*idxs): |
| 104 |
return sum(map(lambda x: longish(status[x]), idxs)) |
| 105 |
return new |
| 106 |
|
| 107 |
innodb_status = defaultdict(int) |
| 108 |
innodb_status['active_transactions'] |
| 109 |
individual_buffer_pool_info = False |
| 110 |
|
| 111 |
for line in innodb_status_raw: |
| 112 |
istatus = line.split() |
| 113 |
|
| 114 |
isum = sumof(istatus) |
| 115 |
|
| 116 |
# SEMAPHORES |
| 117 |
if "Mutex spin waits" in line: |
| 118 |
innodb_status['spin_waits'] += longish(istatus[3]) |
| 119 |
innodb_status['spin_rounds'] += longish(istatus[5]) |
| 120 |
innodb_status['os_waits'] += longish(istatus[8]) |
| 121 |
|
| 122 |
elif "RW-shared spins" in line: |
| 123 |
if innodb_version == 1.0: |
| 124 |
innodb_status['spin_waits'] += isum(2, 8) |
| 125 |
innodb_status['os_waits'] += isum(5, 11) |
| 126 |
elif innodb_version >= 5.5: |
| 127 |
innodb_status['spin_waits'] += longish(istatus[2]) |
| 128 |
innodb_status['os_waits'] += longish(istatus[7]) |
| 129 |
|
| 130 |
elif "RW-excl spins" in line and innodb_version >= 5.5: |
| 131 |
innodb_status['spin_waits'] += longish(istatus[2]) |
| 132 |
innodb_status['os_waits'] += longish(istatus[7]) |
| 133 |
|
| 134 |
# TRANSACTIONS |
| 135 |
elif "Trx id counter" in line: |
| 136 |
if innodb_version >= 5.6: |
| 137 |
innodb_status['transactions'] += longish(istatus[3]) |
| 138 |
elif innodb_version == 5.5: |
| 139 |
innodb_status['transactions'] += hexlongish(istatus[3]) |
| 140 |
else: |
| 141 |
innodb_status['transactions'] += isum(3, 4) |
| 142 |
|
| 143 |
elif "Purge done for trx" in line: |
| 144 |
if innodb_version >= 5.6: |
| 145 |
innodb_status['transactions_purged'] += longish(istatus[6]) |
| 146 |
elif innodb_version == 5.5: |
| 147 |
innodb_status['transactions_purged'] += hexlongish(istatus[6]) |
| 148 |
else: |
| 149 |
innodb_status['transactions_purged'] += isum(6, 7) |
| 150 |
|
| 151 |
elif "History list length" in line: |
| 152 |
innodb_status['history_list'] = longish(istatus[3]) |
| 153 |
|
| 154 |
elif "---TRANSACTION" in line and innodb_status['transactions']: |
| 155 |
innodb_status['current_transactions'] += 1 |
| 156 |
if "ACTIVE" in line: |
| 157 |
innodb_status['active_transactions'] += 1 |
| 158 |
|
| 159 |
elif "LOCK WAIT" in line and innodb_status['transactions']: |
| 160 |
innodb_status['locked_transactions'] += 1 |
| 161 |
|
| 162 |
elif 'read views open inside' in line: |
| 163 |
innodb_status['read_views'] = longish(istatus[0]) |
| 164 |
|
| 165 |
# FILE I/O |
| 166 |
elif 'OS file reads' in line: |
| 167 |
innodb_status['data_reads'] = longish(istatus[0]) |
| 168 |
innodb_status['data_writes'] = longish(istatus[4]) |
| 169 |
innodb_status['data_fsyncs'] = longish(istatus[8]) |
| 170 |
|
| 171 |
elif 'Pending normal aio' in line: |
| 172 |
innodb_status['pending_normal_aio_reads'] = longish(istatus[4]) |
| 173 |
innodb_status['pending_normal_aio_writes'] = longish(istatus[7]) |
| 174 |
|
| 175 |
elif 'ibuf aio reads' in line: |
| 176 |
innodb_status['pending_ibuf_aio_reads'] = longish(istatus[3]) |
| 177 |
innodb_status['pending_aio_log_ios'] = longish(istatus[6]) |
| 178 |
innodb_status['pending_aio_sync_ios'] = longish(istatus[9]) |
| 179 |
|
| 180 |
elif 'Pending flushes (fsync)' in line: |
| 181 |
innodb_status['pending_log_flushes'] = longish(istatus[4]) |
| 182 |
innodb_status['pending_buffer_pool_flushes'] = longish(istatus[7]) |
| 183 |
|
| 184 |
# INSERT BUFFER AND ADAPTIVE HASH INDEX |
| 185 |
elif 'merged recs' in line and innodb_version == 1.0: |
| 186 |
innodb_status['ibuf_inserts'] = longish(istatus[0]) |
| 187 |
innodb_status['ibuf_merged'] = longish(istatus[2]) |
| 188 |
innodb_status['ibuf_merges'] = longish(istatus[5]) |
| 189 |
|
| 190 |
elif 'Ibuf: size' in line and innodb_version >= 5.5: |
| 191 |
innodb_status['ibuf_merges'] = longish(istatus[10]) |
| 192 |
|
| 193 |
elif 'merged operations' in line and innodb_version >= 5.5: |
| 194 |
in_merged = 1 |
| 195 |
|
| 196 |
elif 'delete mark' in line and 'in_merged' in vars() and innodb_version >= 5.5: |
| 197 |
innodb_status['ibuf_inserts'] = longish(istatus[1]) |
| 198 |
innodb_status['ibuf_merged'] = 0 |
| 199 |
del in_merged |
| 200 |
|
| 201 |
# LOG |
| 202 |
elif "log i/o's done" in line: |
| 203 |
innodb_status['log_writes'] = longish(istatus[0]) |
| 204 |
|
| 205 |
elif "pending log writes" in line: |
| 206 |
innodb_status['pending_log_writes'] = longish(istatus[0]) |
| 207 |
innodb_status['pending_chkp_writes'] = longish(istatus[4]) |
| 208 |
|
| 209 |
elif "Log sequence number" in line: |
| 210 |
if innodb_version >= 5.5: |
| 211 |
innodb_status['log_bytes_written'] = longish(istatus[3]) |
| 212 |
else: |
| 213 |
innodb_status['log_bytes_written'] = isum(3, 4) |
| 214 |
|
| 215 |
elif "Log flushed up to" in line: |
| 216 |
if innodb_version >= 5.5: |
| 217 |
innodb_status['log_bytes_flushed'] = longish(istatus[4]) |
| 218 |
else: |
| 219 |
innodb_status['log_bytes_flushed'] = isum(4, 5) |
| 220 |
|
| 221 |
# BUFFER POOL AND MEMORY |
| 222 |
elif "INDIVIDUAL BUFFER POOL INFO" in line: |
| 223 |
# individual pools section. We only want to record the totals |
| 224 |
# rather than each individual pool clobbering the totals |
| 225 |
individual_buffer_pool_info = True |
| 226 |
|
| 227 |
elif "Buffer pool size, bytes" in line and not individual_buffer_pool_info: |
| 228 |
innodb_status['buffer_pool_pages_bytes'] = longish(istatus[4]) |
| 229 |
|
| 230 |
elif "Buffer pool size" in line and not individual_buffer_pool_info: |
| 231 |
innodb_status['buffer_pool_pages_total'] = longish(istatus[3]) |
| 232 |
|
| 233 |
elif "Free buffers" in line and not individual_buffer_pool_info: |
| 234 |
innodb_status['buffer_pool_pages_free'] = longish(istatus[2]) |
| 235 |
|
| 236 |
elif "Database pages" in line and not individual_buffer_pool_info: |
| 237 |
innodb_status['buffer_pool_pages_data'] = longish(istatus[2]) |
| 238 |
|
| 239 |
elif "Modified db pages" in line and not individual_buffer_pool_info: |
| 240 |
innodb_status['buffer_pool_pages_dirty'] = longish(istatus[3]) |
| 241 |
|
| 242 |
elif "Pages read" in line and "ahead" not in line and not individual_buffer_pool_info: |
| 243 |
innodb_status['pages_read'] = longish(istatus[2]) |
| 244 |
innodb_status['pages_created'] = longish(istatus[4]) |
| 245 |
innodb_status['pages_written'] = longish(istatus[6]) |
| 246 |
|
| 247 |
# ROW OPERATIONS |
| 248 |
elif 'Number of rows inserted' in line: |
| 249 |
innodb_status['rows_inserted'] = longish(istatus[4]) |
| 250 |
innodb_status['rows_updated'] = longish(istatus[6]) |
| 251 |
innodb_status['rows_deleted'] = longish(istatus[8]) |
| 252 |
innodb_status['rows_read'] = longish(istatus[10]) |
| 253 |
|
| 254 |
elif "queries inside InnoDB" in line: |
| 255 |
innodb_status['queries_inside'] = longish(istatus[0]) |
| 256 |
innodb_status['queries_queued'] = longish(istatus[4]) |
| 257 |
|
| 258 |
# Some more stats |
| 259 |
innodb_status['transactions_unpurged'] = innodb_status['transactions'] - innodb_status['transactions_purged'] |
| 260 |
innodb_status['log_bytes_unflushed'] = innodb_status['log_bytes_written'] - innodb_status['log_bytes_flushed'] |
| 261 |
|
| 262 |
return innodb_status |
| 263 |
|
| 264 |
if __name__ == '__main__': |
| 265 |
from optparse import OptionParser |
| 266 |
|
| 267 |
parser = OptionParser() |
| 268 |
parser.add_option("-H", "--Host", dest="host", help="Host running mysql", default="localhost") |
| 269 |
parser.add_option("-u", "--user", dest="user", help="user to connect as", default="") |
| 270 |
parser.add_option("-p", "--password", dest="passwd", help="password", default="") |
| 271 |
parser.add_option("-S", "--socket", dest="unix_socket", help="unix_socket", default="") |
| 272 |
(options, args) = parser.parse_args() |
| 273 |
|
| 274 |
try: |
| 275 |
conn = MySQLdb.connect(user=options.user, host=options.host, passwd=options.passwd, unix_socket=options.socket) |
| 276 |
|
| 277 |
cursor = conn.cursor(MySQLdb.cursors.Cursor) |
| 278 |
cursor.execute("SHOW /*!50000 ENGINE*/ INNODB STATUS") |
| 279 |
innodb_status = parse_innodb_status(cursor.fetchone()[0].split('\n')) |
| 280 |
cursor.close() |
| 281 |
|
| 282 |
conn.close() |
| 283 |
except MySQLdb.OperationalError, (errno, errmsg): |
| 284 |
raise |