author | Radek Brich <brich.radek@ifortuna.cz> |
Thu, 20 Mar 2014 16:33:34 +0100 | |
changeset 95 | 6adcb7ee4517 |
parent 93 | b72591087495 |
permissions | -rwxr-xr-x |
93
b72591087495
Change python3.2 to python3 in scripts.
Radek Brich <brich.radek@ifortuna.cz>
parents:
83
diff
changeset
|
1 |
#!/usr/bin/env python3 |
2 | 2 |
|
83
515fadd3d286
Add dependency on pycolib. Move common modules to pycolib. Add example table schema for meta DB.
Radek Brich <radek.brich@devl.cz>
parents:
38
diff
changeset
|
3 |
from pgtoolkit import pgbrowser, toolbase |
95
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
4 |
from pycolib.prettysize import prettysize_short |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
5 |
from pycolib.ansicolor import highlight |
2 | 6 |
|
7 |
||
7
685b20d2d3ab
Reorganize directories. PgDataDiff - reworked. PgManager - add fetchone_adapted, fetchall_adapted to cursor.
Radek Brich <radek.brich@devl.cz>
parents:
5
diff
changeset
|
8 |
class BigTablesTool(toolbase.SimpleTool): |
2 | 9 |
def __init__(self): |
7
685b20d2d3ab
Reorganize directories. PgDataDiff - reworked. PgManager - add fetchone_adapted, fetchall_adapted to cursor.
Radek Brich <radek.brich@devl.cz>
parents:
5
diff
changeset
|
10 |
toolbase.SimpleTool.__init__(self, name='bigtables', desc='List largest tables.') |
95
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
11 |
self.parser.add_argument('-n', '--limit', metavar='NUM', dest='limit', type=int, default=5, help='Show NUM biggest tables.') |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
12 |
self.parser.add_argument('-v', '--details', dest='details', action='store_true', help='Show sizes of data and individual indexes.') |
2 | 13 |
self.init() |
14 |
||
15 |
def main(self): |
|
16 |
browser = pgbrowser.PgBrowser(self.pgm.get_conn('target')) |
|
17 |
||
38
d3593869d624
Update bigtables tool: scan all schemas.
Radek Brich <radek.brich@devl.cz>
parents:
9
diff
changeset
|
18 |
# scan all tables from all shemas, remember names and sizes |
d3593869d624
Update bigtables tool: scan all schemas.
Radek Brich <radek.brich@devl.cz>
parents:
9
diff
changeset
|
19 |
all_tables = [] |
95
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
20 |
all_indexes = [] |
38
d3593869d624
Update bigtables tool: scan all schemas.
Radek Brich <radek.brich@devl.cz>
parents:
9
diff
changeset
|
21 |
schemas = browser.list_schemas() |
d3593869d624
Update bigtables tool: scan all schemas.
Radek Brich <radek.brich@devl.cz>
parents:
9
diff
changeset
|
22 |
for schema in schemas: |
d3593869d624
Update bigtables tool: scan all schemas.
Radek Brich <radek.brich@devl.cz>
parents:
9
diff
changeset
|
23 |
tables = browser.list_tables(schema['name']) |
d3593869d624
Update bigtables tool: scan all schemas.
Radek Brich <radek.brich@devl.cz>
parents:
9
diff
changeset
|
24 |
for table in tables: |
d3593869d624
Update bigtables tool: scan all schemas.
Radek Brich <radek.brich@devl.cz>
parents:
9
diff
changeset
|
25 |
table_name = '%s.%s' % (schema['name'], table['name']) |
95
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
26 |
indexes = browser.list_indexes(table['name'], schema['name']) |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
27 |
for index in indexes: |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
28 |
all_indexes.append({'name': index['name'], 'table': table_name, 'size': index['size']}) |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
29 |
all_tables.append({'name': table_name, 'size': table['size'], 'indexes': indexes}) |
83
515fadd3d286
Add dependency on pycolib. Move common modules to pycolib. Add example table schema for meta DB.
Radek Brich <radek.brich@devl.cz>
parents:
38
diff
changeset
|
30 |
|
38
d3593869d624
Update bigtables tool: scan all schemas.
Radek Brich <radek.brich@devl.cz>
parents:
9
diff
changeset
|
31 |
# print names and sizes of 20 largest tables |
95
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
32 |
for table in sorted(all_tables, reverse=True, key=lambda x: x['size'])[:self.args.limit]: |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
33 |
size_of_indexes = sum(index['size'] for index in table['indexes']) |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
34 |
print(highlight(1) + prettysize_short(table['size'] + size_of_indexes, trailing_zeros=True).rjust(8) + highlight(0), |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
35 |
'(total)'.ljust(8), |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
36 |
highlight(1) + table['name'] + highlight(0), sep=' ') |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
37 |
if self.args.details: |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
38 |
print(prettysize_short(table['size'], trailing_zeros=True).rjust(8), |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
39 |
'(data)'.ljust(8), sep=' ') |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
40 |
for index in sorted(table['indexes'], reverse=True, key=lambda x: x['size']): |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
41 |
print(prettysize_short(index['size'], trailing_zeros=True).rjust(8), |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
42 |
'(index)'.ljust(8), index['name'], sep=' ') |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
43 |
print() |
2 | 44 |
|
45 |
||
95
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
46 |
if __name__ == '__main__': |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
47 |
tool = BigTablesTool() |
6adcb7ee4517
Update bigtables tool: Add size of indexes.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
48 |
tool.main() |
2 | 49 |