When issuing ALTER TABLE .. DROP PARTITION p1 mysql have to flush pages to disk. My question is: does mysql flush pages in the whole table (in each partition) or only in a partition to be dropped? MySQL server 5.7

table partition exp. is: PARTITION BY RANGE (UNIX_TIMESTAMP(dt))

  • Are you asking about flushing from the buffer_pool? Or about disk activity after the DROP, which may be noticeable. And you say "flush to disk" -- what if it chooses to remove them from the buffer_pool without writing to disk.
    – Rick James
    Jan 7 at 17:05
  • "what if it chooses to remove them from the buffer_pool without writing to disk.". Hmm, I always thougt that mysql flushes dirty pages to disk before deleting a partition (not allowing tham to stay in buffer pool). Docs say that clean pages stay in bp (and will be evicted eventually, asyncroniosly), but I have no idea what about dirty pages, should they stay in bp or should they be flushed to disk (to .ibd file) before dropping a partition (and if this the case, what pages are to be flushed - the ones that belong to the partition to be dropped or pages in the whole table).
    – Max32Nov
    Jan 8 at 9:02
  • In general, I have heavy loaded system (lots rps, 40k+) and when I drop a partition, server experiencing a performance drop. And I try to figure out what couses it: locks on the partitioned table (hard to acquire a metadata-lock coz table very popular) or internal activities while dropping a partition (like flushing dirty pages). Server is on linux, ext4, good ssd. Dropping a partition file (every day one file) (50G each) should be fast from disk perspective (I dont see the disk to suffer when partition is being dropped and I dont see, that acquiring a metadata-lock on table takes much time).
    – Max32Nov
    Jan 8 at 10:10
  • It's hard to distinguish whether the buffer_pool is the issue or the OS freeing up the blocks of the partition is.
    – Rick James
    Jan 8 at 17:00

According to the MySQL 5.7 Documentation under the subheading Affected DDL statements, here is what says about Locking for DDL on Partitioned Tables:

CREATE VIEW does not cause any locks.

ALTER TABLE ... EXCHANGE PARTITION prunes locks; only the exchanged table and the exchanged partition are locked.

ALTER TABLE ... TRUNCATE PARTITION prunes locks; only the partitions to be emptied are locked.

In addition, ALTER TABLE statements take metadata locks on the table level.

As far as I can see in that page, it locks the table's metadata. There is nothing here suggesting that all the .ibd files of the other partitions need to be flushed. It would affect only those files involved.

In your case, it would involve the following steps:

  • Acquire metadata lock on the .par file of the partitioned table
  • Update list of partitions in the .par file
  • Remove locks of .ibd you are going to drop
  • Unlock the .par file

NOTE : Dropping a partition is DDL not DML.


Only partition to be dropped

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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