TimesTen 应用层数据库缓存学习：13. 全局数据缓存(cache grid)

本文讲述了TimesTen Global Cache Group的基本概念，并用一个例子促进对概念的理解

本实验环境使用一台虚拟机，包括一个TimesTen instance和一个Oracle Instance。两个TimesTen数据库(cachedb1和cachedb2)共享Oracle(sid=ttorcl)中的数据。
Oracle中的数据来源于HR Schema，可以从$TT_HOME/quickstart/sample_scripts/hrschema中安装

TimesTen数据库的DSN描述如下，唯一需要解释的是CacheAWTParallelism是Data Store的属性，必须在建立时指定：

[cachedb1]
Driver=/home/oracle/TimesTen/tt1122/lib/libtten.so
DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/cachedb1
PermSize=32
TempSize=64
LogFileSize=32
LogBufMB=32
DatabaseCharacterSet=AL32UTF8
OracleNetServiceName=ttorcl
CacheAWTParallelism=4

[cachedb2]
Driver=/home/oracle/TimesTen/tt1122/lib/libtten.so
DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/cachedb2
PermSize=32
TempSize=64
LogFileSize=32
LogBufMB=32
DatabaseCharacterSet=AL32UTF8
OracleNetServiceName=ttorcl
CacheAWTParallelism=4

建立cachedb1中的schema用户

$ ttisql -v1 cachedb1Command> set prompt 'cachedb1> 'cachedb1> create user tthr identified by tthr;User created.cachedb1> grant admin to tthr;

设置cache admin 的用户名和口令，因为TimesTen需要用此用户访问Oracle中的表，相应的，这个用户也必须具备访问Oracle中cached table的权限。注意一下术语，Cache Group中有两个cache 用户，在Oracle中的称为cache administrator, 在TimesTen中的称为cache manager。cached table指Oracle中的表，cache table指TimesTen中对应于cached table的表。
以下两种方法任选其一即可

$ ttadmin -connstr "dsn=cachedb1;uid=tthr;pwd=tthr;oraclepwd=tthr" -cacheUidPwdSet -cacheUid cacheadm -cachePwd cacheadmCache User Id                   : cacheadmRAM Residence Policy            : manualManually Loaded In RAM          : TrueReplication Agent Policy        : manualReplication Manually Started    : FalseCache Agent Policy              : manualCache Agent Manually Started    : False或者$ ttisql "dsn=cachedb1;uid=tthr;pwd=tthr;oraclepwd=tthr"Command> call ttCacheUidPwdSet('cacheadm','cacheadm')

创建cache grid

$ ttisql -v1 -e "set prompt 'cachedb1> '" "dsn=cachedb1;uid=tthr;pwd=tthr;oraclepwd=tthr" cachedb1> call ttGridCreate ('samplegrid');cachedb1> call ttGridInfo;< SAMPLEGRID, CACHEADM, Linux x86-64, 64-bit, 11, 2, 2 >cachedb1> call ttGridNameSet ('samplegrid');

启动cache agent，有两种方法

$ ttisql -v1 -e "set prompt 'cachedb1> '" cachedb1 cachedb1> call ttCacheStart()或者$ ttadmin -cacheStart cachedb1RAM Residence Policy            : manualManually Loaded In RAM          : TrueReplication Agent Policy        : manualReplication Manually Started    : FalseCache Agent Policy              : manualCache Agent Manually Started    : True

创建Global Dynamic Asynchronous Writethrough Cache Group

$ ttisql -v1 -e "set prompt 'cachedb1> '" "dsn=cachedb1;uid=tthr;pwd=tthr;oraclepwd=tthr" cachedb1> create dynamic asynchronous writethrough global cache group g_awt from         > tthr.employees ( employee_id number (6) not null,        >                first_name    varchar2(20),        >                last_name     varchar2(25) not null,        >                email         varchar2(25) not null,        >                phone_number  varchar2(20),        >                hire_date     date not null,        >                job_id        varchar2(10) not null,        >                salary        number (8,2),        >                commission_pct number (2,2),        >                manager_id    number (6),        >                department_id number(4),        >    primary key (employee_id)),        > tthr.job_history (employee_id  number(6) not null,        >                 start_date   date  not null,        >                 end_date     date  not null,        >                 job_id       varchar2(10) not null,        >                 department_id number(4),         >    primary key (employee_id,start_date),        >    foreign key (employee_id)         >    references tthr.employees (employee_id));cachedb1> cachegroups;Cache Group TTHR.G_AWT:  Cache Group Type: Asynchronous Writethrough global (Dynamic)  Autorefresh: No  Aging: LRU on  Root Table: TTHR.EMPLOYEES  Table Type: Propagate  Child Table: TTHR.JOB_HISTORY  Table Type: Propagatecachedb1> cachedb1> repschemes;Replication Scheme TTREP._AWTREPSCHEME:  Element: _1798032                         Type: Table TTHR.EMPLOYEES  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: _ORACLE from TIMESTEN-HOL   Element: _1798048                         Type: Table TTHR.JOB_HISTORY  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: _ORACLE from TIMESTEN-HOL   Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: _ORACLE from TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled

启动Replication Agent，两种方法皆可。
注意，只有当定义了复制scheme后，才可以启动Replication Agent，否则报错：

8191: This store (CACHEDB1 on TIMESTEN-HOL) is not involved in a replication scheme

[oracle@timesten-hol info]$ ttisql -v1 -e "set prompt 'cachedb1> '" cachedb1 cachedb1> call ttRepStart();或者$ ttadmin -repStart cachedb1RAM Residence Policy            : manualManually Loaded In RAM          : TrueReplication Agent Policy        : manualReplication Manually Started    : TrueCache Agent Policy              : manualCache Agent Manually Started    : True

将cachedb1 attach到cache grid

$ ttisql -v1 -e "set prompt 'cachedb1> '" "dsn=cachedb1;uid=tthr;pwd=tthr" cachedb1> call ttGridNodeStatus;cachedb1> call ttGridAttach (1,'cachedb1','localhost',9991);cachedb1> call ttGridNodeStatus;< SAMPLEGRID, 1, 1, T, localhost, SAMPLEGRID_cachedb1_1, 127.0.0.1, 9991, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >

到此为止，我们已建立了一个global cache grid:SAMPLEGRID，而且已经有一个成员cachedb1，接下来我们在添加一个成员cachedb2，整个过程与cachedb1几乎一样

建立cachedb1中的schema用户

$ ttisql -v1 -e "set prompt 'cachedb2> '" cachedb2cachedb2> create user tthr identified by tthr;User created.cachedb2> grant admin to tthr;

设置cache admin账户信息

$ ttisql -v1 "dsn=cachedb2;uid=tthr;pwd=tthr;oraclepwd=tthr"Command> call ttCacheUidPwdSet('cacheadm','cacheadm');

将cachedb2 associate到cache grid
Grid只需创建一次即可，信息存放在Oracle中，因此，cachedb2在设置完cache admin后，就可以查询到grid的信息

$ ttisql -v1 "dsn=cachedb2;uid=tthr;pwd=tthr;oraclepwd=tthr"Command> call ttGridInfo;< SAMPLEGRID, CACHEADM, Linux x86-64, 64-bit, 11, 2, 2 >Command> call ttGridNameSet ('samplegrid');

启动cache agent

$ ttisql -v1 -e "set prompt 'cachedb2> '" cachedb2cachedb2> call ttCacheStart()

创建相同的Global Dynamic Asynchronous Writethrough Cache Group

[oracle@timesten-hol info]$ ttisql -v1 -e "set prompt 'cachedb2> '" "dsn=cachedb2;uid=tthr;pwd=tthr;oraclepwd=tthr"cachedb2> create dynamic asynchronous writethrough global cache group g_awt from         > tthr.employees ( employee_id number (6) not null,        >                first_name    varchar2(20),        >                last_name     varchar2(25) not null,        >                email         varchar2(25) not null,        >                phone_number  varchar2(20),        >                hire_date     date not null,        >                job_id        varchar2(10) not null,        >                salary        number (8,2),        >                commission_pct number (2,2),        >                manager_id    number (6),        >                department_id number(4),        >    primary key (employee_id)),        > tthr.job_history (employee_id  number(6) not null,        >                 start_date   date  not null,        >                 end_date     date  not null,        >                 job_id       varchar2(10) not null,        >                 department_id number(4),         >    primary key (employee_id,start_date),        >    foreign key (employee_id)         >    references tthr.employees (employee_id));cachedb2> cachegroups;Cache Group TTHR.G_AWT:  Cache Group Type: Asynchronous Writethrough global (Dynamic)  Autorefresh: No  Aging: LRU on  Root Table: TTHR.EMPLOYEES  Table Type: Propagate  Child Table: TTHR.JOB_HISTORY  Table Type: Propagatecachedb2> repschemes;Replication Scheme TTREP._AWTREPSCHEME:  Element: _1798032                         Type: Table TTHR.EMPLOYEES  Master Store: CACHEDB2 on TIMESTEN-HOL Transmit Durable  Subscriber Store: _ORACLE from TIMESTEN-HOL   Element: _1798048                         Type: Table TTHR.JOB_HISTORY  Master Store: CACHEDB2 on TIMESTEN-HOL Transmit Durable  Subscriber Store: _ORACLE from TIMESTEN-HOL   Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: _ORACLE from TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled

启动Replication Agent

$ ttisql -v1 cachedb2Command> call ttrepstart();

将cachedb2 attach到SAMPLEGRID:

$ ttisql -v1 -e "set prompt 'cachedb2> '" "dsn=cachedb2;uid=tthr;pwd=tthr;"cachedb2> call ttGridAttach (1,'cachedb2','localhost',9992);cachedb2> call ttGridNodeStatus;< SAMPLEGRID, 1, 1, T, localhost, SAMPLEGRID_cachedb1_1, 127.0.0.1, 9991, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >< SAMPLEGRID, 2, 1, T, localhost, SAMPLEGRID_cachedb2_2, 127.0.0.1, 9992, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >

至此，包含两个grid member的global cache group已经建成，下面可以开始测试了。

一个Oracle数据表可以cache 到多个TimesTen数据库，左边的方式是建立多个独立的local cache group，但这时可能会出现多个TimesTen同时更新一条数据的情形，影响数据一致性；而global cache group可以避免这种情况。
在global cache group中，一个cache instance只能位于一个TimesTen数据库，或者说只能有一个属主，因此，TimesTen数据库越多，可以缓存的数据就阅读，处理能力就越强，这就是所说的扩展性。而左边的情形，一个cache instance可以位于所有的TimesTen数据库，可以有多个属主。

使用以下语句分别登录cachedb1和cachedb2:
ttisql -v1 -e “set prompt ‘cachedb1> ‘” “dsn=cachedb1;uid=tthr;pwd=tthr;oraclepwd=tthr”
ttisql -v1 -e “set prompt ‘cachedb2> ‘” “dsn=cachedb2;uid=tthr;pwd=tthr;oraclepwd=tthr”

T1（cachedb1）：

cachedb1> select count(*) from employees;
< 0 >
cachedb1> load cache group “TTHR”.”G_AWT” where manager_id = 100 commit every 256 rows;
cachedb1> select count(*) from employees;
< 15 >

T2（cachedb2）：

cachedb2> select count(*) from employees;
< 0 >
cachedb2> load cache group “TTHR”.”G_AWT” where manager_id = 101 commit every 256 rows;
cachedb2> select count(*) from employees;
< 5 >
cachedb2> select distinct(employee_id) from employees;
< 108 >
< 200 >
< 203 >
< 204 >
< 205 >

在前两步，cachedb1和cachedb2各自加载了自己的数据，他们的数据交集为空（对于Global Cache Group，交集总是为空），并集为20条数据

T3（cachedb1）
由于employee_id=200的数据位于cachedb2，cachedb1访问此数据时，将其移动到cachedb1

cachedb1> select first_name from employees where employee_id = 200;
< Jennifer >

T4：（cachedb2）

cachedb2> select distinct(employee_id) from employees;
< 108 >
< 203 >
< 204 >
< 205 >
4 rows found.
cachedb2> select * from employees where first_name = ‘Jennifer’;
0 rows found.
此条数据的属主已经变为cachedb1

其它的一些global cache group的操作包括：

执行全局查询（遍历所有grid member）

cachedb2> autocommit 0;
cachedb2> CALL ttOptSetFlag(‘GlobalProcessing’, 1);
cachedb2> select count(*) from employees;
< 20 >
1 row found.
cachedb2> select * from employees where first_name = ‘Jennifer’;
< 200, Jennifer, Whalen, JWHALEN, 515.123.4444, 1987-09-17 00:00:00, AD_ASST, 4400, , 101, 10 >

Global queries with local joins

You can execute a global query with a local join. This means that the
SELECT statement is global (selects across grid members), but the join
result is local (the join resides on the local node).

查询数据的位置信息：

cachedb1> autocommit 0;
cachedb1> CALL ttOptSetFlag(‘GlobalProcessing’, 1);
cachedb1> SELECT employee_id, TTGRIDUSERASSIGNEDNAME(), TTGRIDMEMBERID() FROM employees;
< 70, cachedb1, 1 >
< 101, cachedb1, 1 >
< 102, cachedb1, 1 >
< 114, cachedb1, 1 >
< 120, cachedb1, 1 >
< 121, cachedb1, 1 >
< 122, cachedb1, 1 >
< 123, cachedb1, 1 >
< 124, cachedb1, 1 >
< 145, cachedb1, 1 >
< 146, cachedb1, 1 >
< 147, cachedb1, 1 >
< 148, cachedb1, 1 >
< 149, cachedb1, 1 >
< 200, cachedb1, 1 >
< 201, cachedb1, 1 >
< 108, cachedb2, 2 >
< 203, cachedb2, 2 >
< 204, cachedb2, 2 >
< 205, cachedb2, 2 >

查询执行命令涉及的grid member

$ ttisql -v1 -e “set prompt ‘cachedb2> ‘” “dsn=cachedb2;uid=tthr;pwd=tthr;oraclepwd=tthr”
cachedb2> select TTGRIDNODENAME() from dual;
< SAMPLEGRID_cachedb2_2 >
cachedb2> autocommit 0;
cachedb2> CALL ttOptSetFlag(‘GlobalProcessing’, 1);
cachedb2> select TTGRIDNODENAME() from dual;
< SAMPLEGRID_cachedb2_2 >
< SAMPLEGRID_cachedb1_1 >

一点重要的提示，虽然可以跨节点存取数据，但这毕竟是有开销的，从应用层面还是应该合理的做好数据分区，避免节点间数据的移动。有时，使用多个local cache group来缓存同一数据表，从应用层面来进行数据定向和来保证数据一致性，也是一种可行的方法。

遗留问题：
海量数据采集到Oracle，Oracle无法吸收，这是采用Global AWT Cache Group作为前端来介绍数据，后续再导入Oracle。如何控制导入的时间，导入的速度?

参考：Oracle® TimesTen Application-Tier Database Cache User’s Guide |6.Creating Other Cache Grid Members | Example of data sharing among the grid members