def __init__(self,
host,
host_user,
host_user_pwd,
db_user,
db_user_pwd,
db_name,
db_port,
ssh_port=22):
"""
This class is used to abstract the database instance and interact with the database.
All operations such as obtaining database information and setting knobs
need to be implemented through this class.
:param host: String type, meaning the same as variable name.
:param host_user: Same as above.
:param host_user_pwd: Same as above.
:param db_user: Same as above.
:param db_user_pwd: Same as above.
:param db_name: Same as above.
:param db_port: Int type, meaning the same as variable name.
:param ssh_port: Same as above.
"""
# Set database authorization information:
self.ssh = ExecutorFactory() \
.set_host(host) \
.set_user(host_user) \
.set_pwd(host_user_pwd) \
.set_port(ssh_port) \
.get_executor()
self.host_user = host_user
self.db_user = host_user if not db_user else db_user
self.db_user_pwd = db_user_pwd
self.db_name = db_name
self.db_port = db_port
self.data_path = None
self.knobs = None
self.ordered_knob_list = None
self.check_connection_params()
# Set a connection session as unlimited.
self.set_knob_value("statement_timeout", 0)
# Initialize database metrics.
self.metric = OpenGaussMetric(self)
def test_remote(self):
ssh = ExecutorFactory().set_host('').set_user('').set_pwd(
'').get_executor() # Padding your information.
self.assertIsNotNone(
ssh.exec_command_sync(
"cat /proc/cpuinfo | grep \"processor\" | wc -l"))
self.assertIsNotNone(
ssh.exec_command_sync("cat /proc/self/cmdline | xargs -0"))
self.assertIsNotNone(
ssh.exec_command_sync("echo -e 'hello \\n world'")[0].count('\n'))
self.assertIsNotNone(
ssh.exec_command_sync("echo -e 'hello \\n world'")[0])
self.assertIsNotNone(ssh.exec_command_sync('echo $SHELL'))
def test_local(self):
proc = ExecutorFactory().get_executor()
self.assertIsNotNone(proc.exec_command_sync("ping -h"))
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
import importlib
import os
import types
from tuner.exceptions import ConfigureError
from tuner.executor import ExecutorFactory
# Create a local shell with resident memory.
# We must pass a local shell as an API to benchmark instance,
# maybe the shell will be used by benchmark instance sometime.
local_ssh = ExecutorFactory() \
.set_host('127.0.0.1') \
.get_executor()
def get_benchmark_instance(script, path, cmd, db_info):
name = script.rstrip('.py')
if not os.path.exists(os.path.join(os.path.dirname(__file__),
name + '.py')):
raise ConfigureError(
'Incorrect configuration option benchmark_script. '
'Enter the filename of the script in the benchmark directory '
'and ensure that the script file exists.')
bm = importlib.import_module('tuner.benchmark.{}'.format(name))
# Verify the validity of the benchmark script.
# An exception will be thrown if benchmark instance does not have specified attributes.
class DB_Agent:
def __init__(self,
host,
host_user,
host_user_pwd,
db_user,
db_user_pwd,
db_name,
db_port,
ssh_port=22):
"""
This class is used to abstract the database instance and interact with the database.
All operations such as obtaining database information and setting knobs
need to be implemented through this class.
:param host: String type, meaning the same as variable name.
:param host_user: Same as above.
:param host_user_pwd: Same as above.
:param db_user: Same as above.
:param db_user_pwd: Same as above.
:param db_name: Same as above.
:param db_port: Int type, meaning the same as variable name.
:param ssh_port: Same as above.
"""
# Set database authorization information:
self.ssh = ExecutorFactory() \
.set_host(host) \
.set_user(host_user) \
.set_pwd(host_user_pwd) \
.set_port(ssh_port) \
.get_executor()
self.host_user = host_user
self.db_user = host_user if not db_user else db_user
self.db_user_pwd = db_user_pwd
self.db_name = db_name
self.db_port = db_port
self.data_path = None
self.knobs = None
self.ordered_knob_list = None
self.check_connection_params()
# Set a connection session as unlimited.
self.set_knob_value("statement_timeout", 0)
# Initialize database metrics.
self.metric = OpenGaussMetric(self)
def check_connection_params(self):
# Check whether the binary files of the database can be located through environment variables.
try:
self.exec_command_on_host("which gsql")
self.exec_command_on_host("which gaussdb")
self.exec_command_on_host("which gs_guc")
self.exec_command_on_host("which gs_ctl")
except ExecutionError as e:
logging.exception(
"An exception occurred while checking connection parameters: %s",
e)
raise OptionError(
"The parameters about SSH login user are incorrect. Please check.\n"
"Hint: The binary file path of the database cannot be obtained after the login."
"Please ensure that the paths of tools such as gsql and gs_ctl are added to environment "
"variables ($PATH).") from None
# Check whether the third-party libraries can be properly loaded.
try:
self.exec_command_on_host("gaussdb --version")
self.exec_command_on_host("gsql --version")
except ExecutionError as e:
logging.exception(
"An exception occurred while checking connection parameters: %s",
e)
raise DBStatusError(
"The database environment is incorrectly configured. "
"login to the database host as the user and check whether "
"the database environment can be used properly. "
"For example, check environment variables $LD_LIBRARY_PATH."
) from None
# Check whether the database is started.
# And check whether the user name and password are correct.
try:
if not self.is_alive():
raise DBStatusError("Failed to login to the database. "
"Check whether the database is started. ")
# Get database instance pid and data_path.
self.data_path = self.exec_statement(
"SELECT datapath FROM pg_node_env;")[0][0]
except ExecutionError as e:
logging.exception(
"An exception occurred while checking connection parameters: %s",
e)
raise DBStatusError(
"Failed to login to the database. "
"Check whether the user name or password is correct, "
"and whether the database information passed to the X-Tuner is correct."
) from None
# Check whether the current user has sufficient permission to perform tuning.
try:
self.exec_statement("select * from pg_user;")
except ExecutionError as e:
logging.exception(
"An exception occurred while checking connection parameters: %s",
e)
raise DBStatusError(
"The current database user may not have the permission to tune best_knobs. "
"Please assign the administrator permission temporarily so that you can "
"obtain more information from the database.") from None
def set_tuning_knobs(self, knobs):
if not isinstance(knobs, RecommendedKnobs):
raise TypeError
self.knobs = knobs
self.ordered_knob_list = self.knobs.names()
# Check whether the knob value exceeds the upper limit and lower limit.
wherein_list = list()
for knob in self.ordered_knob_list:
check_special_character(knob)
wherein_list.append("'%s'" % knob)
sql = "SELECT name, setting, min_val, max_val FROM pg_settings WHERE name IN ({})".format(
','.join(wherein_list))
# If the value is missing, use the default value obtained in the system table to fill it.
tuples = self.exec_statement(sql)
for name, setting, min_val, max_val in tuples:
knob = self.knobs[name]
if knob.type != Knob.TYPE.BOOL:
min_val = float(min_val)
max_val = float(max_val)
knob.min = min_val if knob.min is None else max(
knob.min, min_val)
knob.max = max_val if knob.max is None else min(
knob.max, max_val)
knob.original = setting
# If user did not set default field, then make the original value as the default value.
# The default value (knob.current) is the starting point while tuning.
if knob.current is None:
knob.current = knob.to_numeric(setting)
def exec_statement(self, sql, timeout=None):
"""
Connect to the remote database host through SSH,
run the `gsql` command to execute SQL statements, and parse the execution result.
p.s This is why we want users who log in to the Linux host
to have environment variables that can find `gsql` commands.
:param sql: SQL statement
:param timeout: Int type. Unit second.
:return: The parsed result from SQL statement execution.
"""
command = "gsql -p {db_port} -U {db_user} -d {db_name} -W {db_user_pwd} -c \"{sql}\";".format(
db_port=self.db_port,
db_user=self.db_user,
db_name=self.db_name,
db_user_pwd=self.db_user_pwd,
sql=sql)
stdout, stderr = self.ssh.exec_command_sync(command, timeout)
if len(stderr) > 0 or self.ssh.exit_status != 0:
logging.error(
"Cannot execute SQL statement: %s. Error message: %s.", sql,
stderr)
raise ExecutionError("Cannot execute SQL statement: %s." % sql)
return to_tuples(stdout)
def is_alive(self):
"""
Check whether the database is running.
:return: True means running and vice versa.
"""
try:
stdout = self.exec_command_on_host("ps -ux | grep gaussdb | wc -l")
at_least_count = 1 # Includes one 'grep gaussdb' command.
if int(stdout.strip()) <= at_least_count:
return False
except ExecutionError:
return False
sql = "SELECT now();"
try:
self.exec_statement(sql, timeout=1)
return True
except ExecutionError:
return False
def exec_command_on_host(self,
cmd,
timeout=None,
ignore_status_code=False):
"""
The SSH connection is reused to execute shell commands on the Linux host.
This method can be used to restart the database, collect database running environment information,
and set database knobs.
:param cmd: Shell command.
:param timeout: Int type. Unit second.
:param ignore_status_code: If the exit status code returned after the command is executed is unequal to 0,
determine whether to ignore it. By default, it is not ignored, which may throw an
exception, and some operations are insignificant or even have to be ignored.
:return: Returns the standard output stream result after the command is executed.
:raise: Raise ExecutionError if found error.
"""
stdout, stderr = self.ssh.exec_command_sync(cmd, timeout=timeout)
if len(stderr) > 0 or self.ssh.exit_status != 0:
error_msg = "An error occurred when executing the command '%s'. " \
"The error information is: %s, the output information is %s." % (cmd, stderr, stdout)
if ignore_status_code and not (len(stderr) > 0
and self.ssh.exit_status != 0):
logging.warning(error_msg)
else:
raise ExecutionError(error_msg)
return stdout
def get_knob_normalized_vector(self):
nv = list()
for name in self.ordered_knob_list:
val = self.get_knob_value(name)
nv.append(self.knobs[name].to_numeric(val))
return nv
def get_default_normalized_vector(self):
"""
In order to get the initial performance and rollback the settings while tuning is finished.
:return: the vector that normalized from original/initial knobs.
"""
nv = list()
for name in self.ordered_knob_list:
val = self.knobs[name].original
nv.append(self.knobs[name].to_numeric(val))
return nv
def set_knob_normalized_vector(self, nv):
restart = False
for i, val in enumerate(nv):
name = self.ordered_knob_list[i]
knob = self.knobs[name]
self.set_knob_value(name, knob.to_string(val))
restart = True if knob.restart else restart
if restart:
self.restart()
def get_knob_value(self, name):
check_special_character(name)
sql = "SELECT setting FROM pg_settings WHERE name = '{}';".format(name)
value = self.exec_statement(sql)[0][0]
return value
def set_knob_value(self, name, value):
logging.info("change knob: [%s=%s]", name, value)
try:
self.exec_command_on_host("gs_guc reload -c \"%s=%s\" -D %s" %
(name, value, self.data_path))
except ExecutionError as e:
if str(e).find('Success to perform gs_guc!') < 0:
logging.warning(e)
def reset_state(self):
self.metric.reset()
def restart(self):
logging.info(
construct_dividing_line("Restarting database.", padding="*"))
try:
self.exec_statement(
"checkpoint;"
) # Prevent the database from being shut down for a long time.
except ExecutionError:
logging.warning(
"Cannot checkpoint perhaps due to bad GUC settings.")
self.exec_command_on_host(
"gs_ctl stop -D {data_path}".format(data_path=self.data_path),
ignore_status_code=True)
self.exec_command_on_host(
"gs_ctl start -D {data_path}".format(data_path=self.data_path),
ignore_status_code=True)
if self.is_alive():
logging.info("The database restarted successfully.")
else:
logging.fatal("The database restarted failed.")
raise DBStatusError("The database restarted failed.")
logging.info(construct_dividing_line())
def drop_cache(self):
try:
# Check whether frequent input password is required.
user_desc = self.exec_command_on_host('sudo -n -l -U %s' %
self.host_user,
ignore_status_code=True)
if (not user_desc) or user_desc.find('NOPASSWD') < 0:
logging.warning(
"Hint: You must add this line '%s ALL=(ALL) NOPASSWD: ALL' to the file '/etc/sudoers' "
"with administrator permission.", self.host_user)
return False
self.exec_command_on_host('sync')
self.exec_command_on_host(
'sudo bash -c "echo 1 > /proc/sys/vm/drop_caches"')
return True
except Exception as e:
logging.warning("Cannot drop cache. %s.", e)
return False