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.
_________________
-
jp

[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

[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
_________________
-
jp

[zeek]

[jpdasst]