Everything was going fine, but since a couple of hours ago MySQL is using from 400% to 550% of the CPU.
I'm getting a lot of these errors:
1003 14:03:17 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/var/mysql_tmpfs/#sql_10ff_8.MYI'; try to repair it 141003 14:03:17 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.38/storage/myisam/mi_write.c:226
I have no broken tables.
Also, using iotop, I discovered that MySQL was writing files inside the temp directory at a speed of around 150MB/s, that seems too high to me.
After googling a while I discovered that the problem could be a full /tmp directory, so I've created a 4GB ramdisk mounted on "/var/mysql_tmpfs".
After I setted this ramdisk as MySQL's default temp folder the server was able to slowly process some requests. but still not even an half of what it usually handles.
What can I do to solve the problem?
All the software are updated. The OS is Ubuntu Server 14.04.1 64-bit. The machine has 6 cores and 32gb of RAM.
I've tried to manually run this query:
SELECT * FROM my_table WHERE ID = 400000
And it takes one second or more to output a result, obviusly I have an index in that column and there are around 450000 rows in that table.
The profiler says that 100% of the time has been spend on "Waiting for Table Level Lock", isn't it strange?