Bug 11974 - Enable non-default unix socket location for database connections.
Summary: Enable non-default unix socket location for database connections.
Status: BLOCKED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Olli-Antti Kivilahti
QA Contact: Testopia
URL:
Keywords:
Depends on: 18721
Blocks:
  Show dependency treegraph
 
Reported: 2014-03-20 12:35 UTC by Olli-Antti Kivilahti
Modified: 2020-06-17 07:33 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 11974 - Enable unix socket connections for database connections. (3.30 KB, patch)
2014-03-20 12:56 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 11974 - Enable unix socket connections for database connections. (4.31 KB, patch)
2014-03-20 13:11 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 11974 - Enable unix socket connections for database connections. (4.30 KB, patch)
2014-03-20 13:13 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 11974 - Enable unix socket connections for database connections. Added installer options. (2.95 KB, patch)
2014-03-20 14:37 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 11974 - Enable unix socket connections for database connections. (4.89 KB, patch)
2014-03-21 08:54 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 11974 - Enable non-default unix socket location for database connections. (5.02 KB, patch)
2017-06-02 14:44 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 11974 - Enable non-default unix socket location for database connections. Squashable1 (1.30 KB, patch)
2017-06-02 14:44 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 11974 - Enable non-default unix socket location for database connections. Squashable2 (1.19 KB, patch)
2017-06-02 14:45 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 11974 - Enable non-default unix socket location for database connections. Squashable3 (1.07 KB, patch)
2017-06-02 14:45 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 11974 - (follow-up) Enable non-default unix socket location for database connections. (992 bytes, patch)
2018-03-21 09:08 UTC, Lari Taskula
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Olli-Antti Kivilahti 2014-03-20 12:35:44 UTC
-----------------
- 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.
Comment 1 Olli-Antti Kivilahti 2014-03-20 12:56:19 UTC Comment hidden (obsolete)
Comment 2 Marcel de Rooy 2014-03-20 13:10:18 UTC
Hi,
Could the socket patch be inserted when answering the Makefile.PL questions?

Marcel
Comment 3 Marcel de Rooy 2014-03-20 13:10:29 UTC
patch -> path
Comment 4 Olli-Antti Kivilahti 2014-03-20 13:11:49 UTC Comment hidden (obsolete)
Comment 5 Olli-Antti Kivilahti 2014-03-20 13:13:20 UTC Comment hidden (obsolete)
Comment 6 Olli-Antti Kivilahti 2014-03-20 14:37:10 UTC
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'.
Comment 7 Frédéric Demians 2014-03-20 15:47:47 UTC
(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.
Comment 8 Frédéric Demians 2014-03-20 16:02:12 UTC
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.
Comment 9 Olli-Antti Kivilahti 2014-03-20 16:11:05 UTC
(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.
Comment 10 Frédéric Demians 2014-03-20 17:09:13 UTC
> 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.
Comment 11 Olli-Antti Kivilahti 2014-03-20 17:33:27 UTC
(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 :)
Comment 12 Petter Goksøyr Åsen 2014-03-20 21:43:58 UTC
>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.
Comment 13 Frédéric Demians 2014-03-21 06:40:29 UTC
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.
Comment 14 Petter Goksøyr Åsen 2014-03-21 08:06:44 UTC
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 :)
Comment 15 Olli-Antti Kivilahti 2014-03-21 08:54:08 UTC
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!
Comment 16 Frédéric Demians 2014-03-21 10:46:50 UTC
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
Comment 17 Olli-Antti Kivilahti 2014-04-10 13:00:18 UTC
Hi there!

Managed to get my setup working using a symbolic link.
So simple :)

This whole feature makes no sense.
Comment 18 Olli-Antti Kivilahti 2014-04-10 13:01:53 UTC
+ This doesn't work with DBIx
Comment 19 Frédéric Demians 2014-04-10 17:26:26 UTC
Thanks for trying :-) I'm sure this sad attempt will pave the way to successful contributions!
Comment 20 Katrin Fischer 2014-04-10 22:09:19 UTC
Just changing the status to be a bit more clear
Comment 21 Olli-Antti Kivilahti 2014-04-11 06:30:45 UTC
(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 :)
Comment 22 Olli-Antti Kivilahti 2017-06-02 09:43:56 UTC
Beating a dead horse which might not be dead for long.

Bug 18721 refactors the installer.
Comment 23 Olli-Antti Kivilahti 2017-06-02 14:44:14 UTC
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!
Comment 24 Olli-Antti Kivilahti 2017-06-02 14:44:38 UTC
Created attachment 63941 [details] [review]
Bug 11974 - Enable non-default unix socket location for database connections. Squashable1
Comment 25 Olli-Antti Kivilahti 2017-06-02 14:45:19 UTC
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.
Comment 26 Olli-Antti Kivilahti 2017-06-02 14:45:39 UTC
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.
Comment 27 Katrin Fischer 2017-10-15 13:35:49 UTC
Patch depends on a bug that is in Failed QA. Setting to BLOCKED until dependency can be resolved.
Comment 28 Lari Taskula 2018-03-21 09:08:05 UTC
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!
Comment 29 David Cook 2020-06-17 05:09:01 UTC
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.
Comment 30 David Cook 2020-06-17 05:41:47 UTC
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).
Comment 31 David Cook 2020-06-17 06:18:58 UTC
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...
Comment 32 David Cook 2020-06-17 07:23:34 UTC
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.
Comment 33 David Cook 2020-06-17 07:33:06 UTC
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?