Show the largest MySQL tables on a box

This post shows some handy things you can do with the information_schema database created by default in instances of MySQL 5. I especially liked the query to show the largest five tables in a given instance. I wrapped that query in a Python script, and after adding a bash alias to where that code lives, I can now do:

Most of the length is due to the addition of a function to handle pretty printing. Download the file or view the script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

#!/usr/bin/env python
#-----------------------------------------------------------------------------
# Name: mysql-table-size
# Purpose:
# This script is designed to print out the top five largest MySQL tables on
# the box.
#
# Author: Samuel Huckins
#
# Started: 11/14/2008
# Copyright: (c) 2008 Samuel Huckins
#-----------------------------------------------------------------------------
#
# For grabbing arguments passed:
import sys
from optparse import OptionParser
# For connecting to MySQL
import MySQLdb
# For pretty printing
import operator
import cStringIO
import math
#
def indent(rows, hasHeader=True, headerChar='-', delim=' | ', justify='left',
separateRows=False, prefix='|', postfix='|', wrapfunc=lambda x:x):
"""
Utility function to print pretty tables.
"""
# closure for breaking logical rows to physical, using wrapfunc
def rowWrapper(row, width=40):
newRows = [wrapfunc(item).split('\n') for item in row]
return [[substr or '' for substr in item] for item in \
map(None,*newRows)]
# break each logical row into one or more physical ones
logicalRows = [rowWrapper(row) for row in rows]
# columns of physical rows
columns = map(None,*reduce(operator.add,logicalRows))
# get the maximum of each column by the string length of its items
maxWidths = [max([len(str(item)) for item in column]) for column in \
columns]
rowSeparator = headerChar * (len(prefix) + len(postfix) + sum(maxWidths) \
+ len(delim)*(len(maxWidths)-1))
# select the appropriate justify method
justify = {'center':str.center, 'right':str.rjust, \
'left':str.ljust}[justify.lower()]
output=cStringIO.StringIO()
if separateRows: print >> output, rowSeparator
for physicalRows in logicalRows:
for row in physicalRows:
print >> output, \
prefix \
+ delim.join([justify(str(item),width) for (item,width) in \
zip(row,maxWidths)]) \
+ postfix
if separateRows or hasHeader: print >> output, rowSeparator; \
hasHeader=False
return output.getvalue()

def runMySQL(user, passwd):
"""
"""
db = MySQLdb.connect("localhost", user, passwd, "information_schema")
cursor = db.cursor()
sql = "SELECT concat(table_schema,'.',table_name) table_name, concat(round(data_length/(1024*1024),2),'M') data_length FROM TABLES ORDER BY data_length DESC LIMIT 5;"
cursor.execute(sql)
data = cursor.fetchall()
db.close()
return data

def run():
"""
"""
usage = "usage: %prog [options] arg"
parser = OptionParser(usage)
parser.add_option("-u", "--user", dest="user", help="The MySQL user", default="root")
parser.add_option("-p", "--password", dest="passwd", help="The MySQL password")
(options, args) = parser.parse_args()
if options.passwd is None:
print "Please enter a password!"
sys.exit(-1)
data = runMySQL(options.user, options.passwd)
headertoadd = ("Table Name", "Size")
datatable = (headertoadd,) + data
print indent(datatable)

#-----------------------------------------------------------------------------
if __name__ == "__main__":
run()

Be Sociable, Share!