| Author |
|
Steffen
Moderator
Joined: 15 Oct 2005
Posts: 3092
Location: Hilversum, NL, EU
|
 Posted: Tue 09 Jun '15 18:59 Post subject: Improving the performance of MySQL on Windows |
 |
|
Intresting:
In this blog entry I’d like to describe how you might be able to improve how MySQL performs on Windows by ensuring that you take advantage of a Windows specific configuration setting.
On Unix systems, MySQL programs treat the localhost host name specially. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file, which has some performance advantages over a TCP/IP connection. Windows does not support Unix sockets, however, and hence does not benefit from this optimisation.
However, the use of shared memory connections on Windows can offer significant performance improvements over the use of TCP/IP connections. Shared memory connections are obviously only useful when both the MySQL client and server processes are executing on the same machine, but when they are the performance benefits of using shared memory connections can be helpful. To enable shared memory connections, you would use the shared_memory system variable.
Read more.... http://mysqlserverteam.com/improving-the-performance-of-mysql-on-windows/ |
|
| Back to top |
|
gijs
Joined: 27 Apr 2012
Posts: 189
Location: The Netherlands
|
| Posted: Tue 09 Jun '15 20:27 Post subject: |
|
|
| Interesting, to enable this we simply add shared_memory = 1 to our my.ini? |
|
| Back to top |
|
James Blond
Moderator
Joined: 19 Jan 2006
Posts: 7371
Location: Germany, Next to Hamburg
|
| Posted: Wed 10 Jun '15 17:05 Post subject: |
|
|
| gijs wrote: | | Interesting, to enable this we simply add shared_memory = 1 to our my.ini? |
it seems to be so according to the mysql com docs. |
|
| Back to top |
|
gijs
Joined: 27 Apr 2012
Posts: 189
Location: The Netherlands
|
| Posted: Wed 10 Jun '15 21:03 Post subject: |
|
|
| Doesn't the application that connects to MySQL also need support for this? |
|
| Back to top |
|
James Blond
Moderator
Joined: 19 Jan 2006
Posts: 7371
Location: Germany, Next to Hamburg
|
| Posted: Thu 11 Jun '15 11:17 Post subject: |
|
|
I've looked into the docs[1] of mariadb the fork from mysql.
| Quote: | | Windows only, specifies the name of the shared memory to use for shared memory connection. Mainly used when running more than one instance on the same physical machine |
The windows mysql command line client can use TCP/IP or named pipe connection.
However the mysqlnd uh (PECL) driver in PHP can use the shared memory[2]. I have to say that I nerver tried that.
Just googled this (TM)
[1] https://mariadb.com/kb/en/mariadb/server-system-variables/
[2] http://php.net/manual/de/mysqlnd-uh.constants.php |
|
| Back to top |
|
gijs
Joined: 27 Apr 2012
Posts: 189
Location: The Netherlands
|
| Posted: Thu 11 Jun '15 18:49 Post subject: |
|
|
Interesting, on Linux we need to set localhost or a path to the Linux socket.
Is this necessary on Windows for this to work? |
|
| Back to top |
|
James Blond
Moderator
Joined: 19 Jan 2006
Posts: 7371
Location: Germany, Next to Hamburg
|
| Posted: Wed 17 Jun '15 12:48 Post subject: |
|
|
As I write | Quote: | | Just googled this (TM) |
I've no experience with that pecl extension.
For sure the pecl extension is not shipped in the default php extensions.
The docs are unclear to use localhost or the IP.
On linux you should use the socket which is faster than the tcp connection with the name / IP. |
|
| Back to top |
|
gijs
Joined: 27 Apr 2012
Posts: 189
Location: The Netherlands
|
| Posted: Sun 26 Mar '17 15:16 Post subject: |
|
|
Apparently you need to add the following two lines in your my.cnf
shared_memory = 1
enable-named-pipe
To use it in MySQL workbench, simply select named pipes instead of TCP IP. You can leave the settings empty and it will connect.
----
The question of how to configure PHP and C# applications to use named pipes or shared memory remains.
I've tried using localhost on a C# application, however it still connects to MySQL by TCP/IP (V4)
On PHP I've set mysqli.default_socket = mysql, in the php.ini and in the PHP application I've let the DB host empty or set to localhost.
PHP also keeps using TCP/IP (V6) to connect to MySQL.
Any ideas on how to enforce named pipes or shared memory? |
|
| Back to top |
|
