Redmine

"Development log"? "SQLite"? No kidding it's slow. Other than: either dump humongous amounts of RAM in the box and give each of your redmine processes 500MB of RAM or install a DB instead of SQLite and run it in production mode? No, can't think of anything else than downgrading.

I was running in production mode. I changed to development mode to track down why the 0.9.3 installation was running so slowly.

Development vs production seems to make no visible difference for this issue.

Yes a different DB may be the answer, but it ran absolutely fine as 0.8.7, but after upgrading to 0.9.3, its now getting on for 10x as slow to show an issue.

As far as I can tell, the second query gets more information more precisely, i.e. I suppose the first query was hand-crafted and refined and completed afterwards in the redmine code and subsequent queries, the second query is ActiveRecord-generated and contains information about what information exactly is needed. It is admitted that specifying high-level ActiveRecord relationships leads to better readability at the code level and better optimization at the query-level, which then offloads the bulk of the sorting out work to the DB, which in general is better/faster at this sort of stuff than ruby/rails, especially because the optimizer of the DB might also be able to get more juice out of one specific query rather than several general-purpose ones.

Anyway, there is big work being done on the redmine code to get rid of custom SQL and to replace it with expressive ActiveRecord relationships, so the DB might and will get more to chew on over time. That might be the problem you are facing with the SQLite DB, because you get no caching between the different concurrent rails processes or even consecutive rails processes, and each rails process has to load and deal with a complete DB and associated DB engine, which in general is slower when getting less memory (as opposed to only having to allocate memory to your DB once and be done with it). Anyway, I'd wager the "new" request hits some kind of memory limit or whatever that makes the whole thing go haywire, or SQLite is just plain bad with OUTER JOINs.

Long story short: the request might have been quick, it isn't anymore, and I'd blame it on SQLite. If you somehow feel this is a problem with redmine, please file a bug to discuss with one of the devs why the queries have been changed, or that you think there is a bug in this query.

Thanks Felix, that explains why the query is now so complex. Looking at http://sqlite.org/omitted.html it states that RIGHT and FULL OUTER JOIN aren't supported in sqlite3 so perhaps this is the issue.

sqlite3 is performing perfectly for our needs (where not a huge number of users) so have never seen the need to set up a separate database server such as mysql. I am now looking in to moving to mysql. Initial testing shows that even with our large number of changes (well I've got it to 240,000 so not quite the 300,000 we had in sqlite) and it is taking 0ms for the query.

The downside is there doesn't appear to be a full-proof way of moving from sqlite to mysql. I've tried yaml_db but am getting seq-faults when loading the yaml file back in to the new database (I stripped all repositories from our sqlite db and got it down to 11MB so it isn't huge by yaml still doesn't seem to be able to deal with it).

And mysql won't import the sqlite SQL dump directly as they use slightly different syntax. Global search and replace on certain text fixes most of this (such as AUTO_INCREMENT for AUTOINCREMENT and ` for ") but there are escaping issues. For example our LDAP auth user name contains a '\' as it includes a domain. This comes out in the SQL as a single back-slash but mysql sees this as an escape and just removes it.

So it then comes down to all the text inside tickets/forum posts etc may get corrupted slightly.

So I think I will report this as a performance bug as it certainly is a performance issue when using 0.9 vs 0.8 and if sqlite doesn't support outer joins well, then they ought to be worked around, but I assume this will just be passed off as an issue with ActiveRecord rather than Redmine.

If anyone has a good way of migrating from an sqlite3 db to mysql 5.0.x (looks like the mysql gem only supports 5.0.x not 5.1.x) then that would be very useful.

Cheers

Russell

I have same problem with MySQL 5.1 on Windows Server 2003 R2 Standard Edition.
Any further information I can provide to help tracing the problem?

Alan Liu wrote:

I have same problem with MySQL 5.1 on Windows Server 2003 R2 Standard Edition.
Any further information I can provide to help tracing the problem?

To trace it, run in development mode and your log will contain the SQL and timings for all queries for a page render so you can see which query is running slowly.

But for me, moving from sqlite3 to mysql has solved the issue entirely.

Please not that MySQL 5.1 is not supported by the mysql gem used by rails to connect to the DB, so you might have problems with or because of that.

Felix Schäfer wrote:

Please not that MySQL 5.1 is not supported by the mysql gem used by rails to connect to the DB, so you might have problems with or because of that.

It could be. I'm running on 5.0.90 without issues.

Unlucky to me. DB is MySQL 5.0.90 now. UI doesn't display although log file tells that DB query completes (in seconds). Any comment is appreciated.

Alan Liu wrote:

Unlucky to me. DB is MySQL 5.0.90 now. UI doesn't display although log file tells that DB query completes (in seconds). Any comment is appreciated.

Looking at that log it isn't your db thats slow, just Redmine. Look at the completed lines, e.g. for WelcomeController#Index:

Completed in 1532ms (View: 1391, DB: 31)

Db access 31ms but Redmine too 1391ms to do the render, total of 1532ms. So I'd say msql is in the clear. It would be good to try and get some comparative stats from your 0.8.x install if you have a backup and then perhaps posting a performance degradation bug.

Cheers

Russell

After performing 'gem update', 0.9.3 works well on localhost.
The problem now is page displaying is very slow if accessing from other machines.

Are you sure it isn't a network issue then? I doesn't sound like a Redmine issue anyway, more of an issue with your webserver/network setup if accessing from localhost works fine.

I think it isn't a network issue 'cause 0.8.7 works perfactly in the same environment. And it might not be a pure Redmine issue either. Seems somebody met similiar problem too (http://www.redmine.org/boards/2/topics/10708?r=11519#message-11519). After swithing to mongrel, the performance problem is gone. Thanks, Russell.

• gem install mongrel
• mongrel_rails start -e production -p 3000

I'll go out on a limb here, but (seemingly) excessive network latency for website lookups from "outside" are often related to a webserver misconfiguration, i.e. apache is sometimes configured per default to log not only the IP but also the hostname of the requester, which causes latency if the (reverse) DNS is slow or not reachable.