MySQL connections: sockets vs. TCP

You can connect to MySQL servers on Linux boxes in two ways: TCP or a socket. For security purposes, if your app is on the same box as your database, you will want to disable remote TCP connections. There’s two ways to do this from your MySQL server: set bind-address to 127.0.0.1 (not 0.0.0.0, that means everything can connect!) or skip-networking. Which to pick? From a security point, it seems to be a wash. The docs say this about skip-networking:

This option is highly recommended for systems where only local clients are permitted.

While the default config file that comes with MySQL says:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.

So what about performance?

On Greasy Fork, we have a database that stores user scripts, which can be up to a couple megabytes and are frequently served up by a Rails app. After upgrading in response to Shellshock, I saw in my New Relic dashboard that the time spent by database operations jumped up by an average of 50ms/request. (Why? No idea.) The queries were not such that the database would have to think hard – simple selects by primary key. Watching SHOW PROCESS LIST, I could sometimes catch the queries, and they were in the state “Writing to net”. How much time could a TCP connection to localhost be taking? I switched the Rails database config to use a socket, and…

dbperf

That’s the New Relic graph of database time spent per request before and after I made the change. From ~60ms/r to ~5ms/r. Well worth it!

Of course, you can use socket connections even when networking is enabled in MySQL, but switching all connections to sockets will let you reduce the overhead of MySQL on top of the performance savings mentioned above.

Such drastic improvements are likely limited to cases where large amounts of data are coming out of the database, but it’s fairly simple thing to try that might help your site’s performance.

Advertisements

One thought on “MySQL connections: sockets vs. TCP

  1. Great post friend, thanks.

    Just to say that there is actually a reason that someone might want to use tcp over socket in a local client/server senario, despite the performance overhead: several vps have got terrible open file limits causing awful 500 errors with log entries such as ‘cannot connect to socket’.

    Cheers!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s