http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 --- Comment #1 from Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> --- Created attachment 26484 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=26484&action=edit Bug 11974 - Enable unix socket connections for database connections. ----------------- - BUSINESS CASE - ----------------- Currently Koha doesn't support using a non-default unix socket for database connections. Using unix sockets is generally ~30-40% faster compared to using TCP packets. Because Koha abuses the database a lot, this will make a difference. ------------ - SOLUTION - ------------ Mysql uses unix sockets for localhost connections only, but DBI::Mysql cannot automatically find the non-default socket path, thus it needs to be explicitly given. This patch modifies the C4::Context->dbh() routine to check for the presence of mysql_socket in koha-conf.xml and prioritize the socket connection. If making the socket connection fails, dbh() defaults to the TCP-connection if available. This patch works on MariaDB and Mysql, hence the configuration variable name mysql_socket. -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 --- Comment #2 from M. de Rooy <m.de.rooy@rijksmuseum.nl> --- Hi, Could the socket patch be inserted when answering the Makefile.PL questions? Marcel -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #26484|0 |1 is obsolete| | --- Comment #4 from Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> --- Created attachment 26485 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=26485&action=edit Bug 11974 - Enable unix socket connections for database connections. ----------------- - BUSINESS CASE - ----------------- Currently Koha doesn't support using a non-default unix socket for database connections. Using unix sockets is generally ~30-40% faster compared to using TCP packets. Because Koha abuses the database a lot, this will make a difference. ------------ - SOLUTION - ------------ Mysql uses unix sockets for localhost connections only, but DBI::Mysql cannot automatically find the non-default socket path, thus it needs to be explicitly given. This patch modifies the C4::Context->dbh() routine to check for the presence of mysql_socket in koha-conf.xml and prioritize the socket connection. If making the socket connection fails, dbh() defaults to the TCP-connection if available. This patch works on MariaDB and Mysql, hence the configuration variable name mysql_socket. ------------- - TEST PLAN - ------------- DEFAULT: 1. edit koha-conf.xml 1.1. uncomment row <mysql_socket>... and put your socket path here, by default /var/run/mysqld/mysqld.sock . or create the xml-entry <mysql_socket>/var/run/mysqld/mysqld.sock</mysql_socket> 2. load any koha's web-page. Shouldn't get any errors. SOCKET ONLY: replicate DEFAULT and do the following instructions: 3. From koha-conf.xml, remove hostname or set to bad name. 4. load any koha's web page. 5. Mysql/MariaDB connection works faster than ever! BAD SOCKET: 1. but a bad path as your mysql_socket, eg /var/run/mysqld/mysqld.sockBADBAD 2. load any koha's web-page. see your koha-error_log or koha-opac-error_log for the error. You can still use Koha normally, but it is recommended to fix bad socket ASAP. TOTAL DESTRUCTION: replicate BAD SOCKET and do the following instructions: 3. from koha-conf.xml remove hostname or change to a bad one. 4. load any koha's web page. Enjoy the "Software error". Thank you for testing! -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 --- Comment #6 from Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> --- Created attachment 26487 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=26487&action=edit Bug 11974 - Enable unix socket connections for database connections. Added installer options. Modified the Makefile.PL to ask for the non-default unix socket when using mysql or mariadb. Modified the rewrite-config.PL to actually include the new variable in the koha-conf.xml To test: 1. Reinstall using perl Makefile.PL 1.1. Write some path to the question: 'mysql socket location' 2. make, make test, make install 3. Verify that the installed koha-conf.xml has the new variable 'mysql_socket'. -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 Frédéric Demians <frederic@tamil.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |frederic@tamil.fr --- Comment #7 from Frédéric Demians <frederic@tamil.fr> --- (In reply to Olli-Antti Kivilahti from comment #4)

Using unix sockets is generally ~30-40% faster compared to using TCP packets. Because Koha abuses the database a lot, this will make a difference.

Are you sure? Do you have figures to prove that Koha is faster using your patch socket-mode? By default, Koha access MySQL server via localhost. My understanding is that localhost means that communication with MySQL server is done via socket (/var/run/mysqld/mysqld.sock on Debian). Take a look here: http://dev.mysql.com/doc/refman/5.0/en/connecting.html Specifically this sentence: On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 --- Comment #9 from Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> --- (In reply to Frédéric Demians from comment #8)

Koha MySQL client is DBD::mysql module:

https://metacpan.org/pod/DBD::mysql

See the 'port' argument of the 'connect' method:

The hostname, if not specified or specified as '' or 'localhost', will default to a MySQL server running on the local machine using the default for the UNIX socket. To connect to a MySQL server on the local machine via TCP, you must specify the loopback IP address (127.0.0.1) as the host.

My feature is about using non-default unix socket for mariadb. Try moving the mariadb/mysql socket to another path and then see what happens with the localhost directive with perl DBI.

...

Using unix sockets is generally ~30-40% faster compared to using TCP packets. Because Koha abuses the database a lot, this will make a difference.

Are you sure? Do you have figures to prove that Koha is faster using your patch socket-mode?

Good thing that you smelt something fishy :) I stated that socket connections are _generally_ 30-40% faster which I got from an obscure googling result. I haven't ran any tests with Koha because it is obvious that sockets > TCP and the fix was rather easy to do. petter from Oslo ran some tests and confirmed that you can get 0-20% performance increase. But I can imagine the performance benefits on live environment when doing checkouts for example. -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 --- Comment #11 from Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> --- (In reply to Frédéric Demians from comment #10)

...

My feature is about using non-default unix socket for mariadb. Try moving the mariadb/mysql socket to another path and then see what happens with the localhost directive with perl DBI.

Practically, why did you need to use a non-standard socket? You just could have done a symbolic link. Do you have several DB servers running simultaneously on the same server?

...

petter from Oslo ran some tests and confirmed that you can get 0-20% performance increase. But I can imagine the performance benefits on live environment when doing checkouts for example.

Yes, this magnitude of performance improvement is very interesting. But once again, we already have it using 'localhost' MySQL hostname.

Your code seems ok to my eyes, and could be integrated as it is, but we need to know in which situation your DB socket mode is required vs the localhost connection mode which has the same performance.

I am using this patch to share a unix socket between two LXC containers in a mounted "middle" partition. So I move the socket to the shared partition. I think this could also be achieved using a symlink, not sure. That was my initial idea but I had some issues moving the socket (and possibly due to unrelated issues) I failed to get the symlink version working. Give or take this feature will atleast be useful to someone running multiple databases on the same OS :) -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 --- Comment #13 from Frédéric Demians <frederic@tamil.fr> --- Thanks Olli-Antti and Petter for this explanations. I'm ready to sign-off this patch, since it doesn't come with any regression, but I'm still waiting Petter last tests. Would you mind also modifying patch description based on this discussion, underlying the use case (non-standard socket path, your LXC scenario for example), and understating performance gain. -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #26486|0 |1 is obsolete| | --- Comment #15 from Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> --- Created attachment 26505 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=26505&action=edit Bug 11974 - Enable unix socket connections for database connections. ----------------- - BUSINESS CASE - ----------------- Currently Koha doesn't support using a non-default unix socket for database connections. Using unix sockets is generally ~20-30% faster compared to using TCP packets. Because Koha abuses the database a lot, this will make a difference. Using a non-default unix socket is useful if you want to run multiple databases (eg. mysql and mariadb) in the same OS (eg. for testing purposes). Also if one needs to move the unix socket, for example to share it between LXC containers using a shared partition, this feature might be handy. Using unix sockets for connections are faster than TCP because there is no need for TCP and IP layer packet generation. Instead applications can communicate directly without extra overhead. The benefit is marginal for a single query, around 0.5ms, but escalates linearly when hundreds of queries are performed. ------------ - SOLUTION - ------------ Mysql uses unix sockets for localhost connections only, but DBI::Mysql cannot automatically find the non-default socket path, thus it needs to be explicitly given. This patch modifies the C4::Context->dbh() routine to check for the presence of mysql_socket in koha-conf.xml and prioritize the socket connection. If making the socket connection fails, dbh() defaults to the TCP-connection if available. This patch works on MariaDB and Mysql, hence the configuration variable name mysql_socket. ------------- - TEST PLAN - ------------- DEFAULT: 1. edit koha-conf.xml 1.1. uncomment row <mysql_socket>... and put your socket path here, by default /var/run/mysqld/mysqld.sock . or create the xml-entry <mysql_socket>/var/run/mysqld/mysqld.sock</mysql_socket> 2. load any koha's web-page. Shouldn't get any errors. SOCKET ONLY: replicate DEFAULT and do the following instructions: 3. From koha-conf.xml, remove hostname or set to bad name. 4. load any koha's web page. 5. Mysql/MariaDB connection works faster than ever! BAD SOCKET: 1. but a bad path as your mysql_socket, eg /var/run/mysqld/mysqld.sockBADBAD 2. load any koha's web-page. see your koha-error_log or koha-opac-error_log for the error. You can still use Koha normally, but it is recommended to fix bad socket ASAP. TOTAL DESTRUCTION: replicate BAD SOCKET and do the following instructions: 3. from koha-conf.xml remove hostname or change to a bad one. 4. load any koha's web page. Enjoy the "Software error". Thank you for testing! -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|gmcharlt@gmail.com |olli-antti.kivilahti@jns.fi -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 --- Comment #18 from Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> --- + This doesn't work with DBIx -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11974 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Patch doesn't apply |RESOLVED CC| |katrin.fischer@bsz-bw.de Resolution|--- |INVALID --- Comment #20 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Just changing the status to be a bit more clear -- You are receiving this mail because: You are watching all bug changes.