checking permissions during DROP DATABASE

  • Filter
  • Time
  • Show
Clear All
new posts

  • checking permissions during DROP DATABASE

    Ok this is what I do know. Performing a drop on a Database causes the query to be in "Checking Permissions" state for a long time. This appears to be CPU Bound as nothing really is happening with disk I/O. The thread itself is using 100% of one of the CPU cores. It seems to be related to the INORMATION_SCHEMA and so any other query from other user on the DBMS hangs.

    We are dropping large databases around 16GB to 65GB with tens of thousands of tables anywhere from 10K to 34K tables. Does it need to check permissions for every table? Any way to by pass that if we are logged in with super user privileges?

    There is a MySQL bug thread that is, I think loosely coupled to to my problem, that suggests upgrading to 5.1.64. The current stable GA release for Percona is 5.1.63 (one short). We are currently using 5.1.56 http://bugs.mysql.com/bug.php?id=60961

    Is there anything in the my.cnf file below that stands out? We are using innodb_file_per_table and innodb_lazy_drop_table enabled.

    Percona Version: 5.1.56
    OS:CentOS release 5.5 (Final) 64-bit
    CPU: Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
    cache size: 12288 KB
    Cores: 16
    Memory: 72GB
    Disk:RAID 10, 300GB SAS 10K
    Server: Dell R710

    server_id = 230
    datadir = /var/lib/mysql
    log_bin = mysql-bin
    #relay_log = relay-bin
    expire_logs_days = 1
    max_allowed_packet = 32M
    collation_server = utf8_general_ci
    character_set_server = utf8
    init_connect ='SET NAMES utf8'
    old_passwords = 0
    character_set_server = utf8
    collation_server = utf8_general_ci
    open_files_limit = 65535
    max_connections = 200

    tmp_table_size = 32M
    sort_buffer = 32M
    key_buffer_size = 3GB
    join_buffer_size = 4M
    read_buffer_size = 128K
    thread_cache_size = 50
    max_allowed_packet = 32M
    query_cache_type = 0
    query_cache_size = 32M
    default_storage_engine = 'innodb'

    innodb_file_per_table = 1
    innodb_lazy_drop_table = 1
    innodb_buffer_pool_size = 50G
    innodb_log_file_size = 256M
    innodb_log_buffer_size = 32M
    innodb_log_files_in_group = 2
    innodb_flush_log_at_trx_commit = 2
    innodb_flush_method = O_DIRECT
    innodb_open_files = 4096
    table_definition_cache = 65536
    table_open_cache = 10240


    init-connect='SET NAMES utf8'

  • #2
    There are a number of things involved when dropping a database, and with your number of tables involved more work needs to be done - i.e. how big your table cache is, innodb data dictionary operations etc. Have you tried dropping per table so it is less intrusive?
    Our documentation has a lot of answers about common questions on Percona software, have you checked there before posting that question here? http://www.percona.com/forums/core/i...lies/smile.png

    Join us at the annual Percona Live MySQL Users Conference - http://www.percona.com/live/mysql-conference-2014/


    • #3
      Hi Thanks for the response, I believe this is what your asking for:

      table_definition_cache = 65536
      table_open_cache = 10240

      I have to add that I did have innodb_flush_method = O_DIRECT commented out. It's also commented out in our production environment, but we are not dropping databases in production.

      What I don't get is that if we are using a multi threaded databases, why is the system preventing other threads from accessing INFORMATION_SCHEMA or other tables?



      • #4
        What I don't get is that if we are using a multi threaded databases, why is the system preventing other threads from accessing INFORMATION_SCHEMA or other tables?

        The problem is basically if you're running innodb_file_per_table=1 the tablespace is dropped when you're running DROP TABLE, and Innodb has to go through LRU list and discard the pages which belong to this tablespace. This can take a lot of time with large buffer pool. Worst of all this is done while table_cache lock is being held so no other queries can start.

        Check below posts for more details.

        http://www.mysqlperformanceblog.com/2011/02/03/performance-p roblem-with-innodb-and-drop-table/

        http://www.mysqlperformanceblog.com/2011/04/20/drop-table-pe rformance/


        • #5
          Hi Niljoshi, we have innodb_lazy_drop_table=1 (enabled) and what the second article states is that there is a cleanup thread that handles that in the backgound. So that should free up the rest of the system to operate normally. It's seems that it's asynchronous drop.

          Question is do we need to enable the cleanup thread?