EmergencyEMERGENCY? Get 24/7 Help Now!

Drop table performance

 | April 20, 2011 |  Posted In: Benchmarks, Insight for DBAs, Insight for Developers, MySQL, Percona Software

PREVIOUS POST
NEXT POST

There have been recent discussions about DROP TABLE performance in InnoDB. (You can refer to Peter’s post https://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, GB MySQL 5.1.56 Percona Server 5.1.56 MySQL 5.6.2
21GB 310 95 234
52GB 776 210 548
104GB 1635 466 1210

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
Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

22 Comments

  • 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

  • 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.

  • 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 :)

  • 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

  • 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.

  • 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

  • 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.

  • 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.

  • 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

  • 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.

  • 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

  • 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

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.