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

go4

• October 21, 2015 20:11

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 'xxx_xxx@xxx.xxx.com' ignored in --skip-name-resolve mode.
+ another 500 lines
151021 19:16:40 [Warning] 'db' entry 'xxx\xxx xxx@xxx.xxx.com' 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

  • October 22, 2015 14:17

  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.

  0
• 

  go4

  • October 25, 2015 23:50

  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...
  

  0
• 

  anton_latvia

  • October 27, 2015 09:44

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

  0
• 

  go4

  • October 27, 2015 22:23

  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
  

  0
• 

  anton_latvia

  • October 28, 2015 10:11

  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

  0

Please sign in to leave a comment.