作者选择了自由软件基金会作为 写给捐款计划的一部分接受捐款。
介绍
ProxySQL是一个 SQL 知情的代理服务器,可以在您的应用程序和您的数据库之间定位。它提供了许多功能,如 多 MySQL 服务器之间的负载平衡和用于查询的 caching layer。本教程将专注于 ProxySQL 的缓存功能,以及如何为您的 MySQL 数据库优化查询。
MySQL 缓存 发生在一个查询的结果被存储时,以便当该查询重复时,该结果可以返回而无需通过数据库进行排序。这可以显著提高常见查询的速度。但在许多缓存方法中,开发人员必须修改他们的应用程序的代码,这可能会引入代码库中的错误。
在透明缓存中,只有数据库管理员需要更改ProxySQL配置以启用最常见查询的缓存,这些更改可以通过ProxySQL管理界面进行。
在本教程中,您将使用ProxySQL为Ubuntu 16.04上的MySQL服务器设置透明缓存,然后将使用mysqlslap测试其性能,包括缓存,以展示缓存的效果以及在执行许多类似查询时可以节省多少时间。
前提条件
在您开始本指南之前,您将需要以下内容:
- 一个具有至少 2 GB RAM 的 Ubuntu 16.04 服务器,配备具有 sudo 特权和防火墙的非 root 用户,如 [我们的 Ubuntu 16.04 初始服务器设置指南] 所指示。
步骤 1 – 安装和设置MySQL服务器
首先,您将安装MySQL服务器,并将其配置为ProxySQL作为客户端查询的后端服务器。
在Ubuntu 16.04上,可以使用以下命令安装mysql-server:
1sudo apt-get install mysql-server
按Y来确认安装。
然后,您将被要求提供您的MySQL root用户密码. 输入一个强大的密码,并将其保存用于以后的使用。
你需要为ProxySQL添加一个 monitor用户来监控MySQL服务器,因为ProxySQL通过SQL协议倾听后端服务器,而不是使用TCP连接或HTTPGET请求来确保后端正在运行。
首先,请登录到 MySQL 壳:
1mysql -uroot -p
-root 登录您使用 MySQL root 用户,而 -p 提示您使用 root 用户的密码.这个 root 用户与您的服务器的 root 用户不同,密码是您在安装 mysql-server 包时输入的密码。
输入 root 密码,然后按ENTER。
现在你将创建两个用户,一个叫做ProxySQL的 monitor,另一个你会用来执行客户端查询并赋予他们正确的特权。
创建用户监视器:
1CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';
使用%表示用户可以从任何IP地址连接,IDENTIFIED BY为新用户设置密码;输入您想要的密码,但请确保将其记住以便以后使用。
创建了用户 monitor,接下来创建了用户 sammy:
1CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';
接下来,向新用户授予权限. 运行以下命令来配置 monitor:
1GRANT SELECT ON sys.* TO 'monitor'@'%';
在这里,您只授予SELECT在sys数据库中的所有表中给 monitor用户;它只需要此特权来聆听后端服务器。
现在将所有数据库的所有权限授予用户 sammy:
1GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';
这将使 sammy能够进行必要的查询,以便稍后测试您的数据库。
通过运行以下操作来应用特权更改:
1FLUSH PRIVILEGES;
最后,输出mysql壳:
1exit;
您现在已经安装了mysql-server,并创建了一个用户,用于ProxySQL监控您的MySQL服务器,另一个用于执行客户端查询。
第2步:安装和配置ProxySQL服务器
现在您可以安装ProxySQL服务器,该服务器将用于查询的缓存层。 缓存层存在于应用程序服务器和数据库后端服务器之间的停机处;它用于连接到数据库并将一些查询的结果保存到其内存中以便在以后快速访问。
该 ProxySQL 发布 Github 页面为常见的 Linux 发行版提供安装文件. 对于本教程,您将使用「wget」下载 ProxySQL 版本 2.0.4 Debian 安装文件:
1wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb
接下来,安装使用dpkg的包:
1sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb
一旦安装,请使用此命令启动 ProxySQL:
1sudo systemctl start proxysql
您可以通过此命令检查 ProxySQL 是否正确启动:
1sudo systemctl status proxysql
您将获得类似于此的输出:
1[secondary_label Output]
2root@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql
3● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
4 Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
5 Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago
6 Docs: man:systemd-sysv-generator(8)
7 Tasks: 0
8 Memory: 0B
9 CPU: 0
现在是时候将您的ProxySQL服务器连接到MySQL服务器了。 为此,请使用ProxySQL admin SQL接口,默认情况下它会听到6032的端口,并将admin作为其用户名和密码。
通过运行以下操作连接到接口:
1mysql -uadmin -p -h 127.0.0.1 -P6032
请在提示密码时输入admin。
「-uadmin」將使用者名稱設定為「admin」,而「-h」旗則指定主機為「localhost」。
在这里,您必须明确指定主机和端口,因为默认情况下,MySQL客户端使用本地接口文件和端口3306连接。
现在,您已登录到mysql壳作为admin,请配置 monitor用户以便 ProxySQL 可以使用它。
1UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
2UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';
变量 mysql-monitor_username 指定了将用于检查后端服务器是否活跃的 MySQL 用户名。 变量 mysql-monitor_password 指向将用于连接后端服务器时的密码。
每当你在 ProxySQL 管理界面中创建一个更改时,你需要使用正确的LOAD命令将更改应用到正在运行的 ProxySQL 实例中。
1LOAD MYSQL VARIABLES TO RUNTIME;
接下来,‘保存’对磁盘数据库的更改,以便在重新启动之间保持更改。ProxySQL 使用自己的 SQLite本地数据库存储自己的表和变量:
1SAVE MYSQL VARIABLES TO DISK;
现在,你会告诉 ProxySQL 关于后端服务器的信息. 表mysql_servers包含每个后端服务器的信息,ProxySQL 可以连接和执行查询,所以使用标准 SQL 语句INSERT添加一个新的记录,其中包含以下值为hostgroup_id,hostname和port:
1INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);
若要应用更改,请再次运行加载和保存:
1LOAD MYSQL SERVERS TO RUNTIME;
2SAVE MYSQL SERVERS TO DISK;
最后,您会告诉ProxySQL哪个用户将连接到后端服务器;将 sammy设置为用户,并用您之前创建的密码替换sammy_password:
1INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);
表mysql_users包含用于连接后端服务器的用户信息;您指定了用户名,密码和默认_主机组。
加载和保存更改:
1LOAD MYSQL USERS TO RUNTIME;
2SAVE MYSQL USERS TO DISK;
然后输出mysql壳:
1exit;
要测试您是否可以使用 ProxySQL 连接到后端服务器,请执行以下测试查询:
1mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"
在此命令中,您使用e旗执行查询并关闭连接,该查询打印了后端服务器的主机名。
<$>[注]
注: 默认情况下,ProxySQL 使用端口 6033 来收听接入连接。
输出将是这样的,你的_hostname 将被你的 hostname 取代:
1[secondary_label Output]
2+----------------------------+
3| hostname |
4+----------------------------+
5| your_hostname |
6+----------------------------+
有关 ProxySQL 配置的更多信息,请参阅如何在 Ubuntu 16.04 上使用 ProxySQL 作为负载平衡器(How To Use ProxySQL as a Load Balancer)。
到目前为止,您已配置 ProxySQL 以使用您的 MySQL 服务器作为后端并使用 ProxySQL 连接到后端。
步骤 3 – 使用mysqlslap进行测试而无需缓存
在此步骤中,您将下载一个测试数据库,以便您可以使用mysqlslap对其执行查询,以测试延迟而无需缓存,为查询的速度设置基准。
mysqlslap是一个负载模拟客户端,用于MySQL的负载测试工具。它可以用自动生成的查询或在数据库上执行的一些自定义查询来测试MySQL服务器。它与MySQL客户端包一起安装,所以您不需要安装它;相反,您只会下载用于测试的数据库,您可以使用‘mysqlslap’。
在本教程中,您将使用 示例员工数据库。您将使用这个员工数据库,因为它具有大数据集,可以说明查询优化的差异。
要下载数据库,首先使用此命令克隆 Github 存储库:
1git clone https://github.com/datacharmer/test_db.git
然后输入test_db目录,并使用以下命令将数据库加载到MySQL服务器:
1cd test_db
2mysql -uroot -p < employees.sql
此命令使用 shell 重定向来读取 employers.sql 文件中的 SQL 查询,并在 MySQL 服务器上执行它们以创建数据库结构。
你会看到这样的输出:
1[secondary_label Output]
2INFO
3CREATING DATABASE STRUCTURE
4INFO
5storage engine: InnoDB
6INFO
7LOADING departments
8INFO
9LOADING employees
10INFO
11LOADING dept_emp
12INFO
13LOADING dept_manager
14INFO
15LOADING titles
16INFO
17LOADING salaries
18data_load_time_diff
1900:00:32
一旦数据库被加载到您的MySQL服务器,请测试mysqlslap是否正在使用以下查询:
1mysqlslap -usammy -p -P6033 -h127.0.0.1 --auto-generate-sql --verbose
mysqlslap具有类似于mysql客户端的旗帜;以下是本命令中使用的旗帜:
-u指定使用者连接到服务器.-p提示用户的密码.-P使用指定的端口连接.-h连接到指定的主机.--auto-generate-sql允许 MySQL 使用其自身生成的查询进行负载测试。
您将获得类似于以下的输出:
1[secondary_label Output]
2Benchmark
3 Average number of seconds to run all queries: 0.015 seconds
4 Minimum number of seconds to run all queries: 0.015 seconds
5 Maximum number of seconds to run all queries: 0.015 seconds
6 Number of clients running queries: 1
7 Average number of queries per client: 0
在此输出中,您可以看到执行所有查询所花费的平均、最小和最大秒数。
接下来,通过查看ProxySQL的stats_mysql_query_digest来找出在最后一个命令中执行的mysqlslap查询,这将为我们提供查询的 digest等信息,这是一种 SQL 陈述的正常化形式,可以稍后引用以允许缓存。
使用此命令输入 ProxySQL admin 接口:
1mysql -uadmin -p -h 127.0.0.1 -P6032
然后执行此查询以在stats_mysql_query_digest表中找到信息:
1SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
您将看到类似于以下的输出:
1+------------+----------+-----------+--------------------+----------------------------------+
2| count_star | sum_time | hostgroup | digest | digest_text |
3+------------+----------+-----------+--------------------+----------------------------------+
4| 1 | 598 | 1 | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname |
5| 1 | 0 | 1 | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
6+------------+----------+-----------+--------------------+----------------------------------+
72 rows in set (0.01 sec)
上一个查询从stats_mysql_query_digest表中选择数据,其中包含有关 ProxySQL 中的所有执行查询的信息。
count_star: 执行此查询的次数.sum_time: 执行此查询所需的毫秒总时间.hostgroup: 主机组用来执行查询.digest: 执行查询的数值.digest_text: 实际查询. 在本教程的例子中,第二个查询是使用?标记来参数变量。
现在您已经知道如何在stats_mysql_query_digest表中检查查询数据,请退出mysql壳:
1exit;
您下载的数据库包含一些示范数据的表格,您现在将通过选择任何from_date大于2000-04-20的记录来测试dept_emp表中的查询,并记录平均执行时间。
使用此命令来运行测试:
1mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose
这里你正在使用一些新的旗帜:
--concurrency=100:此设置了要模拟的用户数,在这种情况下是100.--iterations=20:这导致测试运行20次,并从所有这些测试中计算结果。--create-schema=employees: 在这里您选择了employees数据库.- `--query="SELECT * from dept_emp WHERE from_date>'2000-04-20'': 在这里您指定了测试中执行的查询。
测试需要几分钟,完成后,您将获得类似于以下的结果:
1[secondary_label Output]
2Benchmark
3 Average number of seconds to run all queries: 18.117 seconds
4 Minimum number of seconds to run all queries: 8.726 seconds
5 Maximum number of seconds to run all queries: 22.697 seconds
6 Number of clients running queries: 100
7 Average number of queries per client: 1
你的数字可能会有所不同. 将这些数字保存在某个地方,以便在启用缓存后与结果进行比较。
在没有缓存的情况下测试 ProxySQL 后,是时候再次运行相同的测试了,但这次已启用缓存。
步骤 4 – 使用mysqlslap进行缓存测试
在此步骤中,缓存将有助于我们在执行类似查询时减少延迟时间. 在这里,您将识别执行的查询,从ProxySQL的stats_mysql_query_digest表中获取它们的测量,并使用它们来启用缓存。
要启用缓存,您需要知道将缓存的查询的探测器。 使用此命令登录 ProxySQL 管理界面:
1mysql -uadmin -p -h127.0.0.1 -P6032
然后再次执行此查询以获取执行查询列表及其测量:
1SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
你会得到一个类似的结果:
1[secondary_label Output]
2+------------+-------------+-----------+--------------------+------------------------------------------+
3| count_star | sum_time | hostgroup | digest | digest_text |
4+------------+-------------+-----------+--------------------+------------------------------------------+
5| 2000 | 33727110501 | 1 | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? |
6| 1 | 601 | 1 | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname |
7| 1 | 0 | 1 | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
8+------------+-------------+-----------+--------------------+------------------------------------------+
93 rows in set (0.00 sec)
看看第一行. 这是一个被执行 2000 次的查询. 这是之前执行的基准查询. 取其加载并保存以用于添加缓存的查询规则。
接下来的几个查询将为 ProxySQL 添加一个新的查询规则,该规则将匹配前一个查询的查询,并为其设置一个 cache_ttl 值。
1INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);
在此命令中,您正在向mysql_query_rules表添加一个新记录;此表包含在执行查询之前应用的所有规则. 在此示例中,您正在为cache_ttl列添加一个值,该值会导致该测量器对应的查询在该列中指定的数毫秒内缓存。
加载和保存这些更改,然后离开mysql壳:
1LOAD MYSQL QUERY RULES TO RUNTIME;
2SAVE MYSQL QUERY RULES TO DISK;
3exit;
现在已启用缓存,重新运行测试以检查结果:
1mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose
这将产生类似于以下的产量:
1[secondary_label Output]
2Benchmark
3 Average number of seconds to run all queries: 7.020 seconds
4 Minimum number of seconds to run all queries: 0.274 seconds
5 Maximum number of seconds to run all queries: 23.014 seconds
6 Number of clients running queries: 100
7 Average number of queries per client: 1
在这里,你可以看到平均执行时间的巨大差异:它从18.117秒下降到7.020。
结论
在本文中,您设置了透明的 ProxySQL 缓存以缓存数据库查询结果,您还测试了缓存和没有缓存的查询速度,以查看缓存可以做出的差异。
你已经在本教程中使用了一个缓存级别。你也可以尝试, web caching,它坐在网页服务器前面并缓存对类似请求的响应,将响应发送回客户端而不打击后端服务器。这与ProxySQL缓存非常相似,但在不同的水平上。 有关网页缓存的更多信息,请参阅我们的 Web Caching Basics: Terminology, HTTP Headers, and Caching Strategies primer。
MySQL 服务器也有自己的查询缓存,您可以在我们的 如何在 Ubuntu 18.04 上优化 MySQL 使用查询缓存中了解更多。