pgbench测试

安装完PostgreSQL之后就有pgbench了，不需要单独安装。

输入pgbench -V查看版本号。

测试

尝试一下怎么使用，创建一个虚空的数据库测试一下

# 切换用户
su - postgres 
# 进入postgresql
psql
#列出所有的数据库
\l 
# 创建pgbench测试的数据库
CREATE DATABASE pgbench;
# 连接到pgbench数据库
\c pgbench
# 列出所有表
\d 
# 列出accounts的数量，需要下一步初始化后才能查到
select count(*) from pgbench_accounts;

另开一个终端

# 切换用户
su - postgres 
# 初始化数据-i到pgbench数据库，-s即放大一百倍，默认是十万，一百是一千万条数据。
pgbench -i pgbench -s 100
# 测试命令，-c 模拟的客户数，-t每个客户端跑的事务数目，跑的是默认脚本
pgbench -c 10 -t 100 pgbench
# -n不对表进行清理？ -r报告每个命令的平均延迟时间 -T基准测试的持续时间（秒）。 -P每隔NUM秒显示线程进度报告 -j线程数
pgbench -n -r -T 60 -P 1 -c 64 -j 64 pgbench

自定义测试

建表

--建表
CREATE TABLE test (
    id int8 PRIMARY KEY,
    info text DEFAULT 'tessssssssssssssssssssssssssssssssssssst',
    state int DEFAULT 0,
    crt_time timestamp DEFAULT now(),
    mod_time timestamp DEFAULT now()
);

-- 插入数据
INSERT INTO test
SELECT
    generate_series(1, 10000000);

SELECT * FROM test LIMIT 10;

自定义脚本

\set id random(1,100000000)  
SELECT
    *
FROM
    test
WHERE
    id = :id;

运行自定义的测试脚本

# -f指定文件 
pgbench -M prepared -n -r -P 1 -f ./test2.pgsql -c 2 -j 2 -T 60 pgbench

空间数据测试

参考链接：https://trac.osgeo.org/postgis/wiki/UserWikiRandomPoint

创建函数，在多边形内随机生成多个点

CREATE OR REPLACE FUNCTION RandomPointsInPolygon (geom geometry, num_points integer, srid integer)
    RETURNS SETOF geometry
    AS $BODY$
DECLARE
    target_proportion numeric;
    n_ret integer := 0;
    loops integer := 0;
    x_min float8;
    y_min float8;
    x_max float8;
    y_max float8;
    rpoint geometry;
BEGIN
    -- Get envelope and SRID of source polygon
    SELECT
        ST_XMin (geom),
        ST_YMin (geom),
        ST_XMax (geom),
        ST_YMax (geom),
        ST_SRID (geom) INTO x_min,
        y_min,
        x_max,
        y_max,
        srid;
    -- Get the area proportion of envelope size to determine if a
    -- result can be returned in a reasonable amount of time
    SELECT
        ST_Area (geom) / ST_Area (ST_Envelope (geom)) INTO target_proportion;
    RAISE DEBUG 'geom: SRID %, NumGeometries %, NPoints %, area proportion within envelope %', srid, ST_NumGeometries (geom), ST_NPoints (geom), round(100.0 * target_proportion, 2) || '%';
    IF target_proportion < 0.0001 THEN
        RAISE EXCEPTION 'Target area proportion of geometry is too low (%)', 100.0 * target_proportion || '%';
    END IF;
    RAISE DEBUG 'bounds: % % % %', x_min, y_min, x_max, y_max;
    WHILE n_ret < num_points LOOP
        loops := loops + 1;
        SELECT
            ST_SetSRID (ST_MakePoint (CAST((random() * (x_max - x_min) + x_min) AS DECIMAL(9, 6)), CAST((random() * (y_max - y_min) + y_min) AS DECIMAL(9, 6))), srid) INTO rpoint;
        IF ST_Contains (geom, rpoint) THEN
            n_ret := n_ret + 1;
            RETURN NEXT rpoint;
        END IF;
    END LOOP;
    RAISE DEBUG 'determined in % loops (% efficiency)', loops, round(100.0 * num_points / loops, 2) || '%';
END
$BODY$
LANGUAGE plpgsql
VOLATILE
COST 100 ROWS 1000;

ALTER FUNCTION RandomPointsInPolygon (geometry, integer) OWNER TO postgres;

然后启用空间函数插件，随机生成十万个。

-- 使用postgis插件
CREATE EXTENSION postgis;

-- 查询postgis版本
SELECT
    postgis_full_version ();

-- 删表
DROP TABLE geo_test;

-- 建表
CREATE TABLE IF NOT EXISTS geo_test (
    "id" serial,
    "geom" geometry(point, 4326) --这个为空间点的创建语句
);

-- 建立空间索引
CREATE INDEX geo_test_geom_idx ON geo_test USING GIST (geom);

-- 使用randomPoistsInPolygon函数创建随机生成点
INSERT INTO geo_test (geom)
SELECT
    RandomPointsInPolygon ('POLYGON ((115.25 39.28 ,117.35 39.28 ,117.35 41.03 ,115.25 41.03,115.25 39.28))', 100000, 4326);

SELECT COUNT(*) FROM geo_test;

SELECT
    id,
    ST_AsText (geom)
FROM
    geo_test
LIMIT 10;

自定义脚本

\set id random(1,100000000)  
SELECT
    *
FROM
    test
WHERE
    id = :id;\set longitude random(115.250000,117.359999)
\set latitude random(39.280000,41.039999)
\set radius random(100,10000)

SELECT
    id,
    ST_AsText (geom)
FROM
    geo_test
WHERE
    ST_DWithin (geom::geography, ST_SetSRID (ST_MakePoint (:longitude, :latitude), 4326)::geography, :radius);

其他

pgbench默认脚本

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;

pgsql命令

\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

pgbench参数

-c 数据库客户端数量,可以理解为数据库会话数量(postgres进程数),默认为1

-C 每个事务创建一个连接,由于PG使用进程模型,可以测试频繁Kill/Create进程的性能表现

-D VARNAME=VALUE 通过客户脚本为用户定义变量

-f FILENAME 从文件 FILENAME 读取事务脚本

-j pgbench的工作线程数（默认1）

-i  写事务时间到日志文件

-M{simple|extended|prepared} 给服务器提交查询的协议，simple表示简单查询，就最普通的。
extended扩展查询协议将上述简单查询协议分解为多个步骤，重复利用提高效率。prepared是类似绑定

-n 在测试之前不运行 VACUUM

-N 不更新表 “pgbench_tellers” “pgbench_branches”

-r 报告每条命令(SQL语句)的平均延时

-s NUM 在输出中报告规模因子

-S 执行 SELECT-only 事务

-t NUM 每个客户端运行的事务数（默认：10）

-T 以秒为单位的压测时长

-v 每次测试前执行vacuum命令，避免"垃圾"空间的影响

-p 显示每个进程所需要的时间

scale 乘以 10 万：表示测试数据量

range：表示活跃数据量