使用Filegroup的一些好处-（4）选择率（selectivity）的相...-CentOS 6.3 Minimal yum 安装 PostgreSQL 9.2.2

当前位置: 数据库>其它

本页文章导读:

▪使用Filegroup的一些好处针对于大的数据库，使用Filegroup可以给我们带来性能和维护上的诸多好处，比如： 1. 遇到磁盘瓶颈时可以增加磁盘，将经常访问的一些数据量大的表分散到不同的文件组，.........

▪（4）选择率（selectivity）的相关问题本章将讨论采用标准的选择率计算方式却产生不合适结果的一些最常见原因。 1、前导零创建一个包含2000000行数据的表，它有一个id列，采用序号并利用0来进行填充其他位。因此典型的存储&#.........

▪CentOS 6.3 Minimal yum 安装 PostgreSQL 9.2.2 环境：CentOS 6.3 最小化缺省安装，配置好网卡。安装PostgreSQL前，确认Internet连接正常，以便下载安装文件。先使用 yum -y update 指令升级系统到最新版本。＃修改防火墙设置，打开5432端口vi /etc/.........

[1]使用Filegroup的一些好处

来源: 互联网发布时间: 2013-11-07

针对于大的数据库，使用Filegroup可以给我们带来性能和维护上的诸多好处，比如：

1. 遇到磁盘瓶颈时可以增加磁盘，将经常访问的一些数据量大的表分散到不同的文件组，分散到不同的磁盘，这样可以提供IO的性能。

2. 如果非聚集索引非常大，可以考虑将非聚集索引和表分开放到不同的Filegroup，从而分散IO。

3. 如果数据库同时存在只读和读写的表，可以考虑将只读数据放入到只读Filegroup,而读写的表放入读写Group,这样不光IO性能会有帮助，而且只读的Filegroup的查询性能也会有提高。

4. 可以针对不同的Filegroup进行备份操作和还原，从而减少备份和还原的时间。

5. 可以针对不同的Filegroup进行DBCC CHECKFILEGROUP操作，并且多个进程并行，从而减少维护时间。

6. 在某些情况下可以将出问题的Filegroup Offline从而让SQLServer其他部分正常工作。

7.迁移会比较方便，针对于一个大的数据库，单个Filegroup就没有那么大了，数据传输过程中不容易出现问题。

当然对于小的数据库多个Filegroup就不一定需要了。

作者：SmithLiu328 发表于2013-2-6 9:53:08 原文链接

阅读：13 评论：0 查看评论

[2]（4）选择率（selectivity）的相关问题

来源: 互联网发布时间: 2013-11-07

本章将讨论采用标准的选择率计算方式却产生不合适结果的一些最常见原因。 1、前导零

创建一个包含2000000行数据的表，它有一个id列，采用序号并利用0来进行填充其他位。因此典型的存储值将是A00000000000000001，系统中绝大部分使用这种策略的查询，类似于where id={string constant}的形式；但是，如果它们使用了基于区间的谓词，可能将出现一些奇怪的性能问题。

SQL> create table t1 
  2  nologging
  3  pctfree 0
  4  as
  5  with generator as (
  6    select
  7      rownum   id
  8    from  all_objects 
  9    where  rownum <= 2000
 10  )
 11  select
 12    /*+ ordered use_nl(v2) */
 13    trunc((rownum-1)/10000)    grp_id,
 14    'A' || lpad(rownum, 17, '0')  id
 15  from
 16    generator  v1,
 17    generator  v2
 18  where
 19    rownum <= 2000000
 20  ;

表已创建。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  ownname=> user,
  4  tabname=> 't1',
  5  cascade=> true,
  6  estimate_percent=> null, 
  7  method_opt=>'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> set autotrace traceonly;
SQL> select * from t1 where id between 'A00000000000060000' and 'A00000000000070000';

已选择10001行。


执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    17 |   374 |  1190   (3)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| T1   |    17 |   374 |  1190   (3)| 00:00:15 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"<='A00000000000070000' AND "ID">='A00000000000060000')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7520  consistent gets
       6849  physical reads
          0  redo size
     325111  bytes sent via SQL*Net to client
       7741  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10001  rows processed

针对该查询得到的基数是非常低的（17），但是很明显却返回了10001行。下面重新创建直方图，直方图的默认值为75个桶。

SQL> set autotrace off;
SQL> begin
  2  dbms_stats.gather_table_stats(
  3  ownname=> user,
  4  tabname=> 't1',
  5  cascade=> true,
  6  estimate_percent=> null, 
  7  method_opt=>'for all columns size 75'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> set autotrace traceonly;
SQL> select * from t1 where id between 'A00000000000060000' and 'A00000000000070000';

已选择10001行。


执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  8924 |   191K|  1190   (3)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| T1   |  8924 |   191K|  1190   (3)| 00:00:15 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"<='A00000000000070000' AND "ID">='A00000000000060000')


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7520  consistent gets
       6849  physical reads
          0  redo size
     325111  bytes sent via SQL*Net to client
       7741  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10001  rows processed

直方图的默认值为75个桶，执行计划显示估计的基数为8924——这个结果至少大体上是正确的。创建直方图是一种方法，如果id列的值没有字符，是可以转换为数字的字符串（00000000000000001），那么在id列上面创建一个函数索引也是一种办法：create index t1_i1 on t1(grp_id, to_number(id));

2、致命的默认值

即时是数据库应用程序正确的使用了日期类型，也仍然需要避免null值。为了不让任何列为null，每个可空的列都有一个默认值。因此，大部分独立于数据库的开发人员会选择一个什么样的值来表示null日期呢？如果表示很久以后的日期呢？比如4000年12月31日。

SQL> create table t1 
  2  as
  3  with generator as (
  4  select
  5  rownum id
  6  from all_objects 
  7  where rownum <= 2000
  8  )
  9  select
 10  /*+ ordered use_nl(v2) */
 11  decode(
 12      mod(rownum - 1,1000),
 13        0,to_date('4000-12-31','yyyy-mm-dd'),
 14          to_date('2000-01-01','yyyy-mm-dd') + trunc((rownum - 1)/100) 
 15    )  date_closed
 16  from
 17    generator  v1,
 18    generator  v2
 19  where rownum <= 1827 * 100;

表已创建。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  ownname=> user,
  4  tabname=> 't1',
  5  cascade=> true,
  6  estimate_percent=> null, 
  7  method_opt=>'for all columns size 1'    --直方图的默认值为1桶。
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> set autotrace traceonly;
SQL> select *
  2    from t1
  3   where date_closed between to_date('2003-01-01', 'yyyy-mm-dd') and
  4         to_date('2003-12-31', 'yyyy-mm-dd');

已选择36463行。


执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   291 |  2328 |    61   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   291 |  2328 |    61   (4)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DATE_CLOSED"<=TO_DATE(' 2003-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "DATE_CLOSED">=TO_DATE(' 2003-01-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2759  consistent gets
          0  physical reads
          0  redo size
     494301  bytes sent via SQL*Net to client
      27145  bytes received via SQL*Net from client
       2432  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      36463  rows processed     --这里实际放回了36463行记录，但是oracle却计算错误了（291）。

SQL> set autotrace off;
SQL> begin
  2  dbms_stats.gather_table_stats(
  3  ownname=> user,
  4  tabname=> 't1',
  5  cascade=> true,
  6  estimate_percent=> null, 
  7  method_opt=>'for all columns size 11'    --直方图的默认值为11桶。
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> set autotrace traceonly;
SQL> select *
  2    from t1
  3   where date_closed between to_date('2003-01-01', 'yyyy-mm-dd') and
  4         to_date('2003-12-31', 'yyyy-mm-dd');

已选择36463行。


执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 36320 |   283K|    61   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 36320 |   283K|    61   (4)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DATE_CLOSED">=TO_DATE(' 2003-01-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "DATE_CLOSED"<=TO_DATE(' 2003-12-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2759  consistent gets
          0  physical reads
          0  redo size
     494301  bytes sent via SQL*Net to client
      27145  bytes received via SQL*Net from client
       2432  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      36463  rows processed          --36463：36320这次oracle计算得比较准确了。

可以看见加大了直方图的桶数之后，CBO估算的行数就比较接近真实值了，那我们再加大直方图的桶数试一下呐！

SQL> set autotrace off;
SQL> begin
  2  dbms_stats.gather_table_stats(
  3  ownname=> user,
  4  tabname=> 't1',
  5  cascade=> true,
  6  estimate_percent=> null, 
  7  method_opt=>'for all columns size 75'
  8  );
  9  end;
 10  /

[3]CentOS 6.3 Minimal yum 安装 PostgreSQL 9.2.2

来源: 发布时间: 2013-11-07

环境：CentOS 6.3 最小化缺省安装，配置好网卡。

安装PostgreSQL前，确认Internet连接正常，以便下载安装文件。

先使用 yum -y update 指令升级系统到最新版本。

＃修改防火墙设置，打开5432端口

vi /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

＃重启防火墙使新设置生效
service iptables restart

＃访问 http://yum.pgrpms.org/repopackages.php 页面，查找最新的postgresql的yum配置的url。

＃根据版本选择需要的rpm后执行下列命令:
rpm -Uvh http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm

＃修改原始的yum仓库配置
vi /etc/yum.repos.d/CentOS-Base.repo
在[base]和[updates]部分的尾部追加 exclude=postgresql* 用以取消从默认仓库安装和更新PostgreSQL。

＃安装postgresql
yum -y install postgresql-server

＃执行数据库初始化脚本
service postgresql-9.2 initdb

＃启动服务
service postgresql-9.2 start

＃变更登录用户
su - postgres

＃登录数据库，修改postgres用户的数据库密码
psql
postgres=# ALTER USER postgres PASSWORD '123456';
postgres=# \q

＃退出变更登录
exit

＃编辑配置文件，配置可访问数据库的网络地址
vi /var/lib/pgsql/9.2/data/postgresql.conf
listen_addresses = '*'

＃编辑配置文件，设置密码md5验证
vi /var/lib/pgsql/9.2/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
#host all all 127.0.0.1/32 ident
host all all all md5

＃重启数据库服务
service postgresql-9.2 restart

# 设置开机自动启动服务
chkconfig postgresql-9.2 on

>>>>>>>>>>>>>>>完成

本文链接