Пример #1
0
    def __init__(self, args):

        if not self._is_connection_work():
            logging.error('Can\'t connect to PostgreSQL')
            sys.exit(5)

        self.args = args
        self.sys_info = SysInfo()

        self._memory()
        self._auto_vacuum()
        self._bgwriter()
        self._checkpointer()
        self._configure_pgbadger()
        self._configure_extensions()
        self._configure_virt_guest()
        self._miscellaneous()

        self._reload_config()
Пример #2
0
class AutoTunePgsl(object):

    def __init__(self, args):

        if not self._is_connection_work():
            logging.error('Can\'t connect to PostgreSQL')
            sys.exit(5)

        self.args = args
        self.sys_info = SysInfo()

        self._memory()
        self._auto_vacuum()
        self._bgwriter()
        self._checkpointer()
        self._configure_pgbadger()
        self._configure_extensions()
        self._configure_virt_guest()
        self._miscellaneous()

        self._reload_config()

    def _configure_extensions(self):

        extensions = self._run_query(
            "select name from pg_catalog.pg_available_extensions")
        if extensions is None:
            return
        extensions = [row[0] for row in extensions]

        needed_libraries = []

        if 'pg_stat_statements' in extensions:
            needed_libraries.append('pg_stat_statements')
        elif 'pg_buffercache' in extensions:
            needed_libraries.append('pg_buffercache')
        else:
            logging.warning("Please install 'contrib' modules: "
                            "need for 'pg_stat_statements'")

        if 'pg_wait_sampling' in extensions:
            needed_libraries.append('pg_wait_sampling')

        if len(needed_libraries) == 0:
            return

        libraries = self._run_query('show shared_preload_libraries;')
        if libraries is None:
            return
        elif not len(libraries) == 1:
            return
        elif not len(libraries[0]) == 1:
            return

        libraries = libraries[0][0]
        if len(libraries) == 0:
            libraries = needed_libraries
        else:
            libraries = libraries.split(',')
            libraries = [ext.strip() for ext in libraries]
            if needed_libraries not in libraries:
                for ext in needed_libraries:
                    if ext not in libraries:
                        libraries.append(ext)
        libraries = ','.join(libraries)
        self._run_query(
            "alter system set shared_preload_libraries to {0};".format(
                libraries))

    def _memory(self):
        if platform.WINDOWS:
            logging.info('No memory tune for windows')
            return

        sysmemory = self.sys_info.meminfo['_TOTAL']
        if sysmemory == 0:
            return

        self._run_query(
            "alter system set shared_buffers to '{0}';".format(
                self._humansize_and_round_bytes(sysmemory / 4)))
        self._run_query(
            "alter system set effective_cache_size to '{0}';".format(
                self._humansize_and_round_bytes(3 * sysmemory / 4)))
        self._run_query(
            "alter system set work_mem to '{0}';".format(
                self._humansize_and_round_bytes(sysmemory / 100)))
        self._run_query(
            "alter system set maintenance_work_mem to '{0}';".format(
                self._humansize_and_round_bytes(sysmemory / 10)))

    def _auto_vacuum(self):
        self._run_query(
            "alter system set autovacuum_max_workers to 20;")
        self._run_query(
            "alter system set autovacuum_analyze_scale_factor to 0.01;")
        self._run_query(
            "alter system set autovacuum_vacuum_scale_factor to 0.02;")
        self._run_query(
            "alter system set vacuum_cost_delay to 1;")

    def _bgwriter(self):
        self._run_query(
            "alter system set bgwriter_delay to 10;")
        self._run_query(
            "alter system set bgwriter_lru_maxpages to 800;")

    def _checkpointer(self):

        self._run_query(
            "alter system set checkpoint_completion_target to 0.75")

        if platform.WINDOWS:
            logging.info('No wal_size tune for windows')
            return

        sysmemory = self.sys_info.meminfo['_TOTAL']
        if sysmemory < 4 * 1024 * 1024 * 1024:
            return

        wal_size = min(sysmemory / 4, 8.0 * 1024 * 1024 * 1024)
        if Pooler.server_version_greater('9.5'):
            self._run_query(
                "alter system set max_wal_size to '{0}';".format(
                    self._humansize_and_round_bytes(wal_size)))

    def _configure_pgbadger(self):
        if self.args.pgbadger is not None:
            return
        self._run_query(
            "alter system set logging_collector to on;")
        self._run_query(
            "alter system set log_filename to 'postgresql-%%a.log';")
        self._run_query(
            "alter system set log_checkpoints to on;")
        self._run_query(
            "alter system set log_connections to on;")
        self._run_query(
            "alter system set log_disconnections to on;")
        self._run_query(
            "alter system set log_lock_waits to on;")
        self._run_query(
            "alter system set log_temp_files to 0;")
        self._run_query(
            "alter system set log_autovacuum_min_duration to 0;")
        self._run_query(
            "alter system set track_io_timing to on;")
        self._run_query(
            "alter system set log_line_prefix to "
            "'%%t [%%p]: [%%l-1] db=%%d,user=%%u,app=%%a,client=%%h ';")

    def _configure_virt_guest(self):
        if platform.WINDOWS:
            logging.info('No virt_guest tune for windows')
            return
        if not self.sys_info.is_virt_guest():
            return
        self._run_query(
            "alter system set synchronous_commit to off;")

    def _miscellaneous(self):
        if platform.WINDOWS:
		    self._run_query(
            "alter system set update_process_title to off;")

    def _reload_config(self):
        if self.args.reload_config is not None:
            return
        self._run_query('select pg_catalog.pg_reload_conf();')

    def _is_connection_work(self):
        try:
            Pooler.query('select 1')
            return True
        except Exception as e:
            logging.error('Test query error: {0}'.format(e))
            return False

    def _run_query(self, query='', exit_on_fail=True):
        if self.args.dry_run:
            logging.info('dry run (query):\t{0}'.format(
                query.replace('%%', '%')))
            return None
        try:
            return Pooler.query(query)
        except Exception as e:
            logging.error('Query {0} error: {1}'.format(query, e))
            if exit_on_fail:
                sys.exit(6)

    def _humansize_and_round_bytes(self, nbytes):
        suffixes = ['', 'kB', 'MB', 'GB', 'TB']
        if nbytes < 1024:
            return str(nbytes)
        i = 0
        while nbytes >= 1024 and i < len(suffixes) - 1:
            nbytes /= 1024.
            i += 1
        f = ('%.2f' % nbytes).rstrip('0').rstrip('.')
        return '%s%s' % (int(round(float(f))), suffixes[i])