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"?

S
Forum|alt.badge.img

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.

View original
Did this help you find an answer to your question?
This post is closed to further activity.
It may be a question with a best answer, an implemented idea, or just a post needing no comment.
If you have a follow-up or related question, 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

S
Forum|alt.badge.img
  • siriosus
  • Author
  • December 8, 2015

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

david_r
Evangelist
  • david_rEvangelist
  • December 8, 2015

Hi

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

David

S
Forum|alt.badge.img
  • siriosus
  • Author
  • 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
Evangelist
  • david_rEvangelist
  • December 8, 2015
siriosus wrote:

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+43
  • 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!

S
Forum|alt.badge.img
  • siriosus
  • Author
  • December 9, 2015
mark2atsafe wrote:

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

S
Forum|alt.badge.img
  • siriosus
  • Author
  • December 9, 2015
mark2atsafe wrote:

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
Evangelist
  • david_rEvangelist
  • December 9, 2015
siriosus wrote:

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
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • 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.

Helpful Members This Week

Community Stats

30,992
Posts
117,467
Replies
38,774
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.