Loading phpmyadmin / table_open_cache

[ruiz]

Hi guys,

I have a few huge databases with 6000+ tables in it so everytime i try to load one of then with phpmyadmin it takes from 3secs to a whole minute to load. I noticed this is related to table_open_cache, because phpmyadmin needs to open all tables in that particular database, so, i reised my table_open_cache to those values:
table_open_cache=16384
table_definition_cache=16384

It worked for a while(consuming almost 1gb of ram in the meantime), but eventually my opened tables filled up and again it took a minute to load phpmyadmin.

So, what's my problem? Is phpmyadmin just inneficient? Is there some option to fix it? Is 6k+ tables too much? Thanks!!

 

[cPanelTristan]

These databases actually have over 6000 individual tables rather than rows? I could see having a high number of rows, but I am trying to understand why any database would need 6k+ tables?

 

[ruiz]

Hi cPanelTristan thanks for your response,

Because we have 1500+ clients, each one with a couple tables for their services. I store all those databases in a single server.

Do you think it would be better to merge some of then (or all?) ?? Maybe, but it would be a problem if i need to move one of my clients website to his own hosting.

 

[cPanelTristan]

The number of databases isn't the question, it is the number of tables in one database. Why do you have a database with 6k+ tables in it? What type of database has that many tables in it? If you are storing multiple clients in one database and using tables to differentiate their sites, then that would not be advisable. No database should need to have 6k+ tables in it.

For rows, I could foresee having a high number of rows. I cannot see any reason to have that many tables in one database.

 

[ruiz]

The number of databases isn't the question, it is the number of tables in one database. Why do you have a database with 6k+ tables in it? What type of database has that many tables in it? If you are storing multiple clients in one database and using tables to differentiate their sites, then that is not advisable at all. No database should need to have 6k+ tables in it. For rows, I could foresee having a high number of rows. I cannot see any reason to have that many tables in one database.

I'm sorry i typed it wrong in my previous post. I have a couple databases with thousands of tables in it.

The main reason is so it would be easier to migrate then to another hosting service, if they wish for it. If i merge all accounts, that would still be possible, but will require some scripting for it.

So having that much tables is not advisable? Its working great so far except for phpmyadmin, but i guess i'll try to merge some services. Thanks

 

[cPanelTristan]

I wouldn't suggest having that many tables in any single database. These users should be in separate databases rather than merging into one database with many thousands of tables. The larger the MySQL database, the bigger concern for corruption.

If you wish to still utilize this many tables, you could always use MySQL command line in root SSH, which you can reach simply by issuing the "mysql" command there. You can then select a set database with:

\u databasename

Here databasename would be the full database name such as myuser_mydatabase.