Slow performance when expanding projects

Answered

With a lot of build plans the performance is very slow when expanding a project.

For instance we have a "Extensions" project (parent is _Root) which has child projects for more than 100 extensions. Each of these extensions has 4 child projects. And inside them are about 10'000 build plans.

  • Root
    • Extensions
      • A-Extension
        • Branch / Branch
          • dozens of build plans
        • Branch / Tag
          • dozens of build plans
        • Tag / Branch
          • dozens of build plans
        • Tag / Tag
          • dozens of build plans
      • B-Extension


To expand a node (no matter which) takes each time about 10 seconds on a powerful Amazon Linux c-xlarge instance.

The MySql CPU goes up each time a node is expanded and sometimes TeamCity server crashes because of out of memory. We can provide more RAM, but that does not solve the performance issue.

We haven't yet analysed it further. But maybe that already is enough for someone to tell us why the performance is so slow for this action?

3 comments

According to mysql statistics 10'000 sql commands are executed per second and 10 mb loaded from database which takes 10 seconds to display the project tree in the browser. So it is not a single slow sql query.

0

I did some little SQL research and found something very interesting.

Preconditions: You have lots of build configurations (10'000 or more), all of them are set to be visible and all folders are collpsed.

  1. Delete general log file in /var/lib/mysql
  2. Log into MySql so that you have its command line ready
  3. Reload TeamCity in the browser
  4. Expand a folder in TeamCity UI which contains no or very few build types (that little expansion will now take 10 seconds on a fast environment)
  5. In the MySql command line immediately execute: SET GLOBAL general_log = 'ON';
  6. Wait a few seconds and execute: SET GLOBAL general_log = 'OFF';
  7. Open general log file in folder var/lib/mysql in an editor


In the log file I can see hundreds and thousands of queries like:

select     BS.build_type_id,     BS.build_id,     BS.id,     A.build_number,     A.agent_name,     A.agent_type_id,     A.build_start_time_server,     A.build_start_time_agent,      A.build_finish_time_server,     A.status,     A.status_text,    A.user_status_text,    A.pin,     A.requestor,     A.is_canceled,     C.user_id,     C.description   from build_state BS   join history A on BS.build_type_id = A.build_type_id and BS.build_id = A.build_id  left join canceled_info C on C.build_id = A.build_id  where (A.build_id = 56302) and (BS.remove_from_queue_time is not null and BS.is_deleted = 0) order by BS.remove_from_queue_time desc, BS.build_id desc

If I execute it on command line I see the build type id (for instance bt255621) which I can use to execute in a browser and get a data XML: https://my.teamcity.com/httpAuth/app/rest/buildTypes/bt255621

Then I see that the build type is one which is in the collapsed area and which was not expanded. TeamCity should not load this data, thats wasting a lot of CPU ressources if thousands of build types exist. That takes 10 seconds each time unnecessarily.

Can you please verify this? If you can confirm it, can you please improve the implementation so that the performance will be much better in a future version of TeamCity?

0

Please sign in to leave a comment.