Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a CentOS server that is running cPanel (shared hosting server), I have had troubles with MySQL before and I spent some time configuring and tuning it, until finally the load dropped (from 170%!! to 10% or so), until 2 days ago when MySQL started acting all crazy!

The server got "stuck", Apache and MySQL specifically! I logged and checked the /tmp directory with df and it was using 100% of the available space (2 GB), I ran du -h and it reported only 129 MB used space! I had no idea where is the other space gone, I tried lsof +L1 to see open files and it listed a whole bunch of open deleted files in the temp directory (most of them had 0 size) except for one owned by MySQL which had 1.5 GB, I killed the process and the space was freed.

I did not know what that file is as I don't know how to access it so what did cause this file? And what should `lsof +L`` usually output? is this output normal?

httpd    6450 nobody  559w   REG    8,2        0     0 2492169 /usr/local/apache/logs/ssl_mutex (deleted)
httpd    6450 nobody  560u   REG    8,4        0     0     151 /tmp/ZCUDj2EaZi (deleted)
httpd    6490 nobody  559w   REG    8,2        0     0 2492169 /usr/local/apache/logs/ssl_mutex (deleted)
httpd    6490 nobody  560u   REG    8,4        0     0     151 /tmp/ZCUDj2EaZi (deleted)
httpd    6528 nobody  559w   REG    8,2        0     0 2492169 /usr/local/apache/logs/ssl_mutex (deleted)
httpd    6528 nobody  560u   REG    8,4        0     0     151 /tmp/ZCUDj2EaZi (deleted)
mysqld  46096  mysql    4u   REG    8,4        0     0     141 /tmp/ibvvhkQX (deleted)
mysqld  46096  mysql    5u   REG    8,4      432     0     143 /tmp/ibrHhJ9J (deleted)
mysqld  46096  mysql    6u   REG    8,4        0     0     144 /tmp/ibsWv8sw (deleted)
mysqld  46096  mysql    7u   REG    8,4        0     0     145 /tmp/ibjEeVSi (deleted)
mysqld  46096  mysql   17u   REG    8,4        0     0     146 /tmp/ibFo67JJ (deleted)
httpd   47389   root  559w   REG    8,2        0     0 2492169 /usr/local/apache/logs/ssl_mutex (deleted)
httpd   47389   root  560u   REG    8,4        0     0     151 /tmp/ZCUDj2EaZi (deleted)

There are like 100 files but it's not like I'm gonna post it all.

It's not a heavily loaded server and it is even VERY powerful for the websites it serves, so this surely is a mis-configuration or a bug problem, here is my server's information:

CPU: Intel(R) Xeon(R) CPU E31230 @ 3.20GHz/ 8MB cache RAM: 16 GB OS: CENTOS 6.4 MySQL: 5.1.68

Sometimes MySQL's CPU usage is reaching over 100%, steadily increasing while mysqladmin show processlist is only showing few processes, and this is my my.cnf file:

max_connections=500
innodb_file_per_table=1
local-infile=0
slow_query_log
key_buffer_size=2G
query_cache_limit=4M
query_cache_size=32M
join_buffer_size=2M
tmp_table_size=256M
max_heap_table_size=256M
thread_cache_size=4
table_cache=256
innodb_buffer_pool_size=512M

And one important thing to mention is, the swap is being used sometimes while I still have plenty of free physical RAM, and I think this should not happen.

For some reason I think the server is sometimes having some DoS attacks, I don't know if that is related but I know this cos sometimes when I ran netstat I see some IPs with 250 open connections (and these are not search engine bots), if I block these IPs new IPs will start connecting and again 100 - 200 open connections from one IP.

Sorry if I made this longer than it needs to be, but I want to know how to troubleshoot this problem, mainly about the large MySQL temp file and the "steadily" increasing load.

Thanks.

share|improve this question
    
I also wanted to ask, I was going to add a cron job that checks to see if there is any >1GB file opened by MySQL and restart MySQL so this does not happen again, temporarily until I find the solution, so is this safe? –  AL-Kateb Mar 13 '13 at 14:47
add comment

1 Answer

up vote 0 down vote accepted

I would recommend you to upgrade MySQL to latest version.

mysql_upgrade --force


replace slow_query_log with log-slow-queries=/var/lib/mysql/slow.log in my.cnf
then execute commands

touch /var/lib/mysql/slow.log
chmod 660 /var/lib/mysql/slow.log
chown mysql:mysql /var/lib/mysql/slow.log

Remove innodb_file_per_table=1 from my.cnf too.

You can install CSF Firewall if you doubt you are under DDoS attack:
http://configserver.com/free/csf/install.txt

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.