MySQL crashing --open-files-limit=50000 error / --skip-name-resolve

[go4]

Hi,

Trying to track down MySQL issues on a VPS that has around 65 accounts, 78 databases. New to this so any help appreciated.

So two things I can see (that may or may not be related).

1. Open files limit

Process Manager constantly shows

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/xxxx.err --open-files-limit=50000 --pid-file=/var/lib/mysql/xxx.pid --socket=/var/lib/mysql/mysql.sock --port=xxx

Could increase open files limit but it seems unlikely we should have 50000 open files?

2. --skip-name-resolve errors

We're getting LOTS of --skip-name-resolve errors in logs. Logs show intense periods where around 1000 entries appear with exactly the same timestamp, the first 500 showing [Warning] 'user' entry '-' ignored in --skip-name-resolve mode then another 500 corresponding db/user entries showing [Warning] 'db' entry...

151021 19:16:40 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.

+ another 500 lines

151021 19:16:40 [Warning] 'db' entry 'xxx\xxx [email protected]' ignored in --skip-name-resolve mode.

+ another 500 lines

Thanks.

Server info

/etc/redhat-release:CentOS release 6.7 (Final)
/usr/local/cpanel/version:11.52.0.17
/var/cpanel/envtype:xen pv
CPANEL=release
Server version: Apache/2.2.27 (Unix)
Server built:   Jul 17 2014 06:46:24
Cpanel::Easy::Apache v3.26.0 rev9999
PHP 5.4.30 (cli) (built: Jul 17 2014 06:54:51)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies
    with the ionCube PHP Loader v4.6.1, Copyright (c) 2002-2014, by ionCube Ltd., and
    with Zend Guard Loader v3.3, Copyright (c) 1998-2013, by Zend Technologies
mysql  Ver 14.14 Distrib 5.5.45, for Linux (x86_64) using readline 5.1

 

[cPanelMichael]

Hello

Do you notice any specific entries in the MySQL error log (/var/lib/mysql/$hostname.err) just before it crashes (other than the warning messages you posted)?

Thank you.

 

[go4]

Thanks cPanelMichael,

Appreciate your help.

To clairify what I mean when I say crash: although from the frontend we're getting "Cannot connect to database" and everything going down the xxx.err log doesn't show a crash.

What I can see in the log though is that instantly on restart those --skip-name-resolve ignored errors start up, hundreds of lines logging at the same second restart completes.

150927 19:25:09 mysqld_safe Number of processes running now: 0
150927 19:25:12 mysqld_safe mysqld restarted
150927 19:25:13 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
150927 19:25:13 [Note] Plugin 'FEDERATED' is disabled.
150927 19:25:13 InnoDB: The InnoDB memory heap is disabled
150927 19:25:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150927 19:25:13 InnoDB: Compressed tables use zlib 1.2.3
150927 19:25:13 InnoDB: Using Linux native AIO
150927 19:25:14 InnoDB: Initializing buffer pool, size = 128.0M
150927 19:25:14 InnoDB: Completed initialization of buffer pool
150927 19:25:14 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
150927 19:25:14  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
150927 19:25:23  InnoDB: Waiting for the background threads to start
150927 19:25:24 InnoDB: 5.5.42 started; log sequence number 3135004108
150927 19:25:24 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150927 19:25:24 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
150927 19:25:24 [Note] Server socket created on IP: '0.0.0.0'.
150927 19:25:24 [Warning] 'user' entry 'xx@xx' ignored in --skip-name-resolve mode.
150927 19:25:24 [Warning] 'user' entry 'xx@xx' ignored in --skip-name-resolve mode.
150927 19:25:24 [Warning] 'user' entry 'xx@x'x ignored in --skip-name-resolve mode.
150927 19:25:24 [Warning] 'user' entry 'xx@xx' ignored in --skip-name-resolve mode.
...etc etc etc...

 

[anton_latvia]

is that end? Does MySQL server operate normally afterwards? open-files-limits can be lowered.

 

[go4]

Thanks anton,

No afterwards we straight away get hundreds of those
[Warning] 'user' entry 'xx@xx' ignored in --skip-name-resolve mode entries and then always in Process Manager

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/xxx.com.err --open-files-limit=50000 --pid-file=/var/lib/mysql/xxx.com.pid --socket=/var/lib/mysql/mysql.sock --port=3306

 

[anton_latvia]

There might be other reasons for crashing, although in general MySQL is a stable piece of software.. Take a look at example configuration here: Optimizing MySQL configuration at blog.hosting.guru