postgresql测试的简单介绍

如何用 sysbench 并行装载 PostgreSQL 测试数据

sysbench原来自带的lua数据装载脚本是使用以下方式串行装载的，速度比较慢(比单条insert快，但是比COPY慢)。

insert into table1 values (),(),()....

insert into table2 values (),(),()....

...

insert into tablen values (),(),()....

使用prepare导入数据的用法举例

./sysbench_pg --test=lua/oltp.lua --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=1921 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=postgres --oltp-tables-count=64 --oltp-table-size=1000000 --num-threads=64 prepare

prepare 表示装载数据，但是它串行的。

sysbench0.5中可以在命令行中指定测试时启动的并行线程数，这个测试过程是使用run命令，而且是多线程并发的，所以我们可以使用sysbench的run命令来造数据，而不再使用其提供的prepare命令的方法来造数据。run命令会根据命令行参数--num-threads来指定并发线程数的多少。

在sysbench中自定义的lua脚本中要求实现以下几个函数：

function thread_init(thread_id): 此函数在线程创建后只被执行一次

function event(thread_id): 每执行一次就会被调用一次。

由上可以知道，本次造数据的脚本我们只需要实现thread_init()函数就可以了。

生成测试数据的脚本沿用老唐提供的代码：

#include stdio.h

#include stdlib.h

#include time.h

#include stdint.h

#include sys/time.h

uint64_t my_rand(struct random_data * r1, struct random_data * r2)

{

uint64_t rand_max = 100000000000LL;

uint64_t result;

uint32_t u1, u2;

random_r(r1, u1);

random_r(r2, u2);

result = (int64_t)u1 * (int64_t)u2;

result = result % rand_max;

return result;

}

int main(int argc, char *argv[])

{

struct timeval tpstart;

struct random_data r1, r2;

int i;

int r;

int max_value;

char rand_state1[128];

char rand_state2[128];

if (argc !=2)

{

printf("Usage: %s rownums\n", argv[0]);

return 1;

}

max_value = atoi(argv[1]);

gettimeofday(tpstart,NULL);

initstate_r(tpstart.tv_usec,rand_state1,sizeof(rand_state1),r1);

srandom_r(tpstart.tv_usec, r1);

gettimeofday(tpstart,NULL);

initstate_r(tpstart.tv_usec,rand_state2,sizeof(rand_state1),r2);

srandom_r(tpstart.tv_usec, r2);

for (i=1; imax_value+1; i++)

{

r = my_rand(r1, r2) % max_value;

printf("%d,%d,%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu,%011llu-%011llu-%011llu-%011llu-%011llu\n",

i,

r,

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2),

my_rand(r1, r2)

);

}

return 0;

}

编译此C语言程序的方法如下：

gcc gendata.c -o gendata

新建一个copy.lua的脚本，内容如下

调用 common.lua中的 set_vars() 继承来自 common.lua 的全局变量。

函数 copydata(table_id) ： 创建表，创建管道，将管道数据传输到psql -c "copy ..."客户端的方式导入数据。

函数 create_index(table_id) ： 创建索引，调整SEQUENCE next val。

注意咯, oltp_tables_count 必须是 num_threads 的倍数，在 thread_init 中， 以num_threads 为步调，以thread_id+1为起始值，设置i的值，并调用copydata(table_id)和create_index(table_id)。

$ vi lua/copy.lua

pathtest = string.match(test, "(.*/)") or ""

dofile(pathtest .. "common.lua")

function copydata(table_id)

local query

query = [[

CREATE UNLOGGED TABLE sbtest]] .. table_id .. [[ (

id SERIAL NOT NULL,

k INTEGER,

c CHAR(120) DEFAULT '' NOT NULL,

pad CHAR(60) DEFAULT '' NOT NULL,

PRIMARY KEY (id)

) ]]

db_query(query)

os.execute ('export PGPASSWORD=' .. pgsql_password)

os.execute ('rm -f sbtest' .. table_id .. '.dat')

os.execute ('mknod sbtest' .. table_id .. '.dat p')

os.execute ('./gendata ' .. oltp_table_size .. ' sbtest'..table_id ..'.dat ')

os.execute ('cat sbtest' .. table_id .. '.dat | psql -h ' .. pgsql_host .. ' -p ' .. pgsql_port .. ' -U ' .. pgsql_user .. ' -d ' .. pgsql_db .. ' -c "copy sbtest' .. table_id .. ' from stdin with csv"')

os.execute ('rm -f sbtest' .. table_id .. '.dat')

end

function create_index(table_id)

db_query("select setval('sbtest" .. table_id .. "_id_seq', " .. (oltp_table_size+1) .. ")" )

db_query("CREATE INDEX k_" .. table_id .. " on sbtest" .. table_id .. "(k)")

end

function thread_init(thread_id)

set_vars()

print("thread prepare"..thread_id)

for i=thread_id+1, oltp_tables_count, num_threads do

copydata(i)

create_index(i)

end

end

function event(thread_id)

os.exit()

end

用法，必须把psql放到路径中，因为lua中需要用到psql命令

export PATH=/home/digoal/pgsql9.5/bin:$PATH

生成数据，速度比以前快多了

./sysbench_pg --test=lua/copy.lua \

--db-driver=pgsql \

--pgsql-host=127.0.0.1 \

--pgsql-port=1921 \

--pgsql-user=postgres \

--pgsql-password=postgres \

--pgsql-db=postgres \

--oltp-tables-count=64 \

--oltp-table-size=1000000 \

--num-threads=64 \

run

清除数据, drop table

./sysbench_pg --test=lua/copy.lua \

--db-driver=pgsql \

--pgsql-host=127.0.0.1 \

--pgsql-port=1921 \

--pgsql-user=postgres \

--pgsql-password=postgres \

--pgsql-db=postgres \

--oltp-tables-count=64 \

--oltp-table-size=1000000 \

--num-threads=64 \

cleanup

lua全局变量代码：

sysbench/scripting/lua/src/lua.h:#define lua_register(L,n,f) (lua_pushcfunction(L, (f)), lua_setglobal(L, (n)))

sysbench/scripting/lua/src/lua.h:#define lua_setglobal(L,s) lua_setfield(L, LUA_GLOBALSINDEX, (s))

sysbench/scripting/lua/src/lbaselib.c: lua_setglobal(L, "_G");

sysbench/scripting/lua/src/lbaselib.c: lua_setglobal(L, "_VERSION"); /* set global _VERSION */

sysbench/scripting/lua/src/lbaselib.c: lua_setglobal(L, "newproxy"); /* set global `newproxy' */

sysbench/scripting/script_lua.c: lua_setglobal(state, opt-name);

sysbench/scripting/script_lua.c: lua_setglobal(state, "sb_rand");

sysbench/scripting/script_lua.c: lua_setglobal(state, "sb_rand_uniq");

sysbench/scripting/script_lua.c: lua_setglobal(state, "sb_rnd");

sysbench/scripting/script_lua.c: lua_setglobal(state, "sb_rand_str");

sysbench/scripting/script_lua.c: lua_setglobal(state, "sb_rand_uniform");

sysbench/scripting/script_lua.c: lua_setglobal(state, "sb_rand_gaussian");

sysbench/scripting/script_lua.c: lua_setglobal(state, "sb_rand_special");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_connect");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_disconnect");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_query");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_bulk_insert_init");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_bulk_insert_next");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_bulk_insert_done");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_prepare");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_bind_param");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_bind_result");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_execute");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_close");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_store_results");

sysbench/scripting/script_lua.c: lua_setglobal(state, "db_free_results");

sysbench/scripting/script_lua.c: lua_setglobal(state, "DB_ERROR_NONE");

sysbench/scripting/script_lua.c: lua_setglobal(state, "DB_ERROR_DEADLOCK");

sysbench/scripting/script_lua.c: lua_setglobal(state, "DB_ERROR_FAILED");

sysbench/scripting/script_lua.c: lua_setglobal(L, "db_driver");

传入参数，可以把sysbench_pg的参数-替换成_在lua脚本中使用这些变量，例子

--pgsql-host=127.0.0.1 - 对应lua中的变量名 pgsql_host

--pgsql-port=1921 - 对应lua中的变量名 pgsql_port

--pgsql-user=postgres - 对应lua中的变量名 pgsql_user

--pgsql-password=postgres - 对应lua中的变量名 pgsql_password

--pgsql-db=postgres - 对应lua中的变量名 pgsql_db

--oltp-tables-count=64 - 对应lua中的变量名 oltp_tables_count

--oltp-table-size=1000000 - 对应lua中的变量名 oltp_table_size

--num-threads=64 - 对应lua中的变量名 num_threads

相关问答

Q1: postgresql 建立索引

一、索引的类型：

PostgreSQL提供了多种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。

1. B-Tree:

CREATE TABLE test1 (

id integer,

content varchar

);

CREATE INDEX test1_id_index ON test1 (id);

B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符" 、=和"作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询，如LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE 'foo%'或col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。

2. Hash：

CREATE INDEX name ON table USING hash (column);

散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。

这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。

3. GiST：

GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。

4. GIN：

GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的操作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型，如：、=、等。

二、复合索引：

PostgreSQL中的索引可以定义在数据表的多个字段上，如：

CREATE TABLE test2 (

major int,

minor int,

name varchar

}

CREATE INDEX test2_mm_idx ON test2 (major, minor);

1. B-Tree类型的复合索引：

在B-Tree类型的复合索引中，该索引字段的任意子集均可用于查询条件，不过，只有当复合索引中的第一个索引字段(最左边)被包含其中时，才可以获得最高效率。

2. GiST类型的复合索引：

在GiST类型的复合索引中，只有当第一个索引字段被包含在查询条件中时，才能决定该查询会扫描多少索引数据，而其他索引字段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值，那么此时应用GiST索引就会比较低效。

3. GIN类型的复合索引：

与B-Tree和GiST索引不同的是，GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响，无论是哪种组合，都会得到相同的效率。

使用复合索引应该谨慎。在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间。除非表的使用模式非常固定，否则超过三个字段的索引几乎没什么用处。

三、组合多个索引：

PostgreSQL可以在查询时组合多个索引(包括同一索引的多次使用)，来处理单个索引扫描不能实现的场合。与此同时，系统还可以在多个索引扫描之间组成AND和OR的条件。比如，一个类似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查询，可以被分解成四个独立的基于x字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果OR在一起并生成最终的结果。另外一个例子是，如果我们在x和y上分别存在独立的索引，那么一个类似WHERE x = 5 AND y = 6的查询，就会分别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行AND操作并生成最终的结果行。

为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个BITMAP，它将给出索引扫描出的数据在数据表中的物理位置。然后，再根据查询的需要，把这些位图进行AND或者OR的操作并得出最终的BITMAP。最后，检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原来的索引的排序都将消失。如果查询中有ORDER BY子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即使有多个索引可用也会如此。

四、唯一索引：

CREATE UNIQUE INDEX name ON table (column [, ...]);

五、表达式索引：

表达式索引主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较的情况，如：

SELECT * FROM test1 WHERE lower(col1) = 'value';

此时，如果我们仅仅是在col1字段上建立索引，那么该查询在执行时一定不会使用该索引，而是直接进行全表扫描。如果该表的数据量较大，那么执行该查询也将会需要很长时间。解决该问题的办法非常简单，在test1表上建立基于col1字段的表达式索引，如：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

和上面的例子一样，尽管我们可能会为first_name和last_name分别创建独立索引，或者是基于这两个字段的复合索引，在执行该查询语句时，这些索引均不会被使用，该查询能够使用的索引只有我们下面创建的表达式索引。

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

CREATE INDEX命令的语法通常要求在索引表达式周围书写圆括弧，就像我们在第二个例子里显示的那样。如果表达式只是一个函数调用，那么可以省略，就像我们在第一个例子里显示的那样。

从索引维护的角度来看，索引表达式要相对低效一些，因为在插入数据或者更新数据的时候，都必须为该行计算表达式的结果，并将该结果直接存储到索引里。然而在查询时，PostgreSQL就会把它们看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于简单索引的查询。通常而言，我们只是应该在检索速度比插入和更新速度更重要的场景下使用表达式索引。

六、部分索引：

部分索引(partial index)是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索引只包含表中那些满足这个谓词的行。

由于不是在所有的情况下都需要更新索引，因此部分索引会提高数据插入和数据更新的效率。然而又因为部分索引比普通索引要小，因此可以更好的提高确实需要索引部分的查询效率。见以下三个示例：

1. 索引字段和谓词条件字段一致：

CREATE INDEX access_log_client_ip_ix ON access_log(client_ip)

WHERE NOT (client_ip inet '192.168.100.0' AND client_ip inet '192.168.100.255');

下面的查询将会用到该部分索引：

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

下面的查询将不会用该部分索引：

一个不能使用这个索引的查询可以是

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

2. 索引字段和谓词条件字段不一致：

PostgreSQL支持带任意谓词的部分索引，唯一的约束是谓词的字段也要来自于同样的数据表。注意，如果你希望你的查询语句能够用到部分索引，那么就要求该查询语句的条件部分必须和部分索引的谓词完全匹配。 准确说，只有在PostgreSQL能够识别出该查询的WHERE条件在数学上涵盖了该索引的谓词时，这个部分索引才能被用于该查询。

CREATE INDEX orders_unbilled_index ON orders(order_nr) WHERE billed is not true;

下面的查询一定会用到该部分索引：

SELECT * FROM orders WHERE billed is not true AND order_nr 10000;

那么对于如下查询呢？

SELECT * FROM orders WHERE billed is not true AND amount 5000.00;

这个查询将不像上面那个查询这么高效，毕竟查询的条件语句中没有用到索引字段，然而查询条件"billed is not true"却和部分索引的谓词完全匹配，因此PostgreSQL将扫描整个索引。这样只有在索引数据相对较少的情况下，该查询才能更有效一些。

下面的查询将不会用到部分索引。

SELECT * FROM orders WHERE order_nr = 3501;

3. 数据表子集的唯一性约束：

CREATE TABLE tests (

subject text,

target text,

success boolean,

...

);

CREATE UNIQUE INDEX tests_success_constraint ON tests(subject, target) WHERE success;

该部分索引将只会对success字段值为true的数据进行唯一性约束。在实际的应用中，如果成功的数据较少，而不成功的数据较多时，该实现方法将会非常高效。

七、检查索引的使用：

见以下四条建议：

1. 总是先运行ANALYZE。

该命令将会收集表中数值分布状况的统计。在估算一个查询返回的行数时需要这个信息，而规划器则需要这个行数以便给每个可能的查询规划赋予真实的开销值。如果缺乏任何真实的统计信息，那么就会使用一些缺省数值，这样肯定是不准确的。因此，如果还没有运行ANALYZE就检查一个索引的使用状况，那将会是一次失败的检查。

2. 使用真实的数据做实验。

用测试数据填充数据表，那么该表的索引将只会基于测试数据来评估该如何使用索引，而不是对所有的数据都如此使用。比如从100000行中选1000行，规划器可能会考虑使用索引，那么如果从100行中选1行就很难说也会使用索引了。因为100行的数据很可能是存储在一个磁盘页面中，然而没有任何查询规划能比通过顺序访问一个磁盘页面更加高效了。与此同时，在模拟测试数据时也要注意，如果这些数据是非常相似的数据、完全随机的数据，或按照排序顺序插入的数据，都会令统计信息偏离实际数据应该具有的特征。

3. 如果索引没有得到使用，那么在测试中强制它的使用也许会有些价值。有一些运行时参数可以关闭各种各样的查询规划。

4. 强制使用索引用法将会导致两种可能：一是系统选择是正确的，使用索引实际上并不合适，二是查询计划的开销计算并不能反映现实情况。这样你就应该对使用和不使用索引的查询进行计时，这个时候EXPLAIN ANALYZE命令就很有用了。

Q2: 测试MySQL和Postgresql数据库查询性能怎么做到单线程和多线程查询

可以用多进程模拟。如果用批处理脚本的话。

看你怎么测。

如果使用jdbc程序段，多线程确实可以模拟。一个线程一个连接。

设计好标准的数据集。网上或许有下载的。记录好测试环境和测试各个阶段所花时间。

Q3: 如何测试很多个用户同时查询postgresql数据库时的效率

一、使用EXPLAIN：

PostgreSQL为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响是极为关键的。PostgreSQL本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。

PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树，其中最底层的节点为表扫描节点，用于从数据表中返回检索出的数据行。然而，不同

的扫描节点类型代表着不同的表访问模式，如：顺序扫描、索引扫描，以及位图索引扫描等。如果查询仍然需要连接、聚集、排序，或者是对原始行的其它操作，那

么就会在扫描节点"之上"有其它额外的节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。EXPLAIN将为规划树中的每

个节点都输出一行信息，显示基本的节点类型和规划器为执行这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计，这个数

值就是规划器试图最小化的数值。

这里有一个简单的例子，如下：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1;

QUERY PLAN

-------------------------------------------------------------

Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)

EXPLAIN引用的数据是：

1). 预计的启动开销(在输出扫描开始之前消耗的时间，比如在一个排序节点里做排续的时间)。

2). 预计的总开销。

3). 预计的该规划节点输出的行数。

4). 预计的该规划节点的行平均宽度(单位：字节)。

这里开销(cost)的计算单位是磁盘页面的存取数量，如1.0将表示一次顺序的磁盘页面读取。其中上层节点的开销将包括其所有子节点的开销。这里的输出

行数(rows)并不是规划节点处理/扫描的行数，通常会更少一些。一般而言，顶层的行预计数量会更接近于查询实际返回的行数。

现在我们执行下面基于系统表的查询：

复制代码 代码如下:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

从查询结果中可以看出tenk1表占有358个磁盘页面和10000条记录，然而为了计算cost的值，我们仍然需要知道另外一个系统参数值。

复制代码 代码如下:

postgres=# show cpu_tuple_cost;

cpu_tuple_cost

----------------

0.01

(1 row)

cost = 358(磁盘页面数) + 10000(行数) * 0.01(cpu_tuple_cost系统参数值)

下面我们再来看一个带有WHERE条件的查询规划。

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 7000;

QUERY PLAN

------------------------------------------------------------

Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)

Filter: (unique1 7000)

EXPLAIN的输出显示，WHERE子句被当作一个"filter"应用，这表示该规划节点将扫描表中的每一行数据，之后再判定它们是否符合过滤的条

件，最后仅输出通过过滤条件的行数。这里由于WHERE子句的存在，预计的输出行数减少了。即便如此，扫描仍将访问所有10000行数据，因此开销并没有

真正降低，实际上它还增加了一些因数据过滤而产生的额外CPU开销。

上面的数据只是一个预计数字，即使是在每次执行ANALYZE命令之后也会随之改变，因为ANALYZE生成的统计数据是通过从该表中随机抽取的样本计算的。

如果我们将上面查询的条件设置的更为严格一些的话，将会得到不同的查询规划，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 100;

QUERY PLAN