0

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?

Improve this question

1 Answer 1

Reset to default
-1
  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
Improve this answer
8
  • Thanks for your help, unfortunatelly I've already done everything you said.
    – Fabio Spampinato
    Commented Oct 3, 2014 at 19:06
  • So you have to dump the base, delete it and restore from the dump. Seems you have an unrecoverable failure.
    – Kondybas
    Commented Oct 3, 2014 at 19:14
  • It doesn't sound good :S I've added more informations that I've just discovered to my original question.
    – Fabio Spampinato
    Commented Oct 3, 2014 at 19:17
  • Well, it's time to look at your query queue. Launch this: SELECT USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 51200) AS Info FROM information_schema.PROCESSLIST;
    – Kondybas
    Commented Oct 3, 2014 at 19:24
  • Done it, it shows around 15 queries and almost all of them with the status "Waiting for Table Level Lock". I've switched my main table to InnoDB, runned again yout query and the status was "Sending data", unfortunately the performances were the same. I would like not to paste here the full result of your query since it will expose part of the structure of my database.
    – Fabio Spampinato
    Commented Oct 3, 2014 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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