Skip to main content
Solved

What does mean "LOG: automatic vacuum of table "fmeserver.public.fme_jobs": could not (re)acquire exclusive lock for truncate scan"?

  • December 8, 2015
  • 9 replies
  • 83 views
siriosus
Contributor
Forum|alt.badge.img+4

Hello,

I faced with a problem while working with one workspace that I published on FME Server. Job aim is to accept mails with xml files and process them with extraction particular data and putting this data to a database (in my case Oracle). Everything works fine except that after some time job execution begin slow down, miss emails and even stops until you restart FME Server Database and FMEServer. I began to monitor Event logs and found that problems begin estimately after this event "LOG: automatic vacuum of table "fmeserver.public.fme_jobs": could not (re)acquire exclusive lock for truncate scan". If this could be a reason, is it possible to fix this issue?

Thanks!

Best answer by fmelizard

@siriosus it might be that the FME Server database has a large number of job records. I'd recommend purging the jobs from the database. This can be done with PG Admin or you can enable a preinstalled schedule to perform this task regularly. See instructions for doing so in 2013:

http://docs.safe.com/fme/2013sp4/html/FME_Server_D... You may need to edit the workspace. In FME Server 2016, it is even easier to configure this.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

9 replies

siriosus
Contributor
Forum|alt.badge.img+4
  • siriosusContributor
  • Author
  • Contributor
  • December 8, 2015

Forgot to mention I use FME Server 2013 installed on Windows Server 2008R2 64bit.

david_r
Celebrity
  • david_rCelebrity
  • December 8, 2015

Hi

Silly question, maybe, but are you sure this is with Oracle? The error message sounds more like Postgresql.

David

siriosus
Contributor
Forum|alt.badge.img+4
  • siriosusContributor
  • Author
  • Contributor
  • December 8, 2015

Hi

I use Oracle as receiving database for workspace (no problem with Oracle), problem is PostgreSQL which is internal database engine of FME server as far as I understand. Service is called FME Server Database, but in fact it is a PostgreSQL server.

What I found by googling PostgreSQL forums an KB's, this issue is related to parallel transaction that prevents some function to update the line until transaction will be committed.

I'm not sure if this is the case but the problems begins exactly after this event.

david_r
Celebrity
  • david_rCelebrity
  • December 8, 2015

Hi

I use Oracle as receiving database for workspace (no problem with Oracle), problem is PostgreSQL which is internal database engine of FME server as far as I understand. Service is called FME Server Database, but in fact it is a PostgreSQL server.

What I found by googling PostgreSQL forums an KB's, this issue is related to parallel transaction that prevents some function to update the line until transaction will be committed.

I'm not sure if this is the case but the problems begins exactly after this event.

There is always a slight possibility of the internal Postgresql database being corrupted, although I've not seen it myself.

I would first try to reboot the server, secondly re-installing FME Server.

If you feel comfortable doing it, you could also consider using PgAdmin and do a full vacuum analyze on the fme_jobs table and rebuilding the indexes.

David

mark2atsafe
Safer
Forum|alt.badge.img+59
  • mark2atsafeSafer
  • Safer
  • December 8, 2015

That shouldn't be the cause. It means that Postgres is doing an automatic cleanup but has put it to the side because there is already a transaction taking place on that table. I think it is possible to turn off auto-vacuum for Postgres using some configuration file, so if you felt really strongly that this was the start of the problem you could turn it off and see what happens. But performance might degrade because the tables aren't being cleaned.

When you restart, do you restart just the database? Or the entire FME Server? Just restarting the database might be one way to see if the problem is really there.

Alternatively, please do contact the Server support team (http://safe.com/support) who will probably know more about this than I do!

FME Evangelist to the Rich and Famous!!
siriosus
Contributor
Forum|alt.badge.img+4
  • siriosusContributor
  • Author
  • Contributor
  • December 9, 2015

That shouldn't be the cause. It means that Postgres is doing an automatic cleanup but has put it to the side because there is already a transaction taking place on that table. I think it is possible to turn off auto-vacuum for Postgres using some configuration file, so if you felt really strongly that this was the start of the problem you could turn it off and see what happens. But performance might degrade because the tables aren't being cleaned.

When you restart, do you restart just the database? Or the entire FME Server? Just restarting the database might be one way to see if the problem is really there.

Alternatively, please do contact the Server support team (http://safe.com/support) who will probably know more about this than I do!

FMEServer is a dependent service, so when I restart FME Server Database FMEServer restarts after it immediately. I'll monitor events more. Thanks

siriosus
Contributor
Forum|alt.badge.img+4
  • siriosusContributor
  • Author
  • Contributor
  • December 9, 2015

That shouldn't be the cause. It means that Postgres is doing an automatic cleanup but has put it to the side because there is already a transaction taking place on that table. I think it is possible to turn off auto-vacuum for Postgres using some configuration file, so if you felt really strongly that this was the start of the problem you could turn it off and see what happens. But performance might degrade because the tables aren't being cleaned.

When you restart, do you restart just the database? Or the entire FME Server? Just restarting the database might be one way to see if the problem is really there.

Alternatively, please do contact the Server support team (http://safe.com/support) who will probably know more about this than I do!

So how to connect to PostgreSQL internal database which was installed by default and no additional configuration was made? Is there any default username/pass and engine instance name?

david_r
Celebrity
  • david_rCelebrity
  • December 9, 2015

So how to connect to PostgreSQL internal database which was installed by default and no additional configuration was made? Is there any default username/pass and engine instance name?

Try connecting on port 7082 with username and password "postgres".

fmelizard
Safer
Forum|alt.badge.img+21
  • fmelizardSafer
  • Safer
  • Best Answer
  • December 29, 2015

@siriosus it might be that the FME Server database has a large number of job records. I'd recommend purging the jobs from the database. This can be done with PG Admin or you can enable a preinstalled schedule to perform this task regularly. See instructions for doing so in 2013:

http://docs.safe.com/fme/2013sp4/html/FME_Server_D... You may need to edit the workspace. In FME Server 2016, it is even easier to configure this.

Community Stats

32,154
Posts
122,043
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.