Mysql deletes & recreates socket due to high load?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Mysql deletes & recreates socket due to high load?

Post 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?
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

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

Post by DigitalMind »

I would suggest to run queries with LIMIT inside php loop with short pauses.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

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

Post 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?
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

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

Post 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. :)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

If a DB sevrer starts swapping, you are lost IMHO :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

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

Post 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...
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

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

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