PostgreSQL表膨胀监控案例(精确计算)

膨胀率的精确计算

PostgreSQL自带了pgstattuple模块，可用于精确计算表的膨胀率。譬如这里的tuple_percent字段就是元组实际字节占关系总大小的百分比，用1减去该值即为膨胀率。

#插入1000W数据
postgres=# insert into t select id,id from generate_series(1,10000000) as id;
INSERT 0 10000000

#表膨胀系数为0.097
postgres=# select *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('t');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent |   bloat
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+------------------------
 442818560 | 10000001 | 400000040 |   90.33 |    0 |    0 |     0 | 1304976 |   0.29 | 0.09669540499838127833
(1 row)

#占用54055个page
postgres=# select * from pg_relpages('t');
 pg_relpages
-------------
  54055
(1 row)

#删除数据
postgres=# delete from t where id<>10000000;
DELETE 9999999

#仍然占用54055个page
postgres=# select * from pg_relpages('t');
 pg_relpages
-------------
  54055
(1 row)

#膨胀率已经为0.999999
postgres=# select *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('t');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent |   bloat
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+----------------------------
 442818560 |   2 |  80 |    0 |   9999999 |  399999960 |    90.33 | 1304976 |   0.29 | 0.999999819339099065766349

#vacuum表
postgres=# vacuum (verbose,full,analyze) t;
INFO: vacuuming "public.t"
INFO: "t": found 5372225 removable, 2 nonremovable row versions in 54055 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.89 s, system: 0.00 s, elapsed: 0.89 s.
INFO: analyzing "public.t"
INFO: "t": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
VACUUM

补充：pg索引膨胀问题---重建索引

问题：

发现数据库中很多表的索引大小超过数据大小。经检查，生产CA、CZ、MU、HU、PSG、RIUE库都存在这个现象。

原因：据运行同事介绍索引膨胀问题无法避免，频繁更新就会带来这个问题。

解决方法：

对于大的索引可以采用重建的方式解决。以下两种方法推荐第一种。

方法一：停止应用（这个操作会锁表），重建索引（注：重建完索引名称不变）

sql：reindex index 索引名称

时间：速度较快。2G大小的表，基本上1分钟左右可以建完索引。

还可以针对表重建索引，这个操作会加排他锁 ：

reindex table 表名

方法二：在线建新索引，再把旧索引删除

sql：根据不同索引采用不同的建索引命令，例如：

普通索引

create index concurrently idx_tbl_2 on tbl(id);
drop index idx_tbl_1;

唯一索引

create unique index concurrently user_info_username_key_1 on user_info(username);
begin;
alter table user_info drop constraint user_info_username_key;
alter table user_info add constraint user_info_username_key unique using index user_info_username_key_1;
end;

主键索引

create unique index concurrently user_info_pkey_1 on user_info(id);
begin;
alter table user_info drop constraint user_info_pkey;
alter table user_info add constraint user_info_pkey primary key using index user_info_pkey_1;
end;

时间：不停应用的话，业务忙的时候可能会非常长的时间。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • postgresql修改自增序列操作

    在 mysql 中,实现 id 自增的方式是依靠加一个 auto_increment 标志,而在 pgsql 中,id 自增是通过序列 SEQUENCE. 创建表时设置自增序列为: CREATE TABLE "config" ( "id" int4 NOT NULL DEFAULT nextval('config_id_seq'::regclass), ... }; 如果需要修改创建表之后的序列,如: ----删除前先解除 id 对该序列的依赖 ALTER TABL

  • PostgreSQL 实现登录及修改密码操作

    PostgreSQL 实现登录及修改密码操作

    PostgreSQL登录 1.可通过客户端pgAdmin III直接登录 2.可通过命令行 命令:psql -h 10.10.10.10 -U user -d postgres -p 5570 -h:数据库IP -U:登录用户 -d:登录的数据库 -p:登录端口 方法:进入postgreSQL的客户端安装目录(我的安装目录:C:\Program Files\PostgreSQL\9.4\bin),执行psql命令,其中\q表示退出数据库 修改密码 直接执行以下sql即可修改密码 alter us

  • postgresql查看表和索引的情况,判断是否膨胀的操作

    索引膨胀的几个来源: 1 大量删除发生后,导致索引页面稀疏,降低了索引使用效率. 2 PostgresQL 9.0之前的版本,vacuum full 会同样导致索引页面稀疏. 3 长时间运行的事务,禁止vacuum对表的清理工作,因而导致页面稀疏状态一直保持. 查看重复索引 SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE, (array_agg(idx))[1] AS idx1, (array_agg(idx))

  • 在PostgreSQL中设置表中某列值自增或循环方式

    在postgresql中,设置已存在的某列(num)值自增,可以用以下方法: //将表tb按name排序,利用row_number() over()查询序号并将该列命名为rownum,创建新表tb1并将结果保存到该表中 create table tb1 as (select *, row_number() over(order by name) as rownum from tb); //根据两张表共同的字段name,将tb1中rownum对应值更新到tb中num中 update tb set

  • postgresql 中的加密扩展插件pgcrypto用法说明

    postgresql 中的加密扩展插件pgcrypto用法说明

    近期测试了一下postgresql的加密扩展插件pgcrypto的aes加密 安装加密扩展插件:pgcrypto 在主节点上安装 create extension pgcrypto; aes加解密函数简单介绍 encrypt(data bytea, key bytea, type text) --加密 decrypt(data bytea, key bytea, type text) --解密 data 是需要加密的数据:type 用于指定加密方法 ASE方式加密: select encrypt

  • Postgresql 默认用户名与密码的操作

    在UNIX平台中安装PostgreSQL之后,PostgreSQL会在UNIX系统中创建一个名为"postgres"当用户.PostgreSQL的默认用户名和数据库也是"postgres", 不过没有默认密码.在安装PostgreSQL之后可以以默认用户登录,也可以创建新当用户名. 以默认用户登录 Yu @Coat ~ $ sudo su postgres #切换至postgres postgres @Coat /home /Yu $ psql postgres #

  • PostgreSQL表膨胀监控案例(精确计算)

    膨胀率的精确计算 PostgreSQL自带了pgstattuple模块,可用于精确计算表的膨胀率.譬如这里的tuple_percent字段就是元组实际字节占关系总大小的百分比,用1减去该值即为膨胀率. #插入1000W数据 postgres=# insert into t select id,id from generate_series(1,10000000) as id; INSERT 0 10000000 #表膨胀系数为0.097 postgres=# select *, 1.0 - tu

  • postgreSQL数据库的监控及数据维护操作

    postgreSQL数据库的监控及数据维护操作

    目前postgreSQL数据库的管理,数据查询等都需要安装postgreSQL软件或安装pgadmin等,远程访问都需要先登录到服务器等繁琐的操作.如果是开发团队,那么每个开发,测试,管理人员都要经历这个麻烦的过程. 使用Treesoft数据库管理系统,基于web方式管理postgreSQL,只需安装一次,大家直接通过浏览器使用,维护及监控数据库,大大简化工作量. 监控的指标包括:连接数,数据库大小,表空间,锁情况,QPS事务数等. Treesoft数据库管理系统,支持直接网络在线维护数据,数据

  • postgresql 删除重复数据案例详解

    1.建表 /* Navicat Premium Data Transfer Source Server : localhost Source Server Type : PostgreSQL Source Server Version : 110012 Source Host : localhost:5432 Source Catalog : postgres Source Schema : public Target Server Type : PostgreSQL Target Server

  • 用VBS精确计算100的阶乘的实现代码

    今天又无聊的逛了一下搜搜问问,找到这样一个问题: 有谁能提供vbs里20的阶乘的详细程序 下面有人给出了这样的答案: 复制代码 代码如下: function jx(x) j=1 for i=2 to x j=j*i next jx=j end function msgbox jx(20) 运行一下上面的程序,输出2.43290200817664E+18.笑而不语,再次证明了我之前的结论,在这种网站上回答问题的,大部分水平都不咋地. 其实之前在<用VBS精确计算2的100次方>我已经写过了VBS

  • C#精确计算年龄的方法分析

    本文实例讲述了C#精确计算年龄的方法.分享给大家供大家参考.具体如下: 该源码在vs2010测试通过 复制代码 代码如下: using System; using System.Collections.Generic; using System.Text; namespace PublicClass {     public static class CalculationDate     {         /// <summary>         /// 由两个日期计算出年龄(岁.月.天

  • js中精确计算加法和减法示例

    /精确计算加法和减法.例如0.1+0.2=0.3或 0.1-0.2=-0.1 复制代码 代码如下: function addFn(dataOne,dataTwo){ var dataOneInt=dataOne.toString().split(".")[0]; var dataOneFloat=""; var dataTwoInt=dataTwo.toString().split(".")[0]; var dataTwoFloat="

  • PHP精确计算功能示例

    本文实例讲述了PHP精确计算功能.分享给大家供大家参考,具体如下: 引言:一定要确保数据的准确性.这是一个好的程序员的基本素养. <?php /** * 精确加法 * @param [type] $a [description] * @param [type] $b [description] */ function math_add($a,$b,$scale = '2') { return bcadd($a,$b,$scale); } /** * 精确减法 * @param [type] $a

  • 详解js加减乘除精确计算

    JS无法进行精确计算的bug 在做CRM,二代审核需求审核详情页面时.需要按比例(后端传类似0.8的小数)把用户输入的数字显示在不同的地方. 在做dubheInvest = invest * (1 - ratio);运算时发现问题.具体如下: 示例代码: console.log( 1 - 0.8 ); //输出 0.19999999999999996 console.log( 6 * 0.7 ); //输出 4.199999999999999 console.log( 0.1 + 0.2 );

  • JavaScript使用math.js进行精确计算操作示例

    本文实例讲述了JavaScript使用math.js进行精确计算操作.分享给大家供大家参考,具体如下: javascript进行浮点运算会有尾差,比如算个0.1+0.7试试,得到的不是0.8 .这个问题可以使用math.js解决. Math.js是一款开源的JavaScript和Node.js数学库,用于处理数字.大数.复数.单位和矩阵. 官网: http://mathjs.org/ cdn地址:https://cdnjs.cloudflare.com/ajax/libs/mathjs/5.0.

  • sqoop 实现将postgresql表导入hive表

    使用sqoop导入数据至hive常用语句 直接导入hive表 sqoop import --connect jdbc:postgresql://ip/db_name --username user_name --table table_name --hive-import -m 5 内部执行实际分三部,1.将数据导入hdfs(可在hdfs上找到相应目录),2.创建hive表名相同的表,3,将hdfs上数据传入hive表中 sqoop根据postgresql表创建hive表 sqoop creat

随机推荐