[Koha-devel] performance issues with mysql and koha on 2 physical servers

[Dobrica Pavlinusic]

On Fri, Mar 30, 2012 at 10:10:08AM +0200, Claire Hernandez wrote:
> Hello all,
> 
> I would like to better understand why when we put koha source on a
> different mysql physical server we have a performance problems.
> 
> If I put mysql on koha source server, an opac-search take 2 times
> less than if I separate mysql server and koha sources.
> 
> I don't really know why:
> - perl libs? (CGI + DBH?)
> - mysql setting?
> - network setting?

I would add another reason:

- too many database queries coupled with TCP overhead

How do I know that?

When running OPAC under plack with plack in bug 7848[1] with debug pannels
which lately include very useful DBI profile[2] feature I get following:

4.730399 s for whole request
2.106 s (44%) for DBI

So I could guess that half of page load time is load on the database.

My personal favourite is this:

Profile Path: SELECT tagfield,tagsubfield,liblibrarian,libopac,tab,mandatory,repeatable,authorised_value,authtypecode,value_builder,kohafield,seealso,hidden,isurl,link,defaultvalue,maxlength FROM marc_subfield_structure WHERE frameworkcode=? ORDER BY tagfield,tagsubfield
Profile Data: 0.183024s / 10673 = 0.000017s avg (first 0.000037s, min 0.000004s, max 0.064041s)

That's 10673 SQL queries to retrive framework for one search page!

How many frameworks do you have? What about caching or memoize
(which I plan to implement in bug 7177[3], which now needs re-works since
we have plack and nice profiling tools).

In fact, I plan to use most of my time fixing problems like this. To be
honest, plack didn't bring all performance we can squeeze from current
code - bug 7846[4] is my favourite example.

List of bugs mentioned in this mail, waiting for sign off :-)

1: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7847
2: PLACK_DEBUG=1 ./opac-plack.sh srvgit
3: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7177
4: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7846

-- 
Dobrica Pavlinusic               2share!2flame            dpavlin at rot13.org
Unix addict. Internet consultant.             http://www.rot13.org/~dpavlin