1#!/usr/bin/env python3
2
3"""Retrieve and display performance information from the DB_STATS table.
4"""
5
6
7
8from collections import OrderedDict
9from datetime import datetime, date
10from functools import partial
11from operator import itemgetter
12
13from chart import db
14import chart.db.ts
15from chart.common.args import ArgumentParser
16from chart.common.prettyprint import Table
17from chart.common.prettyprint import show_time_m
18
19db_fields = (
20 'sensing_time',
21 'tables_size',
22 'indexes_size',
23 'partition_size',
24 'partition_free',
25 'total_reads-large_reads as small_reads',
26 ('(total_reads-large_reads)-(lag(total_reads) over (order by sensing_time)-'
27 'lag(large_reads) over (order by sensing_time)) as small_reads_d'),
28 'total_writes-large_writes as small_writes',
29 ('(total_writes-large_writes)-(lag(total_writes) over (order by sensing_time)-'
30 'lag(large_writes) over (order by sensing_time)) as small_writes_d'),
31 'large_reads',
32 'large_reads-lag(large_reads) over (order by sensing_time) as large_reads_d',
33 'large_writes',
34 'large_writes-lag(large_writes) over (order by sensing_time) as large_writes_d',
35 'total_reads',
36 'total_reads-lag(total_reads) over (order by sensing_time) as total_reads_d',
37 'total_writes',
38 'total_writes-lag(total_writes) over (order by sensing_time) as total_writes_d',
39 'bytes_read',
40 'bytes_read-lag(bytes_read) over (order by sensing_time) as bytes_read_d',
41 'bytes_written',
42 'bytes_written-lag(bytes_written) over (order by sensing_time) as bytes_written_d',
43 'tables_size-lag(tables_size) over (order by sensing_time) as tables_size_d',
44 'indexes_size-lag(indexes_size) over (order by sensing_time) as indexes_size_d',
45 'total_reads-lag(total_reads) over (order by sensing_time) as total_reads_d',
46 'total_writes-lag(total_writes) over (order by sensing_time) as total_writes_d',
47 )
48
49db_field_names = [x.split()[-1] for x in db_fields]
50
51
52def MB_displayer(rowpos, row): # pylint: disable=C0103
53 """Display bytes as MB.
54 """
55
56 return int(row[rowpos] / 1024 / 1024)
57
58
59def GB_displayer(rowpos, row): # pylint: disable=C0103
60 """Display bytes as GB.
61 """
62
63 if row[rowpos] is None:
64 return None
65 else:
66 return '{0:.3f}'.format(row[rowpos] / 1024 / 1024 / 1024)
67
68
69def KB_GB_displayer(rowpos, row): # pylint: disable=C0103
70 """Display a field which is stored in KB in Oracle in GB.
71 """
72
73 return int(row[rowpos] / 1024 / 1024)
74
75
76def dbsize_displayer(row):
77 """Display the DBSTATS.DBSIZE field nicely.
78 """
79
80 return '{0:.3f}'.format(
81 (row['tables_size'] + row['indexes_size']) / 1024 / 1024 / 1024)
82
83
84def dbsize_d_displayer(row):
85 """Display the DBSIZE delta fields correctly.
86 """
87
88 if row['tables_size_d'] is None or row['indexes_size_d'] is None:
89 return None
90 else:
91 return '{0:.3f}'.format(
92 (row['tables_size_d'] + row['indexes_size_d']) / 1024 / 1024 / 1024)
93
94
95def partition_usage_displayer(row):
96 """Format the partition free/used space field correctly.
97 """
98
99 return '{used:.0f}/{size:.0f}'.format(
100 used=(row['partition_size'] - row['partition_free']) / 1024 / 1024,
101 size=row['partition_size'] / 1024 / 1024)
102
103
104def time_displayer(row):
105 """Adaptor to display a datetime to minutes accuracy.
106 """
107
108 #print row
109 return show_time_m(row['sensing_time'])
110
111#def
112
113# non CHART space usage + delta
114
115display_fields = OrderedDict((
116 ('sensing_time',
117 {'description': 'Timestamp of data reading',
118 'unit': None,
119 'fn': time_displayer}),
120 ('tables_size',
121 {'description': 'Total size of all tables',
122 'unit': 'GB',
123 'fn': partial(GB_displayer, 'tables_size')}),
124 ('indexes_size',
125 {'description': 'Total size of all indexes',
126 'unit': 'GB',
127 'fn': partial(GB_displayer, 'indexes_size')}),
128 ('db_size',
129 {'description': 'Total size of all tables and indexes',
130 'unit': 'GB',
131 'fn': dbsize_displayer}),
132 ('db_size_d',
133 {'description': 'Daily change in total size of tables plus indexes',
134 'unit': 'GB',
135 'fn': dbsize_d_displayer}),
136 ('partition_size',
137 {'description': 'Total size of the /opt/oracle disk partition',
138 'unit': 'GB',
139 'fn': partial(KB_GB_displayer, 'partition_size')}),
140 ('partition_free',
141 {'description': 'Free space on the /opt/oracle partition',
142 'unit': 'GB',
143 'fn': partial(KB_GB_displayer, 'partition_free')}),
144 ('partition_usage',
145 {'description': 'Total partition space used / partition size',
146 'unit': 'GB',
147 'fn': partition_usage_displayer}),
148 ('small_reads',
149 {'description': 'Total number of small reads',
150 'unit': None,
151 'fn': itemgetter('small_reads')}),
152 ('small_reads_d',
153 {'description': 'Daily change in small reads',
154 'unit': None,
155 'fn': itemgetter('small_reads_d')}),
156 ('small_writes',
157 {'description': 'Total number of small writes',
158 'unit': None,
159 'fn': itemgetter('small_writes')}),
160 ('small_writes_d',
161 {'description': 'Daily change in small reads',
162 'unit': None,
163 'fn': itemgetter('small_writes_d')}),
164 ('large_reads',
165 {'description': 'Total number of large reads',
166 'unit': None,
167 'fn': itemgetter('large_reads')}),
168 ('large_reads_d',
169 {'description': 'Daily change in large reads',
170 'unit': None,
171 'fn': itemgetter('large_reads_d')}),
172 ('large_writes',
173 {'description': 'Total number of large writes',
174 'unit': None,
175 'fn': itemgetter('large_writes')}),
176 ('large_writes_d',
177 {'description': 'Total number of large writes',
178 'unit': None,
179 'fn': itemgetter('large_writes')}),
180 ('large_writes_d',
181 {'description': 'Daily change in large writes',
182 'unit': None,
183 'fn': itemgetter('large_writes_d')}),
184 ('total_reads',
185 {'description': 'Total reads since start of measurement',
186 'unit': None,
187 'fn': itemgetter('total_reads')}),
188 ('total_reads_d',
189 {'description': 'Daily change in total reads',
190 'unit': None,
191 'fn': itemgetter('total_reads_d')}),
192 ('total_writes',
193 {'description': 'Total writes since start of measurement',
194 'unit': None,
195 'fn': itemgetter('total_writes')}),
196 ('total_writes_d',
197 {'description': 'Daily writes',
198 'unit': None,
199 'fn': itemgetter('total_writes_d')}),
200 ('gb_read',
201 {'description': 'Total GB read',
202 'unit': None,
203 'fn': partial(GB_displayer, 'bytes_read')}),
204 ('gb_read_d',
205 {'description': 'Daily change in total GB read',
206 'unit': None,
207 'fn': partial(GB_displayer, 'bytes_read_d')}),
208 ('gb_written',
209 {'description': 'Total GB written',
210 'unit': None,
211 'fn': partial(GB_displayer, 'bytes_written')}),
212 ('gb_written_d',
213 {'description': 'Daily change in total GB written',
214 'unit': None,
215 'fn': partial(GB_displayer, 'bytes_written_d')}),
216 ))
217
218
219def display_rows(title, field_names, rows):
220 """Build our final table for display.
221 """
222
223 headings = []
224 for field_name in field_names:
225 if display_fields[field_name]['unit'] is not None:
226 headings.append(field_name + ' (' + display_fields[field_name]['unit'] + ')')
227 else:
228 headings.append(field_name)
229
230 t = Table(title=title,
231 headings=headings)
232
233 for db_row in rows:
234 display_row = []
235 for field_name in field_names:
236 display_row.append(display_fields[field_name]['fn'](db_row))
237
238 t.append(display_row)
239
240 return t
241
242
243def read_rows(start_time, stop_time):
244 """Query DB_STATS table.
245 """
246
247 for row in db.query("SELECT {fields} "
248 "FROM db_stats "
249 "WHERE sensing_time>=:start_time "
250 "AND sensing_time<:stop_time "
251 "ORDER BY sensing_time".format(
252 fields=','.join(db_fields)),
253 start_time=start_time,
254 stop_time=stop_time):
255 yield dict(list(zip(db_field_names, row)))
256
257if __name__ == '__main__':
258 parser = ArgumentParser(__doc__)
259 parser.add_argument('--start',
260 metavar='DATE',
261 type=ArgumentParser.datetime,
262 help='Only show readings starting from DATE',
263 default=date(2009, 1, 1))
264 parser.add_argument('--stop',
265 metavar='DATE',
266 type=ArgumentParser.datetime,
267 help='Only show readings up to DATE',
268 default=datetime.utcnow().date())
269 parser.add_argument('--sizes',
270 action='store_true',
271 help='Show fields related to database size')
272 parser.add_argument('--iops',
273 action='store_true',
274 help='Show fields I/O counts')
275 parser.add_argument('--iopsd',
276 action='store_true',
277 help='Show daily changes in I/O counts')
278 parser.add_argument('--fields', '-f',
279 nargs='*',
280 help='List of fields to display (use --show-fields to see available fields')
281 parser.add_argument('--csv',
282 action='store_true',
283 help='Display output in csv format')
284 parser.add_argument('--show-fields',
285 action='store_true',
286 help='Show list of available fields')
287 args = parser.parse_args()
288
289 rows = read_rows(args.start,
290 args.stop)
291
292 # default display
293 title = 'DB stats from {start} to {stop}'.format(start=args.start,
294 stop=args.stop)
295 headings = ('sensing_time',
296 'db_size',
297 'partition_usage',
298 'total_writes_d',
299 'total_reads_d')
300
301 if args.sizes:
302 title = 'DB sizes from {start} to {stop}'.format(start=args.start,
303 stop=args.stop)
304 headings = ('sensing_time',
305 'tables_size',
306 'indexes_size',
307 'db_size_d',
308 'partition_usage')
309
310 if args.iops:
311 title = 'IOPS from {start} to {stop}'.format(start=args.start,
312 stop=args.stop)
313 headings = ('sensing_time',
314# 'total_reads',
315# 'total_writes',
316 'small_reads',
317 'small_writes',
318 'large_reads',
319 'large_writes',
320 'gb_written',
321 'gb_read',
322 )
323
324 if args.iopsd:
325 title = 'IOPS from {start} to {stop}'.format(start=args.start,
326 stop=args.stop)
327 headings = ('sensing_time',
328 'small_reads_d',
329 'small_writes_d',
330 'large_reads_d',
331 'large_writes_d',
332 'gb_written_d',
333 'gb_read_d',
334 )
335
336 if args.fields is not None and len(args.fields) > 0:
337 headings = args.fields
338
339 table = display_rows(title,
340 headings,
341 rows)
342
343 if args.csv:
344 table.write_csv()
345 else:
346 table.write()