Greenplum定期vacuum系统表以及定期删除日志

最新推荐文章于 2024-07-09 11:37:21 发布

魂醉

最新推荐文章于 2024-07-09 11:37:21 发布

阅读量6.2k

点赞数

分类专栏： # Greenplum

本文链接：https://blog.csdn.net/dazuiba008/article/details/77370500

版权

Greenplum 专栏收录该内容

33 篇文章 4 订阅

订阅专栏

1.系统表定期vacuum，重建索引

建议定期vacuum系统表并重建索引，以下脚本可以定期执行
#!/bin/bash
DBNAME="<database-name>"
SYSTABLES="' pg_catalog.' || relname || ';' from pg_class a, pg_namespace b
where a.relnamespace=b.oid and b.nspname='pg_catalog' and a.relkind='r'"
psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME

reindexdb --system -d $DBNAME

analyzedb -s pg_catalog -d $DBNAME

2.数据库日志文件管理
greenplum日志存放在pg_log下，master节点和每个segment节点都会有，格式为gpdb-YYYY-MM-DD_hhmmss.cs
需要我们定期清理

定期清理master节点的日志，保留最近8天的日志

find master日志目录 -type f -name "gpdb-*.csv" -ctime +8 -exec rm {} \;

平时如果gp有问题，也可以查看相关日志，日志格式见下表

Table 3. Greenplum Database Server Log Format
#	Field Name	Data Type	Description
1	event_time	timestamp with time zone	时间戳
2	user_name	varchar(100)	数据库用户名
3	database_name	varchar(100)	数据库名
4	process_id	varchar(10)	系统进程ID
5	thread_id	varchar(50)	The thread count (prefixed with "th")
6	remote_host	varchar(100)	主机名或者地址
7	remote_port	varchar(10)	master或者segment的端口
8	session_start_time	timestamp with time zone	session打开时间
9	transaction_id	int	Top-level transaction ID on the master. This ID is the parent of any subtransactions.
10	gp_session_id	text	Session identifier number (prefixed with "con")
11	gp_command_count	text	The command number within a session (prefixed with "cmd")
12	gp_segment	text	The segment content identifier (prefixed with "seg" for primaries or "mir" for mirrors). The master always has a content ID of -1.
13	slice_id	text	The slice ID (portion of the query plan being executed)
14	distr_tranx_id	text	Distributed transaction ID
15	local_tranx_id	text	Local transaction ID
16	sub_tranx_id	text	Subtransaction ID
17	event_severity	varchar(10)	Values include: LOG, ERROR, FATAL, PANIC, DEBUG1, DEBUG2
18	sql_state_code	varchar(10)	SQL state code associated with the log message
19	event_message	text	Log or error message text
20	event_detail	text	Detail message text associated with an error or warning message
21	event_hint	text	Hint message text associated with an error or warning message
22	internal_query	text	The internally-generated query text
23	internal_query_pos	int	The cursor index into the internally-generated query text
24	event_context	text	The context in which this message gets generated
25	debug_query_string	text	User-supplied query string with full detail for debugging. This string can be modified for internal use.
26	error_cursor_pos	int	The cursor index into the query string
27	func_name	text	The function in which this message is generated
28	file_name	text	The internal code file where the message originated
29	file_line	int	The line of the code file where the message originated
30	stack_trace	text	Stack trace text associated with this message