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.

simple phpmyadmin command causes load to spike

Discussion in 'Database Discussions' started by bmett, Sep 4, 2013.

  1. bmett

    bmett Well-Known Member

    Joined:
    Aug 5, 2010
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brisbane, AU
    Hi there,

    On one of our (older) servers we experience some strange behaviour when using phpMyAdmin.

    Every single command (like opening a table, deleting a row, creating a new table, search, etc.) causes the load to spike and the command takes unusally long (up to 5-10 seconds). This is obviously quite a long time to open a table. Mind you the tables we talk about here are not huge or anything.

    Strangely enough the websites on the server all load fine and don't seem to have problems accessing the databases.

    Hope someone can help.

    Cheers
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You can try forcing an update of phpMyAdmin via:

    Code:
    /usr/local/cpanel/bin/updatephpmyadmin --force
    However, you should also check to make sure the database you are working with is not corrupted. Try repairing/optimizing it's tables to see if that helps. Also, do you use any custom configuration values in the /etc/my.cnf file? If so, you may want to temporarily disable them, restart MySQL, and see if the issue persists.

    Thank you.
     
  3. bmett

    bmett Well-Known Member

    Joined:
    Aug 5, 2010
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brisbane, AU
    Hi Michel,

    Thanks for the reply.

    phpMyAdmin is up-to-date (4.0.5).

    I repaired/checked/optimised the tables with no change.

    Please see below the my.cnf file. Maybe you can see anything unusual in there:
    Code:
    [mysqld]
    innodb_buffer_pool_size=20M
    safe-show-database
    local-infile=0
    #datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    skip-innodb
    query_cache_limit=8M
    query_cache_size=8M
    query_cache_type=1
    max_connections=150
    max_user_connections=10
    interactive_timeout=1200
    wait_timeout=1200
    connect_timeout=6
    thread_cache_size=4
    key_buffer=512M
    key_buffer_size=512M
    join_buffer=1M
    max_allowed_packet=100M
    table_cache=2200
    record_buffer=1M
    sort_buffer_size=2M
    read_buffer_size=2M
    read_rnd_buffer_size=64M
    max_connect_errors=10
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency=4
    myisam_sort_buffer_size=64M
    #log-bin
    server-id=1
    log-error=/var/log/mysql/error.log
    log-slow-queries=/var/lib/mysql/slow.log
    tmp_table_size=40M
    
    open_files_limit=5000
    [mysql.server]
    user=mysql
    #basedir=/var/lib
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    log-error=/var/log/mysql/error.log
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    #safe-updates
    
    [isamchk]
    key_buffer=32M
    sort_buffer=32M
    read_buffer=16M
    write_buffer=16M
    
    [myisamchk]
    key_buffer=32M
    sort_buffer=32M
    read_buffer=16M
    write_buffer=16M
    
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    It's difficult to pinpoint the issue to any specific configuration value. I recommend disabling all of them temporarily, and see if the issue persists after restarting MySQL. If the issue is resolved, you can start manually adding the entries back in one at a time to see which one is the culprit.

    Thank you.
     
  5. bmett

    bmett Well-Known Member

    Joined:
    Aug 5, 2010
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brisbane, AU
    Hi Michael,

    I had no luck so far with modifing the my.cnf file.

    I noticed though, that the accounts I work on with phpMyAdmin have a lot of core dump file in them. They obviously result from the use of phpMyAdmin.

    Any idea what that could mean?

    Cheers,
     
    #5 bmett, Sep 15, 2013
    Last edited: Sep 15, 2013
  6. MaestriaNick

    MaestriaNick Well-Known Member

    Joined:
    Aug 6, 2008
    Messages:
    159
    Likes Received:
    3
    Trophy Points:
    18
    It is only with phpmyadmin ? Are you able to open the tables etc directly connecting to the mysql server ? (i mean, connecting to mysql after ssh ) ? In my opinion mysql have nothing to do with those coredump files.
     
  7. cPMatthewM

    cPMatthewM Member
    Staff Member

    Joined:
    Jul 22, 2013
    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Hello,

    I don't quite know whether or not this is a phpmyadmin issue. What I mean is just based by your my.cnf you are using no later than 5.1(MYSQL won't start with a large number of those variables in anything past 5.1). That being said it's more likely you are on an older version of mysql which is also on an old server(old hardware) and that could be causing issues.

    When you run your queries in PMA what exactly is causing the load spike? It would be worth a shot to run top/a command from PMA to see if it's PMA or mysql.

    Also, PMA has a query function within it, you can actually run the same query you would from the mysql prompt in PMA. You may want to run the same query in both and see what the actual difference in time/load is.

    Yours truly,
    phpMattadmin
    P.S.-That nick name does actually get used...unfortunately ;)
     
    Infopro likes this.
Loading...

Share This Page