Drop table performance

PREVIOUS POST
NEXT POST

There have been recent discussions about DROP TABLE performance in InnoDB. (You can refer to Peter’s post http://www.percona.com/blog/2011/02/03/performance-problem-with-innodb-and-drop-table/ and these bug reports: http://bugs.mysql.com/bug.php?id=51325 and http://bugs.mysql.com/bug.php?id=56332.) It may not sound that serious, but if your workload often uses DROP TABLE and you have a big buffer pool, it may be a significant issue. This can get especially painful, as during this operation InnoDB holds the LOCK_open mutex, which prevents other queries from executing. So, this is a problem for a server with a large amount of memory, like the one we have in our lab: a Cisco UCS C250 with 340GB of RAM.

To fix this problem, we implemented “background table drop”, which is available in Percona Server 5.1.56 and Percona Server 5.5.10. Also, looking at the MySQL 5.6.2 announcement, the InnoDB team has implemented “cleaning thread”, which is also supposed to fix this problem.

I ran a small benchmark to see what improvement we have. Hardware was the Cisco UCS C250, the InnoDB buffer_pool size was 144GB, and I filled the buffer pool with different amounts of data: 104GB, 52GB, 21GB. I ran a simple cycle. First I created 1000 tables:

And then I dropped it. I measured the time to drop 1000 times in MySQL 5.1.56, in Percona Server 5.1.56 with “innodb_lazy_drop_table”, and in MySQL 5.6.2.

Here are my results (results in seconds; fewer is better):

Allocated pages, GBMySQL 5.1.56Percona Server 5.1.56MySQL 5.6.2
21GB31095234
52GB776210548
104GB16354661210

Also, to show the dynamics, here is a graph showing the number of dropped tables per second (more is better):

As we see, all three versions are affected by a bigger buffer pool: The more memory used, the slower DROP TABLE operates. MySQL 5.6.2 shows some improvement compared to MySQL 5.1.56, but Percona Server 5.1.56 has a better result.

The relative comparison for the 104GB case: Pecona Server is 3.5x better than MySQL 5.1.56 and 2.6x better than MySQL 5.6.2.

(Disclaimer: The “innodb_lazy_drop_table” feature in Percona Server and this benchmark are sponsored by a Well Known Social Network.)

PREVIOUS POST
NEXT POST

Comments

  1. Claudio Nanni says

    I have always been surprised on how little talk and probably research there is around using raw partitions for storing data in MySQL, is there any particular reason? like it is so bad you don’t want to even think about it? From a pure theoretical standpoint you would get rid of one layer (fs) and its limitations, but may be InnoDB is not enough mature to deal efficiently with raw devices? (may be some R&D is worth doing in this direction?) I have worked with Oracle and raw devices are an option, actually you want them especially if you use extra layers like ASM. I would appreciate an opinion from the experts.
    http://dev.mysql.com/doc/refman/5.1/en/innodb-raw-devices.html

  2. Vojtech Kurka says

    Vadim,

    I think this benchmark doesn’t show the real effect of the fix. For the real workloads it would be much more interesting to do this:

    1.) Run OLTP benchmark on large buffer pool.
    2.) Run the same OLTP benchmark and run 1 create+drop table per second in parallel .
    3.) Compare the results, including response time percentiles.

    Many users might think this patch just makes DROP faster, but the most important effect is making the OLTP workload stable without stalls on every DROP.

  3. Baron Schwartz says

    We fixed this problem in Percona Server, release 5.5.10-20.1 with the innodb_lazy_drop_table option.

  4. Vadim Tkachenko says

    Dave,

    There is one big problem with ext3, and unfortunately it was not fixed with ext4.

    It is when you use O_DIRECT ( and it is what we recommend for InnoDB), the writes are serialized for inode, that is you can’t write in parallel into the same file.
    This this big showstopper for fast storages, and if you do not use innodb_file_per_table
    you may be affected by this.
    ( see http://dom.as/2008/08/11/notes-from-land-of-io/ )

    There is also know performance issues with ext3 and sync_binlog
    http://www.mysqlperformanceblog.com/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/

    And in general XFS shows better and stable performance than ext3.
    ext4 looks better than ext3, but still many issues remains.
    And this is topic for another big post :)

  5. Peter Zaitsev says

    Less than a minute like 5 seconds or like 55 seconds ?

    Here is as I see it. If it says 10 seconds to create/drop tables with empty buffer pool and it takes 466 seconds with 100G buffer pool than Percona Server has 46 times regression with large buffer
    pool, which is significant number. MySQL may have it at over 150 times but in my opinion both of these numbers are beyond being acceptable

  6. Peter Zaitsev says

    Vadim,

    One more question. Did you run the test with empty buffer pool by chance ? I’m wondering how long would the test take in this case – this would give us idea about performance once should have if performance of drop table is made independent from buffer pool size.

  7. Dave Juntgen says

    Vadim –

    I’ve moved away from innodb_file_per_table=1 because of this drop table issues, I was seeing lockups dropping very small tables with innodb buffer pool set to 24G. It appears that the lazy drop table flag is invald when innodb_file_per_table=0, is this true and is there any advantage to dropping a table when using one large table space?

    And last, and maybe for another post, why is XFS “the only real filesystem”?

    Thanks,

    –Dave

  8. Vadim Tkachenko says

    Ryan,

    I do not know, but the problem there is CPU bound, not disk related.
    DROP TABLE has to scan all pages in buffer_pool to perform operation on it.

  9. says

    Vadim,

    So it is a lot faster in Percona Server but still the more memory we have allocated for buffer pool the longer it takes, why is that ? My understanding with lazy drop table we should have had create/drop table speed independent on buffer pool size with no operations which become slower with buffer pool size.

  10. Dave Juntgen says

    Vadim –

    I’ve moved away from innodb_file_per_table=1 because of this drop table issues, I was seeing lockups dropping very small tables with innodb buffer pool set to 24G. It appears that the lazy drop table flag is invald when innodb_file_per_table=0, is this true and is there any advantage to dropping a table when using one large table space?

    And last, and maybe for another post, why is XFS “the only real filesystem”?

    Thanks,

    –Dave

  11. says

    Vadim,

    One more question. Did you run the test with empty buffer pool by chance ? I’m wondering how long would the test take in this case – this would give us idea about performance once should have if performance of drop table is made independent from buffer pool size.

  12. says

    Less than a minute like 5 seconds or like 55 seconds ?

    Here is as I see it. If it says 10 seconds to create/drop tables with empty buffer pool and it takes 466 seconds with 100G buffer pool than Percona Server has 46 times regression with large buffer
    pool, which is significant number. MySQL may have it at over 150 times but in my opinion both of these numbers are beyond being acceptable

  13. says

    Dave,

    There is one big problem with ext3, and unfortunately it was not fixed with ext4.

    It is when you use O_DIRECT ( and it is what we recommend for InnoDB), the writes are serialized for inode, that is you can’t write in parallel into the same file.
    This this big showstopper for fast storages, and if you do not use innodb_file_per_table
    you may be affected by this.
    ( see http://dom.as/2008/08/11/notes-from-land-of-io/ )

    There is also know performance issues with ext3 and sync_binlog
    http://www.mysqlperformanceblog.com/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/

    And in general XFS shows better and stable performance than ext3.
    ext4 looks better than ext3, but still many issues remains.
    And this is topic for another big post :)

Leave a Reply

Your email address will not be published. Required fields are marked *