13 million rows in action_history table in TeamCity SQL database

I'm using TeamCity 7.1.5.  We started using TeamCity in July 2013.

I have about 13 million rows in the action_history table.  We are growing at a rate of about 180K rows per week.

I tried to lower the "Build History Clean-up" to keep only 30 days of everything (see below).   Any ideas how to reduce the size of the table ?

10 comments
Comment actions Permalink

Hi.

I am wondering if the number of records in the action_history table normal.  Does anyone else have a similar number of rows ?   I would appreciate any feedback on this.

This doesn't seem scalable if I can't cleanup.

Thanks,
Jayesh

0
Comment actions Permalink

Jayesh,

Does the current size of the table causes any issues?

The table stores "audit" data (visible on Administration > Audit). What are the most of the entries listed there? Do you use lots of automatic scripting to change the settings?

If the table gives you problems, in theory you can drop the oldest entries in it - no data will be lost other then the audit entries.

0
Comment actions Permalink

We had major performance issues due to varchar converstion to nvarchar (http://youtrack.jetbrains.com/issue/TW-32808).  While debugging the issue, we found it very unusual that this table was so big.

Yes. We do have automation to update settings.  Here's one possibility: We are adding/removing users from groups.  REST call to add user to a group seems to remove all users from group and then re-adds them all again.  My colleague will shortly post about this issue.

If I try to access history, it slows down the whole instance.   It gets so slugish that I have to restart services.  I can't simply do that during the day everytime someone decides to look at history.   Is there a safe way to cut down on older records ?

Thanks,
Jayesh

0
Comment actions Permalink

Hi Jayesh,

Thank you for hte details.

There is an issue to improve cleaning up audit table: http://youtrack.jetbrains.com/issue/TW-18856
It also has a workaround described to reduce the number of days to keep audit data for.

> We had major performance issues due to varchar converstion to nvarchar (http://youtrack.jetbrains.com/issue/TW-32808).

Could you please detail these? What TeamCity version was in use and what performance issue have you seen?


> If I try to access history, it slows down the whole instance.   It gets so slugish that I have to restart services.

This is something we might need to look into to improve performance. I would appreciate if you can get a set of server thread dumps and post a new issue into the tracker with the data

0
Comment actions Permalink

Thanks Yegor for the workaround.  This worked great in our dev server, but
when trying in production, keeping the last 30 days didn't change the number of rows.  When changing the value to keep 120 days (need to restart services after each change and running cleanup), it reduced the number of rows to 12 million.  So I will gradually bring the number to 30 days in the next couple of days.

We are using TeamCity 7.1.4 and MS SQL database.  When we had the issue with nvarchar conversion with 13 million rows, the CPU on the SQL server reached 100%.  Even restarting services didn't help as the process of bringing up teamcity started the same queries.  The service was unusable and all applications on that database server were affected as a result.  That issue was quickly fixed by converting the table to nvarchar.  I later found the open ticket with the workaround :-(

As requested, I posted the threadDump in the ticket:
http://youtrack.jetbrains.com/issue/TW-35831

0
Comment actions Permalink

Jayesh,

> when trying in production, keeping the last 30 days didn't change the number of rows.

Not sure I fully understnad this. Do you mean after cleanup the number of rows did not reduce? Were there any erors in the server log?


> When we had the issue with nvarchar conversion with 13 million rows

Could you please detail that - was this on server upgrade? Or the server performance just reduced suddenly?

> That issue was quickly fixed by converting the table to nvarchar.

Do you mean you have manually run modification queries on the database? Or you mean on TeamCity server upgrade?


> As requested, I posted the threadDump in the ticket:
In that dump, the server is out of memory, it is recommended to increase the -Xmx setting of the server memory.

0
Comment actions Permalink

> > when trying in production, keeping the last 30 days didn't change the number of rows.
> Not  sure I fully understnad this. Do you mean after cleanup the number of  rows did not reduce? Were there any erors in the server log?

Here's a snippet of the teamcity-server.log:

[2014-03-26 22:59:36,822]   WARN -  jetbrains.buildServer.CLEANUP - Error in general data cleaner class jetbrains.buildServer.serverSide.impl.audit.AuditLogDataCleaner. Unexpected exception SQLServerException: SQL error when doing: Executing  with parameters: [1393296888492] caused SQL error:
SQL query: delete from action_history where (select comments.when_changed from comments where comments.id = action_history.comment_id) < ? and action in (-1, 1, 2, 7, 8, 9, 10, 15, 16, 17, 18, 19, 20, 21, 22, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 117, 118, 119, 120, 121)
SQL exception: The transaction log for database 'tcmswm' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
jetbrains.buildServer.serverSide.db.UnexpectedDBException: Unexpected exception SQLServerException: SQL error when doing: Executing  with parameters: [1393296888492] caused SQL error:
SQL query: delete from action_history where (select comments.when_changed from comments where comments.id = action_history.comment_id) < ? and action in (-1, 1, 2, 7, 8, 9, 10, 15, 16, 17, 18, 19, 20, 21, 22, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 117, 118, 119, 120, 121)
SQL exception: The transaction log for database 'tcmswm' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:320)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery.doExecuteQuery(GenericQuery.java:423)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery.access$600(GenericQuery.java:28)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery$8.action(GenericQuery.java:396)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery.underPreparedStatement(GenericQuery.java:485)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery.access$700(GenericQuery.java:28)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery$10.apply(GenericQuery.java:473)
        at jetbrains.buildServer.serverSide.db.DBFunctions.withDB(DBFunctions.java:2260)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery.underPreparedStatement(GenericQuery.java:470)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery.doExecuteQuery(GenericQuery.java:393)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery.access$100(GenericQuery.java:28)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery$1.run(GenericQuery.java:136)
        at jetbrains.buildServer.serverSide.db.DBFacade$5.doInConnection(DBFacade.java:391)
        at jetbrains.buildServer.serverSide.db.DBFacade$6.doInConnection(DBFacade.java:415)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:341)
        at jetbrains.buildServer.serverSide.db.DBFacade._runSql(DBFacade.java:411)
        at jetbrains.buildServer.serverSide.db.DBFacade.runSql(DBFacade.java:389)
        at jetbrains.buildServer.serverSide.db.queries.GenericQuery.execute(GenericQuery.java:139)
        at jetbrains.buildServer.serverSide.impl.audit.AuditLogCleanerImpl.cleanBefore(AuditLogCleanerImpl.java:1)
        at jetbrains.buildServer.serverSide.impl.audit.AuditLogDataCleaner.performCleanup(AuditLogDataCleaner.java:43)
        at jetbrains.buildServer.serverSide.impl.cleanup.ServerCleanupManagerImpl.runGlobalCleaners(ServerCleanupManagerImpl.java:131)
        at jetbrains.buildServer.serverSide.impl.cleanup.ServerCleanupManagerImpl.access$400(ServerCleanupManagerImpl.java:17)
        at jetbrains.buildServer.serverSide.impl.cleanup.ServerCleanupManagerImpl$3.performCleanup(ServerCleanupManagerImpl.java:3)
        at jetbrains.buildServer.serverSide.db.DBFacade$1$1.doInConnection(DBFacade.java:178)
        at jetbrains.buildServer.serverSide.db.DBFacade$6.doInConnection(DBFacade.java:415)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:341)
        at jetbrains.buildServer.serverSide.db.DBFacade._runSql(DBFacade.java:411)
        at jetbrains.buildServer.serverSide.db.DBFacade.access$000(DBFacade.java:33)
        at jetbrains.buildServer.serverSide.db.DBFacade$1.doInTransaction(DBFacade.java:174)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
        at jetbrains.buildServer.serverSide.db.DBFacade.compact(DBFacade.java:171)
        at jetbrains.buildServer.serverSide.impl.cleanup.ServerCleanupManagerImpl.startCleanup(ServerCleanupManagerImpl.java:119)
        at jetbrains.buildServer.serverSide.impl.cleanup.ServerCleanupManagerImpl$$FastClassByCGLIB$$ba2c8525.invoke(<generated>)
        at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
        at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at jetbrains.buildServer.serverSide.impl.auth.TeamCityMethodSecurityInterceptor.invoke(TeamCityMethodSecurityInterceptor.java:3)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.Cglib2AopProxy$FixedChainStaticTargetInterceptor.intercept(Cglib2AopProxy.java:572)
        at jetbrains.buildServer.serverSide.impl.cleanup.ServerCleanupManagerImpl$$EnhancerByCGLIB$$23d6c3e1.startCleanup(<generated>)
        at jetbrains.buildServer.controllers.admin.cleanup.ManualCleanupRunner$1.run(ManualCleanupRunner.java:3)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
        at java.util.concurrent.FutureTask.run(FutureTask.java:138)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:206)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:662)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The transaction log for database 'tcmswm' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases


> > When we had the issue with nvarchar conversion with 13 million rows
> Could you please detail that - was this on server upgrade? Or the server performance just reduced suddenly?

We installed 7.1.4 and haven't upgraded yet.  The SQL server performance must have been reaching 100% CPU utilization as the table size grew.   We saw degraded performance, but we were tweeking the java heap size on the server.

> > That issue was quickly fixed by converting the table to nvarchar.
> Do you mean you have manually run modification queries on the database? Or you mean on TeamCity server upgrade?

The DBA created a new table with nvarchar, copied the older table contents to the new table and renamed the two tables.  Since it was an outage that affected more than on application, we resorted to this quick fixed.

> > As requested, I posted the threadDump in the ticket:
> In that dump, the server is out of memory, it is recommended to increase the -Xmx setting of the server memory.

We increased the memory several times.  It has been set to the following since Feb 14: "-Xms750m -Xmx2048m -XX:MaxPermSize=270m".
Not to create another thread within this one, but we have 48GB RAM on the dedicated teamcity linux server, but teamcity could not start if -Xmx was set to 4096m.  So I had to keep it at 2048m (which should be enough).

0
Comment actions Permalink

> Not to create another thread within this one, but we have 48GB RAM on the dedicated teamcity linux server, but teamcity could not start if -Xmx was set to 4096m.  So I had to keep it at 2048m (which should be enough).

You are probably running 32-bit java. You can find its version at Administration->Diagnostics page.

0
Comment actions Permalink

Hello Jayesh,

how much the transaction log space in the TC database?

If it less than 4G, I'd recommend increasing it.

0

Please sign in to leave a comment.