Best way to connect to MySQL locally?

Question

MySQL offers many ways (on Windows) to connect to it, those are:

• Shared memory
• Named pipes
• TCP/IP

My question is, if MySQL will be run on the same machine of the web server, then which method is best for security and performance?

P.S. This is a one web server scenario, no web farm.

Thanks

Answer 1

In terms of performance if both are running on the same machine then shared memory is the better option as it wont run into issues with firewall software that may be on the machine or installed at a later date.

However if in the future you need to seperate the web server and database due to upsizing, you will need to make changes to support this.

Therefore in the long run the better option is to go with TCP/IP as this will allow the servers to be seperated with the minimum of heartache.

Answer 2

TCP/IP in all cases

I'd say the same, it is the only configuration that is tested thoroughly in development. Also

1) named pipes are not a guarantee for security. One can access them via \ServerName\pipe\PipeName (and Connector/NET can do exactly that, even if C client won't).

2) Shared memory has couple of shortcomings. For example, it is impossible to know if shared memory client has died. That is, if application has exited without disconnect, on the server side resources are not cleaned (for the n wait_timeout period which 8 hours by default). Also, there is excessive synchronization when using shared memory.

3) Localhost TCP access is very fast, at least on modern Windows, though I believe it was not the case until Vista/WS2008. I benchmarked it against pipes, there is no notable difference. As for security, this is what firewall is for.

Answer 3

Shared memory is 4 times more faster than tcp/ip. To insert simple row into the table takes ~200 microseconds using tcp/ip and ~55 microseconds using shared memory (on my i5 3ghz). Tested using c/connector. Using net/connector speed increase is only about 3 times.

Answer 4

I'd say named pipes as it does provide significant difference than TCP/IP (tested with large dump imported).

But you might need to consider how the actual application on the web server will perform using any of these connections, because the driver might not be capable of working with named pipes for example.

I had problems with php (on windows) and named pipes so you'd need to check that part of the story as well.