FogBugz Technical Support

A forum for technical support discussion related to Fogbugz.
The current FogBugz Knowledge Base can be found at http://help.fogcreek.com/fogbugz.

Posts by Fog Creek Employees are marked:

Documentation
Release Notes
Network Status

FB6 Slow List Performance on LAMP

FB speed screams as long as we don't click on "List" which takes between 15-20 seconds to load. I've been tuning our setup and have made noticeable speed improvements except for the one area that needed it.

The system is small at the moment with a few hundred cases, couple dozen wikis, half dozen Departments/Clients. Same results on IE, FF, Safari.

The list filter is set to "All open cases" about 30.

Any ideas on how to speed the List view up?
 
CentOS 5 - 2.6.18
Apache 2.2.3
PHP 5.1.6
MySQL 5.0.45
Mono 1.2.4
eAccelerator is installed
(2) 2.5Ghz CPUs
2 Gigs of RAM


Followed all of the advise on the FogBugz

http://www.fogcreek.com/FogBugz/docs/60/topics/setup/UnixSystemRequirements.html

Also:

http://www.ibm.com/developerworks/views/linux/libraryview.jsp?topic_by=All+topics+and+related+products&sort_order=desc&lcl_sort_order=desc&search_by=tuning+lamp&search_flag=true&type_by=Articles&show_abstract=true&sort_by=Relevance&end_no=100&show_all=false

Cheers,
Mitch
Mitch Hagerty Send private email
Wednesday, October 29, 2008
 
 
Hmmm.  List just loads the most recently loaded filter for the user.  What happens when you click on Filters > My Cases, then into a case, then click on List?
Rich Armstrong Send private email
Wednesday, October 29, 2008
 
 
I would confirm that eAccelerator is actually installed and running (i.e. cacheing).  The performance you're seeing sounds like it may not be working correctly as this is well outside the norm.
Michael H. Pryor Send private email
Wednesday, October 29, 2008
 
 
I can second this performance issue. Our site has approximately 8 users, <1000cases, and a few dozen wiki pages. When you use any of your filters (clicking List or loading a specific one) it takes several seconds each and every time. I figured this was just "how it was". Everything else is fairly snappy for the most part (save for EBS reports, which are slugs).

The performance for this doesn't very much, if at all, when using IE7 or FFX3. It has always been this way from what I've seen - it wasn't introduced when we upgraded a package or FogBugz installation.

I have tuned the ever living crap out of MySQL and I've spent a decent amount of effort tuning the rest of the LAMP stack as well. I read through the LAMP stack tuning article at IBM that Mitch posted (Thanks Mitch!). The only additional things it recommends which were applicable and we weren't already doing were:
  - Getting rid of .htaccess (/opt/fogbugz/Website) and moving that information to the config file (/opt/fogubgz/Accessories/fogbugz.conf). I've done this.
  - Turning on KeepAlives (I set ours to On, 100 max transactions, 5second time out - we only have about 8users)
  - Turning off atimes (I have but this is pending a reboot).

CentOS 5
Kernel 2.6.18-92.1.13.el5
Apache 2.2.3
PHP 5.1.6
MySQL 5.0.45
eAccelerator 0.9.5.2 (Cache size set to 128MB, never uses more than 32MB)
Dual Intel Xeon 2.4GHz CPUs
2GB RAM
100GB RAID 5 (4 x 36GB 10K RPM SCSI U320)
Filesystem: ext3 on top of LVM2
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 
Since Mitch stated it is taking 15-20seconds and I only said "several", I thought I should clarify. It generally takes between 3 and 10 seconds for a list or filter to load.
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 
Changing the Filter to Me instead of Anybody takes it down to about 3 seconds. Putting it back to Anybody puts it back at about 15 seconds. Unfortunately I need my filter set as Anybody so I can triage cases.                             

eaccelerator is running properly according to phpinfo() i did change zend_extension = ... to extention = eaccelerator.so as I read somewhere that FB doesn't like zend but it didn't seem to make any difference.

Looks like a place FB needs some optimization.
Mitch Hagerty Send private email
Wednesday, October 29, 2008
 
 
Query or PHP optimization may help, but I wouldn't be surprised if there is some configuration, package, or OS related issue going on here as well however.

I *believe* the FogBugz installation that FogCreek uses in-house for their stuff is running on a Windows stack (IIS, ASP, MS SQL Server) vs our setup which is a LAMP stack. I believe their hosted solution runs on the Windows stack as well. They obviously have way more cases and users on their systems than we do as do many of the other FB users.

If it were purely a code structure or query structure problem, I would have expected the complaints to appear much sooner on these larger installations.

My first inclination (after checking eAccelerator) would be to investigate MySQL and my second inclination would be to investigate PHP. MySQL is notorious for creating performance issues with schemas/queries that "just work" in MS SQL Server. It's not that MySQL can't be performant, it just often times needs some TLC to get it there.

I admittedly haven't done the detective work I need to determine where the majority of the time is being spent during these operations, so take my thoughts with a grain of salt.
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 
You could arrange to send us your db and I can run it on a VM server CentOS box I have here.  If it runs fast under my VM then it's clearly a machine issue.  If it runs slow under my VM, I can trace it to find out what is going on.
Michael H. Pryor Send private email
Wednesday, October 29, 2008
 
 
I would absolutely love to, but I'm under company proprietary/intellectual property constraints (how about you Mitch?). May I suggest you take a DB you have available there and run it under your CentOS VM for comparison sake?

If there is a marked difference in performance, then we know we are on to something. If there is no difference, I probably /can/ scrub our package configuration files (like httpd.conf, php.ini, etc) and send them to you for comparison.
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 
Actually, can you run VMWare?  (I think the player is free).  I could just give you the VM.
Michael H. Pryor Send private email
Wednesday, October 29, 2008
 
 
I will leave this up briefly so you can have a look.

http://flmconnect.com/phpinfo.html

cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
skip-bdb
skip-innodb

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords = 1

key_buffer = 128M
key_buffer_size = 128M

thread_cache = 128M
thread_concurrency = 2
concurrent_insert = 2

max_allowed_packet = 64M
max_heap_table_size = 128M
tmp_table_size = 128M
table_cache = 256M

join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
read_rnd_buffer_size = 1M

max_connections = 200
wait_timeout = 10
connect_timeout = 10
max_connect_errors = 100

long_query_time = 5
#log-slow-queries
#log-queries-not-using-indexes


[mysqld_safe]
nice = -5
open_files_limit = 2048
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Mitch Hagerty Send private email
Wednesday, October 29, 2008
 
 
Yeah, we use VMware Player all the time (and yes, it is free!).
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 
I've noticed Mitch that your eAccelerator Cached Scripts (257) and Memory Allocated (31.6MB) is almost identical to ours (256 and 31.4MB respectively). This may make total sense since the script number may be driven by the FB source and therefore be relatively static and it appears we have similar size DBs so that may explain the Memory Allocated size.

Do you find that your eAccelerator Memory Allocated size never goes to 32MB or above, irregardless of the amount of uptime and traffic? Ours never does and I don't know if this is lack of demand or if it is hitting 32MB as a hard stop since that is the default value out of the box.

For what it is worth, here is our my.cnf:

[mysqld]
datadir=/srv/db/mysql
socket=/srv/db/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_allowed_packet=50M

#################################
# Performance Tuning Parameters #
#################################
# A value that is 25% of total memory on a machine that mainly runs MySQL is quite common
key_buffer_size=256M
# The number of open tables for all threads
table_cache=256
# Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations
sort_buffer_size=16M
# Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans.
read_buffer_size=4M
# Enable query caching
query_cache_size = 128M
query_cache_type=1
query_cache_limit=1M

[mysql.server]
user=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 
Yesterday was the first time I looked at eAccelerator so I couldn't say if it ever reaches the max...
Mitch Hagerty Send private email
Wednesday, October 29, 2008
 
 
Doing a quick load trace with Google Chrome (simplistic but somewhat helpful) it seems like roughly 50% of the time (~5s in this query) is spent server side handling default.php and about 35-40% (~4s in this query) is spent client side handling the JS.

Doing a simple watch of top during the page loads shows something like 2.5% CPU utilization by mysqld and 35-100% CPU utilization over a longer period of time for httpd.

I'm going to time the SQL queries for my filters to see what type of time they are taking to execute. Be be shortly...
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 
Forgot to post the screenshot of the trace:

http://img219.imageshack.us/my.php?image=fogbugzfiltertraceog2.png
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 
Zach,

Take a look at

http://ebergen.net/wordpress/2006/03/06/3-minute-mysql-tuning/

and try adding these to your my.cnf

#log-slow-queries
#log-queries-not-using-indexes
Mitch Hagerty Send private email
Wednesday, October 29, 2008
 
 
I logged the queries when I select a particular filter. Loading it generated 86 MySQL queries. I then profiled all 86 queries using MySQl's profiler. In total, all 86 execute in <= 1second. However, several(about 8) of them generate quite a bit of output, so while the query may be finished that quickly, it takes a bit longer for the result to be piped over.

I profiled default.php using APD and got the following:

(/tmp)> pprofp -u pprof.08532.3

Trace for /opt/fogbugz/Website/default.php
Total Elapsed Time = 10.03
Total System Time  = 0.98
Total User Time    = 8.69


        Real        User        System            secs/    cumm
%Time (excl/cumm)  (excl/cumm)  (excl/cumm) Calls    call    s/call  Memory Usage Name
--------------------------------------------------------------------------------------
18.1 1.75 1.75  1.57 1.57  0.10 0.10  27762  0.0001  0.0001            0 HandleError
7.7 0.74 0.74  0.67 0.67  0.06 0.06  27924  0.0000  0.0000            0 str_replace
7.3 0.68 0.68  0.63 0.63  0.09 0.09  37161  0.0000  0.0000            0 strlen
7.1 0.61 0.61  0.61 0.61  0.01 0.01  2202  0.0003  0.0003            0 datetimezone->gettransitions
5.5 0.45 0.45  0.47 0.47  0.01 0.01  2199  0.0002  0.0002            0 datetimezone->getoffset
3.9 0.39 0.39  0.34 0.34  0.04 0.04  20168  0.0000  0.0000            0 intval
3.8 0.36 0.36  0.33 0.33  0.02 0.02  7576  0.0000  0.0000            0 gettype
3.5 0.41 1.33  0.30 1.13  0.14 0.20  14266  0.0000  0.0001            0 strftime
1.9 0.18 0.18  0.17 0.17  0.01 0.01  4923  0.0000  0.0000            0 function_exists
1.7 0.22 0.86  0.15 0.75  0.04 0.09  22807  0.0000  0.0000            0 replace
1.5 0.14 0.39  0.13 0.36  0.01 0.01  3529  0.0000  0.0001            0 strtotime
1.5 0.15 0.15  0.13 0.13  0.02 0.02  7653  0.0000  0.0000            0 courresponse->Write
1.3 0.13 0.13  0.11 0.11  0.01 0.01  5709  0.0000  0.0000            0 substr
1.2 0.12 0.12  0.10 0.10  0.02 0.02  4985  0.0000  0.0000            0 regexp->Modifiers
1.2 0.12 0.12  0.10 0.10  0.01 0.01  4911  0.0000  0.0000            0 preg_replace
1.2 0.11 0.11  0.10 0.10  0.01 0.01  4973  0.0000  0.0000            0 chr
1.2 0.10 0.10  0.10 0.10  0.01 0.01  5453  0.0000  0.0000            0 array_let
1.1 0.10 0.10  0.10 0.10  0.01 0.01  4558  0.0000  0.0000            0 cthistledate->__construct
1.1 0.10 0.10  0.10 0.10  0.00 0.00  3513  0.0000  0.0000            0 sizeof
1.1 0.11 0.51  0.10 0.48  0.01 0.04  7576  0.0000  0.0001            0 TypeName

For friendlier formatting, go here: http://rafb.net/p/bxhqoe49.html
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 
Here is a more informative paste (I'm not very savvy with APD so please excuse my floundering around).

http://rafb.net/p/Pezibo99.html

It appears the major of the time( ~8 of 8.69s) is spent in this function (ShowBugSearchOrFilterDetails) and it's children:

91.1 0.00 8.80  0.00 7.92  0.00 0.88    1  0.0000  7.9178            0 ShowBugSearchOrFilterDetails
Zach Burlingame Send private email
Wednesday, October 29, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz Bug Tracking and Evidence-Based Scheduling.