Alibaba DBA Team

关于大数据量模糊查询的方法

July 21, 2008 – 11:39 pm

说实话，在ITPUB上也好，CNOUG上也好，看到很多人问数据模糊查询的问题，特别是一张表的数据量在200M以后，你的查询速度越来越慢。

其实最最简单的就是 like ‘%xx%’，但是这样效率极差，纯粹在拼机器的IO了。

其实在我的日常工作中，对于模糊查询，主要使用以下几种方案提高效率：

A：全文索引

Oracle自带的，SQL SERVER,MYSQL也有这个功能。它的主要实现方式是拆字。记得“玉面飞龙”有一篇文章介绍ORACLE全文索引的，介绍的很详细。

但是全文索引也有很多不足，如：繁体字，孤僻字的支持不好；内部BUG比较多；自身维护的代价和成本很高；新来的开发搞不懂全文索引查询语法；

目前也就少数几个数据库在用全文索引了，打算在不远的将来，将全文索引拿下线了。全部使用Search实现。

B：搜索引擎

其实alibaba集团全部使用的搜索技术，叫“ISEARCH”；100%的知识产权归阿里巴巴所有。

其实在业界最有名的搜索引擎叫“Lunce”，它不仅仅支持JAVA,也支持.NET。

使用搜索最大的好处，它是可以线性扩展的，不像我们的DB。此外我介绍一下搜索引擎大致过程：

数据库有张LOG表，记录变化过的数据》》搜索引擎根据LOG表的数据，DUMP出一份BUILD数据》》把DUMP的东西分发到各个集群》》每台机器再把这个应用到索引文件》》APCHE提供WEB SERVICE给前台

大致过程就这样。至于索引文件本身是个二进制文件。

我觉得搜索引擎本身的实现机制和ORACLE的全文索引类似，也是根据分词器来分词。如果去查找也是根据一个个字Hash mapping出来的，然后根据位置先后次序，找出哪些是符合的。

By vogts Posted in 大话技术 | No Comments »

ASM 磁盘组也可以当成AIX的VG来用

July 21, 2008 – 8:50 am

当多台AIX连接相同的SAN存储时，存储lun可以同时输出到各个主机，这样VG可以在各个主机自由的切换，
为拷贝转移文件提供了极大的便利，现在发现ASM也可以这么做

我们的RAC环境是4个linux接点加一个linux standby
存储输出是4个lun ，disk[1,2,3,4],每个512G,每个lun都同时输出到5台主机，
其中disk1,disk2作为4个RAC接点的共享磁盘，做了裸设备，并创建了asm磁盘组 mydiskgroup
disk3,disk4作为standby的磁盘，也做了asm磁盘组 diskgroupstb

现在rac里面已经导入了点数据，数据库有400G，要做standby，走网络拷贝到备机上感觉太慢了，就试了下上面的想法

1.备机上卸载磁盘组diskgroupstb
@>shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown

2.4个rac接点的ASM参数文件修改asm_diskstring
@>alter system set asm_diskstring=’/opt/oracle/oradata/myasm/disk[1,2,3,4]‘ scope = spfile;
System altered.

3.重起asm，并挂上diskgroupstb
@>startup force;
ASM instance started

Total System Global Area 283930624 bytes
Fixed Size                  2143704 bytes
Variable Size             256621096 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
@>show parameter string

NAME                                 TYPE        VALUE
———————————— ———– ——————————
asm_diskstring                       string      /opt/oracle/oradata/myasm/disk
                                                 [1,2,3,4]
@>alter diskgroup diskgroupstb mount;

Diskgroup altered.

ASMCMD> ls -l
State    Type    Rebal Name
MOUNTED EXTERN N      DISKGROUPSTB/
MOUNTED EXTERN N      MYDISKGROUP/

4.开始备份
alter database create standby controlfile as ‘+DISKGROUPSTB/datafile/control01.ctl’;

run
{
copy datafile ‘+MYDISKGROUP/datafile/system01.dbf’         to ‘+DISKGROUPSTB/datafile/system01.dbf’       ;
copy datafile ‘+MYDISKGROUP/datafile/sysaux01.dbf’         to ‘+DISKGROUPSTB/datafile/sysaux01.dbf’       ;
copy datafile ‘+MYDISKGROUP/datafile/undotbs01.dbf’        to ‘+DISKGROUPSTB/datafile/undotbs01.dbf’      ;
copy datafile ‘+MYDISKGROUP/datafile/undotbs2.dbf’         to ‘+DISKGROUPSTB/datafile/undotbs2.dbf’       ;
copy datafile ‘+MYDISKGROUP/datafile/undotbs3.dbf’         to ‘+DISKGROUPSTB/datafile/undotbs3.dbf’       ;
copy datafile ‘+MYDISKGROUP/datafile/undotbs4.dbf’         to ‘+DISKGROUPSTB/datafile/undotbs4.dbf’       ;
copy datafile ‘+MYDISKGROUP/datafile/tbs_big_test.dbf’     to ‘+DISKGROUPSTB/datafile/tbs_big_test.dbf’   ;
copy datafile ‘+MYDISKGROUP/datafile/tbs_statspack01.dbf’ to ‘+DISKGROUPSTB/datafile/tbs_statspack01.dbf’;
copy datafile ‘+MYDISKGROUP/datafile/tbs_test_sunwg.dbf’   to ‘+DISKGROUPSTB/datafile/tbs_test_sunwg.dbf’ ;
}

两个接点同时做rman，速度大概有300M/S，这个比起网络传输，可是快的多了

5.拷贝完成，RAC接点全部dismount掉diskgroupstb
@>alter diskgroup diskgroupstb dismount;

Diskgroup altered.

6.回到备机，挂上diskgroupstb，standby就可以直接启动了
@>startup;
ASM instance started

Total System Global Area 283930624 bytes
Fixed Size                  2143704 bytes
Variable Size             256621096 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

[oracle@hack5 dbs]$ asmcmd
ASMCMD> ls -l
State    Type    Rebal Name
MOUNTED EXTERN N      DISKGROUPSTB/
ASMCMD> cd DISKGROUPSTB
ASMCMD> ls -l
Type Redund Striped Time             Sys Name
                                        Y    SWORD/
                                        N    archive/
                                        N    datafile/
ASMCMD> cd datafile
ASMCMD> ls -l
Type         Redund Striped Time             Sys Name
                                               N    control01.ctl => +DISKGROUPSTB/SWORD/CONTROLFILE/Backup.256.660528811
                                               N    sysaux01.dbf => +DISKGROUPSTB/SWORD/DATAFILE/SYSAUX.258.660528931
                                               N    system01.dbf => +DISKGROUPSTB/SWORD/DATAFILE/SYSTEM.257.660528893
                                               N    tbs_big_test.dbf => +DISKGROUPSTB/SWORD/DATAFILE/TBS_BIG_TEST.264.660530865
                                               N    tbs_statspack01.dbf => +DISKGROUPSTB/SWORD/DATAFILE/TBS_STATSPACK.265.660532623
                                               N    tbs_test_sunwg.dbf => +DISKGROUPSTB/SWORD/DATAFILE/TBS_TEST_SUNWG.260.660529175
                                               N    undotbs01.dbf => +DISKGROUPSTB/SWORD/DATAFILE/UNDOTBS1.259.660528967
                                               N    undotbs2.dbf => +DISKGROUPSTB/SWORD/DATAFILE/UNDOTBS2.261.660529203
                                               N    undotbs3.dbf => +DISKGROUPSTB/SWORD/DATAFILE/UNDOTBS3.262.660530331
                                               N    undotbs4.dbf => +DISKGROUPSTB/SWORD/DATAFILE/UNDOTBS4.263.660530599
ASMCMD>
alter database add standby logfile group 17 ‘+DISKGROUPSTB/datafile/redostb01.log’ size 512M;
alter database add standby logfile group 18 ‘+DISKGROUPSTB/datafile/redostb02.log’ size 512M;
alter database add standby logfile group 19 ‘+DISKGROUPSTB/datafile/redostb03.log’ size 512M;
alter database add standby logfile group 20 ‘+DISKGROUPSTB/datafile/redostb04.log’ size 512M;

@>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
@>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
@>ALTER DATABASE OPEN READ ONLY;
Database altered.
@>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.

By 八神 Posted in 大话技术 | 4 Comments »

Oracle 11G RAC环境的load balance + TAF配置

July 14, 2008 – 12:02 pm

一.客户段的load balance + TAF
sword =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hack1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hack2-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hack3-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hack4-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAILOVER = ON)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sword.db.alibaba.com)
      (FAILOVER_MODE =
        (TYPE = SESSION)
        (METHOD = BASIC)
      )
    )
)

LOAD_BALANCE = yes表示程序走sword连接的时候，将在下面4个地址里面随机的选择一个，达到接点间连接均匀
failover表示开启TAF(Transparent Application Failover)特性，其中TYPE = SESSION表示当一个连接好的会话
的实例发生故障，系统会自动将会话切换到其他可用的实例，前台应用无须再度发起连接，但会话正在执行的SQL
需要重新执行，METHOD = BASIC表示初始连接就连接一个接点，他还有个选项是preconnect，表示初始连接所有的
接点，在failover的时候可以很快就切过去，这个基本是资源浪费

二.服务器端的负载平衡
1.listener.ora文件里面配置
LISTENER_SWORD_HACK<n> =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hack<n>-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hack<n>-priv)(PORT = 1521)(IP = FIRST))
    )
)

2.tnsnames.ora文件里面加入
LISTENERS_SWORD =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hack1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hack2-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hack3-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hack4-vip)(PORT = 1521))
)

3.各个实例修改remote_listener参数
alter system set REMOTE_LISTENER = ‘LISTENERS_SWORD’ scope=both sid=’sword1′;
alter system set REMOTE_LISTENER = ‘LISTENERS_SWORD’ scope=both sid=’sword2′;
alter system set REMOTE_LISTENER = ‘LISTENERS_SWORD’ scope=both sid=’sword3′;
alter system set REMOTE_LISTENER = ‘LISTENERS_SWORD’ scope=both sid=’sword4′;

服务器段的负载均衡主要是通过REMOTE_LISTENER参数控制的，在连接被定位到某个接点后，系统会根据REMOTE_LISTENER
考虑多个接点的负载(主机load是个很重要的参考标准)，把连接再次分发出去，这个和客户段的load balance是可以结合
在一起混合使用的，互不干扰

三.连接RAC数据库
[oracle@hack2 admin]$ sqlplus system/sg6n9ol0ij117@sword
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jul 14 10:29:33 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server

如果出现ORA-12520错误，应该是oracle的BUG，解决方法是加入local_listener参数控制各个接点service的注册
1.tnsnames.ora文件添加
LISTENER_HACK1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hack1-vip)(PORT =1521))
)

LISTENER_HACK2 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hack2-vip)(PORT =1521))
)

LISTENER_HACK3 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hack3-vip)(PORT =1521))
)

LISTENER_HACK4 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hack4-vip)(PORT =1521))
)

2.修改各个接点的local_listener参数
alter system set LOCAL_LISTENER = ‘LISTENER_HACK1′ scope=both sid=’sword1′;
alter system set LOCAL_LISTENER = ‘LISTENER_HACK2′ scope=both sid=’sword2′;
alter system set LOCAL_LISTENER = ‘LISTENER_HACK3′ scope=both sid=’sword3′;
alter system set LOCAL_LISTENER = ‘LISTENER_HACK4′ scope=both sid=’sword4′;

3.重新启动listener
lsnrctl start LISTENER_SWORD_HACK<n>

4.连接
[oracle@hack2 admin]$ sqlplus system/sg6n9ol0ij117@sword
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jul 14 11:50:52 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options
SQL> show user
USER is “SYSTEM”

错误详细信息请参考metalink Note:342419.1

By 八神 Posted in 大话技术 | 1 Comment »

我的一些分析

July 9, 2008 – 10:25 pm

今天一个QQ朋友告诉我，他的数据库有点问题，’Checkpoint not complete’。我叫他ALTER文件传过来,下面是我的一些分析：
1：日志切换太频繁，几乎是1分钟不到，就切了（还是归档模式,50M一个）。建议加大日志组成员，另建议问问开发，到底在跑什么？因为晚上11点的LOG，还是很频繁；
2：他说查询很慢，我看了ALTER文件，发现才1.1G的SGA，data buffer就1G，但是PGA倒是有400多M，
如果内存不多的话，还不如PGA小点，多给点databuffer.
我不相信，他的数据库命中率有100％，估计也不可能，因为写日志太频繁了，根据LRU来说，总是有写data buffer要换进换出。

PS：因为我只看到了ALTER文件，所以只能这样定性了。人家没给STATSPACK。

好像有点武断的成分，但是确实也只能这样了。

By vogts Posted in 大话技术 | 1 Comment »

ORACLE RAW类型的存储

July 9, 2008 – 5:48 pm

最近我管的数据库，上了一套新的系统：《工作流引擎――JBPM》。表上使用了一种很孤僻的类型: RAW。

开发就是把一个对象直接存入到DB里了，额的神啊。。。。这样设计上是方便了，DB这边就不方便了啊。说实话，用不用RAW类型，Donny和我最最关心的还是数据本身的存储，担心RAW和LOB类型的字段一样，存储在别的Segment上。验证了下一下，这种担心不必了。验证如下：

@>SELECT DISTINCT segment_type FROM user_segments;

SEGMENT_TYPE

——————

INDEX

TABLE

@>desc JBPM_BYTEBLOCK

Name Null? Type

———————————————————————————– ——– ——————————————————–

PROCESSFILE_ NOT NULL NUMBER(19)

BYTES_ RAW(1024)

INDEX_

By vogts Posted in 大话技术 | No Comments »

看样子全文索引，还需要定期BUILD

July 7, 2008 – 4:19 pm

今天有人告诉我数据库上有如下错误：

很明显是全文索引的错误，我接下去做了rebuild ，还不能REBUILD ONLINE，FT～···

@>alter index SFA_ACCOUNT_COM_CTXIND rebuild online;

alter index SFA_ACCOUNT_COM_CTXIND rebuild online

*

ERROR at line 1:

ORA-29874: warning in the execution of ODCIINDEXALTER routine

ORA-29960: line 1,

DRG-10595: ALTER INDEX SFA_ACCOUNT_COM_CTXIND failed

DRG-10562: missing alter index parameter

@>alter index SFA_ACCOUNT_COM_CTXIND rebuild;

Index altered.

接下去，一切正常了：

查了METALINK：

Bug# 3039404   See Note 3039404.8

      OERI[12406] using NOT CONTAINS with local partitioned TEXT index

      Fixed: 9.2.0.5, 10.1.0.2

By vogts Posted in 大话技术 | 4 Comments »

swap导致的数据库不能访问

June 30, 2008 – 10:16 pm

事情总是一块来的。今天因为是月底最后一天，刚好还有2个数据订正要做，正在编写脚本、同开发人员确认处理逻辑，且催得很急。不巧的是，测试db这时也出问题，load很高。没办法订正先放下来，解决测试的问题，否则影响下午的测试。

新建ssh连接没有反应，好在此前已经有一个ssh terminal连在服务器上，top命令打下去就没有响应了，等了几分钟终于出来结果。发现top的进程列表排在最前面的是两个进程kscand和kswapd,注意到swap分配的内存为0，被使用的swap内存也为0. 因为cpu太忙，输入的命令都不可能，通知测试人员先把测试服务器的应用停了下来。为什么swap内存没有分配了，fdisk -l 发现 /dev/sdg2 是swap对应的分区，检查/etc/fstab 发现swap对应的设备是/dev/sda2，奇怪磁盘设备的编号怎么不一样了？

通过powermt查看存储的磁盘路径，有2个lun分配给系统，lun 3有2个通道设备/dev/sda,/dev/sdc,lun 8有2个通道设备/dev/sdb,/dev/sdd,记得机器安装配置时lun分配的设备应该只有lun 3。lun 8是后加的，印象中记得以前本地硬盘的设备编号是/dev/sda。应该就是这个导致了系统的load飙高，因为没有swap，导致系统的内存有4G被用作swap，导致页换进换出，系统消耗的cpu很高。swapon /dev/sdg2,编辑/etc/fstab的swap设备，重新mount -a.

通知测试启动环境应用，故障消失。检查db日志和v$pgastat都没有发现异常，因为应用被强制停止的，所以当时在运行的sql和等待事件都没法知道。但从现在来看应该是swap导致了这次的问题，什么原因导致swap的设别名被更改了，还真不太确定，不知道跟上次动态从存储分配一个lun过来有没有关系。

ps：

os: redhat as3 u4 机器内存8G，4cpu，sga 1.6 gb + pga 200m .

By 勇斌 Posted in 大话技术 | 4 Comments »