4

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.

i-CONICA
  • 648
  • 1
  • 9
  • 22
  • What version of Ubuntu are you running, where did you get the percona package? `/var/lib/mysql/mysql.sock` is not the path I typically see, I thought it was usually under `/var/run/mysqld/mysqld.sock`. – Zoredache Mar 18 '14 at 18:59
  • Hi, It's Ubuntu 12.10. In /var/run/mysqld/ there is a file called mysqld.sock, should I change the socket parameter in my.cnf both [mysql] and [mysqld] to this socket file instead? Thanks. – i-CONICA Mar 18 '14 at 19:07
  • I would be strongly tempted to at least try that. I believe that is the default path on Ubuntu. Certainly shouldn't hurt to try it unless you have some kind of weird setup where you are trying to run multiple database server instances. – Zoredache Mar 18 '14 at 19:14
  • Well if I am, I'm not doing it deliberately! I'll try that - Thanks. – i-CONICA Mar 18 '14 at 19:17
  • One last thing, the /var/run directory is a symlink to /run/, should I specify /run/mysqld/mysqld.sock, as that's where you end up when navigating to /var/run/mysqld/mysqld.sock. – i-CONICA Mar 18 '14 at 19:18
  • I certainly shouldn't hurt to use `/run/mysqld/mysqld.sock`. – Zoredache Mar 18 '14 at 19:24
  • 1
    Hi, Can you please post this as an answer so I can accept it. The [mysql] and [mysqld] now both have the /run/mysqld/mysqld.sock parameter specified and the server started and is running well. Thank you very much. – i-CONICA Mar 18 '14 at 21:12

2 Answers2

4

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.

Zoredache
  • 130,897
  • 41
  • 276
  • 420
  • Thank you again. With risk of fragmenting this, is the pid specification significant? As currently, that isn't specified for the same reasons the socket wasn't. If it's not a simple response, I can create another question... Thanks. – i-CONICA Mar 19 '14 at 01:02
  • The pid file may be used by the startup script when starting/restarting the server. It theoretically prevents multiple copies from starting, and is used to properly signal the running process to safely shut down. Though some applications can be controlled via the socket. I haven't looked at the percona init/upstart scripts to see if it is used. – Zoredache Mar 19 '14 at 01:47
  • Thanks. It is defined in Percona's default config files if you use https://tools.percona.com/wizard to create a base config, so they seem to be a fan of it. I'll see if I can add it now. – i-CONICA Mar 19 '14 at 01:54
0

My problem was slightly different, but this question showed up searching. And connecting over TCP instead of sockets fixed my problem. It also help others.

I.e. in your client section put:

host=127.0.0.1

and/or 

protocol=tcp
the
  • 468
  • 8
  • 23