MySQL uses more than 500% of the CPU

database-tuningerrorsMySQLperformance

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.

EDIT 1:

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?

Best Answer

  1. Create folder mysql_tmp on the biggest partition you have.
  2. Add the line tmpdir = /path/to/the/mysql_tmp to the section [mysqld] of the mysql.conf.
  3. restart mysql
  4. perform REPAIR for all the tables in your working base
Related Question