No response to my question in the help forum

Hello all,

I have requested assistance in the Help forum and so far have not gotten any feedback. The company I work for has been using Redmine for almost a year and as time goes on the Search feature has been getting progressively slower.

Here is the query that is run in MySQL the moment when the search feature is run

SELECT count(DISTINCT `issues`.id) AS count_all FROM `issues`  LEFT OUTER JOIN `projects` ON `projects`.id
= `issues`.project_id  LEFT OUTER JOIN `journals` ON `journals`.journalized_id = `issues`.id AND `journals`.journalized_type = 'Issue'WHERE ((projects.status=1 AND projects.id IN
(SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking') AND issues.project_id IN (6)) AND (((LOWER(subject) LIKE '%test%') OR (LOWER(issues.description) LIKE
 '%test%') OR (LOWER(journals.notes) LIKE '%test%'))))

MySQL takes up to 100% of the CPU to process the query which takes over 5 seconds to complete. The direction is already considering abandoning Redmine because they are convinced that within another year, the search feature will be unusable.

I have the 0.9.2 stable version of Redmine running in CentOS.

Here is my current configuration:

Centos 5.2 2.6.18-164.10.1.el5
MySQL 5.0.77
Ruby 1.8.7
MySQL 4.1 or higher (recommended)
openssl + openssl-devel
zlib + zlib-devel
lighttpd-1.4.22
fastcgi
RubyGems

actionmailer (2.3.5)

actionpack (2.3.5)

activerecord (2.3.5)

activeresource (2.3.5)

activesupport (2.3.5)

cgi_multipart_eof_fix (2.5.0)

daemons (1.0.10)

fastthread (1.0.7)

fcgi (0.8.8)

gem_plugin (0.2.3)

mongrel (1.1.5)

mysql (2.8.1)

rack (1.0.1)

rails (2.3.5)

rake (0.8.7)

rmagick (2.0.0

I would like to stay with Redmine, but if my questions remain unanswered, I'd say that's a pretty good sign we won't get the help we need should we run into other problems later on.

Replies (4)

RE: No response to my question in the help forum - Added by Felix Schäfer over 14 years ago

Hello Craig,

I doubt you will get much response from the main devs in the forums, as they are intended as a means for the community to communicate, not for communication with the core team. If you think you have found a bug in redmine or that redmine could be made better, please file a bug or a feature request.

Regarding your problem, there's not much "redmine" can do about it either. I doubt the search query can be optimized much, I think your best shot would be to tweak your MySQL config, as this is clearly a delay caused by MySQL.

As for "support" for redmine: redmine is an open-source project and provided as-is, and there is no "guaranteed" official support I'd know of. What you could do if you rely on redmine so much is to learn a bit more about the rails stack (in this case db config), get someone on board who is proficient in these matters, or look for someone who could/would help on a contract basis. If you decide to go with that last suggestion, I can recommend Eric Davis who is as far as I know available for hire on redmine-related projects.

RE: No response to my question in the help forum - Added by Craig Efrein over 14 years ago

Hello Felix,

Thank you for getting back to me. Here is my current MySQL configuration. Do you have any suggestions on improving it ? I will add more CPU power to see if that helps, but it seems odd that with a fairly small MySQL DB, I'm running into these issues.

log-error                               = /var/log/mysql/mysqld.err
datadir                                 = /var/lib/mysql
skip-locking
key_buffer                              = 16M
max_allowed_packet                      = 1M
table_cache                             = 64
sort_buffer_size                        = 512K
net_buffer_length                       = 8K
read_buffer_size                        = 256K
skip-name-resolve

# INNODB
innodb_buffer_pool_size = 500M
# this is the default, increase it if you have lots of tables
innodb_additional_mem_pool_size = 20M

RE: No response to my question in the help forum - Added by Felix Schäfer over 14 years ago

I think it's more a matter of memory and good use of it rather than processing power, and if I see it correctly the MySQL configuration is still "vanilla", which iirc is laid out for 24- or 32-odd MB of RAM. Anyway, my MySQL config-foo is not good enough to help you with that, but google should be able to help you a little. I will also ping Eric (edavis10) on IRC so that he may help you.

RE: No response to my question in the help forum - Added by Craig Efrein over 14 years ago

Part of my problem has been google and the half answers it typically provides. You are correct in saying that the my.cnf is vanilla except for the two innodb options I added which should have improved performance but didn't.

If anyone could provide me with the optimal my.cnf configuration for Redmine, I think that would probably solve our problems and avoid countless hours of testing configurations that were created by people who really didn't know what they were doing and were themselves looking for help.

I spent over two weeks refining the installation of Redmine here at Systo after spending several days hoplessly searching for answers on Google. I don't know if that would be interesting to have somewhere on Redmine's website, but they are fairly complete and may help someone from wasting there time on Google. It probably needs some tuning though.

redmine_install_centos.txt (8.04 KB) redmine_install_centos.txt

Redmine Install on CentOS 5.2

(1-4/4)

Project

General

Profile

Redmine