1

I have a very large InnoDB table that currently stores about 260 milions of rows and 40GB in size.

mysql> SELECT * FROM  INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db' AND   TABLE_NAME   = 'objects';
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| def           | db           | objects    | NULL           | NULL              |                       NULL |                          NULL | NULL             | NULL                | NULL                 | NULL                    | NULL                  |  225970904 |            171 | 38667747328 |            NULL |   8046510080 |         0 | 2024-04-02 12:08:15 | NULL        | NULL       |     NULL |                   |           | NULL            |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
1 row in set (0.04 sec)

To avoid infinite growth of the table I plan to delete the rows older than 2 years during the hours when the database is less busy every single day. I tried the following querry.

DELETE FROM objects WHERE DATEDIFF(NOW(), timestamp ) >= 731;

But it gives me error:

Error 1206: The total number of locks exceeds the lock table size

The size of innodb_buffer_pool_size is 128 MB which I guess is very low. Unfortunately the host machine has no more than 300 MB of free RAM. I has not tried yet to increase the size of innodb_buffer_pool_size buffer but I suppose that few hundreds of MB won't be enought and there is no more than that room for increasing it. The query is very slow, the host machine is low in RAM, the database is serving actively customers and there is a running application that inserts data consistently into the database. There is another guy that operates with that application and if want to restart the database I have to ask him to stop that application first. So setting innodb_buffer_pool_size by trial and error is a tricky job. Can you suggest me how to calculate approximately the minimum size of innodb_buffer_pool_size to avoid that error?

Another approach that I have not tried yes - since the table has timestamp and objectID columns and it is indexed by these columns the expired rows can be deleted object by object. First let's collect all unique object ids:

SELECT DISTINCT objectID FROM objects;

It takes about 30-40 seconds. Then delete by objectID:

DELETE FROM objects WHERE objectID = ... DATEDIFF(NOW(), timestamp ) >= 731;

But how to unite the two querries into a single one?

DELETE FROM objects WHERE objectID IN (SELECT DISTINCT objectID FROM objects) AND DATEDIFF(NOW(), timestamp ) >= 731;

gives an error

ERROR 1093 (HY000): You can't specify target table 'objects' for update in FROM clause
Description:    Ubuntu 12.04.1 LTS

mysql> select version();
+-----------------------------------+
| version()                         |
+-----------------------------------+
| 5.6.14-1+debphp.org~precise+1-log |
+-----------------------------------+

5
  • Your WHERE IN in the last query obviously makes no sense - remove it at all.
    – Akina
    Apr 3 at 15:37
  • What is result of SELECT @@innodb_file_per_table; ? Apr 7 at 17:32
  • How many years of data is recorded in the OBJECTS table at this moment, so we get some idea of the magnitude of your situation? You might want to consider the kondybas Stored Procedure but only DELETE the OLDEST year first to make the duration more manageable. A tip for you ( if your 5.6.n version supports this Global Variable ) - max_write_lock_count=16 would allow read data at more frequent intervals and is desirable for any MySQL instance, no matter what is going on. Apr 7 at 17:55
  • 1
    @Wilson Hauck SELECT @@innodb_file_per_table is 1. The table has two years of data.
    – 0xC0DEGURU
    Apr 8 at 9:45
  • Since you only have two years of data, seriously consider using the kondybas Stored Procedure suggestion. I was afraid you had multiple years of data to be deleted. Apr 8 at 15:13

3 Answers 3

2

To avoid long locking you have to create a stored procedure that will delete a limited number of the rows in a loop:

 massdel: LOOP
   DELETE 
     FROM table
    WHERE <conditions>
    LIMIT 1000;

   IF ROW_COUNT() = 0   -- number of actually deleted rows
     THEN LEAVE massdel;
   END IF;
 END LOOP massdel;

Each iteration of the loop massdel will lock your table no longer than needed for deleting 1000 rows, and it will be repeated until all the rows satisfied by conditions will be deleted. So its execution will be interleaved with other queries with no significant slowdown - but for price of longer deletion. You can try other limit value to change the granularity of the deletion.

2
  • If I don't search by specific objectID in WHERE clause along with the timestamp the query goes forever. Since my table is indexed by objectID it looks like the querry works much more faster when an objectID is specified. I used the proposed by @Kondybas stored procedure calling it in a loop for each objectID from another procedure. The total time for deleting 4 months of data was between 3 and 4 hours.
    – 0xC0DEGURU
    Apr 8 at 9:59
  • @0xC0DEGURU Please post the code you used for the Kondybas SP suggestion and I believe we can find a way to reduce runtime significantly. Apr 9 at 12:18
1

MySQL is telling you that it doesn’t have enough room to store all of the row locks that it would need to execute your query.

to fix it you need to adjust innodb_buffer_pool_size and restart MySQL.

By default, this is set to only 8MB, so increase it step by step till it meest your requirement.

Read up on MySQL manual how to approach a optimal size

0

1, Note that this is not sargable :

  WHERE DATEDIFF(NOW(), timestamp ) >= 731

Switching to this might help:

  WHERE timestamp >= NOW() - INTERVAL 731 DAY

or

  WHERE timestamp >= CURDATE() - INTERVAL 2 YEAR
  1. Is there an index on timestamp? Having such would help the above, but is not necessary for the following.

  2. Chunk the task based on the PRIMARY KEY, as discussed big deletes

  3. Read about various other techniques in the link above.

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.