| author | Radek Brich <brich.radek@ifortuna.cz> |
| Tue, 06 May 2014 18:37:43 +0200 | |
| changeset 101 | 2a2d0d5df03b |
| parent 93 | analyzeall.py@b72591087495 |
| permissions | -rw-r--r-- |
|
101
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
1 |
from pgtoolkit.toolbase import SimpleTool |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
2 |
from pgtoolkit import pgbrowser |
|
27
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
3 |
|
|
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
4 |
|
|
101
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
5 |
class AnalyzeAllTool(SimpleTool): |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
6 |
|
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
7 |
""" |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
8 |
Analyze/vacuum all tables in selected schemas. |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
9 |
|
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
10 |
Partially emulates VACUUM ANALYZE VERBOSE query. |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
11 |
But this program is more configurable, skips pg_catalog etc. |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
12 |
|
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
13 |
""" |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
14 |
|
|
27
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
15 |
def __init__(self): |
|
101
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
16 |
SimpleTool.__init__(self, name='analyzeall') |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
17 |
self.target_isolation_level = 'autocommit' |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
18 |
|
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
19 |
def specify_args(self): |
|
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
20 |
SimpleTool.specify_args(self) |
|
28
27fc0504663d
analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents:
27
diff
changeset
|
21 |
self.parser.add_argument('-s', dest='schema', nargs='*', help='Schema filter')
|
|
27
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
22 |
self.parser.add_argument('--vacuum', action='store_true', help='Call VACUUM ANALYZE')
|
|
88
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
23 |
self.parser.add_argument('--vacuum-full', action='store_true', help='Call VACUUM FULL ANALYZE')
|
|
52
26121a8fe78b
Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents:
30
diff
changeset
|
24 |
self.parser.add_argument('--reindex', action='store_true', help='Call REINDEX TABLE')
|
|
27
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
25 |
|
|
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
26 |
def main(self): |
|
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
27 |
browser = pgbrowser.PgBrowser(self.pgm.get_conn('target'))
|
|
52
26121a8fe78b
Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents:
30
diff
changeset
|
28 |
|
|
88
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
29 |
query_patterns = ['ANALYZE %s.%s;'] |
|
27
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
30 |
if self.args.vacuum: |
|
88
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
31 |
query_patterns = ['VACUUM ANALYZE %s.%s;'] |
|
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
32 |
if self.args.vacuum_full: |
|
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
33 |
query_patterns = ['VACUUM FULL ANALYZE %s.%s;'] |
|
52
26121a8fe78b
Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents:
30
diff
changeset
|
34 |
if self.args.reindex: |
|
88
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
35 |
query_patterns += ['REINDEX TABLE %s.%s;'] |
|
27
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
36 |
|
|
28
27fc0504663d
analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents:
27
diff
changeset
|
37 |
schema_list = self.args.schema |
|
27fc0504663d
analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents:
27
diff
changeset
|
38 |
if not schema_list: |
|
27fc0504663d
analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents:
27
diff
changeset
|
39 |
schema_list = [schema['name'] for schema in browser.list_schemas() if not schema['system']] |
|
27fc0504663d
analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents:
27
diff
changeset
|
40 |
|
|
27fc0504663d
analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents:
27
diff
changeset
|
41 |
for schema in schema_list: |
|
27
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
42 |
tables = browser.list_tables(schema=schema) |
|
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
43 |
with self.pgm.cursor('target') as curs:
|
|
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
44 |
for table in tables: |
|
88
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
45 |
for query_pattern in query_patterns: |
|
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
46 |
query = query_pattern % (schema, table['name']) |
|
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
47 |
self.log.info(query) |
|
b8b2d28a7f35
Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents:
83
diff
changeset
|
48 |
curs.execute(query, []) |
|
27
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
49 |
|
|
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
50 |
|
|
101
2a2d0d5df03b
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents:
93
diff
changeset
|
51 |
cls = AnalyzeAllTool |
|
27
5fb4883604d6
Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff
changeset
|
52 |