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

[Fridolyn SOMERS]

Hie,

I'm also thinking that the main problem is that there are too many database
access.
In addition to dowload speed (which sould not be a problem), each access
uses network ping.
There are caches but they are in database server.
Each page uses C4::Auth wich loads every system pref, user infos and
permissions, ...
Languages needs also a lot of queries (for sometimes only fr_FR and en).
And like Dobrica said, the performance of frameworks in database is bad.

Note that for 1 user, database performance is two times better if on same
server as Koha.
But what about 1000 users ? A dedicated database server is much more
performant with all RAM and hard disk performance.

1/
In my opinion, for a production context, allmost all datas in C4::Auth
should be cached.
I think the most powerfull system is to generate a perl file which contains
all datas hard coded.
A perl script generating a perl module.

2/
Another idea is to set all preferences in system environment variables
(maybe in Apache conf).

3/
We could think of using 2 databases.
The first on Koha server containing configuration tables for fast access,
the second containing the other tables.
Thus, this seems to be not possible with MySQL.

Do you use virutal machines ? There network performance can be worse than
with real machine.

Regards,

-- 
Fridolyn SOMERS
fridolyn.somers at gmail.com
Marsillargues - France
<fridolyn.somers at gmail.com>

On Fri, Mar 30, 2012 at 6:33 PM, Dobrica Pavlinusic <dpavlin at rot13.org>wrote:

> 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
> _______________________________________________
> Koha-devel mailing list
> Koha-devel at lists.koha-community.org
> http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
> website : http://www.koha-community.org/
> git : http://git.koha-community.org/
> bugs : http://bugs.koha-community.org/
>
-------------- section suivante --------------
Une pi?ce jointe HTML a ?t? nettoy?e...
URL: </pipermail/koha-devel/attachments/20120402/7d36bd76/attachment-0001.htm>