Greenplum 日常维护命令

Greenplum 日常维护1.数据库启动gpstart常用可选参数 -a : 直接启动不提示终端用户输入确认-m:只启动master 实例主要在故障处理时使用2.数据库停止gpstop常用可选参数-a直接停止不提示终端用户输入确认-m只停止master 实例与gpstart –m 对应使用-M fast停止数据库中断所有数据库连接回滚正在运行的事务-u不停止数据库只加载pg_hba.conf 和postgresql.conf中运行时参数当改动参数配置时候使用。评-a用在shell里最多用的还是-M fast。3. 查看实例配置和状态select * from gp_configuration order by 1 ;主要字段说明Content该字段相等的两个实例是一对primary instance和mirrorInstance)Isprimary实例是否作为primary instance 运行Valid实例是否有效如处于false 状态则说明该实例已经down 掉。Port实例运行的端口Datadir:实例对应的数据目录4.gpstate显示Greenplum数据库运行状态详细配置等信息常用可选参数-cprimary instance 和 mirror instance 的对应关系-m只列出mirror 实例的状态和配置信息-f显示standby master 的详细信息-Q显示状态综合信息该命令默认列出数据库运行状态汇总信息常用于日常巡检。评最开始由于网卡驱动的问题做了mirror后segment经常down掉用-Q参数查询综合信息还是比较有用的。5. 查看用户会话和提交的查询等信息select * from pg_stat_activity 该表能查看到当前数据库连接的IP 地址用户名提交的查询等。另外也可以在master 主机上查看进程对每个客户端连接master 都会创建一个进程。ps -ef |grep -i postgres |grep -i con评常用的命令我经常用这个查看数据库死在那个sql上了。6. 查看数据库、表占用空间select pg_size_pretty(pg_relation_size(schema.tablename));select pg_size_pretty(pg_database_size(databasename));必须在数据库所对应的存储系统里至少保留30%的自由空间日常巡检要检查存储空间的剩余容量。评可以查看任何数据库对象的占用空间pg_size_pretty可以显示如mb之类的易读数据另外可以与pg_tablespg_indexes之类的系统表链接统计出各类关于数据库对象的空间信息。7. 收集统计信息回收空间定期使用Vacuum analyze tablename 回收垃圾和收集统计信息尤其在大数据量删除导入以后非常重要评这个说的不全面vacuum分两种一种是analyze优化查询计划的还有一种是清理垃圾数据postres删除工作并不是真正删除数据而是在被删除的数据上坐一个标记只有执行vacuum时才会真正的物理删除这个非常重用有些经常更新的表各种查询、更新效率会越来越慢这个多是因为没有做vacuum的原因。8. 查看数据分布情况两种方式l Select gp_segment_id,count(*) from tablename group by 1 ;l 在命令运行gpskew -t public.ate -a postgres如数据分布不均匀将发挥不了并行计算的优势严重影响性能。评非常有用gp要保障数据分布均匀。9.实例恢复gprecoverseg通过gpstate 或gp_configuration 发现有实例down 掉以后使用该命令进行恢复。10.查看锁信息SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_queryFROM pg_locks l, pg_class c, pg_stat_activity aWHERE l.relationc.oid AND l.pida.procpidORDER BY c.relname;主要字段说明relname: 表名locktype、mode 标识了锁的类型11.explain在提交大的查询之前使用explain分析执行计划、发现潜在优化机会避免将系统资源熬尽。评少写了个analyze如果只是explain统计出来的执行时间是非常坑爹的如果希望获得准确的执行时间必须加上analyze。12.数据库备份gp_dump常用参数-s: 只导出对象定义表结构函数等-n: 只导出某个schemagp_dump 默认在master 的data 目录上产生这些文件gp_catalog_1_dbid_timestamp 关于数据库系统配置的备份文件gp_cdatabase_1_dbid_timestamp数据库创建语句的备份文件gp_dump_1_dbid_timestamp数据库对象ddl语句gp_dump_status_1_dbid_timestamp备份操作的日志在每个segment instance 上的data目录上产生的文件gp_dump_0_dbid_timestamp用户数据备份文件gp_dump_status_0_dbid_timestamp备份日志13.数据库恢复gp_restore必选参数--gp-kkey key 为gp_dump 导出来的文件的后缀时间戳-d dbname 将备份文件恢复到dbname14.登陆与退出Greenplum#正常登陆psql gpdbpsql -d gpdb -h gphostm -p 5432 -U gpadmin#使用utility方式PGOPTIONS-c gp_session_roleutility psql -h -d dbname hostname -p port#退出在psql命令行执行\q15.参数查询psql -c SHOW ALL; -d gpdbgpconfig --show max_connections评这个有用可以管道给grep。创建数据库createdb -h localhost -p 5432 dhdw创建GP文件系统# 文件系统名gpfsdw# 子节点视segment数创建目录mkdir -p /gpfsdw/seg1mkdir -p /gpfsdw/seg2chown -R gpadmin:gpadmin /gpfsdw# 主节点mkdir -p /gpfsdw/masterchown -R gpadmin:gpadmin /gpfsdwgpfilespace -o gpfilespace_configgpfilespace -c gpfilespace_config创建GP表空间psql gpdbcreate tablespace TBS_DW_DATA filespace gpfsdw;SET default_tablespace TBS_DW_DATA;删除GP数据库gpdeletesystem -d /gpmaster/gpseg-1 -f查看segment配置select * from gp_segment_configuration;文件系统select * from pg_filespace_entry;磁盘、数据库空间SELECT * FROM gp_toolkit.gp_disk_free ORDER BY dfsegment;SELECT * FROM gp_toolkit.gp_size_of_database ORDER BY sodddatname;日志SELECT * FROM gp_toolkit.__gp_log_master_ext;SELECT * FROM gp_toolkit.__gp_log_segment_ext;表数据分布SELECT gp_segment_id, count(*) FROM table_name GROUP BY gp_segment_id;表占用空间SELECT relname as name, sotdsize/1024/1024 as size_MB, sotdtoastsize as toast, sotdadditionalsize as otherFROM gp_toolkit.gp_size_of_table_disk as sotd, pg_classWHERE sotd.sotdoid pg_class.oid ORDER BY relname;索引占用空间SELECT soisize/1024/1024 as size_MB, relname as indexnameFROM pg_class, gp_toolkit.gp_size_of_indexWHERE pg_class.oid gp_size_of_index.soioidAND pg_class.relkindi;OBJECT的操作统计SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as timeFROM pg_stat_operationsWHERE objname name;锁SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_queryFROM pg_locks l, pg_class c, pg_stat_activity aWHERE l.relationc.oidAND l.pida.procpidORDER BY c.relname;队列SELECT * FROM pg_resqueue_status;gpfdist外部表# 启动服务gpfdist -d /share/txt -p 8081 –l /share/txt/gpfdist.log # 创建外部表分隔符为’/t’drop EXTERNAL TABLE TD_APP_LOG_BUYER;CREATE EXTERNAL TABLE TD_APP_LOG_BUYER (IP text,ACCESSTIME text,REQMETHOD text,URL text,STATUSCODE int,REF text,name text,VID text)LOCATION (gpfdist://gphostm:8081/xxx.txt)FORMAT TEXT (DELIMITER E/tFILL MISSING FIELDS) SEGMENT REJECT LIMIT 1 percent;# 创建普通表create table test select * from TD_APP_LOG_BUYER;# 索引# CREATE INDEX idx_test ON test USING bitmap (ip);# 查询数据select ip , count(*) from test group by ip order by count(*);gpload# 创建控制文件# 加载数据gpload -f my_load.ymlcopyCOPY country FROM /data/gpdb/country_dataWITH DELIMITER | LOG ERRORS INTO err_countrySEGMENT REJECT LIMIT 10 ROWS;gpfdist外部表# 创建可写外部表CREATE WRITABLE EXTERNAL TABLE unload_expenses( LIKE expenses )LOCATION (gpfdist://etlhost-1:8081/expenses1.out,gpfdist://etlhost-2:8081/expenses2.out)FORMAT TEXT (DELIMITER ,)DISTRIBUTED BY (exp_id);# 写权限GRANT INSERT ON writable_ext_table TO name;# 写数据INSERT INTO writable_ext_table SELECT * FROM regular_table;copyCOPY (SELECT * FROM country WHERE country_name LIKE A%) TO /home/gpadmin/a_list_countries.out;执行sql文件psql gpdbname –f yoursqlfile.sql或者psql登陆后执行\i yoursqlfile.sql