MySQL – Socket Configuration Issue in my.cnf

linuxMySQLperconaUbuntu

I've got an issue that's causing very long "time in database" in my application.

The DB is Percona MySQL 5.5.35.

The application works perfectly, and can read/write from the database just fine. The issue is that I have extraordinarily long DB response times that's definitely not time spent executing the queries, which are all simple, and cached.

For instance, I'll randomly get response times go into several seconds, and other times, it takes just a few milliseconds. There is slow query logs setup and no queries are slow, they're all simple inserts of tiny data and about 80% reads, the entire DB is just 20MB, it's an internet forum.

New Relic app graph showing huge spikes in DB connect time

I'm certain the problem is down to the socket, as I cannot have the socket specified in the mysqld section of my.cnf. If I do, I get an error upon restarting the database saying:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

So for example, here's the live, working copy of my.cnf, with irrelevant portions removed:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock


[mysqld]

# General #
default-storage-engine         = InnoDB

# Database replication #
server-id=1895149
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=reefbase1

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

Notice the port and socket isn't specified in mysqld section?

If I change this to:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock


[mysqld]

port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

# General #
default-storage-engine         = InnoDB

# Database replication #
server-id=1895149
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=reefbase1

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

Which is how I see over and over again, my.cnf to be setup, my server fails to start with the "cannot connect" error.

Any advice would be very much appreciated.

Best Answer

The first point to remember here is many mysql clients (including php) will default to unix socket style communication if the client is configured to use localhost. If your server is not correctly setting up this socket, then clients may be attempting to connect to the socket and failing.

The rest is summarized from our discussion in the comments.

In any case since your server was not configured to create a socket, and the client was pointed a location that is unusual for a Ubuntu system, I think you should try change that first to point at the standard Debian/Ubuntu location for pid and socket files. Which is /var/run/mysqld/mysqld.sock, or most recently /run/mysqld/mysqld.sock.

The other location should theoretically work, though I am guessing something isn't quite write as far as permissions or something.

Related Topic