Redmine 2.X Tuning with MySQL

Added by Jorge Cabrera over 5 years ago

Hi,

I've been running Redmine for more than a year but lately performance is not so good. My main problem is that we have 15000 users most of them in one project. Accessing that project takes more than 15 seconds now and same with creating new issues (it displays all users as watchers). In projects were we have only 30 or 40 users performance is acceptable but on the project with 15k users things are very very slow.

I tried upgrading redmine version to 2.5.0 in our pre-production server but it didn't show any progress. Now I'm trying to do some tuning in MySQL (with the help of mysqltuner) playing with cache since I see in mysql log that Redmine uses the same queries a lot of times when displaying info but haven't seen anything go better there neither.

Can someone please give me some pointers on how to get a better performance on MySQL? 15000 users in one project is something that should work in Redmine or have I passed the limit? Maybe creating indexes on some tables?

The summary of my configuration is this:

  • Redmine version 2.4.2.stable
  • Ruby version 1.9.3-p194 (2012-04-20) [x86_64-linux]
  • Rails version 3.2.16
  • Environment production
  • Database adapter Mysql2
  • MySQL version 5.5.35
  • Operating system Debian 7.4 (Wheezy)
  • CPU 2 Intel Xeon CPU E3-1225 V2 @ 3.20GHz
  • Memory 4GB

Output of mysqltuner is:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-0+wheezy1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 286K (Tables: 2)
[--] Data in InnoDB tables: 122M (Tables: 50)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 50

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 95d 1h 47m 24s (16M q [1.987 qps], 571K conn, TX: 670B, RX: 5B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (14% of installed RAM)
[OK] Slow queries: 0% (898/16M)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/122.0K
[OK] Key buffer hit rate: 100.0% (5B cached / 37 reads)
[OK] Query cache efficiency: 63.1% (8M cached / 13M selects)
[!!] Query cache prunes per day: 23059
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 381K sorts)
[!!] Joins performed without indexes: 42236
[OK] Temporary tables created on disk: 19% (1M on disk / 6M total)
[OK] Thread cache hit rate: 99% (307 created / 571K connections)
[!!] Table cache hit rate: 0% (344 open / 2M opened)
[OK] Open file limit used: 5% (55/1K)
[OK] Table locks acquired immediately: 100% (14M immediate / 14M locks)
[OK] InnoDB data size / buffer pool: 122.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 400)

Tried changing query_cache_size to 200MB and join_buffer_size to 1M and table_cache to 500 but there was little improvement.

Any ideas?

Thanks

Replies (13)

RE: Redmine 2.X Tuning with MySQL - Added by Jorge Cabrera over 5 years ago

Another interesting thing is that it takes a long time rendering a page, for example displaying new issue from production.log:

Rendered issues/new.html.erb within layouts/base (6283.2ms)
Completed 200 OK in 8556.7ms (Views: 6198.0ms | ActiveRecord: 401.8ms)

RE: Redmine 2.X Tuning with MySQL - Added by Martin Denizet (redmine.org team member) over 5 years ago

Hello Jorge,
Which web server are you using? Apache2/Passenger?
Did you run OPTIMIZE TABLE as advised?
Cheers,

RE: Redmine 2.X Tuning with MySQL - Added by Jorge Cabrera over 5 years ago

Hi Martin,

Thank you for your reply. I'm using Apache2/Passenger. I ran Optimize Table on 4 tables (btw this tables are InnoDB) to test:

  • users
  • issues
  • journals
  • journal_details

On every case I got this:

mysql> optimize table users;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| redmine.users | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| redmine.users | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (18.40 sec)

mysql> optimize table issues;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| redmine.issues | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| redmine.issues | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (16.37 sec)

mysql> optimize table journals;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| redmine.journals | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| redmine.journals | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (16.64 sec)

mysql> optimize table journal_details;
+-------------------------+----------+----------+-------------------------------------------------------------------+
| Table                   | Op       | Msg_type | Msg_text                                                          |
+-------------------------+----------+----------+-------------------------------------------------------------------+
| redmine.journal_details | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| redmine.journal_details | optimize | status   | OK                                                                |
+-------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3.37 sec)

RE: Redmine 2.X Tuning with MySQL - Added by Martin Denizet (redmine.org team member) over 5 years ago

What your passenger-status --verbose looks like during work hours?
And your Passenger configuration file?

RE: Redmine 2.X Tuning with MySQL - Added by Jorge Cabrera over 5 years ago

Hi and thanks for the reply.

passenger-status --verbose looks like this:

# passenger-status --verbose
Version : 4.0.37
Date    : 2014-08-28 16:26:43 +0200
Instance: 1762
----------- General information -----------
Max pool size : 6
Processes     : 2
Requests in top-level queue : 0

----------- Application groups -----------
/usr/local/lib/redmine#default:
  App root: /usr/local/lib/redmine
  Requests in queue: 0
  * PID: 14720   Sessions: 1       Processed: 118     Uptime: 21m 21s
    CPU: 4%      Memory  : 346M    Last used: 2s ago
    URL     : http://127.0.0.1:59957
    Password: IUstn4GTNS2vwFgLqgnz86KOYtgfAlRLL5Dl3xfAnDZ
  * PID: 17759   Sessions: 0       Processed: 0       Uptime: 10s
    CPU: 0%      Memory  : 9M      Last used: 10s ago
    URL     : http://127.0.0.1:40013
    Password: c0NP9MtrnOlcxlUzA23x6Ae0jBXG8VLG1YosebqnYin

My Passenger configuration file just looks like this:

# cat /etc/apache2/mods-enabled/passenger.conf
PassengerRoot /usr/local/lib/passenger
PassengerRuby /usr/bin/ruby
PassengerDefaultUser www-data

Thanks for the help.

RE: Redmine 2.X Tuning with MySQL - Added by Martin Denizet (redmine.org team member) over 5 years ago

For the passenger configuration I meant something else. anyway, from the passenger-status I can see you have a max pool size of only 6, on a much smaller server than yours I have it up to 20.
You need to read the Passenger documentation
Especially check the following settings:
  • PassengerMinInstances
  • PassengerMaxPoolSize
  • PassengerPoolIdleTime
  • RailsAppSpawnerIdleTime

Be careful also that more is not always better. If for example you allow more instances than you server can handle, you could slow down the performances.
To mention it, it seems Puma (rewrite of Mongrel) is the fastest Rails server around to handle big loads. I didn't have the occasion to test it with Redmine yet.
Cheers,

RE: Redmine 2.X Tuning with MySQL - Added by Jorge Cabrera over 5 years ago

Great, I will go down that road then. Try tuning passenger and testing Puma.

Thanks a lot for your time.

RE: Redmine 2.X Tuning with MySQL - Added by Martin Denizet (redmine.org team member) over 5 years ago

I hope it helps. I'll be interested to hear about your findings.
I suggest you to establish a baseline before tweaking the configuation to be able to measure your improvements.
Looking forwarding hear from you :)

RE: Redmine 2.X Tuning with MySQL - Added by Jorge Cabrera about 5 years ago

Hi Martin,

Everything is working better now with version 2.4.2. I tried different roads but at the end using new ruby version is what did it best. This is what I tried:

  • Using Puma: No change here, we got the same delivery times on each page.
  • Adding caches to Apache and Varnish: This was a desperate attempt because it has nothing to do with database queries or rendering. No effect at all.
  • Tuning passenger's configuration options: Read the docs and tried everything, small to none effect.
  • Switch from Ruby 1.9.3 (Debian package) to Ruby 2.1.2 (Manual install): Win! Win! Win!

We got to a point that pages like "new issue" with thousands of users in project where taking as much as 25 seconds in being displayed. Now we got it down to 6 seconds.

So we're back to loving Redmine again. Hope things remain stable for a while.

Regards

RE: Redmine 2.X Tuning with MySQL - Added by Martin Denizet (redmine.org team member) about 5 years ago

Very interesting, thanks for the feedback :)

RE: Redmine 2.X Tuning with MySQL - Added by Jane Feng about 5 years ago

Hi Jorge,
How can you Switch from Ruby 1.9.3 (Debian package) to Ruby 2.1.2? Now my redmine version is 2.5.0,but the ruby version is 1.9.3,I want to just upgrade the ruby form 1.9.3 to 2.1.3. I install ruby 2.1.3 in my localhost,but it doesnot work, the redmine's ruby version is still 1.9.3。
Look forward to your reply.

RE: Redmine 2.X Tuning with MySQL - Added by Pavel Potcheptsov about 5 years ago

apt-get purge ruby-1.9.3
and then try to use RVM.

RE: Redmine 2.X Tuning with MySQL - Added by Jane Feng about 5 years ago

Thank you very much for your reply:
My redmine is installed in windows 2012 by one click installation, but in the control panel, I cannot find the ruby 1.9.3. So how can I switch from ruby 1.9.3 to ruby 2.1.3 in windows?

(1-13/13)