VCSA 中的 /storage/db 已满或几乎已满
Article ID: 301253
Updated On:
Products
VMware vCenter Server
Issue/Introduction
Symptoms:
免责声明： 本文是 /storage/db full or nearly full in VCSA (51981) 的翻译版本。 尽管我们会不断努力为本文提供最佳翻译版本，但本地化的内容可能会过时。 有关最新内容，请参见英文版本。
注意：上述日志摘录仅为示例。日期、时间和环境变量可能会因环境而有所不同。
免责声明： 本文是 /storage/db full or nearly full in VCSA (51981) 的翻译版本。 尽管我们会不断努力为本文提供最佳翻译版本，但本地化的内容可能会过时。 有关最新内容，请参见英文版本。
- 在 /var/log/vmware/vpxd/vpxd.log 文件中，您会看到类似以下内容的条目：
No Space left on device
- 在 /storage/db/vpostgres/pg_log/postgresql.log 文件中，您会看到类似以下内容的条目：
psql.bin: could not extend file "pg_tblspc/16392/PG_9.4_201409291/16385/8126296": No space left on device - 从 df -h，您会看到类似以下内容的条目：
50G 50G 0G 100% /storage/db
注意：上述日志摘录仅为示例。日期、时间和环境变量可能会因环境而有所不同。
有关日志文件的详细信息，请参见 Location of vCenter Server log files (1021804)。
Environment
VMware vCenter Server Appliance 6.5.x
Cause
数据库保存的冗余数据称为膨胀数据。
导致膨胀的可能原因有多种：
导致膨胀的可能原因有多种：
- 连接到数据库的应用程序中存在缺陷
- 长时间运行闲置事务
- 内部数据库进程未足够积极地进行数据库管理。
Resolution
要解决此问题，请增加磁盘大小并扩展 df -h 输出表明已满的分区。
要增加磁盘大小，请参见 vCenter Server Appliance with embedded vPostgres database crashes due to full database partition (2058273)。
要扩展 df -h 输出表明已满的分区并使分配的大小翻倍，请执行以下操作：
检查膨胀：
要检查膨胀，请运行命令：
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB
注意：此查询会查看表、索引结构以及表中的行数，并估算保存该数据所需的最小大小。然后，将此估算大小与当前实际大小进行比较，以估算膨胀。如果 tbloat 列或 ibloat 列 > 25，请调度维护以减少此数据库中的膨胀。
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,
COALESCE(c2.relname,'?')AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
(maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+COUNT(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::NUMERIC) AS bs,
CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;
移除膨胀
要移除膨胀，请运行命令：
service-control --stop --all
service-control --start vmware-vpostgres
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB
vacuum full;
vacuum analyze;
\q
service-control --start --all
要增加磁盘大小，请参见 vCenter Server Appliance with embedded vPostgres database crashes due to full database partition (2058273)。
要扩展 df -h 输出表明已满的分区并使分配的大小翻倍，请执行以下操作：
检查膨胀：
要检查膨胀，请运行命令：
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB
注意：此查询会查看表、索引结构以及表中的行数，并估算保存该数据所需的最小大小。然后，将此估算大小与当前实际大小进行比较，以估算膨胀。如果 tbloat 列或 ibloat 列 > 25，请调度维护以减少此数据库中的膨胀。
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,
COALESCE(c2.relname,'?')AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
(maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+COUNT(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::NUMERIC) AS bs,
CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;
移除膨胀
要移除膨胀，请运行命令：
service-control --stop --all
service-control --start vmware-vpostgres
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB
vacuum full;
vacuum analyze;
\q
service-control --start --all
Additional Information
Impact/Risks:
您必须扩展已满的数据库磁盘。
您必须扩展已满的数据库磁盘。
Feedback
Yes
No
Powered by
