----------------- - 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.
Created attachment 26484 [details] [review] 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.
Hi, Could the socket patch be inserted when answering the Makefile.PL questions? Marcel
patch -> path
Created attachment 26485 [details] [review] 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!
Created attachment 26486 [details] [review] 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!
Created attachment 26487 [details] [review] 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'.
(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.
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.
(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.
> 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.
(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 :)
>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. I did, and it looked like I got around 10% increase on average. However, the benchmarks showed quite a big variation when I run them several times, so actually I'm not sure the difference is statistically significant. There where some bechmarks that run slower as well, so not conclusive.. Here is one example of a run: http://paste.koha-community.org/179 I agree with Frederic's reading of the documentation - it clearly says that DBI allready using the socket when connecting to 'localhost'. I'll try to check if that is acutally the case tomorrow, if I can find a way to monitor a socket, I'm not sure its possible.
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.
I could't find out how to actually sniff traffic from an unix domain socket. But it's easy to monitor tcp traffic with tcpdump. And if you monitor the port specified in koha-conf.xml, default to 3306, you'll see that nothing is passing through; 0 packets, 0 bytes. So this leads me to conclude that the documentation is correct; DBI is using the unix socket when connecting to localhost. I agree that the patch does no harm though, even if the performance gain is due to placebo :)
Created attachment 26505 [details] [review] 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!
During connection, in C4::Context, when the connection to socket fails, a warning is sent, and the connection fall back to TCP/IP connection. I'd prefer that the program die. Otherwise one may be mislead, and believe the socket works when it doesn't. Could you add support to socket to the web Installer. Try to do a fresh install. You will see that your socket info is not displayed, and not used. Take a look at: - C4::Installer.pm. line 66, and 438 - koha-tmpl/intranet-tmpl/prog/en/modules/installer/step2.tt line 10
Hi there! Managed to get my setup working using a symbolic link. So simple :) This whole feature makes no sense.
+ This doesn't work with DBIx
Thanks for trying :-) I'm sure this sad attempt will pave the way to successful contributions!
Just changing the status to be a bit more clear
(In reply to Frédéric Demians from comment #19) > Thanks for trying :-) I'm sure this sad attempt will pave the way to > successful contributions! Looking forward to it :)
Beating a dead horse which might not be dead for long. Bug 18721 refactors the installer.
Created attachment 63940 [details] [review] Bug 11974 - Enable non-default unix socket location 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 Koha::Database->_new_schema routine to check for the presence of mysql_socket in koha-conf.xml and prioritize the socket connection. This patch works on MariaDB and Mysql and hopefully on PostgreSQL. ------------- - TEST PLAN - ------------- DEFAULT: 1. edit koha-conf.xml 1.1. put your socket path to the <socket>-attribute by default /var/run/mysqld/mysqld.sock or create the xml-entry <socket>/var/run/mysqld/mysqld.sock</socket> 2. load any koha's web-page. Shouldn't get any errors. BAD SOCKET: 1. but a bad path as your mysql_socket, eg /var/run/mysqld/mysqld.sockBADBAD 2. load any koha's web-page. It should fail due to DB connection error. Thank you for testing!
Created attachment 63941 [details] [review] Bug 11974 - Enable non-default unix socket location for database connections. Squashable1
Created attachment 63942 [details] [review] Bug 11974 - Enable non-default unix socket location for database connections. Squashable2 Add DB_SOCKET to gazillion more separate places to make it pass thorugh the make steps.
Created attachment 63943 [details] [review] Bug 11974 - Enable non-default unix socket location for database connections. Squashable3 Finally foound the last place where the DB_SOCKET instance should be added.
Patch depends on a bug that is in Failed QA. Setting to BLOCKED until dependency can be resolved.
Created attachment 73131 [details] [review] Bug 11974 - (follow-up) Enable non-default unix socket location for database connections. Otherwise t/db_dependent/Search.t messes up!
I am very interested in this, as I've been saying for a while that it would be good for Koha to use Unix sockets instead of TCP sockets when running on the same host. I learned a lot reading the comment section, and I'll do some experimenting locally. For what it's worth, I've seen huge 100% speed improvements with PostgreSQL when using Unix sockets over TCP sockets for apps that abuse the database far worse than Koha. I think it's a universal truth that socket speed works like this: Unix Socket > Localhost TCP Socket using loopback device > External host TCP Socket. In the case of Localhost TCP vs External host TCP, I've seen other apps see 600% performance improvements when dealing with the thousands/millions of records. On an individual per record basis, the performance improvement might be a fraction of a second, so the improvement looks very small, but when you aggregate up the time savings can be *massive*. It depends on your workload.
Oh beautiful... have a library on Koha 19.11 on Ubuntu 18.04 with MySQL 5.7 and it's using the Unix socket even when localhost:3306 is defined in koha-conf.xml. Very cool. Confirmed using tcpdump and "lsof +E /var/run/mysqld/mysqld.sock" which shows that starman and rebuild_zebra.pl are using the Unix socket. (Note I think that lsof command will only work on lsof v4.89+. You can use "ss -p -x | less" too but it's a pain tracing the inode numbers).
Interestingly enough on OpenSUSE "lsof" isn't working even with v4.91. That's confusing. For tcpdump I had to do: tcpdump -i lo 'port 3306' And yeah I'm using Mariadb on OpenSuse which uses the socket at /var/run/mysql/mysql.sock instead of /var/run/mysqld/mysqld.sock. DBD::mysql doesn't seem to be able to detect it. Seeing libraries for both mysql and mariadb... wondering what DBD::mysql got compiled against. I only see headers for mariadb but the "mysql" client which is really the mariadb client uses /var/run/mysql/mysql.sock by default... I imagine that's probably causing some havoc. Symlinking isn't a brilliant option here since /run and /var/run are mounted on a tmpfs file system. I think I could change the socket location in my /etc/my.cnf file. That's probably the easiest thing without messing around with MySQL/Mariadb client libraries and re-compiling DBD::mysql...
Ok this is actually pretty interesting. It looks like DBD::mysql was provided by a stable OS package, so it was already compiled presumably against the Mariadb libraries provided on the system. So that's... interesting. Looks like mysql_real_connect is the thing doing the heavy lifting... https://metacpan.org/source/DVEEDEN/DBD-mysql-4.050/dbdimp.c#L2107 According to https://github.com/mariadb-corporation/mariadb-connector-c/blob/3.1/CMakeLists.txt, the default client socket is /tmp/mysql.sock. I guess it's possible that OpenSuse compile the mysql-server and mysql-client packages with a new default of /var/run/mysql/mysql.sock. Looks like they overrode it in http://download.opensuse.org/source/distribution/leap/15.0/repo/oss/src/mariadb-10.2.14-lp150.1.1.src.rpm and http://download.opensuse.org/source/distribution/leap/15.0/repo/oss/src/mariadb-connector-c-3.0.3-lp150.1.2.src.rpm But that should mean that DBD::mysql was compiled with the same overriden socket address... Especially since the spec file in http://download.opensuse.org/source/distribution/leap/15.0/repo/oss/src/perl-DBD-mysql-4.046-lp150.1.1.src.rpm requires the libmariadb-devel which is created by that mariadb-connector-c source package... Bizarre... I imagine this isn't useful for most people, but maybe it's similar to Olli-Antti Kivilahti's issue.
Nevermind... the problem was Memcached. As soon as I cleared Memcached, the new koha-conf.xml settings were taken up, and now Koha is using the Unix socket instead of the TCP socket. So now I'm curious... Olli-Antti Kivilahti, why are you using a MySQL socket in a path that your local MySQL client library doesn't know?