Hi all, The good news: CCFLS went live with 3.2alpha2 on Tuesday! The bad news: our transactions are *really* slow at times. I suspect the culprit is probably a ton of simultaneous mysql activity. I understand Chris has been working on adding memcached support to Koha, and I'm hoping that will help. I tried enabling it yesterday, but it didn't seem to do anything, so I must have been missing something, or maybe it's not implemented quite yet. Can anyone tell me what we need to do to get it going? Our system: 3.2 alpha2 (with zebra) on debian lenny (64bit), 16gb ram, two dual core opteron 2214 processors. We have nine libraries throughout the county with about 15 circ stations simultaneously circulating items at the busiest times. We have about 29,500 borrowers and 309,000 items. We just upgraded from a far less powerful dev_week system which didn't have these issues. What I've done: installed the debian packages for memcached and libcache-memcached-perl installed Cache::Memcached::Fast from CPAN (had to force it to install) Added these lines to the config section of koha-conf.xml <memcached_servers>127.0.0.1:11211</memcached_servers> <memcached_namespace>KOHA</memcached_namespace> I've also tried mod_perl, which does seem to improve page load time (from 0.8 sec to 0.1 sec on the login page, for example) but I've disabled it again for now because it seemed to cause some errors. Obviously I need to work on that some more. Thanks in advance!! Cindy -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Cindy Murdock Ames IT Services Director Meadville Public Library | CCFLS http://meadvillelibrary.org | http://ccfls.org
Hi all,
The good news: CCFLS went live with 3.2alpha2 on Tuesday! The bad news: our transactions are *really* slow at times. I suspect the culprit is probably a ton of simultaneous mysql activity. Have you done any tuning of mysql? Out of the box it probably is using less memory than it needs for efficient running. A lot of the my.cnf
On 04/06/10 15:42, Cindy Murdock Ames wrote: parameters can be tuned to improve throughput. Memcache can work wonders but it needs to be caching the data thats causing you to thrash the db. I'm not sure we've reached that point yet. Cheers Colin -- Colin Campbell Chief Software Engineer, PTFS Europe Limited Content Management and Library Solutions +44 (0) 208 366 1295 (phone) +44 (0) 7759 633626 (mobile) colin.campbell@ptfs-europe.com skype: colin_campbell2 http://www.ptfs-europe.com
On Fri, Jun 04, 2010 at 04:35:51PM +0100, Colin Campbell wrote:
On 04/06/10 15:42, Cindy Murdock Ames wrote:
Hi all,
The good news: CCFLS went live with 3.2alpha2 on Tuesday! The bad news: our transactions are *really* slow at times. I suspect the culprit is probably a ton of simultaneous mysql activity. Have you done any tuning of mysql? Out of the box it probably is using less memory than it needs for efficient running. A lot of the my.cnf parameters can be tuned to improve throughput. Memcache can work wonders but it needs to be caching the data thats causing you to thrash the db. I'm not sure we've reached that point yet.
I documented my journy over mysql tuning for Koha at: http://blog.rot13.org/2010/04/mysql_is_slow_did_you_tune_your_settings.html Just running mysqltuner.pl or tuning-primer.sh will probably be enough :-) I would highly recommend putting session files at filesystem as opposed to database, especially if you have semi-high load on your website (web crawlers and what not) because it cut down database load by 75% for us. Testing current memcache in Koha, it seemed to be used only for localization caching, and it was a bit slower on our system after some basic profiling. YMMV. -- Dobrica Pavlinusic 2share!2flame dpavlin@rot13.org Unix addict. Internet consultant. http://www.rot13.org/~dpavlin
Thanks Dobrica. I've seen your post before when looking for a solution to those innodb problems. I'll give mysqltuner a shot this weekend while we're closed, as well as the session files. Cheers, c. Dobrica Pavlinusic wrote:
http://blog.rot13.org/2010/04/mysql_is_slow_did_you_tune_your_settings.html
Just running mysqltuner.pl or tuning-primer.sh will probably be enough :-)
I would highly recommend putting session files at filesystem as opposed to database, especially if you have semi-high load on your website (web crawlers and what not) because it cut down database load by 75% for us.
Testing current memcache in Koha, it seemed to be used only for localization caching, and it was a bit slower on our system after some basic profiling. YMMV.
I documented my journy over mysql tuning for Koha at:
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Cindy Murdock Ames IT Services Director Meadville Public Library | CCFLS http://meadvillelibrary.org | http://ccfls.org
Le 04/06/2010 18:40, Dobrica Pavlinusic a écrit :
On Fri, Jun 04, 2010 at 04:35:51PM +0100, Colin Campbell wrote:
On 04/06/10 15:42, Cindy Murdock Ames wrote:
Hi all,
The good news: CCFLS went live with 3.2alpha2 on Tuesday! The bad news: our transactions are *really* slow at times. I suspect the culprit is probably a ton of simultaneous mysql activity. Have you done any tuning of mysql? Out of the box it probably is using less memory than it needs for efficient running. A lot of the my.cnf parameters can be tuned to improve throughput. Memcache can work wonders but it needs to be caching the data thats causing you to thrash the db. I'm not sure we've reached that point yet.
I documented my journy over mysql tuning for Koha at:
http://blog.rot13.org/2010/04/mysql_is_slow_did_you_tune_your_settings.html
Just running mysqltuner.pl or tuning-primer.sh will probably be enough :-)
I would highly recommend putting session files at filesystem as opposed to database, especially if you have semi-high load on your website (web crawlers and what not) because it cut down database load by 75% for us.
Testing current memcache in Koha, it seemed to be used only for localization caching, and it was a bit slower on our system after some basic profiling. YMMV.
memcached is not really meant for speed but for scaling. Speed improvement is a +. So that compared to db access, it is not really faster. But if you have many access and network slowness then it is really interesting. Chris Cormack also did some POCs on Caching whole pages. And not only function results. And results were quite encouraging. But that never hit code base for 3.2. What is really strange is the way Context is evaluated multiple times. And we come to thousands of SQL queries for a simple page. -- Henri-Damien LAURENT BibLibre
Hi Dobrica,
I would highly recommend putting session files at filesystem as opposed to database, especially if you have semi-high load on your website (web crawlers and what not) because it cut down database load by 75% for us.
do you speak about preference 'SessionStorage' ? To insert 'tmp' instead of 'mysql' ? Bye Zeno Tajoli Zeno Tajoli CILEA - Segrate (MI) tajoliAT_SPAM_no_prendiATcilea.it (Indirizzo mascherato anti-spam; sostituisci quanto tra AT con @)
On Fri, Jun 04, 2010 at 07:01:18PM +0200, Zeno Tajoli wrote:
Hi Dobrica,
I would highly recommend putting session files at filesystem as opposed to database, especially if you have semi-high load on your website (web crawlers and what not) because it cut down database load by 75% for us.
do you speak about preference 'SessionStorage' ? To insert 'tmp' instead of 'mysql' ?
Yes, sorry for vague description. I would also suggest to change C4::Auth around like 1344 to something like: session = new CGI::Session("driver:File;serializer:yaml;id:md5", $sessionID, {Directory=>'/dev/shm'}); In this line, /tmp was changed to /dev/shm which is ram-based filesystem on linux, making session serialization free in term of disk i/o. -- Dobrica Pavlinusic 2share!2flame dpavlin@rot13.org Unix addict. Internet consultant. http://www.rot13.org/~dpavlin
I can also attest to the benefit of storing session data somewhere other than in the MySQL database. LLEK has the ability to store its session data through memcached instead of writing to a file (which can create problems in a multi-server configuration) or MySQL. This frees up the database more than one might expect. The constant stream of tiny, non-contiguous INSERTs and UPDATEs contributes heavily to fragmentation of InnoDB writes which in turn lengthens transaction times. (Not to mention Koha's inability to selectively cull stale sessions, causing your table to have millions of rows over time). The drawback is of course that if memcached goes away you lose all your current session data since it's not a permanent storage medium, but this is not a significant loss in most circumstances. The MySQL "slow query" log is also very valuable for tracking down queries that hog DB time. However, the single most effective tool I've seen for finding performance bottlenecks is the NYTProf execution profiler. It can be a bit cumbersome to use, but the results give crystal clear insight into which functions are taking up the most time (though it doesn't necessarily give information about why they take up so much time). I have been surprised at how often MySQL is *not* to blame when it comes to Koha's performance. Clay On Fri, Jun 4, 2010 at 9:40 AM, Dobrica Pavlinusic <dpavlin@rot13.org>wrote:
On Fri, Jun 04, 2010 at 04:35:51PM +0100, Colin Campbell wrote:
On 04/06/10 15:42, Cindy Murdock Ames wrote:
Hi all,
The good news: CCFLS went live with 3.2alpha2 on Tuesday! The bad news: our transactions are *really* slow at times. I suspect the culprit is probably a ton of simultaneous mysql activity. Have you done any tuning of mysql? Out of the box it probably is using less memory than it needs for efficient running. A lot of the my.cnf parameters can be tuned to improve throughput. Memcache can work wonders but it needs to be caching the data thats causing you to thrash the db. I'm not sure we've reached that point yet.
I documented my journy over mysql tuning for Koha at:
http://blog.rot13.org/2010/04/mysql_is_slow_did_you_tune_your_settings.html
Just running mysqltuner.pl or tuning-primer.sh will probably be enough :-)
I would highly recommend putting session files at filesystem as opposed to database, especially if you have semi-high load on your website (web crawlers and what not) because it cut down database load by 75% for us.
Testing current memcache in Koha, it seemed to be used only for localization caching, and it was a bit slower on our system after some basic profiling. YMMV.
-- Dobrica Pavlinusic 2share!2flame dpavlin@rot13.org Unix addict. Internet consultant. http://www.rot13.org/~dpavlin _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
2010/6/5 Fouts, Clay <cfouts@ptfs.com>:
I can also attest to the benefit of storing session data somewhere other than in the MySQL database. LLEK has the ability to store its session data through memcached instead of writing to a file (which can create problems in a multi-server configuration) or MySQL. This frees up the database more than one might expect. The constant stream of tiny, non-contiguous INSERTs and UPDATEs contributes heavily to fragmentation of InnoDB writes which in turn lengthens transaction times. (Not to mention Koha's inability to selectively cull stale sessions, causing your table to have millions of rows over time). The drawback is of course that if memcached goes away you lose all your current session data since it's not a permanent storage medium, but this is not a significant loss in most circumstances.
Storing as temp files on a ram based disk as has already been suggested works much the same way too. Of course a patch for using memcached for session storage would be gratefully accepted and would save on once again doubling up on work already done. We use memcached to a huge extent at work, including storing session data, and have yet to have any real problems occur.
The MySQL "slow query" log is also very valuable for tracking down queries that hog DB time. However, the single most effective tool I've seen for finding performance bottlenecks is the NYTProf execution profiler. It can be a bit cumbersome to use, but the results give crystal clear insight into which functions are taking up the most time (though it doesn't necessarily give information about why they take up so much time). I have been surprised at how often MySQL is *not* to blame when it comes to Koha's performance. Clay
I concur, MySQL is usually not the bottleneck, unless the site is under extreme load, the startup cost of perl is a major factor, as Mason has suggested mysql, zebra and in fact apache2 can fight each other for I/O and CPU. Different partitions/disks for mysql and zebra can provide an easy win. One thing that the Profiler won't tell you, is how fast your browser can render the pages, Koha 3.2 (and 3.0) before it have a lot more css and js on the intranet that previous versions. Different browsers can render the page quite a bit faster, also the expires headers suggested by Mason will stop the browser refetching things that it doesn' t need too. We found at HLT with the new site, that using Chrome instead of Firefox on the circ desk provided a 2-3 second speed up in page render. Something worth trying. Chris
NYTprof combined with firebug (or chrome dev tools) can give you the comprehensive picture. The former atomizes and itemizes the server side costs. The latter details the client side, including graphs of time spent downloading vs. rendering, per file. On Jun 5, 2010 9:38 PM, "Chris Cormack" <chris@bigballofwax.co.nz> wrote: 2010/6/5 Fouts, Clay <cfouts@ptfs.com>:
I can also attest to the benefit of storing session data somewhere other than in the MySQL datab... Storing as temp files on a ram based disk as has already been suggested works much the same way too. Of course a patch for using memcached for session storage would be gratefully accepted and would save on once again doubling up on work already done.
We use memcached to a huge extent at work, including storing session data, and have yet to have any real problems occur.
The MySQL "slow query" log is also very valuable for tracking down queries that hog DB time. Ho... I concur, MySQL is usually not the bottleneck, unless the site is under extreme load, the startup cost of perl is a major factor, as Mason has suggested mysql, zebra and in fact apache2 can fight each other for I/O and CPU. Different partitions/disks for mysql and zebra can provide an easy win.
One thing that the Profiler won't tell you, is how fast your browser can render the pages, Koha 3.2 (and 3.0) before it have a lot more css and js on the intranet that previous versions. Different browsers can render the page quite a bit faster, also the expires headers suggested by Mason will stop the browser refetching things that it doesn' t need too. We found at HLT with the new site, that using Chrome instead of Firefox on the circ desk provided a 2-3 second speed up in page render. Something worth trying. Chris _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-commun...
On 6 June 2010 13:38, Chris Cormack <chris@bigballofwax.co.nz> wrote:
2010/6/5 Fouts, Clay <cfouts@ptfs.com>:
I can also attest to the benefit of storing session data somewhere other than in the MySQL database. LLEK has the ability to store its session data through memcached instead of writing to a file (which can create problems in a multi-server configuration) or MySQL. This frees up the database more than one might expect. The constant stream of tiny, non-contiguous INSERTs and UPDATEs contributes heavily to fragmentation of InnoDB writes which in turn lengthens transaction times. (Not to mention Koha's inability to selectively cull stale sessions, causing your table to have millions of rows over time). The drawback is of course that if memcached goes away you lose all your current session data since it's not a permanent storage medium, but this is not a significant loss in most circumstances.
Storing as temp files on a ram based disk as has already been suggested works much the same way too. Of course a patch for using memcached for session storage would be gratefully accepted and would save on once again doubling up on work already done.
This code is now in the Koha git repository, I'm not sure if it is implemented the same way in LEK, but it's now in Koha anyway. http://git.koha-community.org/gitweb/?p=koha.git;a=shortlog;h=refs/heads/new... Chris
On 2010-06-5, at 2:42 AM, Cindy Murdock Ames wrote:
Hi all,
The good news: CCFLS went live with 3.2alpha2 on Tuesday! The bad news: our transactions are *really* slow at times.
Heya Cindy, Think about moving your zebra and mysql DB's to different physical disks, any decreasing your zebra-rebuild frequency. This one helped us a lot! Does your box get slow only when a zeb-rebuild is happening? Do some mysql/inno tuning to take advantage of most of your delicious RAM ;) innodb_buffer_pool_size = 10000M install munin/munin-node on you box to get some decent stats on what specifically is causing the slow-down. http://packages.debian.org/lenny/munin these additional munin plugins are great for mysql-tuning, i run them on my prod systems http://wiki.github.com/kjellm/munin-mysql/ set good cacheing rules in your apache.conf for css/js/images ------------------------------------------------------------ <FilesMatch "\.(pl|html?)$"> Header set Pragma "no-cache" Header set Cache-Control "no-cache, must-revalidate, post-check=0, pre-check=0" Header set Expires 1 </FilesMatch> <FilesMatch "\.(jpg|gif|png|css|js$)$"> ExpiresDefault "access plus 7 days" Header set Cache-Control "public" </FilesMatch> ------------------------------------------------------------ force all *non* circ-station koha-requests thru a squid-box, to prioritize checkin/checkout requests on circ-stations Cheers, Mason. -- KohaAloha, NZ.
Thanks everyone for the pointers on tuning Koha. I'm working on implementing many of them right now. I'll let you know how things work out. I'm thinking this would be a good topic for the wiki. ;) Cheers! Cindy -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Cindy Murdock Ames IT Services Director Meadville Public Library | CCFLS http://meadvillelibrary.org | http://ccfls.org
Hi all, It looks like we've resolved our transaction speed problem--knock on wood! Many, many, many thanks to everyone who provided suggestions for improvement. Here's what Kyle and I did--I'm not sure what exactly helped, if it was one particular thing or a combo of many. * Mysql config: Increased innodb buffer pool size to 10000M (was 1024M); followed mysqltuner recommendations to set join_buffer_size (32M), query_cache_limit (16M), query_cache_size (16M), tmp_table_size (8M) and max_heap_table_size (8m). Mysqltuner still suggests more for those values, but the speed issue seems to be resolved so I'm not going to worry about it for now. The max amount of system memory it can currently use is 85%. * Moved the zebra db to another drive * Optimized mysql tables; mysqltuner reported that one table was fragmented. * Applied Mason's apache2 caching config recommendations * Storing session files on a ramdisk (/dev/shm) (Dobrica's suggestion) * mod_perl --Kyle got it to work by adding the following to httpd.conf: <Files *.pl> SetHandler perl-script PerlResponseHandler ModPerl::PerlRunPrefork PerlOptions +ParseHeaders PerlSendHeader On Options +ExecCGI </Files> PerlWarn On PerlRequire /home/koha/startup.pl Cheers! Cindy Cindy Murdock Ames wrote:
Thanks everyone for the pointers on tuning Koha. I'm working on implementing many of them right now. I'll let you know how things work out. I'm thinking this would be a good topic for the wiki. ;)
Cheers! Cindy
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Cindy Murdock Ames IT Services Director Meadville Public Library | CCFLS http://meadvillelibrary.org | http://ccfls.org
participants (9)
-
Chris Cormack -
Cindy Murdock Ames -
Colin Campbell -
Dobrica Pavlinusic -
Fouts, Clay -
JAMES Mason -
Joe Atzberger -
LAURENT Henri-Damien -
Zeno Tajoli