Trac becomes very slow with many (>50) milestones
|Reported by:||Owned by:||Christian Boos|
|Severity:||major||Keywords:||mysql performance genshi milestones query|
Performance improvement for the Roadmap, by caching milestone properties.
I have a trac instance on a vhost. first of all, i present you all the informations about this trac:
root@****:/home/trac/log# uname -a Linux ****.stratoserver.net 2.6.32-028stab091.2 #1 SMP Fri Jun 3 00:02:40 MSD 2011 i686 GNU/Linux 1 core of a model name : Quad-Core AMD Opteron(tm) Processor 2347 HE cpu MHz : 1431.597 root@****:/home/trac/log# python --version Python 2.6.6 ii libapache2-mod-python 3.3.1-9+b1 Python-embedding module for Apache 2 ii apache2-mpm-prefork 2.2.16-6+squeeze8 Apache HTTP Server - traditional non-threaded model root@****:/home/trac/log# ls -lha /usr/local/lib/python2.6/dist-packages/ insgesamt 44K drwxrwsr-x 10 root staff 4,0K 25. Sep 20:49 . drwxrwsr-x 4 root staff 128 19. Jun 2011 .. drwxr-sr-x 4 root staff 4,0K 25. Sep 20:49 Babel-0.9.6-py2.6.egg -rw-r--r-- 1 root staff 427 25. Sep 20:49 easy-install.pth drwxr-sr-x 4 root staff 4,0K 25. Sep 20:49 Genshi-0.6-py2.6.egg drwxr-sr-x 4 root staff 4,0K 13. Sep 11:40 IniAdmin-0.2_r11914-py2.6.egg drwxr-sr-x 5 root staff 4,0K 8. Sep 09:02 Trac-1.0-py2.6.egg drwxr-sr-x 4 root staff 4,0K 13. Sep 11:40 TracAccountManager-0.3.2-py2.6.egg drwxr-sr-x 4 root staff 4,0K 21. Jun 2011 TracHTTPAuth-1.1-py2.6.egg drwxr-sr-x 4 root staff 4,0K 13. Sep 11:39 TracWysiwyg-0.12.0.4_r11158-py2.6.egg drwxr-sr-x 4 root staff 4,0K 13. Sep 11:39 TracXMLRPC-1.1.2_r11439-py2.6.egg httpconf: <Location /> SetHandler mod_python PythonHandler trac.web.modpython_frontend PythonPath "sys.path + ['/usr/share/trac']" PythonOption TracEnv /home/trac PythonOption TracUriRoot / PythonOption TracLocale "de_DE.UTF-8" </Location>
okay. so far so good, now you know on what box im working on. we have plenty of milestones (about 15 open, rest closed)
and we have serious performance issues, the box itsself idles most of the time, only a svn and this trac is running on the box. i tried nearly everything to tune the performace. static content is hosted on a seperated domain, not served by trac itself.
when it comes to navigating through the trac instance it sometimes is very slow. as database backend im using mysql. there are no slow queries so i decided to enable sql logging and take a look whats happening. when i browse through pages with no database access, everything is quite fine. but when it comes to the wiki or to the roadmap i usually have to wait 3-10 seconds till the page get served (messured in google chrome, with only one user at this time which was me!)
so i took a look into the debug log and found, that there is no caching and queries got fired multiple times instead of get cached or something. it seems, not a single query is the problem, but the hugh amount of queries fired (multiple times)
i attached a samplelog for only one pagerequest (roadmap). i should mention that only about 15 milestones are open and i display only open milestones (so why is there a query to closed milestones?!) and why so many queries?
this result in very poor performance.
i attach a image recording the pageloadtimes and the example log