1

I'm getting

'PDO Connection Error: SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

error when I'm trying to make too much update and insert. With too much update, I mean 20k-30k transaction per minute. For example, when I try to make a batch processing over 10k records in a table, I'm starting to get this error.

I searched in serverfault and MySQL reference docs, and I found, invalid sock file location or down mysql server can cause this error, but my sock files path is true and mysql server is up. Because normally, I'm able to connect to mysql and run query.

Can connection limit cause this error? My current max_connections variable value is 151, and this can be not enough for 10k transactions per minutes. But as I know, when too much connections opened to mysql, it gives too many connections error not can't connect through socket error.

I have changed, my application to to use mysql_pconnect instead of mysql_connect. I don't know what will happen when I try to run cron job to process 10k records.

Do you have any idea? If I can't found a answer for this problem, I will try to get a big machine for MySQL with 32 gb ram, 4-8 processors and try with this setup.

Gurkan Oluc
  • 11
  • 1
  • 3

2 Answers2

1

Does your system show any signs of slowness or lag when this happens? If so, a beefier machine will help. If not, then there are probably some default safeguards preventing a storm of requests.

See this bug for more information:

http://bugs.mysql.com/bug.php?id=10525

Importantly, raise mysql's ability to queue "back log" requests by setting

back_log=1000

in your /etc/mysql/my.cnf file under the [mysqld] section (and restarting mysql).

Secondly, also raise the same limit under linux:

sudo sysctl -w net.core.somaxconn=1024

Don't forget to edit /etc/sysctl.conf and add 

net.core.somaxconn=1024

To the bottom if this solves your problem, otherwise it will be back on the next reboot!

fimbulvetr
  • 286
  • 1
  • 3
0

It could be an issue with SQL's handling of the localhost loopback. Try making the connection to 127.0.0.1 or the server's internal IP instead.

inecrafter
  • 23
  • 4