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.