CODEWITHSUNDEEP
mysqlwindows
Waleed Eissa
Waleed Eissa

Reputation: 10533

Best way to connect to MySQL locally?

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

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

Upvotes: 12

Views: 13989

Answers (8)

JairoV
JairoV

Reputation: 2104

For better performance I can hint you some samples depending on where are located client and server:

Same machine: Shared memory, then named pipes, then TCP/IP

Different machine: TCP/IP

The reason is when you are in the same machine sharing memory avoids the roundtrip through the network stack hosted in the operating system. Shared memory is a more efficient IPC method.

Upvotes: 0

Aistis Raudys
Aistis Raudys

Reputation: 121

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.

Upvotes: 5

Vladislav Vaintroub
Vladislav Vaintroub

Reputation: 5673

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.

Upvotes: 5

Bogdan F
Bogdan F

Reputation:

For security reasons, it is clearly beneficial to disable TCP/IP-networking. I'm working on a .NET app, and I have had no problems connecting over a named pipe using the MySql connector. It works with the Mysql GUI tools as well :), and thats all I require.

Upvotes: 1

Vanja
Vanja

Reputation: 178

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.

Upvotes: 1

jishi
jishi

Reputation: 24634

Shared memory or named pipes gives less overhead than TCP/IP, however, do windows even support named pipes?

TCP/IP is the most compatible and scalable way to go on the other hand, and many connectors doesn't even support anything else afaik.

You shouldn't restrict your scenario to "same machine" or plattform "windows" if you don't have to, it's always good to have scalability in mind.

Upvotes: 0

Jimoc
Jimoc

Reputation: 1500

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.

Upvotes: 6

Marko
Marko

Reputation: 31473

TCP/IP in all cases.

Upvotes: -2

Related Questions