Debian Codegraph @

gmond/python_modules/db/DBUtil.py

raw
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