def run_query_mysql_container(self, query, db, container_id, return_result=True): config = ProxySQL_Tests_Config(overrides=self.config_overrides) hostname = config.get('ProxySQL', 'hostname') username = config.get('ProxySQL', 'username') password = config.get('ProxySQL', 'password') metadata = self.docker_inspect(container_id) mysql_port = metadata.get('NetworkSettings', {})\ .get('Ports', {})\ .get('3306/tcp', [{}])[0]\ .get('HostPort', None) if mysql_port is not None: mysql_connection = MySQLdb.connect(host=hostname, user=username, passwd=password, port=int(mysql_port), db=db) cursor = mysql_connection.cursor() cursor.execute(query) if return_result: rows = cursor.fetchall() cursor.close() mysql_connection.close() if return_result: return rows else: return None
def get_all_mysql_connection_credentials(self, hostgroup=None): # Figure out which are the containers for the specified hostgroup mysql_backend_ids = self.get_mysql_containers(hostgroup=hostgroup) config = ProxySQL_Tests_Config(overrides=self.config_overrides) hostname = config.get('ProxySQL', 'hostname') username = config.get('ProxySQL', 'username') password = config.get('ProxySQL', 'password') result = [] for container_id in mysql_backend_ids: metadata = self.docker_inspect(container_id) mysql_port = metadata.get('NetworkSettings', {})\ .get('Ports', {})\ .get('3306/tcp', [{}])[0]\ .get('HostPort', None) if mysql_port is not None: result.append({ 'hostname': hostname, 'port': mysql_port, 'username': username, 'password': password }) return result
def get_docker_scenario_templates(self, scenarios=[]): """Retrieve the list of docker templates that will be used to generate scenarios. Why do we need templates for scenarios? Because the scenario will be the same, the only difference will be the configuration of the machines involved (for example, it might be a different operating system). """ if len(scenarios) == 0: config = ProxySQL_Tests_Config(overrides=self.config_overrides) scenarios = config.get('Scenarios', 'default_scenarios').split(',') files = {} dockercompose_path = os.path.dirname(__file__) + "/../docker/scenarios" for item in os.listdir(dockercompose_path): # Filter based on received default scenarios if item not in scenarios: continue dir_path = dockercompose_path + os.sep + item if path.isdir(dir_path): dockercomposefile = dir_path + os.sep + "docker-compose.yml" if path.isfile(dockercomposefile): files[item] = { "dir": dir_path, "dockercomposefile": dockercomposefile } with open (dockercomposefile, "r") as myfile: files[item]["contents"] = data=myfile.read() return files
def run_query_proxysql(cls, query, db, return_result=True, username=None, password=None, port=None): """Run a query against the ProxySQL proxy and optionally return its results as a set of rows.""" config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES) username = username or config.get('ProxySQL', 'username') password = password or config.get('ProxySQL', 'password') port = port or int(config.get('ProxySQL', 'port')) hostname = config.get('ProxySQL', 'hostname') proxy_connection = MySQLdb.connect(hostname, username, password, port=port, db=db) cursor = proxy_connection.cursor() cursor.execute(query) if return_result: rows = cursor.fetchall() cursor.close() proxy_connection.close() if return_result: return rows
def get_docker_scenario_templates(self, scenarios=[]): """Retrieve the list of docker templates that will be used to generate scenarios. Why do we need templates for scenarios? Because the scenario will be the same, the only difference will be the configuration of the machines involved (for example, it might be a different operating system). """ if len(scenarios) == 0: config = ProxySQL_Tests_Config(overrides=self.config_overrides) scenarios = config.get('Scenarios', 'default_scenarios').split(',') files = {} dockercompose_path = os.path.dirname(__file__) + "/../docker/scenarios" for item in os.listdir(dockercompose_path): # Filter based on received default scenarios if item not in scenarios: continue dir_path = dockercompose_path + os.sep + item if path.isdir(dir_path): dockercomposefile = dir_path + os.sep + "docker-compose.yml" if path.isfile(dockercomposefile): files[item] = { "dir": dir_path, "dockercomposefile": dockercomposefile } with open(dockercomposefile, "r") as myfile: files[item]["contents"] = data = myfile.read() return files
def get_proxysql_admin_connection_credentials(self): config = ProxySQL_Tests_Config(overrides=self.config_overrides) return { "hostname": config.get("ProxySQL", "hostname"), "port": config.get("ProxySQL", "admin_port"), "username": config.get("ProxySQL", "admin_username"), "password": config.get("ProxySQL", "admin_password") }
def run_query_mysql(cls, query, db, return_result=True, hostgroup=0, username=None, password=None): """Run a query against the MySQL backend and optionally return its results as a set of rows. IMPORTANT: since the queries are actually ran against the MySQL backend, that backend needs to expose its MySQL port to the outside through docker compose's port mapping mechanism. This will actually parse the docker-compose configuration file to retrieve the available backends and hostgroups and will pick a backend from the specified hostgroup.""" # Figure out which are the containers for the specified hostgroup mysql_backends = cls._get_mysql_containers() mysql_backends_in_hostgroup = [] for backend in mysql_backends: container_name = backend['Names'][0][1:].upper() backend_hostgroup = cls._extract_hostgroup_from_container_name(container_name) mysql_port_exposed=False if not backend.get('Ports'): continue for exposed_port in backend.get('Ports', []): if exposed_port['PrivatePort'] == 3306: mysql_port_exposed = True if backend_hostgroup == hostgroup and mysql_port_exposed: mysql_backends_in_hostgroup.append(backend) if len(mysql_backends_in_hostgroup) == 0: raise Exception('No backends with a publicly exposed port were ' 'found in hostgroup %d' % hostgroup) # Pick a random container, extract its connection details container = random.choice(mysql_backends_in_hostgroup) for exposed_port in container.get('Ports', []): if exposed_port['PrivatePort'] == 3306: mysql_port = exposed_port['PublicPort'] config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES) hostname = config.get('ProxySQL', 'hostname') username = username or config.get('ProxySQL', 'username') password = password or config.get('ProxySQL', 'password') mysql_connection = MySQLdb.connect(hostname, username, password, port=mysql_port, db=db) cursor = mysql_connection.cursor() cursor.execute(query) if return_result: rows = cursor.fetchall() cursor.close() mysql_connection.close() if return_result: return rows
def _start_proxysql_pings(self): """During the running of the tests, the test suite will continuously monitor the ProxySQL daemon in order to check that it's up. This special thread will do exactly that.""" config = ProxySQL_Tests_Config(overrides=ProxySQLBaseTest.CONFIG_OVERRIDES) self.ping_thread = ProxySQL_Ping_Thread(config) self.ping_thread.start()
def _populate_proxy_configuration_with_backends(self): """Populate ProxySQL's admin information with the MySQL backends and their associated hostgroups. This is needed because I do not want to hardcode this into the ProxySQL config file of the test scenario, as it leaves more room for quick iteration. In order to configure ProxySQL with the correct backends, we are using the MySQL admin interface of ProxySQL, and inserting rows into the `mysql_servers` table, which contains a list of which servers go into which hostgroup. """ config = ProxySQL_Tests_Config(overrides=self.config_overrides) proxysql_container_id = self.get_proxysql_container() mysql_container_ids = self.get_mysql_containers() environment_variables = self.get_environment_variables_from_container( proxysql_container_id) proxy_admin_connection = MySQLdb.connect( config.get('ProxySQL', 'hostname'), config.get('ProxySQL', 'admin_username'), config.get('ProxySQL', 'admin_password'), port=int(config.get('ProxySQL', 'admin_port'))) cursor = proxy_admin_connection.cursor() for mysql_container_id in mysql_container_ids: metadata = self.docker_inspect(mysql_container_id) container_name = metadata['Name'][1:].upper() port_uri = environment_variables['%s_PORT' % container_name] port_no = self._extract_port_number_from_uri(port_uri) ip = environment_variables['%s_PORT_%d_TCP_ADDR' % (container_name, port_no)] hostgroup = int( metadata.get('Config', {}).get('Labels', {}).get('com.proxysql.hostgroup')) cursor.execute( "INSERT INTO mysql_servers(hostgroup_id, hostname, port, status) " "VALUES(%d, '%s', %d, 'ONLINE')" % (hostgroup, ip, port_no)) cursor.execute("LOAD MYSQL SERVERS TO RUNTIME") cursor.close() proxy_admin_connection.close()
def run_query_proxysql_admin(cls, query, return_result=True): """Run a query against the ProxySQL admin. Note: we do not need to specify a db for this query, as it's always against the "main" database. TODO(andrei): revisit db assumption once stats databases from ProxySQL are accessible via the MySQL interface. """ config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES) return cls.run_query_proxysql( query, # "main" database is hardcoded within the # ProxySQL admin -- it contains the SQLite3 # tables with metadata about servers and users "main", return_result, username=config.get('ProxySQL', 'admin_username'), password=config.get('ProxySQL', 'admin_password'), port=int(config.get('ProxySQL', 'admin_port')))
def run_query_proxysql_admin(cls, query, return_result=True): """Run a query against the ProxySQL admin. Note: we do not need to specify a db for this query, as it's always against the "main" database. TODO(andrei): revisit db assumption once stats databases from ProxySQL are accessible via the MySQL interface. """ config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES) return cls.run_query_proxysql( query, # "main" database is hardcoded within the # ProxySQL admin -- it contains the SQLite3 # tables with metadata about servers and users "main", return_result, username=config.get('ProxySQL', 'admin_username'), password=config.get('ProxySQL', 'admin_password'), port=int(config.get('ProxySQL', 'admin_port')) )
def run_sysbench_proxysql(cls, threads=4, time=60, db="test", username=None, password=None, port=None): """Runs a sysbench test with the given parameters against the given ProxySQL instance. In this case, due to better encapsulation and reduced latency to ProxySQL, we are assuming that sysbench is installed on the same container with it. """ proxysql_container_id = ProxySQLBaseTest._get_proxysql_container()['Id'] config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES) hostname = config.get('ProxySQL', 'hostname') username = username or config.get('ProxySQL', 'username') password = password or config.get('ProxySQL', 'password') port = port or config.get('ProxySQL', 'port') params = [ "sysbench", "--test=/opt/sysbench/sysbench/tests/db/oltp.lua", "--num-threads=%d" % threads, "--max-requests=0", "--max-time=%d" % time, "--mysql-user=%s" % username, "--mysql-password=%s" % password, "--mysql-db=%s" % db, "--db-driver=mysql", "--oltp-tables-count=4", "--oltp-read-only=on", "--oltp-skip-trx=on", "--report-interval=1", "--oltp-point-selects=100", "--oltp-table-size=400000", "--mysql-host=%s" % hostname, "--mysql-port=%s" % port ] cls.run_bash_command_within_proxysql(params + ["prepare"]) cls.run_bash_command_within_proxysql(params + ["run"]) cls.run_bash_command_within_proxysql(params + ["cleanup"])
def get_docker_images(self, filters): names = filters.pop('names', []) if len(names) == 0: if filters.get('com.proxysql.type') == 'proxysql': config = ProxySQL_Tests_Config(overrides=self.config_overrides) names = config.get('Scenarios', 'default_proxysql_images').split(',') elif filters.get('com.proxysql.type') == 'mysql': config = ProxySQL_Tests_Config(overrides=self.config_overrides) names = config.get('Scenarios', 'default_mysql_images').split(',') args = ["docker", "images"] for k, v in filters.iteritems(): args.append("--filter") args.append("label=%s=%s" % (k, v)) nonemtpy_lines = self._get_stdout_as_lines(args) results = nonemtpy_lines[1:] images = [] for (i, r) in enumerate(results): tokens = r.split(' ') nonempty_tokens = [t for t in tokens if len(t.strip()) > 0] image = nonempty_tokens[1] if image in names: images.append(image) return images
def run_sysbench_proxysql(self, threads=4, time=60, db="test", username=None, password=None, port=None): """Runs a sysbench test with the given parameters against the given ProxySQL instance. In this case, due to better encapsulation and reduced latency to ProxySQL, we are assuming that sysbench is installed on the same container with it. """ config = ProxySQL_Tests_Config(overrides=ProxySQLBaseTest.CONFIG_OVERRIDES) hostname = config.get('ProxySQL', 'hostname') username = username or config.get('ProxySQL', 'username') password = password or config.get('ProxySQL', 'password') port = port or config.get('ProxySQL', 'port') params = [ "sysbench", "--test=/opt/sysbench/sysbench/tests/db/oltp.lua", "--num-threads=%d" % threads, "--max-requests=0", "--max-time=%d" % time, "--mysql-user=%s" % username, "--mysql-password=%s" % password, "--mysql-db=%s" % db, "--db-driver=mysql", "--oltp-tables-count=4", "--oltp-read-only=on", "--oltp-skip-trx=on", "--report-interval=1", "--oltp-point-selects=100", "--oltp-table-size=400000", "--mysql-host=%s" % hostname, "--mysql-port=%s" % port ] self.run_bash_command_within_proxysql(params + ["prepare"]) self.run_bash_command_within_proxysql(params + ["run"]) self.run_bash_command_within_proxysql(params + ["cleanup"])
def _populate_proxy_configuration_with_backends(cls): """Populate ProxySQL's admin information with the MySQL backends and their associated hostgroups. This is needed because I do not want to hardcode this into the ProxySQL config file of the test scenario, as it leaves more room for quick iteration. In order to configure ProxySQL with the correct backends, we are using the MySQL admin interface of ProxySQL, and inserting rows into the `mysql_servers` table, which contains a list of which servers go into which hostgroup. """ config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES) proxysql_container = cls._get_proxysql_container() mysql_containers = cls._get_mysql_containers() environment_variables = cls._get_environment_variables_from_container( proxysql_container['Names'][0][1:]) proxy_admin_connection = MySQLdb.connect(config.get('ProxySQL', 'hostname'), config.get('ProxySQL', 'admin_username'), config.get('ProxySQL', 'admin_password'), port=int(config.get('ProxySQL', 'admin_port'))) cursor = proxy_admin_connection.cursor() for mysql_container in mysql_containers: container_name = mysql_container['Names'][0][1:].upper() port_uri = environment_variables['%s_PORT' % container_name] port_no = cls._extract_port_number_from_uri(port_uri) ip = environment_variables['%s_PORT_%d_TCP_ADDR' % (container_name, port_no)] hostgroup = cls._extract_hostgroup_from_container_name(container_name) cursor.execute("INSERT INTO mysql_servers(hostgroup_id, hostname, port, status) " "VALUES(%d, '%s', %d, 'ONLINE')" % (hostgroup, ip, port_no)) cursor.execute("LOAD MYSQL SERVERS TO RUNTIME") cursor.close() proxy_admin_connection.close()
def get_tests_config(self): return ProxySQL_Tests_Config(overrides=ProxySQLBaseTest.CONFIG_OVERRIDES)
def run_query_mysql(cls, query, db, return_result=True, hostgroup=0, username=None, password=None): """Run a query against the MySQL backend and optionally return its results as a set of rows. IMPORTANT: since the queries are actually ran against the MySQL backend, that backend needs to expose its MySQL port to the outside through docker compose's port mapping mechanism. This will actually parse the docker-compose configuration file to retrieve the available backends and hostgroups and will pick a backend from the specified hostgroup.""" # Figure out which are the containers for the specified hostgroup mysql_backends = cls._get_mysql_containers() mysql_backends_in_hostgroup = [] for backend in mysql_backends: container_name = backend['Names'][0][1:].upper() backend_hostgroup = cls._extract_hostgroup_from_container_name( container_name) mysql_port_exposed = False if not backend.get('Ports'): continue for exposed_port in backend.get('Ports', []): if exposed_port['PrivatePort'] == 3306: mysql_port_exposed = True if backend_hostgroup == hostgroup and mysql_port_exposed: mysql_backends_in_hostgroup.append(backend) if len(mysql_backends_in_hostgroup) == 0: raise Exception('No backends with a publicly exposed port were ' 'found in hostgroup %d' % hostgroup) # Pick a random container, extract its connection details container = random.choice(mysql_backends_in_hostgroup) for exposed_port in container.get('Ports', []): if exposed_port['PrivatePort'] == 3306: mysql_port = exposed_port['PublicPort'] config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES) hostname = config.get('ProxySQL', 'hostname') username = username or config.get('ProxySQL', 'username') password = password or config.get('ProxySQL', 'password') mysql_connection = MySQLdb.connect(hostname, username, password, port=mysql_port, db=db) cursor = mysql_connection.cursor() cursor.execute(query) if return_result: rows = cursor.fetchall() cursor.close() mysql_connection.close() if return_result: return rows