PostgreSQL 与基友们的故事之 - Redis (无限缓存,实时标签...)

在PostgreSQL中直接读写redis。
好处多多，可以降低应用层设计的复杂度，减少交互次数，降低RT。
应用场景举例：
.1. 使用redis作为PostgreSQL的二级缓存， 提升数据库的性能。
例如在生产中有多台redis主机或集群，使用redis扩展PG的二级缓存能力，减少对IO的需求。
这个完全可以做成触发器形式的，数据插入或更新的时候，同时插入或更新到redis。
删除的时候，从redis删除。
用来做二级缓存时，更新从postgresql直接操作redis来减少总的交互次数。
查询时直接查询redis或者从postgresql去查询redis都是可以的，如果是乐观的情况，从redis去查是OK的，如果是悲观的情况则从postgresql去读，同时写入redis.
.2.例如用来计算记录的COUNT数，数据插入或删除的时候，记录自增或自减。
.3. 利用来对数据统计标签，例如数据入库时，按照数据的用户ID以及记录的行为习惯进行标签化，将标签信息直接存入REDIS，作为一个实时标签系统。


当然还有更多的玩法，等待发掘。
PostgreSQL的好基友还真多，redis也是其中一个。

下面简单的介绍一下redis_fdw插件的安装和使用以及目前的限制。
注意redis可以在其他主机上，不要被我的文档局限了。
redis_fdw已经有很多用户在生产中使用，而且PG的fdw接口是非常靠谱的。

建议使用新版的gcc
http://blog.163.com/digoal@126/blog/static/163877040201601313814429/
安装好GCC之后

which gcc
~/gcc4.9.3/bin/gcc

安装和部署redis

cd ~
wget http://download.redis.io/releases/redis-3.0.7.tar.gz

tar -zxvf redis-3.0.7.tar.gz

cd redis-3.0.7

make -j 32
make test

make PREFIX=/home/digoal/redis install

cd /home/digoal/redis
cp ~/redis-3.0.7/redis.conf ./

mkdir /disk1/digoal/redis

vi redis.conf
daemonize yes
pidfile /disk1/digoal/redis/redis.pid
port 6379
tcp-backlog 511
bind 0.0.0.0
timeout 60
tcp-keepalive 60
loglevel notice
logfile "/disk1/digoal/redis/redis.log"
databases 16
save 900 1
save 300 10
save 60 10000
stop-writes-on-bgsave-error yes
rdbcompression yes
rdbchecksum yes
dbfilename dump.rdb
dir /disk1/digoal/redis/
requirepass digoal
maxclients 10000
maxmemory 10gb
maxmemory-policy noeviction
maxmemory-samples 5
appendonly no
appendfilename "appendonly.aof"
appendfsync everysec
no-appendfsync-on-rewrite no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
aof-load-truncated yes
lua-time-limit 5000
slowlog-log-slower-than 10000
slowlog-max-len 128
latency-monitor-threshold 0
notify-keyspace-events ""
hash-max-ziplist-entries 512
hash-max-ziplist-value 64
list-max-ziplist-entries 512
list-max-ziplist-value 64
set-max-intset-entries 512
zset-max-ziplist-entries 128
zset-max-ziplist-value 64
hll-sparse-max-bytes 3000
activerehashing yes
client-output-buffer-limit normal 0 0 0
client-output-buffer-limit slave 256mb 64mb 60
client-output-buffer-limit pubsub 32mb 8mb 60
hz 10
aof-rewrite-incremental-fsync yes

./redis-server ./redis.conf

./redis-cli -h 127.0.0.1 -p 6379 -a digoal

测试redis可用性

127.0.0.1:6379> help
redis-cli 3.0.7
Type: "help @<group>" to get a list of commands in <group>
      "help <command>" for help on <command>
      "help <tab>" to get a list of possible help topics
      "quit" to exit

测试一下redis的性能

cd ~/redis-3.0.7/src
./redis-benchmark -h 127.0.0.1 -p 6379 -n 1000000 -c 32
====== PING_INLINE ======
  1000000 requests completed in 14.35 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69676.70 requests per second

====== PING_BULK ======
  1000000 requests completed in 14.39 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69516.86 requests per second

====== SET ======
  1000000 requests completed in 14.42 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69328.90 requests per second

====== GET ======
  1000000 requests completed in 14.47 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69094.18 requests per second

====== INCR ======
  1000000 requests completed in 14.38 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69521.70 requests per second

====== LPUSH ======
  1000000 requests completed in 14.47 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69118.05 requests per second

====== LPOP ======
  1000000 requests completed in 14.49 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69008.35 requests per second

====== SADD ======
  1000000 requests completed in 14.40 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69458.91 requests per second

====== SPOP ======
  1000000 requests completed in 14.58 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 1 milliseconds
100.00% <= 1 milliseconds
68577.70 requests per second

====== LPUSH (needed to benchmark LRANGE) ======
  1000000 requests completed in 14.40 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69463.74 requests per second

====== LRANGE_100 (first 100 elements) ======
  1000000 requests completed in 13.74 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
72796.09 requests per second

====== LRANGE_300 (first 300 elements) ======
  1000000 requests completed in 14.42 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69328.90 requests per second

====== LRANGE_500 (first 450 elements) ======
  1000000 requests completed in 14.38 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69550.70 requests per second

====== LRANGE_600 (first 600 elements) ======
  1000000 requests completed in 14.43 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
69319.29 requests per second

====== MSET (10 keys) ======
  1000000 requests completed in 14.20 seconds
  32 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
70417.58 requests per second

redis的用法可参考
http://redis.io/commands

假设PostgreSQL已安装好，以PostgreSQL 9.5为例：
redis_fdw有几个分支，请根据PostgreSQL的版本切换分支使用。
安装redis_fdw，需要依赖redis的c接口，hiredis。

cd ~
git clone https://github.com/pg-redis-fdw/redis_fdw.git

cd redis_fdw/
git checkout REL9_5_STABLE

git clone https://github.com/redis/hiredis
cd hiredis
make
make PREFIX=/home/digoal/hiredis_bin install

修改redis_fdw的Makefile

vi Makefile
# 末尾追加

LDFLAGS += -L/home/digoal/hiredis_bin/lib

安装redis_fdw

export PATH=/home/digoal/pgsql9.5.0/bin:$PATH
make USE_PGXS=1
make USE_PGXS=1 install

将redis_fdw部署到需要访问redis的PostgreSQL数据库中

cd ~
$ psql
psql (9.5.0)
Type "help" for help.

postgres=# create extension redis_fdw;
CREATE EXTENSION

测试redis_fdw的用法，创建server，创建foreign table。
通过SQL可以写入和读取redis中的数据。

postgres=# CREATE SERVER redis_server
postgres-#     FOREIGN DATA WRAPPER redis_fdw
postgres-#     OPTIONS (address '127.0.0.1', port '6379');
CREATE SERVER

postgres=# CREATE FOREIGN TABLE redis_db0 (key text, val text)
postgres-#     SERVER redis_server
postgres-#     OPTIONS (database '0');
CREATE FOREIGN TABLE

postgres=#
postgres=# CREATE USER MAPPING FOR PUBLIC
postgres-#     SERVER redis_server
postgres-#     OPTIONS (password 'digoal');
CREATE USER MAPPING

postgres=# CREATE FOREIGN TABLE myredishash (key text, val text[])
postgres-#     SERVER redis_server
postgres-#     OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'mytable:');
CREATE FOREIGN TABLE

postgres=# INSERT INTO myredishash (key, val)
   VALUES ('mytable:r1','{prop1,val1,prop2,val2}');
INSERT 0 1

postgres=# UPDATE myredishash
postgres-#     SET val = '{prop3,val3,prop4,val4}'
postgres-#     WHERE key = 'mytable:r1';
UPDATE 1

postgres=# select * from myredishash ;
    key     |           val
------------+-------------------------
 mytable:r1 | {prop3,val3,prop4,val4}
(1 row)

postgres=# select * from myredishash ;
    key     |           val
------------+-------------------------
 mytable:r1 | {prop3,val3,prop4,val4}
(1 row)

postgres=# DELETE from myredishash
postgres-#     WHERE key = 'mytable:r1';
DELETE 1

postgres=# select * from myredishash ;
 key | val
-----+-----
(0 rows)

postgres=# CREATE FOREIGN TABLE myredis_s_hash (key text, val text)
postgres-#     SERVER redis_server
postgres-#     OPTIONS (database '0', tabletype 'hash',  singleton_key 'mytable');
CREATE FOREIGN TABLE

postgres=# INSERT INTO myredis_s_hash (key, val)
postgres-#    VALUES ('prop1','val1'),('prop2','val2');
INSERT 0 2

postgres=# select * from myredis_s_hash ;
  key  | val
-------+------
 prop1 | val1
 prop2 | val2
(2 rows)

postgres=# UPDATE myredis_s_hash
postgres-#     SET val = 'val23'
postgres-#     WHERE key = 'prop1';
UPDATE 1

postgres=# select * from myredis_s_hash ;
  key  |  val
-------+-------
 prop1 | val23
 prop2 | val2
(2 rows)

postgres=# DELETE from myredis_s_hash
postgres-#     WHERE key = 'prop2';
DELETE 1

postgres=# \set VERBOSITY verbose
postgres=# INSERT INTO myredis_s_hash (key, val)
   VALUES ('prop1','val1'),('prop2','val2');
ERROR:  23505: key already exists: prop1
LOCATION:  redisExecForeignInsert, redis_fdw.c:2158
postgres=# select * from myredis_s_hash ;
  key  |  val
-------+-------
 prop1 | val23
(1 row)

redis_fdw详细的用法介绍
.1. CREATE SERVER 支持的 option
指定地址和端口

address: The address or hostname of the Redis server. Default: 127.0.0.1
port: The port number on which the Redis server is listening. Default: 6379

.2. CREATE USER MAPPING 支持的 option
指定密码

password: The password to authenticate to the Redis server with. Default:

.3. CREATE FOREIGN TABLE 支持的 option
指定数据库ID
表类型(hash,list,set,zset或scalar)
key 前缀 key 集合 singleton_key 指定KEY （三选一）

database: The numeric ID of the Redis database to query. Default: 0
(9.2 and later) tabletype: can be 'hash', 'list', 'set' or 'zset' Default: none, meaning only look at scalar values.
(9.2 and later) tablekeyprefix: only get items whose names start with the prefix Default: none
(9.2 and later) tablekeyset: fetch item names from the named set Default: none
(9.2 and later) singleton_key: get all the values in the table from a single named object. Default: none, meaning don't just use a single object.

You can only have one of tablekeyset and tablekeyprefix, and if you use singleton_key you can't have either.
Structured items are returned as array text, or, if the value column is a text array as an array of values.
In the case of hash objects this array is an array of key, value, key, value ...
Singleton key tables are returned as rows with a single column of text in the case of lists sets and scalars, rows with key and value text columns for hashes, and rows with a value text columns and an optional numeric score column for zsets.

限制
.1. There's no such thing as a cursor in Redis, or MVCC, which leaves us with no way to atomically query the database for the available keys and then fetch each value.
So, we get a list of keys to begin with, and then fetch whatever records still exist as we build the tuples.

如果不带条件的查询，或者创建foreign table时没有指定单一KEY，则需要返回集合
redis_fdw/redis_fdw.c

/ initial cursor /
#define ZERO "0"
/ redis default is 10 - let's fetch 1000 at a time /
#define COUNT " COUNT 1000"

/*
 * redisBeginForeignScan
 *      Initiate access to the database
 */
static void
redisBeginForeignScan(ForeignScanState *node, int eflags)
{
...
    / Execute the query /
    if (festate->singleton_key)
    {
        /*
         * We're not using cursors for now for singleton key tables. The
         * theory is that we don't expect them to be so large in normal use
         * that we would get any significant benefit from doing so, and in any
         * case scanning them in a single step is not going to tie things up
         * like scannoing the whole Redis database could.
         */
    ...
    }
    else if (qual_value && pushdown)
    {
        /*
         * if we have a qual, make sure it's a member of the keyset or has the
         * right prefix if either of these options is specified.
         *
         * If not set row to -1 to indicate failure
         */
         ...
    else
    {
        / no qual - do a cursor scan /
        if (festate->keyset)
        {
            festate->cursor_search_string = "SSCAN %s %s" COUNT;
            reply = redisCommand(context, festate->cursor_search_string,
                                 festate->keyset, ZERO);
        }
        else if (festate->keyprefix)
        {
            festate->cursor_search_string = "SCAN %s MATCH %s*" COUNT;
            reply = redisCommand(context, festate->cursor_search_string,
                                 ZERO, festate->keyprefix);
        }
        else
        {
            festate->cursor_search_string = "SCAN %s" COUNT;
            reply = redisCommand(context, festate->cursor_search_string, ZERO);
        }
    }
...

.2. We can only push down a single qual to Redis, which must use the TEXTEQ operator, and must be on the 'key' column.

时间： 2024-02-28 14:47:16

PostgreSQL 与基友们的故事之 - Redis (无限缓存,实时标签...)的相关文章

Redis作为缓存自我总结（完全转载）

转载的目的是为了方便阅读 Redis作为缓存自我总结 来源:http://doushini.iteye.com/blog/1879616?utm_source=tuicool&utm_medium=referral redis缓存服务器笔记 redis是一个高性能的key-value存储系统,能够作为缓存框架和队列 但是由于他是一个内存内存系统,这些数据还是要存储到数据库中的 作为缓存框架: create/updae/delete---同时存到redis和数据库 query--先从redis查,

Spring Boot 整合 Redis 实现缓存操作

一.缓存的应用场景 二.更新缓存的策略 三.运行 springboot-mybatis-redis 工程案例 四.springboot-mybatis-redis 工程代码配置详解 运行环境: Mac OS 10.12.x JDK 8 + Redis 3.2.8 Spring Boot 1.5.1.RELEASE 一.缓存的应用场景 什么是缓存? 在互联网场景下,尤其2C端大流量场景下,需要将一些经常展现和不会频繁变更的数据,存放在存取速率更快的地方.缓存就是一个存储器,在技术选型中,常用 Re

redis来共享各个服务器的session，并同时通过redis来缓存一些常用的资源，加快用户获得请求资源的速度（转）

    时间过得真快,再次登录博客园来写博,才发现距离上次的写博时间已经过去了一个月了,虽然是因为自己找了实习,但这也说明自己对时间的掌控能力还是没那么的强,哈哈,看来还需不断的努力啊!(这里得特别说明一下本人面试的一些感受:做我们IT这一行,一定要使自己精于某个领域,再不断的去涉猎其他的领域,更重要的是学会找出各个领域的相融点,这跟我们学习书本一样,用'Java'和'计算机网络'来举下例子,我们知道Java中的socket编程,对于面向连接的编程来说(包括我们每次在网页上向服务器请求资源时),

Nginx配置srcache_nginx模块搭配Redis建立缓存系统_nginx

1. nginx模块 --add-module=../modules/ngx_devel_kit-0.2.18 --add-module=../modules/set-misc-nginx-module-0.22rc8 --add-module=../modules/srcache-nginx-module-0.22 --add-module=../modules/redis-nginx-module-0.3.6 --add-module=../modules/redis2-nginx-modu

高并发简单解决方案————redis队列缓存+mysql 批量入库(ThinkPhP)

源码地址:https://github.com/Tinywan/PHP_Experience 问题分析 问题一:要求日志最好入库:但是,直接入库mysql确实扛不住,批量入库没有问题,done.[批量入库和直接入库性能差异] 问题二:批量入库就需要有高并发的消息队列,决定采用redis list 仿真实现,而且方便回滚. 问题三:日志量毕竟大,保存最近30条足矣,决定用php写个离线统计和清理脚本. 一.设计数据库表和存储 考虑到log系统对数据库的性能更多一些,稳定性和安全性没有那么高,存储引

redis之（十一）redis实现缓存的功能

［一］redis实现缓存的原理 --->利用键的失效时间设置实现缓存技术 --->由于redis的内存有限,可以在redis的配置文件里设置maxmemory的参数.来限制redis最大可用内存的大小.(单位是字节),当超出这个限制的时候,redis可以依据maxmemory-policy参数指定的策略来删除不需要的键直到redis占用的内存小于指定内存 --->LRU(least Recently Used)最近最少使用 --->事实上redis并不回准确低将整个数据库中最久未被

Redis的缓存策略和主键失效机制

作为缓存系统都要定期清理无效数据,就需要一个主键失效和淘汰策略. >>EXPIRE主键失效机制 在Redis当中,有生存期的key被称为volatile, 在创建缓存时,要为给定的key设置生存期,当key过期的时候(生存期为0),它可能会被删除. (1)影响生存时间的一些操作 生存时间可以通过使用 DEL 命令来删除整个 key 来移除,或者被 SET 和 GETSET 命令覆盖原来的数据, 也就是说,修改key对应的value和使用另外相同的key和value来覆盖以后,当前数据的生存时间

ASP.NET使用Redis共享缓存示例

第一步:下载程序   在https://github.com/MSOpenTech/redis/releases下载安装服务器端.   基本上就是下一步.下一步的,注意:安装过程会询问是否添加环境变量,是否添加防火墙例外规则,这两个都勾上. 第二步:安装服务   1.运行Win+R,输入cmd,调出命令行工具:   2.输入:cd C:\Program Files\Redis 切换工作目录到Redis安装目录:   3.输入:redis-server.exe --service-install

【高并发简单解决方案】redis队列缓存 + mysql 批量入库 + php离线整合

需求背景:有个调用统计日志存储和统计需求,要求存储到mysql中:存储数据高峰能达到日均千万,瓶颈在于直接入库并发太高,可能会把mysql干垮. 问题分析 思考:应用网站架构的衍化过程中,应用最新的框架和工具技术固然是最优选择:但是,如果能在现有的框架的基础上提出简单可依赖的解决方案,未尝不是一种提升自我的尝试. 解决: 问题一:要求日志最好入库:但是,直接入库mysql确实扛不住,批量入库没有问题,done.[批量入库和直接入库性能差异参考文章] 问题二:批量入库就需要有高并发的消息队列,决定