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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 在PostgreSQL中直接读写REDIS。好处多多,可以降低应用层设计的复杂度,减少交互次数,降低RT。同时还能使用redis作为PostgreSQL的二级缓存, 提升数据库的性能。PostgreSQL的好基友还真多,redis也是其中一个。 下面简单的介绍一下redis_fdw插件的安装和

在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,作为一个实时标签系统。
1
2
当然还有更多的玩法,等待发掘。
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.

相关实践学习
基于Redis实现在线游戏积分排行榜
本场景将介绍如何基于Redis数据库实现在线游戏中的游戏玩家积分排行榜功能。
云数据库 Redis 版使用教程
云数据库Redis版是兼容Redis协议标准的、提供持久化的内存数据库服务,基于高可靠双机热备架构及可无缝扩展的集群架构,满足高读写性能场景及容量需弹性变配的业务需求。 产品详情:https://www.aliyun.com/product/kvstore &nbsp; &nbsp; ------------------------------------------------------------------------- 阿里云数据库体验:数据库上云实战 开发者云会免费提供一台带自建MySQL的源数据库&nbsp;ECS 实例和一台目标数据库&nbsp;RDS实例。跟着指引,您可以一步步实现将ECS自建数据库迁移到目标数据库RDS。 点击下方链接,领取免费ECS&amp;RDS资源,30分钟完成数据库上云实战!https://developer.aliyun.com/adc/scenario/51eefbd1894e42f6bb9acacadd3f9121?spm=a2c6h.13788135.J_3257954370.9.4ba85f24utseFl
相关文章
|
2天前
|
缓存 NoSQL 中间件
【后端面经】【缓存】36|Redis 单线程:为什么 Redis 用单线程而 Memcached 用多线程?epoll、poll和select + Reactor模式
【5月更文挑战第19天】`epoll`、`poll`和`select`是Linux下多路复用IO的三种方式。`select`需要主动调用检查文件描述符,而`epoll`能实现回调,即使不调用`epoll_wait`也能处理就绪事件。`poll`与`select`类似,但支持更多文件描述符。面试时,重点讲解`epoll`的高效性和`Reactor`模式,该模式包括一个分发器和多个处理器,用于处理连接和读写事件。Redis采用单线程模型结合`epoll`的Reactor模式,确保高性能。在Redis 6.0后引入多线程,但基本原理保持不变。
19 2
|
3天前
|
缓存 NoSQL Redis
【后端面经】【缓存】36|Redis 单线程:为什么 Redis 用单线程而 Memcached 用多线程?--epoll调用和中断
【5月更文挑战第18天】`epoll`包含红黑树和就绪列表,用于高效管理文件描述符。关键系统调用有3个:`epoll_create()`创建epoll结构,`epoll_ctl()`添加/删除/修改文件描述符,`epoll_wait()`获取就绪文件描述符。`epoll_wait()`可设置超时时间(-1阻塞,0立即返回,正数等待指定时间)。当文件描述符满足条件(如数据到达)时,通过中断机制(如网卡或时钟中断)更新就绪列表,唤醒等待的进程。
30 6
|
4天前
|
NoSQL Redis 缓存
【后端面经】【缓存】36|Redis 单线程:为什么 Redis 用单线程而 Memcached 用多线程?
【5月更文挑战第17天】Redis常被称为单线程,但实际上其在处理命令时采用单线程,但在6.0后IO变为多线程。持久化和数据同步等任务由额外线程处理,因此严格来说Redis是多线程的。面试时需理解Redis的IO模型,如epoll和Reactor模式,以及其内存操作带来的高性能。Redis使用epoll进行高效文件描述符管理,实现高性能的网络IO。在讨论Redis与Memcached的线程模型差异时,应强调Redis的单线程模型如何通过内存操作和高效IO实现高性能。
32 7
【后端面经】【缓存】36|Redis 单线程:为什么 Redis 用单线程而 Memcached 用多线程?
|
7天前
|
缓存 NoSQL 关系型数据库
【Redis】Redis 缓存重点解析
【Redis】Redis 缓存重点解析
22 0
|
7天前
|
缓存 NoSQL 关系型数据库
【Redis】Redis作为缓存
【Redis】Redis作为缓存
8 0
|
7天前
|
存储 缓存 监控
利用Redis构建高性能的缓存系统
在现今高负载、高并发的互联网应用中,缓存系统的重要性不言而喻。Redis,作为一款开源的、内存中的数据结构存储系统,它可以用作数据库、缓存和消息代理。本文将深入探讨Redis的核心特性,以及如何利用Redis构建高性能的缓存系统,并通过实际案例展示Redis在提升系统性能方面的巨大潜力。
|
7天前
|
存储 缓存 NoSQL
【技术分享】求取列表需求的redis缓存方案
【技术分享】求取列表需求的redis缓存方案
35 0
|
10月前
|
SQL Cloud Native 关系型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
800 1
|
10月前
|
数据可视化 关系型数据库 MySQL
将 PostgreSQL 迁移到 MySQL 数据库
将 PostgreSQL 迁移到 MySQL 数据库
1123 2
|
9月前
|
SQL 存储 自然语言处理
玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
在当今社交媒体的时代,人们通过各种平台分享自己的生活、观点和情感。然而,对于平台管理员和品牌经营者来说,了解用户的情感和意见变得至关重要。为了帮助他们更好地了解用户的情感倾向,我们可以使用PostgreSQL中的pg_jieba插件对这些发帖进行分词和情感分析,来构建一个社交媒体情感分析系统,系统将根据用户的发帖内容,自动判断其情感倾向是积极、消极还是中性,并将结果存储在数据库中。
玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词

相关产品

  • 云原生数据库 PolarDB