MySQL Non Responive

[DRJ]

Not sure if there is the right place to ask. Recently my website has been having issues where the site becomes unresponsive. If I restart the MySQL service in WHM everything comes back up for a while.
I am running vBulletin 4.2.5 but even disabling the forums did not stop the issue.
The host has not been much help and says everything is good on their end.
What is odd is while the site is offline I can run SQL queries from PHPMyAdmin and when I check the Process List there is nothing queued up.
When we check for memory and bandwidth usage everything seems low so I am not sure what else to do and wondering if someone had any ideas.

[Dave]

Are you running the forum on a shared host or VPS? Generally I never recommend to run a forum on a shared host because 99% of the time they are slow or have very strict limitations in place.

[DRJ]

It is a dedicated server with 10,000 GB bandwidth, 16 GB RAM i5 760 2.8 GHz processor.
Has been running fine for years but for a month or so this issue has come up.
When the site is not responding I can't load any page that uses the database, normal static pages load fine. And when I restart MySQL it loads just fine and runs for a few hours.

--------------- Added 1595979204 at 1595979204 ---------------

My host gave me this info, I am not sure what it all means but maybe someone here can assist.


The data seems to be fine I don't see a high load either.

[root@server ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 7.8G 0 7.8G 0% /dev
tmpfs 7.8G 4.0K 7.8G 1% /dev/shm
tmpfs 7.8G 41M 7.8G 1% /run
tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup
/dev/sda3 456G 136G 298G 32% /
/dev/sdb1 459G 73M 435G 1% /backup
/dev/sda1 473M 254M 195M 57% /boot
/dev/loop0 3.9G 9.1M 3.7G 1% /tmp
[root@server ~]# df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
devtmpfs 2029506 370 2029136 1% /dev
tmpfs 2032400 2 2032398 1% /dev/shm
tmpfs 2032400 664 2031736 1% /run
tmpfs 2032400 16 2032384 1% /sys/fs/cgroup
/dev/sda3 30427056 498177 29928879 2% /
/dev/sdb1 30531584 11 30531573 1% /backup
/dev/sda1 124928 370 124558 1% /boot
/dev/loop0 262144 263 261881 1% /tmp

Ram usage.

[root@server ~]# free -m
total used free shared buff/cache available
Mem: 15878 1442 11995 41 2439 14107
Swap: 1952 0 1952

Thank you for choosing INAP!



Based on the error I found it looks like innodb is crashing without recovering.

2020-07-27 13:14:25 994 [Note] Giving 5 client threads a chance to die gracefully
2020-07-27 13:14:25 994 [Note] Event Scheduler: Purging the queue. 0 events
2020-07-27 13:14:25 994 [Note] Shutting down slave threads
2020-07-27 13:14:27 994 [Note] Forcefully disconnecting 0 remaining clients
2020-07-27 13:14:27 994 [Note] Binlog end
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'partition'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_METRICS'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_CMP'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'INNODB_TRX'
2020-07-27 13:14:28 994 [Note] Shutting down plugin 'InnoDB'
2020-07-27 13:14:28 994 [Note] InnoDB: FTS optimize thread exiting.
2020-07-27 13:14:28 994 [Note] InnoDB: Starting shutdown...
2020-07-27 13:14:29 994 [Note] InnoDB: Shutdown completed; log sequence number 5221764133
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'BLACKHOLE'
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'ARCHIVE'
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'MRG_MYISAM'
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'MyISAM'
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'MEMORY'
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'CSV'
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'sha256_password'
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'mysql_old_password'
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'mysql_native_password'
2020-07-27 13:14:29 994 [Note] Shutting down plugin 'binlog'
2020-07-27 13:14:29 994 [Note] /usr/sbin/mysqld: Shutdown complete

200727 13:14:30 mysqld_safe mysqld from pid file /var/lib/mysql/server.vbaexpress.com.pid ended
200727 13:14:30 mysqld_safe Logging to '/var/lib/mysql/server.vbaexpress.com.err'.
200727 13:14:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2020-07-27 13:14:30 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-07-27 13:14:30 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2020-07-27 13:14:30 0 [Note] /usr/sbin/mysqld (mysqld 5.6.47) starting as process 16866 ...
2020-07-27 13:14:30 16866 [Warning] Buffered warning: Could not increase number of max_open_files to more than 10000 (request: 40000)

2020-07-27 13:14:30 16866 [Note] Plugin 'FEDERATED' is disabled.
2020-07-27 13:14:30 16866 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-07-27 13:14:30 16866 [Note] InnoDB: The InnoDB memory heap is disabled
2020-07-27 13:14:30 16866 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-07-27 13:14:30 16866 [Note] InnoDB: Memory barrier is not used
2020-07-27 13:14:30 16866 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-07-27 13:14:30 16866 [Note] InnoDB: Using Linux native AIO
2020-07-27 13:14:30 16866 [Note] InnoDB: Using CPU crc32 instructions
2020-07-27 13:14:30 16866 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2020-07-27 13:14:30 16866 [Note] InnoDB: Completed initialization of buffer pool
2020-07-27 13:14:30 16866 [Note] InnoDB: Highest supported file format is Barracuda.
2020-07-27 13:14:30 16866 [Note] InnoDB: 128 rollback segment(s) are active.
2020-07-27 13:14:30 16866 [Note] InnoDB: Waiting for purge to start
2020-07-27 13:14:30 16866 [Note] InnoDB: 5.6.47 started; log sequence number 5221764133
2020-07-27 13:14:30 16866 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2020-07-27 13:14:30 16866 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2020-07-27 13:14:30 16866 [Note] Server hostname (bind-address): '*'; port: 3306
2020-07-27 13:14:30 16866 [Note] IPv6 is available.
2020-07-27 13:14:30 16866 [Note] - '::' resolves to '::';
2020-07-27 13:14:30 16866 [Note] Server socket created on IP: '::'.
2020-07-27 13:14:30 16866 [Note] Event Scheduler: Loaded 0 events
2020-07-27 13:14:30 16866 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.47' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
2020-07-27 19:13:59 16866 [Note] /usr/sbin/mysqld: Normal shutdown

2020-07-27 19:13:59 16866 [Note] Giving 5 client threads a chance to die gracefully
2020-07-27 19:13:59 16866 [Note] Event Scheduler: Purging the queue. 0 events
2020-07-27 19:13:59 16866 [Note] Shutting down slave threads
2020-07-27 19:13:59 16866 [ERROR] /usr/sbin/mysqld: Sort aborted: Query execution was interrupted
2020-07-27 19:14:01 16866 [Note] Forcefully disconnecting 0 remaining clients
2020-07-27 19:14:01 16866 [Note] Binlog end
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'partition'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_METRICS'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_CMP'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'INNODB_TRX'
2020-07-27 19:14:01 16866 [Note] Shutting down plugin 'InnoDB'
2020-07-27 19:14:01 16866 [Note] InnoDB: FTS optimize thread exiting.
2020-07-27 19:14:01 16866 [Note] InnoDB: Starting shutdown...
2020-07-27 19:14:02 16866 [Note] InnoDB: Shutdown completed; log sequence number 5222734373
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'BLACKHOLE'
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'ARCHIVE'
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'MRG_MYISAM'
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'MyISAM'
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'MEMORY'
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'CSV'
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'sha256_password'
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'mysql_old_password'
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'mysql_native_password'
2020-07-27 19:14:02 16866 [Note] Shutting down plugin 'binlog'
2020-07-27 19:14:02 16866 [Note] /usr/sbin/mysqld: Shutdown complete

200727 19:14:03 mysqld_safe mysqld from pid file /var/lib/mysql/server.vbaexpress.com.pid ended
200727 19:14:03 mysqld_safe Logging to '/var/lib/mysql/server.vbaexpress.com.err'.
200727 19:14:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2020-07-27 19:14:03 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-07-27 19:14:03 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2020-07-27 19:14:03 0 [Note] /usr/sbin/mysqld (mysqld 5.6.47) starting as process 30727 ...
2020-07-27 19:14:03 30727 [Warning] Buffered warning: Could not increase number of max_open_files to more than 10000 (request: 40000)

2020-07-27 19:14:03 30727 [Note] Plugin 'FEDERATED' is disabled.
2020-07-27 19:14:03 30727 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-07-27 19:14:03 30727 [Note] InnoDB: The InnoDB memory heap is disabled
2020-07-27 19:14:03 30727 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-07-27 19:14:03 30727 [Note] InnoDB: Memory barrier is not used
2020-07-27 19:14:03 30727 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-07-27 19:14:03 30727 [Note] InnoDB: Using Linux native AIO
2020-07-27 19:14:03 30727 [Note] InnoDB: Using CPU crc32 instructions
2020-07-27 19:14:03 30727 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2020-07-27 19:14:03 30727 [Note] InnoDB: Completed initialization of buffer pool
2020-07-27 19:14:03 30727 [Note] InnoDB: Highest supported file format is Barracuda.
2020-07-27 19:14:03 30727 [Note] InnoDB: 128 rollback segment(s) are active.
2020-07-27 19:14:03 30727 [Note] InnoDB: Waiting for purge to start
2020-07-27 19:14:03 30727 [Note] InnoDB: 5.6.47 started; log sequence number 5222734373
2020-07-27 19:14:03 30727 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2020-07-27 19:14:03 30727 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2020-07-27 19:14:03 30727 [Note] Server hostname (bind-address): '*'; port: 3306
2020-07-27 19:14:03 30727 [Note] IPv6 is available.
2020-07-27 19:14:03 30727 [Note] - '::' resolves to '::';
2020-07-27 19:14:03 30727 [Note] Server socket created on IP: '::'.
2020-07-27 19:14:03 30727 [Note] Event Scheduler: Loaded 0 events
2020-07-27 19:14:03 30727 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.47' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

[Dave]

You could try running an optimize & repair against all database tables. You can do this from the vBulletin admincp as well.

On top of that, if possible, try migrating to MariaDB from MySQL. It's based on the MySQL source code so you will likely not have to change anything configuration wise.

[snakes1100]

I'd correct the open files limit issue for starters as it can cause issues.

find / -name mysql.service -print (or mysqld.service)

Change LimitNOFILE= to unlimited

Restart SQL

As we dont know your SQL settings, you may have limits set to high, creating the open files issue, as yours requested 400000

Dont really see a reason at this point to change to MariaDB if this is the only issue.

[DRJ]

I am hopeful that Dave's suggestion works. I optimized all the tabled from the AdminCP and the site has remained up for about 16 hours so maybe there was some table corruption.

--------------- Added 1596089708 at 1596089708 ---------------

Everything seemed to run fine for about 24 hours then locked up again.
My open files is set to 10000.
Let me know if there are any settings you can recommend I change or if you want any specific information.

Thanks