How to handle long running query : DELETE FROM archive_names WHERE NOT EXISTS (SELECT 1 FROM indexed_archives_entries i WHERE i.entry_name_id = name_id)

ISSUE: 

You may see the following query running for a long time.

UPDATE unique_ids SET current_id = $1 where index_type = $2 AND current_id = $3

UPDATE unique_ids SET current_id = $1 where index_type = $2 AND current_id = $3

DELETE FROM archive_names WHERE NOT EXISTS (SELECT 1 FROM indexed_archives_entries i WHERE i.entry_name_id = name_id)

When you run a Java thread dump, then you may see several Java locks.

The error in Artifactory may look like this:

  

2015-07-21 12:15:23,317 [art-exec-279498] [ERROR] (o.q.c.JobRunShell   :211) - Job artifactory.org.artifactory.addon.ha.cluster.HazelcastServiceImpl$HazelcastMembersIntroductionJob#a538e01f-5a70-4152-8a81-df6874477b3d threw an unhandled Exception:org.artifactory.storage.StorageException: Failed to load list of Artifactory servers	at org.artifactory.storage.db.servers.service.ArtifactoryServersServiceImpl.getAllArtifactoryServers(ArtifactoryServersServiceImpl.java:29) ~[artifactory-storage-db-3.9.2.jar:na]	at sun.reflect.GeneratedMethodAccessor69.invoke(Unknown Source) ~[na:na]	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_67]	at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_67]	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-4.1.5.RELEASE.jar:4.1.5.RELEASE]	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201) ~[spring-aop-4.1.5.RELEASE.jar:4.1.5.RELEASE]	at com.sun.proxy.$Proxy35.getAllArtifactoryServers(Unknown Source) ~[na:na]	at org.artifactory.storage.db.servers.service.ArtifactoryServersCommonServiceImpl.getActiveMembers(ArtifactoryServersCommonServiceImpl.java:97) ~[artifactory-core-3.9.2.jar:na]	at org.artifactory.storage.db.servers.service.ArtifactoryServersCommonServiceImpl.getOtherActiveMembers(ArtifactoryServersCommonServiceImpl.java:92) ~[artifactory-core-3.9.2.jar:na]	at org.artifactory.addon.ha.cluster.HazelcastServiceImpl.introduceMembers(HazelcastServiceImpl.java:177) ~[artifactory-addon-ha-3.9.2.jar:na]	at org.artifactory.addon.ha.cluster.HazelcastServiceImpl$HazelcastMembersIntroductionJob.onExecute(HazelcastServiceImpl.java:208) ~[artifactory-addon-ha-3.9.2.jar:na]	at org.artifactory.schedule.quartz.QuartzCommand.execute(QuartzCommand.java:50) ~[artifactory-storage-common-3.9.2.jar:na]	at org.quartz.core.JobRunShell.run(JobRunShell.java:202) ~[quartz-2.2.1.jar:na]	at org.artifactory.schedule.ArtifactoryConcurrentExecutor$RunnableWrapper.run(ArtifactoryConcurrentExecutor.java:102) [artifactory-storage-common-3.9.2.jar:na]	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_67]	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_67]	at java.lang.Thread.run(Thread.java:745) [na:1.7.0_67]Caused by: org.postgresql.util.PSQLException: FATAL: sorry, too many clients already	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:398) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:173) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.Driver.makeConnection(Driver.java:397) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.Driver.connect(Driver.java:267) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:701) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:635) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128) ~[tomcat-jdbc-7.0.47.jar:na]	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) ~[spring-jdbc-4.1.5.RELEASE.jar:4.1.5.RELEASE]	at org.artifactory.storage.db.util.JdbcHelper.getConnection(JdbcHelper.java:66) ~[artifactory-storage-db-3.9.2.jar:na]	at org.artifactory.storage.db.util.JdbcHelper.executeSelect(JdbcHelper.java:86) ~[artifactory-storage-db-3.9.2.jar:na]	at org.artifactory.storage.db.servers.dao.ArtifactoryServersDao.getAllArtifactoryServers(ArtifactoryServersDao.java:84) ~[artifactory-storage-db-3.9.2.jar:na]	at org.artifactory.storage.db.servers.service.ArtifactoryServersServiceImpl.getAllArtifactoryServers(ArtifactoryServersServiceImpl.java:27) ~[artifactory-storage-db-3.9.2.jar:na]	... 16 common frames omitted2015-07-21 12:15:23,319 [art-exec-279498] [ERROR] (o.q.c.ErrorLogger   :2425) - Job (artifactory.org.artifactory.addon.ha.cluster.HazelcastServiceImpl$HazelcastMembersIntroductionJob#a538e01f-5a70-4152-8a81-df6874477b3d threw an exception.org.quartz.SchedulerException: Job threw an unhandled exception.	at org.quartz.core.JobRunShell.run(JobRunShell.java:213) ~[quartz-2.2.1.jar:na]	at org.artifactory.schedule.ArtifactoryConcurrentExecutor$RunnableWrapper.run(ArtifactoryConcurrentExecutor.java:102) [artifactory-storage-common-3.9.2.jar:na]	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_67]	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_67]	at java.lang.Thread.run(Thread.java:745) [na:1.7.0_67]Caused by: org.artifactory.storage.StorageException: Failed to load list of Artifactory servers	at org.artifactory.storage.db.servers.service.ArtifactoryServersServiceImpl.getAllArtifactoryServers(ArtifactoryServersServiceImpl.java:29) ~[artifactory-storage-db-3.9.2.jar:na]	at sun.reflect.GeneratedMethodAccessor69.invoke(Unknown Source) ~[na:na]	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_67]	at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_67]	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-4.1.5.RELEASE.jar:4.1.5.RELEASE]	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201) ~[spring-aop-4.1.5.RELEASE.jar:4.1.5.RELEASE]	at com.sun.proxy.$Proxy35.getAllArtifactoryServers(Unknown Source) ~[na:na]	at org.artifactory.storage.db.servers.service.ArtifactoryServersCommonServiceImpl.getActiveMembers(ArtifactoryServersCommonServiceImpl.java:97) ~[artifactory-core-3.9.2.jar:na]	at org.artifactory.storage.db.servers.service.ArtifactoryServersCommonServiceImpl.getOtherActiveMembers(ArtifactoryServersCommonServiceImpl.java:92) ~[artifactory-core-3.9.2.jar:na]	at org.artifactory.addon.ha.cluster.HazelcastServiceImpl.introduceMembers(HazelcastServiceImpl.java:177) ~[artifactory-addon-ha-3.9.2.jar:na]	at org.artifactory.addon.ha.cluster.HazelcastServiceImpl$HazelcastMembersIntroductionJob.onExecute(HazelcastServiceImpl.java:208) ~[artifactory-addon-ha-3.9.2.jar:na]	at org.artifactory.schedule.quartz.QuartzCommand.execute(QuartzCommand.java:50) ~[artifactory-storage-common-3.9.2.jar:na]	at org.quartz.core.JobRunShell.run(JobRunShell.java:202) ~[quartz-2.2.1.jar:na]	... 4 common frames omittedCaused by: org.postgresql.util.PSQLException: FATAL: sorry, too many clients already	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:398) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:173) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.Driver.makeConnection(Driver.java:397) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.postgresql.Driver.connect(Driver.java:267) ~[postgresql-9.2-1004.jdbc4.jar:na]	at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:701) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:635) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188) ~[tomcat-jdbc-7.0.47.jar:na]	at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128) ~[tomcat-jdbc-7.0.47.jar:na]	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) ~[spring-jdbc-4.1.5.RELEASE.jar:4.1.5.RELEASE]	at org.artifactory.storage.db.util.JdbcHelper.getConnection(JdbcHelper.java:66) ~[artifactory-storage-db-3.9.2.jar:na]	at org.artifactory.storage.db.util.JdbcHelper.executeSelect(JdbcHelper.java:86) ~[artifactory-storage-db-3.9.2.jar:na]	at org.artifactory.storage.db.servers.dao.ArtifactoryServersDao.getAllArtifactoryServers(ArtifactoryServersDao.java:84) ~[artifactory-storage-db-3.9.2.jar:na]	at org.artifactory.storage.db.servers.service.ArtifactoryServersServiceImpl.getAllArtifactoryServers(ArtifactoryServersServiceImpl.java:27) ~[artifactory-storage-db-3.9.2.jar:na]	... 16 common frames omitted

  

REASON & RESOLUTION:

The query DELETE FROM archive_name is triggered by the garbage collector. This query may run for several hours depending on the amount of artifacts that were deleted, removed and cleaned during the unnecessary indexing.

The UPDATE unique_ids query is used to reserve unique id’s (that are being used by Artifactory to identify items with unique id’s). In order to decrease the amount of inquiries to the database you can change the size of reserved id’s by making changes in the $ARTIFACTORY_HOME/etc/artifactory.system.properties file. The value that needs to be modified is:

artifactory.db.idGenerator.fetch.amount={size_of_reserved_ids}. Please uncomment this line. The default value is between 2000. Please increase this value to as needed (e.g. around 10000). Please note that this change requires a restart of the Artifactory server. The increasing the size of the reserved id’s would allow Artifactory to work fluently with less wait time (locks) for reserving the id’s.