Mysql deletes & recreates socket due to high load?
Posted: Sat Feb 26, 2011 1:53 pm
I'm running some queries that each take 24hrs to complete, moving data from one table to another set of tables (normalizing it in the process). Like I said it takes over 24hrs, due to tens of millions of rows. Towards the end of these queries mysql becomes totally unresponsive:
After a while, the monitor physically attached to this server comes on (it was not responding at all at first due to such high memory usage). Then, mysql comes back up on it's own but almost like it reset itself:
I can still see mysql using resources:
26087 mysql 18 0 1405m 426m 3264 S 39.4 21.3 5:37.76 mysqld
under 'top' I showed a load of server load of 30, the machine was 1 CPU so that is pretty crazy to see. In the future how can I avoid this? Should I just tell mysql to use less memory? Should I run smaller queries? Is it unavoidable with these types of queries?
Code: Select all
> show full processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
ERROR:
Can't connect to the server
mysql> show full processlist;
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
ERROR:
Can't connect to the server
mysql> AbortedCode: Select all
# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@localhost www]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 126087 mysql 18 0 1405m 426m 3264 S 39.4 21.3 5:37.76 mysqld
under 'top' I showed a load of server load of 30, the machine was 1 CPU so that is pretty crazy to see. In the future how can I avoid this? Should I just tell mysql to use less memory? Should I run smaller queries? Is it unavoidable with these types of queries?