MySQL Dump Error

[xlegends]

Hi guys,

So I have automatic hourly backups via mysqldump.

Since switching over to XF2 from vb4, I notice at the moment of mysql dump cron, the forum freezes for about 15-20 seconds.


After a month of this today it finally began to fail all together producing errors

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `

It looks like its exhausting memory when I have plenty of disk and ram available.


I did a complete system reboot and it resolved the dump error.

Now it successfully dumps again but I would like to fix the freeze during dump issue.




I have a plain default my.cnf config file

centos7/php72/mysqld57

/etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I added to it

innodb_buffer_pool_size=100M
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600

It didnt help. Although thankfully the dump errors havent returned. The freezes during dumps still exists.

Any suggestions?

 

[xlegends]

urrrgh now im getting this running Rebuild User Caches

An exception occurred: [XF\Db\Exception] MySQL statement prepare error [2006]: MySQL server has gone away in src/XF/Db/AbstractStatement.php on line 228

XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 196
XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 39
XF\Db\Mysqli\Statement->prepare() in src/XF/Db/Mysqli/Statement.php at line 54
XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1261
XF\Mvc\Entity\Finder->fetchOne() in src/XF/Repository/UpgradeCheck.php at line 39
XF\Repository\UpgradeCheck->getLatestUpgradeCheck() in src/XF/Admin/App.php at line 238
XF\Admin\App->renderPageHtml() in src/XF/App.php at line 1995
XF\App->renderPage() in src/XF/Admin/App.php at line 122
XF\Admin\App->renderPage() in src/XF/Mvc/Dispatcher.php at line 402
XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 58
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2184
XF\App->run() in src/XF.php at line 391
XF::runApp() in admin.php at line 13

and my dump errors are back

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `xf_post` at row: 1153253

a system reboot corrected it once again for now.

 

[xlegends]

I finally caught what its doing at the time it freezes and error produces

free -g

available memory: 0

This server has 16gb, I guess I can upgrade memory but any other point other than dumps only, theres always 9-10 gb available and a full swap.