Mysql – Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts

memoryMySQLoptimizationperformance

We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM table. The version of MySQL is: 5.1.56-community-log

For some reason, the server eventually eats all the memory it has, goes into swap and becomes very slow. Even after NOTHING else is happening on the server, it stays that way.

Here's a picture showing htop:
enter image description here

How we do these inserts. There's a job every hour that runs on a server and starts doing a lot of inserts. All inserts are DELAYED. Inserts are NOT extended but we use mysqli::multiple_query to send 2500 of these single statement inserts to the server in one batch.

What would also help is to look inside MySQL and see where it's consuming all that memory. We've already checked all the usual buffers and they are way below the amount of memory we have.

Best Answer

According to the MySQL Documentation

  • When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.
  • Inserts from many clients are bundled together and written in one block (This consumes memory). This is much faster than performing many separate inserts.
  • The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost.
  • INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows (This consumes memory).
  • For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM. This is definitely the case for a MyISAM table that experiences no DELETEs and UPDATEs.

Based on these excerpts from the MySQL Documentation, I would like to suggest the following options:

OPTION 1

Change your target table from MyISAM to InnoDB. This will eliminate the need to use INSERT DELAYED altogether. With InnoDB using MVCC for ACID compliance, the InnoDB log files will better protect data in the events of a crash via MySQL calling for InnoDB to do crash recovery. You will need to tune InnoDB accordingly.

OPTION 2

If you cannot switch the table to InnoDB, you must go with extended INSERTs. However, you must adjust bulk_insert_buffer_size (its default is 8M) to a much larger to compensate using extended INSERT.

Either way, you need to stop using INSERT DELAYED like yesterday.

Related Question