# Mysql big server, strange lags, locking, ...and restarts

## jpdasst

Hi,

Got a big hw gentoo server. (details bellow).

It's s a lamp server with memcached.

Mysql is "heavy" read loaded =~ 1000 q/sec avg , "low" write loaded =~ 60 q/sec avg .

Query cache is on, and correctly sized.

At random times, (2/3 a week), mysql start to "explode". (Connections goes quickly to max, and mysql become non responsive).

Activated lock monitoring, it appears that some queries were not optimized, leading server to create lots of temporary tables and locking lots of tables/rows. 

Lots of query are now cached (in memcached).

After optimizing this queries, situation seems better, less locking bottlenecks, but "mysql explosion" continue, but mysql become non responsive without going to max_conn.

At crash i saw again lots of (dead ?)locking.

Last experiment was to lower innodb_thread_concurrency to values as low as 2 ....In this configuration, "explosions" continue to happens, but "show innodb status" does not mention any lock problem, while mysql "freeze".

I have suspected lots of thing outside mysql (file io bottleneck, kernel max ressources (files...), lack of entropy ...),  lots of monitoring on those point, but nothing seems to explain what happened.

In this kind of (strange..) situation, what would your first favorit suspect ??

==HW Details==

Running 3.2.12 custom "gentoo-sources"  kernel , GCC 4.5.3

Run apache 2.2.22(mpm_prefork) with mod_php (5.3.14) on gentoo/GNULinux (using old mysql extension).

The box is quad Intel Xeon CPU X5690 @ 3.47GHz ( 24 cores) , with 48 Go Ram. Running Raid1 Hard Array on LSI Logic / Symbios Logic MegaRAID It's a Numa memory architecture box.

----------

## Mad Merlin

What happens when mysql becomes non-responsive? High CPU usage? High IO wait time? Neither?

Are you running low on disk space? What's the output from 

```
show full processlist
```

 when it's non-responsive? Does mysql eventually recover, or are you killing and restarting it manually? Are you swapping? How's your mysql config WRT memory? (48G is peanuts for a 24 core server)

----------

## jpdasst

Thanx for your time !

> What happens when mysql becomes non-responsive?

High cpu usage, and that's all.

show processlist report lots of process keeping "Sending Data" (<== here could be a "php/apache" problèm ? )

Now, mysqladmin debug doesn't report any lock problem

free disk space real high.

No swaping problem... 

All "standard" optimization to mysql are done, buffer pool is never full.

We have to restart mysql, he never recover (not in 5 minutes).

I suspect a misconfiguration link to Numa  ... 

It seems that memory from one node is never used .... 

>>>numastat 

                           node0           node1

numa_hit              4011046117      3069524573

numa_miss                      0       217226282

numa_foreign           217226282               0

interleave_hit             22797           22814

local_node            4011043705      3069503979

other_node                  2412       217246876

----------

## zeek

 *jpdasst wrote:*   

> 
> 
> ```
> 
> It seems that memory from one node is never used .... 
> ...

 

I reformatted the numastat output for readability.

I like to run "mytop" when mysql backlogs happen, and kill off the oldest running query.  This usually immediately fixes the problem.  K in mytop is a shortcut to killing the query.  Note never kill long running insert queries, they'll take forever to rollback!

----------

## jpdasst

 *Quote:*   

>  I reformatted the numastat output for readability.

 

Thx, it was awfull

 *Quote:*   

> I like to run "mytop" when mysql backlogs happen, and kill off the oldest running query.  This usually immediately fixes the problem.  K in mytop is a shortcut to killing the query.  Note never kill long running insert queries, they'll take forever to rollback!

 

Thx a lot for this tip.

So you've got some kind of similar problem with your mysql ?

----------

