2009年8月31日星期一

[Postgres Story]最近访问用户问题

前几天有个朋友提了一个问题:

应用平台需要统计最近访问的20个用户的信息。

我第一个想到的是用memcache之类的专用的缓冲,以用户名为键,以最后访问时间为值,如 果用户访问比较均匀,限定一个合适的 超时值,查询的时候遍历过滤就好了,虽然不是很 精确,但是实现起来够简单。

答:不可以,领导要求只在数据库端解决。

环境准备

提问的朋友使用的是 MySQL ,这里我用一个 PostgreSQL 8.4(Enterprise DB Postgres Plus Stand Server 8.4) 建立实验环境。操作系统是 Windows 7 RC。

客户端是 Emacs sql-postgres,\timing on。

使用 explain analyze 分析性能消耗。

尝试

那么,最简单的应该是在日志表上查询:

select username, max(logintime)
from log
group by username
order by 2 desc
limit 20;

这里假定的是会话表(log)中有名为 username 的用户标识列,名为 logintime 的时间戳字 段。

但是显然这个查询的性能并不好,它要遍历整个日志表。

首先,我们给日志表加一个聚集索引,使它按插入时间倒排(显然这对插入效率不利)。

create index idx_logintime on login_session(logintime desc);

alter table login_session cluster on idx_logintime;

然后,再进行查询,可见效率有微弱提升。在我本机的 Postgres 8.4 上,通过 explain analyze 可见有不到百分之二的效率上升。我甚至怀疑这仅仅是源自一些随机事件的影响。

换一个角度分析,只要 select max ... group by ... 的模式不改变,就很难有根本的性 能提升,最好的办法仍然是缓存。

改进

有个简单的办法,可以在PG数据库中制造一个缓存表:

  • 首先,直接建立一个 user->timstamp 键值对表。如果用户量不大,可以直接这样:
  create table sessions(username text, seqs serial, logintime timestamp, primary key(username));
  • 然后创建一个触发器函数
create or replace function on_log() returns trigger as $$
begin
if exists(select * from sessions where username=NEW.username) then
update sessions set seqs = nextval('sessions_seqs_seq') where username=NEW.username;
else
insert into sessions(username, logintime) select NEW.username, NEW.logintime;
end if;
if (select count(*) from sessions)> 20 then
delete from sessions where seqs < (select min(seqs) from (select seqs from
sessions order by seqs desc limit 20) as t);
end if;
return NEW;
end;
$$ language plpgsql;

create trigger onlog after insert on log for each row execute procedure on_log();

OK,这样一来,经过反复测试,在我的笔记本上,插入速度基本没有降低(约 2%~ 5%), 而查询"最新的20个用户"这一操作,速度提升了一百七十余倍。几乎可以视作是一个数据库 端的队列缓存了。这个数值是基于 log 表中有五万条数据,在实际应用中日志表十万以上 (按每日切分导出)比比皆是,性能差距会更为显著。

分析

"最新的 20 个用户",这一问题,与"最近5分钟内的用户"此类问题的区别在于,它不能通 过获取当前时间,简单回溯得到有效数据区间。如何避免遍历整个日志表是一个关键问题———— 现代的在线服务系统每日访问日志量动辄数十万,几百上千万的也不罕见,第一种解法明显 不能满足。相比之下,第二种方案不需要干涉日志表的存储排序,不需要建立多余的索引。 通常我们讲触发器速度比较慢,但具体到这里,只是却是一个比较快速高效的实现方案。

如果用户量不大,在几千以内,为用户表建立一个最后登录时间字段,绑定 null last 索 引(可以加上FASTUPDATE=ON),然后利用基本的order by limit就可以得到比较慢意的效 果了。但是对于大型SNS等应用,数十上百万用户也是有可能的(甚至大型企业内部应用, 数万乃至上十万用户的系统也不在少数)。此时,第二种解决方案就理想的多。虽然看起来 在session表上反复进行计算和写入,但是由于session表的数据量非常小,永远只有几十 条,所以计算速度很快。如果对 log 表进行恰当的分区,同时将sessions表存储到另一个 区域(甚至直接缓存到内存),那么对于海量数据,也会有一个稳定的,良好的性能表现。

更进一步的解决方法,则应该是在数据库或应用层服务器环境,建立一个内存中的队列,来 记录这个数据。只要注意并发写入的问题,就可以得到很好的性能。这方面Haskell的STM机 制、我之前用在MSG.Summoner.Trac的旋转锁机制、都可以比较漂亮的解决这一问题。

优化

sessions 表的读写都非常频繁,通常有比较多的 update,,但是数据量基本恒定。应该积极的执行 vacuum。

可以为日志表添加一个 uuid 字段,以便在数据量上升时按 hash 分表。这样可以获得更好 的写入性能。

可以将 session 的主键索引设为 FASTUPDATE=ON 。

触发器中设定

SET LOCAL synchronous_commit TO OFF;

打开 WAL 异步事务提交,可以进一步提高并发写入速度。

更进一步的,可以写一个常驻的守护进程,用应用语言建立一个队列,将这一需求建立为独 立的服务。这个服务可以通过 pl 嵌入语言与数据库联接,也可以直接联接到应用层,有一 些应用层架构依赖于并发的多 fastcgi 实例,此时要注意并发访问冲突和数据同步的问题。

总结

之所以出现这样一个困扰开发人员的问题,根本在于项目领导不提供,也不认同使用灵活、 开放的思维方式解决此问题。服务项目的架构是一个系统工程,类似这样的问题,完全可以 用更开阔的眼光去寻找出路。建立一个简单的缓存队列实例,用perl或python,只需要数十 行。结合Postgres的plperl或plpython等嵌入脚本,可以非常简单的达到目的。甚至不用担 心使用多实例 fastcgi 时,多个应用服务进程争用缓存队列I/O的问题。

即使从方案二出发,通过利用服务器环境的资源,也可以做出更多优化。如把"删除第20之 后的旧数据"这部分脚本,移到 crontab 中,与vacuum 结合执行。会得到更好的效率。

找我请教的这位朋友,使用的不是Postgres,而是MySQL,相对来说很多PG的服务端编程技 巧难以照搬,再加上团队管理的政治问题,我也只有祝他好运了。



--
话题越大,废话越多;名字越火星,问题越脑残。
……

劉鑫
March.Liu

没有评论: