Not the answer you need?
Register and ask your own question!

For 1 million table, INFORMATION_SCHEMA gets very slow

rmanrman EntrantCurrent User Role Beginner
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</pre>


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.

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.