production.ERROR: PDOException: SQLSTATE[HY000] [2002] Connection refused in....

Published 6 months ago by deansatch

I see this error in my log files randomly, but quite often. Any idea what would cause this sporadically?

Best Answer (As Selected By deansatch)
lostdreamer_nl

I'd first start with enabling the 'mysql slow query log' see what comes up. https://www.google.nl/search?q=mysql+slow+query+log

set it on half a second and after a day or 2 check which queries are the slowest (these are mostly the ones where complete table filtering was needed and thus are using a lot of memory).

After that you could use some memory profiler for finding out which methods are the culprits for memory usage: for instance https://github.com/arnaud-lb/php-memory-profiler

wilburpowery

Local or development?

Looks like mysql is not started in your environment. Maybe you don't have it configured to start automatically and you need to start it manually?

We really need more info about the error. :)

wilburpowery

Also, you might have mysql running on another port. By default the .env file has it set up for 3306. You might want to check that also.

lostdreamer_nl

The error seems to indicate it cannot connect via the socket at ..... some path. I believe your MySQL instance is rebooting at some points.

Can you post a part of the mysql log file from around the time where you see these messages in your laravel log ?

deansatch

@wilburpowery its in production and mysql must be started as the site is up and running most of the time. I have had 2x 1 minute notifications from uptime robot in the last 2 weeks

deansatch

@lostdreamer_nl


2018-02-12T07:41:36.444949Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2018-02-12T07:41:36.445215Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2018-02-12T07:41:36.790070Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-02-12T07:41:36.794459Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.20-0ubuntu0.16.04.1) starting as process 1418 ...
2018-02-12T07:41:36.846238Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-02-12T07:41:36.846293Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-02-12T07:41:36.846299Z 0 [Note] InnoDB: Uses event mutexes
2018-02-12T07:41:36.846318Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-02-12T07:41:36.846324Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2018-02-12T07:41:36.846328Z 0 [Note] InnoDB: Using Linux native AIO
2018-02-12T07:41:36.852588Z 0 [Note] InnoDB: Number of pools: 1
2018-02-12T07:41:36.853962Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-02-12T07:41:36.871179Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-02-12T07:41:36.871573Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2018-02-12T07:41:36.871602Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-02-12T07:41:36.871622Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2018-02-12T07:41:36.871635Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2018-02-12T07:41:36.871643Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-02-12T07:41:36.871651Z 0 [ERROR] Failed to initialize plugins.
2018-02-12T07:41:36.871657Z 0 [ERROR] Aborting

2018-02-12T07:41:36.871839Z 0 [Note] Binlog end
2018-02-12T07:41:36.872236Z 0 [Note] Shutting down plugin 'CSV'
2018-02-12T07:41:36.872259Z 0 [Note] Shutting down plugin 'MyISAM'
2018-02-12T07:41:36.873610Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

lostdreamer_nl

2018-02-12T07:41:36.871179Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2018-02-12T07:41:36.871573Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12 2018-02-12T07:41:36.871602Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool

That's where it goes down.... Your server seems to be running out of memory.

Either add more memory to it, or add swap space, or change the MySQL settings to use less memory.

deansatch

@lostdreamer_nl I’m never a fan of bumping up ram for things like this...much prefer to optimise. What would make MySQL use less memory? Is there any way of pinpointing what is causing so much strain on the sql server?

lostdreamer_nl

It's hard to optimize MySQL without knowing any numbers.

Setting your innodb buffer pool size to 64MB instead of the default 128MB would help you get rid of this problem, but it would probably slow down your queries as well.

Here are a few nice hints on optimizing MySQL for InnoDB tables. https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/

Can you check the following of your server:

  1. total available memory (both physical and swap)
  2. current memory consumption (ssh into the machine and run a 'top' command)
  3. What programs / services are running on the server that use a big chunk of your memory (you can get this from the top command as well)

When we know some specs of the server and it's availability of those resources we can start optimizing the DB and perhaps some of the other processes.

deansatch

@lostdreamer_nl I read that swap files are a bad idea on ssd as it will kill the drive by exceeding the write limits?

Current server is just a 512MB ram but I will be putting up to 1gb tonight.

Nothing seems to use a big chunk of memory %mem is at a steady 0-1. Apache2 occasionally hits 6% and when I load a page mysqld hits 30% and about 10 apache2 processes appear around 6% each


KiB Mem :   500064 total,    61532 free,   274840 used,   163692 buff/cache
KiB Swap:        0 total,        0 free,        0 used.   164500 avail Mem 


lostdreamer_nl

yeah, that will be it than. 512 MB for a server is next to nothing. (right now only 61MB is free to use)

When apache will get a lot of requests, it will start spawning more of those processes, each costing a few (dozen) MB's

With MySQL also taking a 128MB chunk and PHP also needing some, this could get troublesome in no time.

About the swap, it depends: It wont kill your ssd drive very fast these days, but money wise you're better off getting more RAM.

deansatch

Any way to pinpoint what causes the most load on the memory to plan for best case/worst case and look at optimising code?

lostdreamer_nl

I'd first start with enabling the 'mysql slow query log' see what comes up. https://www.google.nl/search?q=mysql+slow+query+log

set it on half a second and after a day or 2 check which queries are the slowest (these are mostly the ones where complete table filtering was needed and thus are using a lot of memory).

After that you could use some memory profiler for finding out which methods are the culprits for memory usage: for instance https://github.com/arnaud-lb/php-memory-profiler

Please sign in or create an account to participate in this conversation.