logo
Apache Lounge
Webmasters

 

About Forum Index Downloads Search Register Log in RSS X


Keep Server Online

If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.

or

Bitcoin

A donation makes a contribution towards the costs, the time and effort that's going in this site and building.

Thank You! Steffen

Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
Post new topic   Forum Index -> How-to's & Documentation & Tips View previous topic :: View next topic
Reply to topic   Topic: Improving the performance of MySQL on Windows
Author
Steffen
Moderator


Joined: 15 Oct 2005
Posts: 3094
Location: Hilversum, NL, EU

PostPosted: Tue 09 Jun '15 18:59    Post subject: Improving the performance of MySQL on Windows Reply with quote

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

PostPosted: Tue 09 Jun '15 20:27    Post subject: Reply with quote

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: 7373
Location: Germany, Next to Hamburg

PostPosted: Wed 10 Jun '15 17:05    Post subject: Reply with quote

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

PostPosted: Wed 10 Jun '15 21:03    Post subject: Reply with quote

Doesn't the application that connects to MySQL also need support for this?
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7373
Location: Germany, Next to Hamburg

PostPosted: Thu 11 Jun '15 11:17    Post subject: Reply with quote

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

PostPosted: Thu 11 Jun '15 18:49    Post subject: Reply with quote

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: 7373
Location: Germany, Next to Hamburg

PostPosted: Wed 17 Jun '15 12:48    Post subject: Reply with quote

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

PostPosted: Sun 26 Mar '17 15:16    Post subject: Reply with quote

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


Reply to topic   Topic: Improving the performance of MySQL on Windows View previous topic :: View next topic
Post new topic   Forum Index -> How-to's & Documentation & Tips