analyzeall.py
author Radek Brich <radek.brich@devl.cz>
Tue, 24 Sep 2013 13:34:17 +0200
changeset 88 b8b2d28a7f35
parent 83 515fadd3d286
child 93 b72591087495
permissions -rwxr-xr-x
Update analyzeall tool: Allow combination of vacuum and reindex.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
#!/usr/bin/env python3.2
30
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     2
"""
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     3
analyzeall
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     4
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     5
Analyze/vacuum all tables in selected schemas.
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     6
See also "VACUUM ANALYZE VERBOSE;" query.
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     7
Unlike that, this program skips pg_catalog etc.
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     8
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     9
"""
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
from pgtoolkit import pgbrowser, toolbase
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
class AnalyzeAllTool(toolbase.SimpleTool):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
    def __init__(self):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
        toolbase.SimpleTool.__init__(self, name='analyzeall', desc='Analyze all tables.')
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    17
        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
    18
        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
    19
        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
    20
        self.parser.add_argument('--reindex', action='store_true', help='Call REINDEX TABLE')
30
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
    21
        self.target_isolation_level = 'autocommit'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
        self.init()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
    def main(self):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
        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
    26
88
b8b2d28a7f35 Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents: 83
diff changeset
    27
        query_patterns = ['ANALYZE %s.%s;']
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
        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
    29
            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
    30
        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
    31
            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
    32
        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
    33
            query_patterns += ['REINDEX TABLE %s.%s;']
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    34
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    35
        schema_list = self.args.schema
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    36
        if not schema_list:
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    37
            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
    38
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    39
        for schema in schema_list:
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    40
            tables = browser.list_tables(schema=schema)
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    41
            with self.pgm.cursor('target') as curs:
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    42
                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
    43
                    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
    44
                        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
    45
                        self.log.info(query)
b8b2d28a7f35 Update analyzeall tool: Allow combination of vacuum and reindex.
Radek Brich <radek.brich@devl.cz>
parents: 83
diff changeset
    46
                        curs.execute(query, [])
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    48
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    49
tool = AnalyzeAllTool()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    50
tool.main()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    51