Page 1 of 1

Mysql deletes & recreates socket due to high load?

Posted: Sat Feb 26, 2011 1:53 pm
by josh
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:

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> Aborted
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:

Code: 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 1
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?

Re: Mysql deletes & recreates socket due to high load?

Posted: Sun Feb 27, 2011 2:11 pm
by DigitalMind
I would suggest to run queries with LIMIT inside php loop with short pauses.

Re: Mysql deletes & recreates socket due to high load?

Posted: Tue Mar 01, 2011 4:43 pm
by josh
"limit" can't be used on an update statement. The obvious work around is to modify the script to work on batches of data, which is done. But that is slow and shouldn't need be done, also it'd be impossible to foresee areas where users are going to use larger amounts of data. Mysql should have no limit on row numbers, should it?

Re: Mysql deletes & recreates socket due to high load?

Posted: Tue Mar 01, 2011 5:44 pm
by DigitalMind
josh wrote:"limit" can't be used on an update statement
Wrong.
josh wrote:also it'd be impossible to foresee areas where users are going to use larger amounts of data.
Sorry, I'm not sure I got you. Maybe because I don't know the project details.
BTW do you use indexes for tables which you modify (insert/update/delete)?
josh wrote:Mysql should have no limit on row numbers, should it?
It shouldn't.
I think you know that every storage engine has own advantages and disadvantages. For example, when you're running an update query MyISAM locks tables but InnoDB locks rows.
I hope you understood me. Sorry for my poor English. :)

Re: Mysql deletes & recreates socket due to high load?

Posted: Tue Mar 01, 2011 10:12 pm
by Weirdan
josh, check /var/log/messages to see if mysql fell victim to OOM killer

If that's the case you need to make sure mysql doesn't use more memory (including swap) than it's available on your system. That's done by adjusting various limits in my.cnf

Re: Mysql deletes & recreates socket due to high load?

Posted: Wed Mar 02, 2011 8:18 am
by VladSun
If a DB sevrer starts swapping, you are lost IMHO :)

Re: Mysql deletes & recreates socket due to high load?

Posted: Thu Mar 03, 2011 5:05 am
by Darhazer
DigitalMind wrote:For example, when you're running an update query MyISAM locks tables but InnoDB locks rows.
I hope you understood me. Sorry for my poor English. :)
That's correct, but if you run update which does not use index for the where clause, it will perform full table scan and will lock all of the rows...

Re: Mysql deletes & recreates socket due to high load?

Posted: Thu Mar 03, 2011 2:17 pm
by DigitalMind
Darhazer wrote:That's correct, but if you run update which does not use index for the where clause, it will perform full table scan and will lock all of the rows...
On the other hand, if indexes are used, that occurs additional time consumptions for rebuilding them during update, insert or delete.