类别

版本

您正在查看9.0 -版本的RapidMiner服务器文档点击这里查看最新版本

PostgreSQL数据库大小

RapidMiner Server 9.0.2及以上版本不受此问题影响。如果您已经将RapidMiner服务器更新到打补丁的版本,并且之前已经手动添加了数据库触发器,您应该删除过时的触发器

如果您使用PostgreSQL运行RapidMiner Server 8.0到9.0.1,数据库可能会变大。这与PostgreSQL处理大型对象的机制有关。在内部,PostgreSQL使用了一个独立的大对象保存对数据库中大型条目的引用的表,但未使用的条目(不再被引用)不会自动从该表中删除。

这个问题可以通过以下步骤解决:

  1. 添加一个触发器函数,该函数将处理为将来的操作删除未使用的引用。
  2. 清理旧的未使用的表引用一次。此操作不会立即回收PostgreSQL已使用的磁盘空间,但分配的磁盘空间将首先被新创建的对象使用。
  3. 可选地运行PostgreSQL的内部机制真空回收大对象表使用的磁盘空间。

在开始本页中概述的过程之前,必须关闭RapidMiner服务器。

建议在关闭RapidMiner Server之后,在开始执行本页中提到的任何语句之前,对数据库模式进行完整备份。

请注意,以下查询必须作为RapidMiner服务器使用的同一用户和同一数据库中执行。

以下查询将锁定数据库中的表,并阻止其他操作同时运行。

第一步在数据库中创建触发器,以便在每次更新特定表时自动删除未使用的引用。要创建它,请执行以下语句。

触发器函数,用于处理包含大对象引用的表列的删除和更新。——如果行被删除或更新,旧的引用将从pg_largeobject表中删除。创建或替换函数rmserver_largeobject_cleanup_trgfunc()返回触发器作为$func$ begin——更新触发器有NEW和OLD,如果TG_OP = ' Update '那么——根据表,如果引用改变,取消链接(删除)旧的大对象。如果TG_TABLE_NAME = 'ra_ent_processexecparam',则如果新建。Processcontext <> old。然后执行lo_unlink(old.processcontext);如果;elseif TG_TABLE_NAME = 'ra_ent_bytebuffer' then if new。Buffer <> old。然后执行lo_unlink(old.buffer);如果;elseif TG_TABLE_NAME = 'ra_ent_jobsubmissionfailure' then if new。进程<> old。process then perform lo_unlink(old.process::bigint); end if; elseif TG_TABLE_NAME = 'ra_ent_jobsubmissionfailure' then if new.message <> old.message then perform lo_unlink(old.message::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job_error' then if new.message <> old.message then perform lo_unlink(old.message::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job_error' then if new.trace <> old.trace then perform lo_unlink(old.trace::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job_error' then if new.explanation <> old.explanation then perform lo_unlink(old.explanation::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job_log' then if coalesce(new.log, '') <> old.log then perform lo_unlink(old.log::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job' then if new.process <> old.process then perform lo_unlink(old.process::bigint); end if; end if; return NEW; elseif TG_OP = 'DELETE' then -- Depending on the table, unlink (delete) the referenced large object. if TG_TABLE_NAME = 'ra_ent_processexecparam' then perform lo_unlink(old.processcontext); elseif TG_TABLE_NAME = 'ra_ent_bytebuffer' then perform lo_unlink(old.buffer); elseif TG_TABLE_NAME = 'ra_ent_jobsubmissionfailure' then perform lo_unlink(old.process::bigint); elseif TG_TABLE_NAME = 'ra_ent_jobsubmissionfailure' then perform lo_unlink(old.message::bigint); elseif TG_TABLE_NAME = 'jobservice_job_error' then perform lo_unlink(old.message::bigint); elseif TG_TABLE_NAME = 'jobservice_job_error' then perform lo_unlink(old.trace::bigint); elseif TG_TABLE_NAME = 'jobservice_job_error' then perform lo_unlink(old.explanation::bigint); elseif TG_TABLE_NAME = 'jobservice_job_log' then perform lo_unlink(old.log::bigint); elseif TG_TABLE_NAME = 'jobservice_job' then perform lo_unlink(old.process::bigint); end if; return OLD; end if; end; $func$ language plpgsql volatile; drop trigger if exists ra_ent_processexecparam_lob_trg on ra_ent_processexecparam; create trigger ra_ent_processexecparam_lob_trg after update or delete on ra_ent_processexecparam for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists ra_ent_bytebuffer_lob_trg on ra_ent_bytebuffer; create trigger ra_ent_bytebuffer_lob_trg after update or delete on ra_ent_bytebuffer for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists ra_ent_jobsubmissionfailure_lob_trg on ra_ent_jobsubmissionfailure; create trigger ra_ent_jobsubmissionfailure_lob_trg after update or delete on ra_ent_jobsubmissionfailure for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists jobservice_job_error_lob_trg on jobservice_job_error; create trigger jobservice_job_error_lob_trg after update or delete on jobservice_job_error for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists jobservice_job_log_lob_trg on jobservice_job_log; create trigger jobservice_job_log_lob_trg after update or delete on jobservice_job_log for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists jobservice_job_lob_trg on jobservice_job; create trigger jobservice_job_lob_trg after update or delete on jobservice_job for each row execute procedure rmserver_largeobject_cleanup_trgfunc();

在开始清理过程之前,让我们验证一下您的设置是否需要这种清理。请执行下面的语句。如果现有的大对象数量较少,并且pg_largeobject表要高得多,您可能应该执行步骤2中概述的清理。

此查询仅适用于PostgreSQL 9.4及更新版本。(旧版本不再被PostgreSQL项目支持,你应该升级。)

其中oid为(select processcontext as loboid from ra_ent_processexecparam union from ra_ent_bytebuffer), text为(select process as loboidt from ra_ent_jobsubmissionfailure union from ra_ent_jobsubmissionfailure union from jobservice_job_error union from jobservice_job_error union from trace from jobservice_job_log union from jobservice_job_error union from log union from jobservice_job_job),Existing_oids as (select loboid from oid where loboid not null), nonexisting_oids as (select loboidt from text where loboidt not null)。Oid作为nexoid从pg_largeobject_metadata lom左外连接existing_oids eo on eo。Loboid = lom。我在哪里?loboid是null), stats as (select 'Existing large objects'::text作为category, count(loboid)作为objectcount, pg_size_pretty(sum(length(lo_get(lom.oid)))))作为size from existing_oids eo内部连接pg_largeobject_metadata lom on lom)。Oid = eo。Loboid union select 'pg_largeobject表'作为category, count(oid), pg_size_pretty(pg_relation_size('pg_largeobject')) from pg_largeobject_metadata lom) select * from stats;

请执行以下语句来释放未使用引用分配的表空间。(我们非常小心地创建了在pg_largeobject表中搜索的完整引用列表。但是,在特殊情况下,如果您将RapidMiner后端数据库用于其他目的,则可能会删除仍然被引用的数据。再次启动服务器后,您将看到类似“大对象252974不存在”的错误消息。在这种情况下,您必须恢复数据库备份。)

其中oid为(select processcontext as loboid from ra_ent_processexecparam union from ra_ent_bytebuffer), text为(select process as loboidt from ra_ent_jobsubmissionfailure union from ra_ent_jobsubmissionfailure union from jobservice_job_error union from jobservice_job_error union from trace from jobservice_job_log union from jobservice_job_error union from log union from jobservice_job_job),Existing_oids as (select loboid from oid where loboid not null), nonexisting_oids as (select loboidt from text where loboidt not null)。Oid作为nexoid从pg_largeobject_metadata lom左外连接existing_oids eo on eo。Loboid = lom。我在哪里?从nonexisting_oid中选择nexoid为invalid_oid, lo_unlink(nexoid为null);

如上所述,清理语句不会立即回收PostgreSQL实例已经使用的磁盘空间。然而,你可以收回它。请执行以下语句。

VACUUM FULL pg_largeobject;

删除引入的触发器和功能

RapidMiner Sever 9.0.2修复了所描述的问题,因此引入的触发器已经过时,应该从数据库中删除。请执行以下语句,移除触发器和相应的功能:

如果ra_ent_processexecparam上存在ra_ent_processexecparam_lob_trg,则删除触发器;如果ra_ent_bytebuffer上存在ra_ent_bytebuffer_lob_trg,则删除触发器;如果ra_ent_jobsubmissionfailure上存在ra_ent_jobsubmissionfailure_lob_trg,删除触发器;如果jobservice_job_error上存在jobservice_job_error_lob_trg,删除触发器;如果jobservice_job_log_lob_trg在jobservice_job_log上存在,删除触发器;如果jobservice_job上存在jobservice_job_lob_trg,删除触发器;删除rmserver_largeobject_cleanup_trgfunc();