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.

skip-external-locking

Discussion in 'Database Discussions' started by refposition, Mar 8, 2011.

  1. refposition

    refposition Member

    Joined:
    Mar 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    Why this process takes 86 % of CPU ?

    is it normal ?

    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/mysite.mysite.com.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock

    i have WHM 11.28.83
    CENTOS 5.3 i686 standard on cpu

    please advise.
     
  2. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hey,

    Mysql is taking large resource in your server. You need to optimize mysql in the server. Execute mysqladmin -pr to check which database is consuming high resource in the server.
     
    #2 LinuxTechie, Mar 8, 2011
    Last edited: Mar 8, 2011
  3. refposition

    refposition Member

    Joined:
    Mar 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    how should i optimize mysql ?

    # wget mysqltuner.pl
    # perl mysqltuner.pl

    ???
     
  4. refposition

    refposition Member

    Joined:
    Mar 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    with this command
    mysqladmin -pr
    i can see only general info
    such as

    root@cpu [/]# mysqladmin -pr
    mysqladmin Ver 8.41 Distrib 5.0.91, for pc-linux-gnu on i686
    Copyright (C) 2000-2006 MySQL AB
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL license

    Administration program for the mysqld daemon.
    Usage: mysqladmin [OPTIONS] command command....
    -c, --count=# Number of iterations to make. This works with -i
    (--sleep) only.
    -#, --debug[=name] Output debug log. Often this is 'd:t:eek:,filename'.
    -f, --force Don't ask for confirmation on drop database; with
    multiple commands, continue even if an error occurs.
    -C, --compress Use compression in server/client protocol.
    --character-sets-dir=name
    Directory where character sets are.
     
  5. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
  6. refposition

    refposition Member

    Joined:
    Mar 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    thank you
    i have also tried
    watch 'mysqladmin processlist'
    but is just a table with changing info
     
  7. refposition

    refposition Member

    Joined:
    Mar 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    is this normal that i see nothing when i enter mysql and make mysqladmin -pr

    mysql> mysqladmin -pr
     
  8. refposition

    refposition Member

    Joined:
    Mar 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    is there any tutorial how to find which database is causing a problem ?

    i do not see any help in WHM - SQL Services >> Show MySQL Processes
     
  9. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hello,

    You need to check mysql processes when cpu utilization gets high. If there are no mysql processing the process list will be empty.
     
  10. refposition

    refposition Member

    Joined:
    Mar 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    yes sure i check it when i have a problem - cpu load 157 %
    /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/cp.now.com.pid --skip-external-locking --port3306 --socket/var/lib/mysql/mysql.sock
     
  11. refposition

    refposition Member

    Joined:
    Mar 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    one more question related to - skip-external-locking
    here in docs
    MySQL :: MySQL 5.0 Reference Manual :: 7.7.4 External Locking
    i have read that
    External locking is unnecessary if you run a single server to access a given data directory (which is the usual case)
    and
    From MySQL 4.0 on, external locking is disabled by default.

    so why it is enable by WHM on my server ?
    do you have it enabled too ?
     
  12. 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
    skip-locking or skip-external-locking is the default for MySQL and has nothing to do with high CPU usage whatsoever. It is only appearing in the command list because the command shows the startup MySQL runtime processes. I've seen other people believe the error log is the issue because it likewise appears in the process when MySQL starts up.

    The issue here is not any runtime configuration component that has no actual high impact on MySQL (such as skip-external-locking or the error logging). You would want to follow the directions in that previously linked thread to run mysqltuner.pl and tweak the variables you have in /etc/my.cnf file:

    http://forums.cpanel.net/f189/need-help-mysql-optimization-189271.html#post774971

    If that doesn't work to sufficiently reduce load, then follow the video tutorial that I did that was also linked in that thread.

    Of note, MySQL will always have high CPU usage from what I've seen on any server that has any amount of activity. It isn't CPU usage that will be the factor in high load but high memory usage with MySQL and high processing, so does the machine actually have high load or high latency? If not, then to be absolutely direct and to the point, why is there a concern about the process?
     
Loading...

Share This Page