For 1 million table, INFORMATION_SCHEMA gets very slow

  • Filter
  • Time
  • Show
Clear All
new posts

  • For 1 million table, INFORMATION_SCHEMA gets very slow

    I have been administering a Mysql Community Server 5.1.
    The database instance has 100 schemas and in every schema there are 12000 tables. So, I have almost 1200000 tables in the database.
    I have to support 500 concurrent users.
    My server is Quad core dual processor with 32 GB RAM where only mysql server is running.

    I set up Key_buffer_Size to 4 GB, thread_cache to 510.

    vi my.cnfskip-lockingkey_buffer_size = 4096Mmax_allowed_packet = 1Gtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 510query_cache_size = 32Mthread_concurrency = 8tmp_table_size = 16777216

    The application is running good. But the problem is when I try to query in INFORMATION_SCHEMA for maintenance purposes, I get stuck.
    INFORMATION_SCHEMA.TABLES take too much time to display result.

    What modifications I should do to improve the above performance?
    Any advice please.

  • #2
    Rman, you have indeed many tables
    Unfortunately quering INFORMATION_SCHEMA.TABLES is slow by nature, especially when you have many tables.
    You can see people were facing this problem for years. Here is example bug report: http://bugs.mysql.com/bug.php?id=19588

    What you can try though, is a very simple hint described here: http://www.mysqlperformanceblog.com/2011/12/23/solving-infor mation_schema-slowness/
    Good luck and let us know if that helped!