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.