Problems with the performance of my application

Added by Yudiel La Rosa G. over 6 years ago

I'm having problems with my application performance.

Application Server:
Distributor ID: Debian
Description: Debian GNU / Linux 6.0.7 (squeeze)
Release: 6.0.7
Codename: squeeze
Cores: 4
RAM: 5 GB

nginx 1.0.7
thin 1.8

Database Server:
Distributor ID: Debian
Description: Debian GNU / Linux 6.0 (squeeze)
Release: 6.0
Codename: squeeze
Cores: 4
RAM: 3 GB

PostgreSQL: 9.0.5
1.4.2 PgBouncer

The tables in the database have the following records:
"issues" has more than 60000
"custom_values​​" has over 5000000

The system currently has 6000 users and is expected to reach 15,000 users. Currently only goes to 500 concurrent users.

The application is being way too slow and many requests ending in timeout error.

As I can do to achieve a great performance suitable for the system. Currently the system uses an average of 80% of RAM and 50% of CPU on both servers.

Thanks for your help

Replies (12)

RE: Problems with my application performance - Added by Yudiel La Rosa G. over 6 years ago

Sorry
redmine versions is 1.3.3, the Rails 2.3.14 and ruby 1.8.7 (2010-08-16 patchlevel 302) [i486-linux].

RE: Problems with the performance of my application - Added by Jean-Baptiste Barth over 6 years ago

One of your colleagues reached me via email so I'll try to answer this post as the subject is very relevant to my current interests. I reproduce some of his thoughts here before replying:

[...]

First the environment:
- Ruby 1.8.7 (We want to update first this to REE or to 1.9.3)
- RedMine 1.3.3
- Rails 2.3.14
- PostgreSQL 9.0.7
- Nginx + Thin for deployment

You know that RedMine has a little complicated data model, where custom_values´s table is the main point to storage all values. In our project, we have 28 different trackers, and based on this approach, we design all trackers with a lot of custom_fields, and when you request for a particular issue, Active Record has a lot of SQL queries doing joins between custom_fields and custom_values, which is very bad for our performance for our numbers. To give you a little background of the statistics that we are managing, look this:
- Number of issues: 52634
- Number of users using the application: 4540 with a trend to grow to 10,000
- Number of rows in custom_value table: 3,079,138

So, you are seeing that the numbers are very huge, so, we are working for a good approach for data partition specifically in the custom_value table.

You were seeing to use a Instagram-based approach, but for that, we have to touch a lot of things in the application, and this is not good for us.[1]

Then, we saw a gem called partitioned developed by Fiksu´s team. [2]
It seems to be good for our use case, but we need an image of the data model of RedMine to see all relationships with the custom_values table to be able to partition it. The problem with this approach, is that we have the system in product already and all this should be done in development stage. So, we are looking for good solutions to solve this.

Can you help us with this issue?
One of the developers of my team published this here:
http://www.redmine.org/boards/2/topics/37066
but he didn´t obtain any answer, so, It would be nice to see how we can solve this.
Thanks a lot for your time.

[...]

[1] http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
[2] https://github.com/fiksu/partitioned

RE: Problems with the performance of my application - Added by Yudiel La Rosa G. over 6 years ago

This is the current environment that we are using today:

  • Ruby version 1.8.7 (i486-linux)
  • RubyGems version 1.3.7
  • Rack version 1.1
  • Rails version 2.3.14
  • Active_record version 2.3.14
  • Active_resource version 2.3.14
  • Action_mailer version 2.3.14
  • Active_support version 2.3.14
  • Edge Rails revision unknown
  • Application root /var/www/sicact133
  • Environment development
  • Database adapter postgresql
  • Database schema version 20120531191832
Redmine plugins:
  • Redmine Local Avatars plugin 0.1.1
  • Redmine Tracker Security Layer plugin 0.0.1
  • Redmine SAML auth plugin 0.2
  • Redmine Project filtering plugin 0.9.5
  • Redmine Schedule plugin 0.0.1
  • Redmine Sicac Patch plugin 0.0.1
  • Diseñador de peticiones 0.1.0
  • Redmine Custom Fields plugin 0.0.1
  • Redmine Plus Security User plugin 0.0.1
  • Tab Plugin GRD 0.3.1

RE: Problems with the performance of my application - Added by Jean-Baptiste Barth over 6 years ago

I'll list various thougths about your request here. No obvious solution but I hope it'll help.

1/ First I'd strongly encourage you to upgrade your components. Redmine 1.3.3 is very, very old now. Your Rails version is not supported anymore, it has a lot of security problems, and Redmine 2.x saw a lot of bugfixes and shiny new features. If you use the debian native packages I'd suggest you reconsider your choices, they're not very well maintained because debian lifecycle is far too slow for something like Redmine. Plus whatever approach you choose in the end, you may want to use some recent gems and gems integration is much easier in new versions of Redmine (thanks to bundler), and it can even be done easily in plugins. If you have incompatible plugins, I hope you have rails skills internally, or maybe you can hire some rails devs to do the job...

2/ Maybe you could even upgrade Postgresql, 9.2 has received various performance improvements, even tho I don't know the details

3/ If you want to track down the slow actions in your Redmine instance, you may want to use a tool like http://rpm.newrelic.com/ ; it has a small cost for 2 servers and it can help you determine what are the slowest actions, is the bottleneck your app or database server, etc.

4/ Be sure you cache static assets properly and you have correct tuning in place for your application server (which is ?) and your database server (mainly shared_memory, work_mem, etc.)

5/ if you have a lot of users and some developers, I'd suggest you invest a bit on the hardware: 4 cores with 3/5GB of RAM is not that high nowadays. I don't really know the load you face though. But Newrelic can help you determine if your hardware is totally full and need replacement, provided you have already done all possible optimizations.

6/ The numbers may appear as pretty high at first sigh but number of users in database or issues shouldn't be a problem per se (redmine.org has 60k users and everything's fine as far as I can tell). Having hundreds or thousands of projects for each user can be problematic, don't know if this is your case. The number of custom_fields or custom_value can also be a problem of course tho I never tested it extensively. Can you explain a bit more how you identified it was the bottleneck ? I guess there won't be many indexes missing from those, but worth a check.

7/ I'd avoid sharding as much as possible and reserve it as an ultimate solution. I don't know the "partitioned" gem, but if you take instagram's road you'll probably have to change many core classes and it's a lot more complexity I think. As I see it you won't be able to go back and this can become a real nightmare.

8/ If you can confirm custom_fields/custom_values implementation is the only (or main) bottleneck, it should be possible to replace it externally in your own implementation (ideally in a tested plugin so you can upgrade things as smoothly as possible over time). The plugin would hide standard custom fields everywhere and replace it with something faster. Either flat columns in appropriate tables, or a unique column with serialized data (might not be ideal for searching features), or a fast external system (redis, other postgres database, whatever), or something based on Postgres' hstore feature ; there are many options that could be faster than current approach, but each of them requires additional complexity, security compromises or are less portable (hstore) than current implementation.

9/ Cache everything possible. I personnally had some performance problems with my work redmine instance in the past few months. If you can identify views or actions that are very slow, it's not that hard to add some fragment caching in some views. For instance the project jump box is pretty slow for users who have 100+ projects, so I put it in a cache for every user so that it's only calculated once for each user. Depending on the bottlenecks you detect it might be possible to wrap parts of some views in a cache call so that you only feel the performance problem on first load. (assuming you have sufficient rails skills internally of course...)

If you have some more information about your current analysis, I'd love to hear it. We'd like to make redmine suitable for big installations like yours but it's not always possible to integrate patches that improve performance for large instances while killing performance on small ones, I hope you'll understand.

RE: Problems with the performance of my application - Added by Jean-Baptiste Barth over 6 years ago

Yudiel La Rosa G. wrote:

  • Environment development

I hope you don't run your production server in development mode, do you ? ;)

RE: Problems with the performance of my application - Added by Etienne Massip over 6 years ago

If that may be of interest it seems that Redmine stores a record with an empty value ('') in custom_values table for optional custom fields even when no value is selected/filled in.

I think, but that need to be tested and I'll let you do the test, that these records can be deleted safely with no side effect in the workflows.

E.g. 68% of my custom_values records have an empty value (I only use optional custom fields).

RE: Problems with the performance of my application - Added by Etienne Massip over 6 years ago

Last thought about Ruby implementations: MRI 1.8.7 is not known to be a fast one but 1.9.3 is and it seems 2.0.0 (which Redmine is compatible with) is even faster.

I think that latest Redmine is not fully compatible with REE or Rubinius as I tried a few months ago to run the full test suite with them via GH+TravisCI and at least encoding issues were raised.

RE: Problems with the performance of my application - Added by Etienne Massip over 6 years ago

You might be interested in using memcached for your AR cache also (although someone has filled and issue about using it but it should be solved by configuration, see #13449).

RE: Problems with the performance of my application - Added by Yudiel La Rosa G. over 6 years ago

1/We have some plugins which are the core of our business here, and we tried to migrate them to the last version of RedMine (2.2.3 with Rails 3.2) and we faced a lot of problems. If you can point me to a good link how to migrate all old plugins to a new version of RedMine, it would be nice.

2/ It's in today's plans. 9.2 has a lof of improvements: Index-only scans, better scalability, cascading replication with Streaming Replication/Hot Standby, etc.

3/ We have to use NewRelic for this. We have seen that the slowest views to render are: projects and issues. WE have 99 main projects and 1587 subprojects.

4/ We use the Nginx + Thin combination, but we have tested unicorn, and we want to migrate to it, because it's very fast.

5/ Ok, we will do that.

6/ 99 main projects and 1587 subprojects, so to render the projects's view, there's an average delay of 10 to 14 s, which s very slow for us.

7/ Yes, we discussed all that yesterday and we took that conclusion too.

8/ We have evaluating to use Redis for custom_values storage/reading, because it's very fast. What do yo think about this approach?

9/ The first candidates to do this are the project's list view and issues's list view.
Thanks a lot for your invaluable help, Jean.

RE: Problems with the performance of my application - Added by Yudiel La Rosa G. over 6 years ago

Etienne Massip wrote:

You might be interested in using memcached for your AR cache also (although someone has filled and issue about using it but it should be solved by configuration, see #13449).

Etienne we began testing right now memcached. Thanks for your help

RE: Problems with the performance of my application - Added by Etienne Massip over 6 years ago

Etienne Massip wrote:

I think that latest Redmine is not fully compatible with REE or Rubinius as I tried a few months ago to run the full test suite with them via GH+TravisCI and at least encoding issues were raised.

Ok, still true with Rbx but REE passed the test suite.

RE: Problems with the performance of my application - Added by Jean-Baptiste Barth over 6 years ago

Etienne Massip wrote:

You might be interested in using memcached for your AR cache also (although someone has filled and issue about using it but it should be solved by configuration, see #13449).

Redmine doesn't use much cache by default (activity, some wiki pages or diffs maybe?)

If you're on a unique application server, I think disk cache is good enough. I do a lot of fragment caching (views) on my redmine instance through plugins, and I didn't see a significant difference between the two. If you scale on multiple servers or caches objects, that's not the same story though.

First Yudiel La Rosa G. wrote:

1/We have some plugins which are the core of our business here, and we tried to migrate them to the last version of RedMine (2.2.3 with Rails 3.2) and we faced a lot of problems. If you can point me to a good link how to migrate all old plugins to a new version of RedMine, it would be nice.

This thread is a starting point: Writing plugins compatible with both Redmine 1.x and 2.x ...

In my experience migrating plugins is a pain if you have a lot of RJS/Prototype, but it should be do-able else. The major problem is that 99% of Redmine plugins are poorly tested so finding bugs is difficult.

2/ It's in today's plans. 9.2 has a lof of improvements: Index-only scans, better scalability, cascading replication with Streaming Replication/Hot Standby, etc.

Yeah! I'll consider that for my own instance then, thanks.

3/ We have to use NewRelic for this. We have seen that the slowest views to render are: projects and issues. WE have 99 main projects and 1587 subprojects.

projects/index ? projects/show ? issues/index ? issues/show ? issues/create|update ?

create/update can typically be slow because notification recipients computation is a very slow process. There's at least one patch to improve that which can easily be backported to 1.x I think, see #13301

[...]

6/ 99 main projects and 1587 subprojects, so to render the projects's view, there's an average delay of 10 to 14 s, which s very slow for us.

I assume you talk about ProjectsController#index (i.e. "Projects" link in the top bar, accessible by all users). It can easily be cached, you just have to think about what would cause the view to be changed, so you can build a cache key. Cache key computation shouldn't be so expensive if you just retrieve the last record for everything.

Then you can wrap the original partial in something like:

<% cache ["projects/index", "user-#{User.current.id}/#{User.current.updated_on.iso8601}", Project.first(:order=>"updated_on desc"), Membership.last] do %>
   <the partial here>
<% end %>

I tried to stick with Rails 2.3 AR style, use at your own risks ;)

Unfortunately:
  • 1/ Redmine doesn't follow exactly Rails' cache expectations about column names (updated_on instead of updated_at) so cache keys for users have to be generated manually
  • 2/ some Redmine models don't have timestamps (Role for instance) which make difficult to have something totally bullet-proof

An other strategy I use sometimes is to cache for X minutes for each user. Etags might also be used for this.

7/ Yes, we discussed all that yesterday and we took that conclusion too.

8/ We have evaluating to use Redis for custom_values storage/reading, because it's very fast. What do yo think about this approach?

I think it's fast enough, good idea, tell me how it goes :) Maybe using a simple Postgres table without joins would be good too, deserves a benchmark.

9/ The first candidates to do this are the project's list view and issues's list view.
Thanks a lot for your invaluable help, Jean.

No problem. Etienne also gave lots of good advices, don't hesitate to tell us if you have good news about your project!

(1-12/12)