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.

Rman, you have indeed many tables :slight_smile:
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:

What you can try though, is a very simple hint described here: mation_schema-slowness/
Good luck and let us know if that helped!