The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Loading phpmyadmin / table_open_cache

Discussion in 'Database Discussions' started by ruiz, Aug 17, 2011.

  1. ruiz

    ruiz Active Member

    Joined:
    Feb 13, 2008
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    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!!
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    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?
     
  3. ruiz

    ruiz Active Member

    Joined:
    Feb 13, 2008
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    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.
     
    #3 ruiz, Aug 17, 2011
    Last edited: Aug 17, 2011
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    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.
     
  5. ruiz

    ruiz Active Member

    Joined:
    Feb 13, 2008
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    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
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    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:

    Code:
    \u databasename
    Here databasename would be the full database name such as myuser_mydatabase.
     
Loading...

Share This Page