kyle=# \d insert_test_log Table "public.insert_test_log" Column | Type | Modifiers --------+---------------+-------------------------------------------------------------- id | integer | not null default nextval('insert_test_log_id_seq'::regclass) day | date | hour | integer | uid | integer | siteid | integer | adid | integer | num | integer | md5 | character(32) | Indexes: "insert_test_log_pk" PRIMARY KEY, btree (id) "insert_test_log_md5_key" UNIQUE, btree (md5) --- "insert_test_log_md5_key" UNIQUE CONSTRAINT, btree (md5)
测试使用插入函数: CREATE OR REPLACE FUNCTION ins_insert_test_log (i_day integer, i_hour integer, i_uid integer, i_siteid integer, i_adid integer, i_num integer) RETURNS integer LANGUAGE plpgsql AS $function$ declare begin insert into insert_test_log (day,hour,uid,siteid,adid,num,md5) values (current_date+i_day,i_hour,i_uid,i_siteid,i_adid,i_num,md5(clock_timestamp()::text)); return 0; exception when others then return 1; end; $function$;
测试使用的pgbench脚本文件: \setrandom i_date -365 365 \setrandom i_hour 1 1999999999 \setrandom i_uid 1 1999999999 \setrandom i_siteid 1 1999999999 \setrandom i_adid 1 1999999999 \setrandom i_num 1 1999999999 select ins_insert_test_log(:i_date,:i_hour,:i_uid,:i_siteid,:i_adid,:i_num);
pgbench测试脚本,同样使用1个连接: #!/bin/bash . /home/postgres/.bash_profile postgres@node1:~/tsql $ pgbench -M prepared -c 1 -f ./t1.sql -j 1 -n -T 60 -p 2011 kyle
测试结果: transaction type: Custom query scaling factor: 1 query mode: prepared number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 205326 tps = 3422.087509 (including connections establishing) tps = 3422.329353 (excluding connections establishing) 每秒处理的事务数是3400多. 数据库服务器90% idle , 负载0.3左右。
来说说单连接和多连接的区别: 从上面的测试来看,PostgreSQL单连接处理的情况下,数据库服务器的资源还很空闲。单条SQL的平均响应时间是1000/3468=0.29毫秒. 如果开2个连接,并且数据库服务器的资源够用的情况下,并且单条SQL平均响应时间趋于不变,每秒应该可以处理一倍的SQL请求=6936。
虽然程序不支持多数据库连接,为了告知他多连接的好处,还是测试一下多连接的情况: postgres@node1:~/tsql $ pgbench -M prepared -c 32 -f ./t1.sql -j 32 -n -T 60 -p 2011 kyle transaction type: Custom query scaling factor: 1 query mode: prepared number of clients: 32 number of threads: 32 duration: 60 s number of transactions actually processed: 291958 tps = 4861.763024 (including connections establishing) tps = 4872.966788 (excluding connections establishing) 开32个连接,数据库服务器90% idle , 负载0.4左右。 每秒处理SQL请求4861次。单条SQL的平均响应时间=1000/(4861/32)=6.6毫秒。 虽然平均SQL响应时间长了,但是总的吞吐量变大了。
另一个应用场景是 : 实时更新URL的访问次数。 做法大概是 update table set uv=uv+1 where md5=$? ; 在测试系统中压力测试,显示PG某些SQL会很慢,长的要好几秒。 对于数据库来说,这是一个比较常见的ROW LOCK等待问题,例如同一个时间段内频繁的更新同一条记录(对于热门访问的URL),因为更新记录需要锁行,就是说一个SESSION在更新某一条记录时,其他SESSION要等待。所以就有可能出现长的要好几秒的情况。
解决这个问题的办法, 1. 提高数据库单条记录更新速度,注意PostgreSQL更新记录的做法是老的记录并没有实际的删除掉,而是修改了行的头部信息,有兴趣的朋友可以看看heaptuple的结构。因此每次不管更新啥,在物理存储上都会多出一条记录,后台VACUUM去回收已经标记为删除并且所有事务都不会看到的记录。当然PG选择这种MVCC模式,也带来了很多好处,比如并发能力非常,可以把DDL封装到事务里面等等。 因此要提高数据库单条UPDATE速度,可以考虑使用另外的MVCC模式的数据库,如 mongoDB , oracle . 2. 缓存访问计数,非实时的更新数据库,就不存在热门URL频繁更新数据库的问题了。
个人还是推荐第二种解决办法。 |