<p class="shortdesc">购买RASESQL实例后，您可以参照本文对实例性能进行性能测试。</p> <section class="section" id="test__section_xtm_bhd_m5b"><h2 class="doc-tairway">测试指标</h2> <ul class="ul" id="test__ul_ekv_chd_m5b"> <li class="li">只读QPS：数据库只读时，每秒执行的SQL数，仅包含SELECT。</li> <li class="li">读写QPS：数据库读写时，每秒执行的SQL数，包含INSERT、SELECT、UPDATE。</li> </ul> </section> <section class="section" id="test__section_lrr_ddd_m5b"><h2 class="doc-tairway">测试环境</h2> <ul class="ul" id="test__ul_z1g_2dd_m5b"> <li class="li">地域：华北，RASESQL实例和ECS实例在同一可用区</li> <li class="li">ECS的实例规格：g5.2xlarge.4（8核32GB）</li> <li class="li">ECS存储规格：普通云磁盘 200GB</li> <li class="li">网络类型：专有网络</li> <li class="li">操作系统：CentOS7.7-64Bit</li> <li class="li">RASESQL版本：开源版9.6 版本</li> </ul> </section> <section class="section prereq" id="test__prereq_yw5_hdd_m5b"><div class="tasklabel"><h2 class="doc-tairway">前提条件</h2></div> <ul class="ul" id="test__ul_atx_hdd_m5b"> <li class="li">您已成功创建RASESQL实例，可参考<a class="xref" href="/ssr/help/database/RASESQL/User_Guide_for_RDS_PostgreSQL.Instance_Management.Create_Instance.Create_Common_Instance_MySQL" target="_blank" rel="external noopener">创建常规实例</a>。</li> <li class="li">您已成功创建ECS，可参考<a class="xref" href="/ssr/help/database/RASESQL/Getting_Started.createECSRASESQL" target="_blank" rel="external noopener">创建ECS</a>。</li> <li class="li">您已成功开通白名单，可参考<a class="xref" href="/ssr/help/database/RASESQL/User_Guide_for_RDS_PostgreSQL.Security_Management_RASESQL.Create_Whitelist_RDS_RASESQL" target="_blank" rel="external noopener">开通白名单</a>。</li> <li class="li">您可以通过ECS连接RASESQL实例，可参考<a class="xref" href="/ssr/help/database/RASESQL/Getting_Started.Connect_RDS_PostgreSQL.Linux_RASESQL" target="_blank" rel="external noopener">Linux系统连接</a>。</li> </ul> </section> <section class="section" id="test__section_dlh_xdd_m5b"><h2 class="doc-tairway">测试工具</h2> <p class="p">pgbench是PostgreSQL自带的一款轻量级压力测试工具，是一种在PostgreSQL上运行基准测试的简单程序。可以在并发的数据库会话中重复运行相同的SQL语句。</p> <p class="p">安装方法：登录ECS，执行如下命令安装PostgreSQL客户端。</p> <pre class="pre codeblock" id="test__codeblock_bzk_gfd_m5b"><code>yum install -y postgresql* </code></pre> </section> <section><div class="tasklabel"><h2 class="doc-tairway">操作步骤</h2></div><ol class="ol steps"><li class="li step stepexpand"> <span class="ph cmd">在进行测试前，请提交工单修改RASESQL的实例参数。</span> <div class="itemgroup info"> <p class="p">需要同时修改主备实例上的参数（即rasesql.auto.conf文件）。</p> <pre class="pre codeblock" id="test__codeblock_jj3_mfd_m5b"><code>synchronous_commit = off max_wal_size = 64GB # 1/2 当前RASESQL实例的规格内存 min_wal_size = 16GB # 1/8 当前RASESQL实例的规格内存 </code></pre> </div> </li><li class="li step stepexpand"> <span class="ph cmd">修改参数后，重启RASESQL实例使配置生效。</span> </li><li class="li step stepexpand"> <span class="ph cmd">登录ECS。</span> </li><li class="li step stepexpand"> <span class="ph cmd">根据目标库的大小，初始化测试数据：</span> <div class="itemgroup info"> <pre class="pre codeblock" id="test__codeblock_g5w_wfd_m5b"><code>//初始化数据50亿： pgbench -i -s 50000 //初始化数据10亿： pgbench -i -s 10000 //初始化数据5亿： pgbench -i -s 5000 //初始化数据1亿： pgbench -i -s 1000 //初始化数据5000万： pgbench -i -s 500</code></pre> </div> </li><li class="li step stepexpand"> <span class="ph cmd">执行以下命令，配置环境变量：</span> <div class="itemgroup info"> <pre class="pre codeblock" id="test__codeblock_r3f_bgd_m5b"><code>export PGHOST=<Domain-Name> export PGPORT=<Port> export PGDATABASE=rasesql export PGUSER=<Account> export PGPASSWORD=<Password> </code></pre> <div class="note note note_note" id="test__note_oqd_dgd_m5b"><span class="note__title">说明：</span> <ul class="ul" id="test__ul_m1w_dgd_m5b"> <li class="li"> <p class="p"> Domain-Name：是实例的访问域名，可参考<a class="xref" href="/ssr/help/database/RASESQL/User_Guide_for_RDS_PostgreSQL.Instance_Management.View_Instance_RASESQL.View_Common_Instance_RASESQL" target="_blank" rel="external noopener">查看实例基本信息</a>。</p> </li> <li class="li"> <p class="p"> Port：实例的连接端口，可参考<a class="xref" href="/ssr/help/database/RASESQL/User_Guide_for_RDS_PostgreSQL.Instance_Management.View_Instance_RASESQL.View_Common_Instance_RASESQL" target="_blank" rel="external noopener">查看实例基本信息</a>。</p> </li> <li class="li"> <p class="p"> Account：实例管理员名称，可参考<a class="xref" href="/ssr/help/database/RASESQL/User_Guide_for_RDS_PostgreSQL.Instance_Management.Create_Instance.Create_Common_Instance_MySQL" target="_blank" rel="external noopener">创建常规实例</a>。</p> </li> <li class="li"> <p class="p"> postgres：需要连接的数据库名称，默认为rasesql数据库。</p> </li> <li class="li"> <p class="p"> Password：创建管理员账号时的密码，可参考<a class="xref" href="/ssr/help/database/RASESQL/User_Guide_for_RDS_PostgreSQL.Instance_Management.Create_Instance.Create_Common_Instance_MySQL" target="_blank" rel="external noopener">创建常规实例</a>。</p> </li> </ul> </div> </div> </li><li class="li step stepexpand"> <span class="ph cmd">创建只读和读写的测试脚本。</span> <div class="itemgroup info"> <ul class="ul" id="test__ul_ayw_jgd_m5b"> <li class="li">创建只读的测试脚本ro.sql：<pre class="pre codeblock" id="test__codeblock_azm_kgd_m5b"><code>\set aid random_gaussian(1, :range, 10.0) SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</code></pre></li> <li class="li"> <div class="p">创建读写的测试脚本rw.sql：<pre class="pre codeblock" id="test__codeblock_ob5_lgd_m5b"><code>\set aid random_gaussian(1, :range, 10.0) \set bid random(1, 1 * :scale) \set tid random(1, 10 * :scale) \set delta random(-5000, 5000) BEGIN; UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; SELECT abalance FROM pgbench_accounts WHERE aid = :aid; UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END;</code></pre></div> </li> </ul> </div> </li><li class="li step stepexpand"> <span class="ph cmd">执行以下命令，进行测试。</span> <div class="itemgroup info"> <ul class="ul" id="test__ul_kz1_4gd_m5b"> <li class="li">只读测试：<pre class="pre codeblock" id="test__codeblock_u5r_4gd_m5b"><code>// rds.pg.test1，总数据量10亿，热数据1亿 pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000 // rds.pg.test1，总数据量10亿，热数据5亿 pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000 //rds.pg.test1，总数据量10亿，热数据10亿 pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000</code></pre></li> <li class="li"> <div class="p">读写测试：<pre class="pre codeblock" id="test__codeblock_r25_qgd_m5b"><code>// rds.pg.test1，总数据量10亿，热数据1亿 pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000 // rds.pg.test1，总数据量10亿，热数据5亿 pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000 // rds.pg.test1，总数据量10亿，热数据10亿 pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000 </code></pre></div> </li> </ul> </div> <div class="itemgroup info"> <div class="p">其中：<table class="table" id="test__table_kcy_sgd_m5b"><caption></caption><colgroup><col><col></colgroup><thead class="thead"> <tr class="row"> <th class="entry" id="test__table_kcy_sgd_m5b__entry__1"> <p class="p">参数</p> </th> <th class="entry" id="test__table_kcy_sgd_m5b__entry__2"> <p class="p">说明</p> </th> </tr> </thead><tbody class="tbody"> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-M</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">请求协议，支持：</p> <ul class="ul" id="test__ul_lcy_sgd_m5b"> <li class="li">simple</li> <li class="li">extended</li> <li class="li">prepared</li> </ul> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-v</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">测试前执行vacuum操作。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-n</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">测试前不执行vacuum操作。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-r</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">在测试结果中显示脚本中每条指令的平均延时。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-P</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">每隔一段时间显示线程进度报告，单位：秒。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-f</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">指定测试脚本位置，默认为pgbench自带的脚本。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-c</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">测试的连接数，测试连接数不代表该规格的最大连接数，最大连接数请参考<a class="xref" href="/ssr/help/database/RASESQL/intro.Instance_Type" target="_blank" rel="external noopener">实例类型</a>。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-j</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">启动线程数，默认为1。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-T</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">测试时间，单位：秒。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">-D</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">传递测试脚本中的变量值。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">Scale</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">测试的数据量，单位：10万。</p> </td> </tr> <tr class="row"> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__1 "> <p class="p">range</p> </td> <td class="entry" headers="test__table_kcy_sgd_m5b__entry__2 "> <p class="p">表示活跃的数据量。</p> </td> </tr> </tbody></table></div> </div> </li></ol></section>