performance issues with mysql and koha on 2 physical servers
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? Do you know thinks about this? did you work or remember works from others ? (I am really not comfortable with hardware architecture or server settings and first I would like to collect your feedbacks.) Claire;
Am 30.03.12 10:10, schrieb Claire Hernandez:
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?
Do you know thinks about this? did you work or remember works from others ?
(I am really not comfortable with hardware architecture or server settings and first I would like to collect your feedbacks.)
When a server and client process are on the same machine, they can communicate over a local socket (also called a Unix socket) which is a lot faster than a TCP network socket which has to be used when the two processes are on the same machine. Careful tuning and refactoring of the SQL commands should minimize the effect, though. And when designed properly, a distributed application can actually be a lot faster, but this depends on the design (the key is that both parts, the DB and the application, can do some meaningful work in parallel, which, of course, is not the case when the application merely waits for the DB results). -mb
* Marc Balmer (marc@msys.ch) wrote:
Am 30.03.12 10:10, schrieb Claire Hernandez:
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?
Do you know thinks about this? did you work or remember works from others ?
(I am really not comfortable with hardware architecture or server settings and first I would like to collect your feedbacks.)
When a server and client process are on the same machine, they can communicate over a local socket (also called a Unix socket) which is a lot faster than a TCP network socket which has to be used when the two processes are on the same machine.
We run almost all our applications with dedicated database servers, including our Koha servers, but those database servers are specced to function fast as database machines. Quite a different profile to a web server. Is your database server at least as powerful if not more powerful than the webserver? Chris -- Chris Cormack Catalyst IT Ltd. +64 4 803 2238 PO Box 11-053, Manners St, Wellington 6142, New Zealand
* Claire Hernandez (claire.hernandez@biblibre.com) 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?)
Unlikely
- mysql setting?
Possible, I would run mysqltuner on the database server and follow its' suggestions.
- network setting?
Also possible. I would test for network latency, (often failing reverse dns can introduce this) or simple packet loss. Chris -- Chris Cormack Catalyst IT Ltd. +64 4 803 2238 PO Box 11-053, Manners St, Wellington 6142, New Zealand
In a related matter, has anyone looked at separating the user login credentials (the passwords) from the rest of the database (putting the passwords on a separate server) for security? -- Greg Lawson Rolling Hills Consolidated Library 1912 N. Belt Highway St. Joseph, MO 64506 -------------------------- On 03/30/2012 03:10 AM, 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?
Do you know thinks about this? did you work or remember works from others ?
(I am really not comfortable with hardware architecture or server settings and first I would like to collect your feedbacks.)
Claire; _______________________________________________ Koha-devel mailing list Koha-devel@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/
-- Greg Lawson Rolling Hills Consolidated Library 1912 N. Belt Highway St. Joseph, MO 64506 816-232-5479
On Fri, Mar 30, 2012 at 10:56 AM, G. Laws <glawson@rhcl.org> wrote:
In a related matter, has anyone looked at separating the user login credentials (the passwords) from the rest of the database (putting the passwords on a separate server) for security?
I think this is essentially what the LDAP stuff does. OpenId integration would be a nice feature, though. I seem to recall that Biblibre did some work on SSO type services... Kind Regards, Chris
Le 30/03/2012 17:20, Chris Nighswonger a écrit :
On Fri, Mar 30, 2012 at 10:56 AM, G. Laws <glawson@rhcl.org <mailto:glawson@rhcl.org>> wrote:
In a related matter, has anyone looked at separating the user login credentials (the passwords) from the rest of the database (putting the passwords on a separate server) for security?
I think this is essentially what the LDAP stuff does.
OpenId integration would be a nice feature, though.
I seem to recall that Biblibre did some work on SSO type services... Good memory ;-)
Koha is already CAS compliant (with possible multiple CAS server to log-in) Shibboleth is underway: it's developped, being tested by the customer (Institut Telecom), should be submitted, but probably not quickly, we have many other things that seem of a larger interest to submit before -- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08
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@rot13.org Unix addict. Internet consultant. http://www.rot13.org/~dpavlin
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@gmail.com Marsillargues - France <fridolyn.somers@gmail.com> On Fri, Mar 30, 2012 at 6:33 PM, Dobrica Pavlinusic <dpavlin@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@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 website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
participants (8)
-
Chris Cormack -
Chris Nighswonger -
Claire Hernandez -
Dobrica Pavlinusic -
Fridolyn SOMERS -
G. Laws -
Marc Balmer -
Paul Poulain