[MySQL]multiple connections, database locking up

最近關心的mysql DB 發生了奇怪的狀況，突然就卡住不動了 QQ…

用show processlist 觀察了一下目前的處理情況，發現一堆的 unauthenticated user…

馬上請google調派天兵天將去查了 unauthenticated user 是怎個回事

在官網看到 multiple connections, database locking up. 討論串@ http://bugs.mysql.com/bug.php?id=2814 :

有很多人提到相同的狀況，有人發現與DNS有關係，因此建議修改 /etc/hosts 或 在mysql 啟動時加入skip-name-resolve ( in my.cnf) 的參數。

另外，在 http://www.debianhelp.co.uk/mysqlperformance.htm 看到了這樣的建議:

Optimize Mysql Database response time

The most important part for getting a system fast is of course the basic design. You also need to know what kinds of things your system will be doing, and what your bottlenecks are.

The most common bottlenecks are:

Disk seeks. It takes time for the disk to find a piece of data. With modern disks in 1999, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimise for a single table. The way to optimise this is to spread the data on more than one disk.

Disk reading/writing. When the disk is at the correct position we need to read the data. With modern disks in 1999, one disk delivers something like 10-20 MB. This is easier to optimise than seeks because you can read in parallel from multiple disks.

CPU cycles. When we have the data in main memory (or if it already were there) we need to process it to get to our result. Having small tables compared to the memory is the most common limiting factor. But then, with small tables speed is usually not the problem.

Memory bandwidth. When the CPU needs more data than can fit in the CPU cache the main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one should be aware of it.

Some of the available options we will see now

Disable DNS Hostname Lookup

open you /etc/mysql/my.cnf file and use –skip-name-resolve option file looks like below.

[mysqld]
…..
……
skip-name-resolve

When this option is activated, you can only use IP numbers in the MySQL Grant table.

照辦囉~~

接下來就是繼續觀察還有沒有卡住的情況了。