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.

MySQL Panicing?

Discussion in 'General Discussion' started by WestBend, Feb 12, 2006.

  1. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    I have two RHEL 3 Servers. Both with CPanel Latest Release
    Both are Dual AMD MP 2600 servers with 2Gbs of Ram.
    Dual Drive Systems with the MYSQL data directory loaded on the second disk.

    Here is a snap shot of the top

    Both seem to have issues with the mysql and when you strace the process that consumes vasts aomount of resources even when the servers are idling.. the following gets dumped out :

    I have tried googling all over and nothing seems to fix this. i have tried adding skip-name-resolve in the my.cnf to no avail.

    I am running mysql 4.0.25 - Standard.

    Any help would be GREATLY appreciated!

     
  2. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    Another snapshot of top :

     
  3. ShockHosts

    ShockHosts Well-Known Member

    Joined:
    Nov 25, 2005
    Messages:
    123
    Likes Received:
    0
    Trophy Points:
    16
    Hire an experienced tech :P
     
  4. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    Already been that route.. apparently hes stumped too..
     
  5. neonix

    neonix Well-Known Member

    Joined:
    Oct 21, 2004
    Messages:
    124
    Likes Received:
    2
    Trophy Points:
    0
    Since you have 2 Gb Ram; try tweaking your my.cnf file; here's the one that I use...

    [mysqld]
    max_connections = 500
    safe-show-database
    key_buffer = 512M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 2M
    read_buffer_size = 2M
    sort_buffer_size = 3M
    table_cache = 512
    thread_cache_size = 128
    wait_timeout = 10
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 32M
    query_cache_type = 1
    skip-innodb

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [isamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M

    [myisamchk]
    key_buffer = 256M
    sort_buffer = 256M
    read_buffer = 16M
    write_buffer = 16M
     
  6. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    thanks for the reply neonix unfortunately I don't think its my.cnf file that is causing the slowdown its this file somewhere that is constantly hammering against mysql (looks like) and right now I have no way to track it down since the error doesent reference whats launching it.. and i cant seem to see it in top..

    Soo i am trying by stopping service after service till i hit one that stops it..
     
  7. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    The problem seems to be caused anytime you connect to the mysql server or is this not a problem.. certainly seems like it though.

    root@bubba [~]# strace -p 10382
    Process 10382 attached - interrupt to quit
    select(5, [3 4], NULL, NULL, NULL
    ===================== RUN COMMAND mysql =========================
    The rest appears :
    ================================================== ===============
    ) = 1 (in [4])
    fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    accept(4, {sa_family=AF_FILE, path="ÿ¿I‰"}, [2]) = 11
    fcntl64(4, F_SETFL, O_RDWR) = 0
    getsockname(11, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
    fcntl64(11, F_GETFL) = 0x2 (flags O_RDWR)
    fcntl64(11, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    setsockopt(11, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
    time(NULL) = 1139946685
    rt_sigprocmask(SIG_SETMASK, NULL, [HUP INT QUIT PIPE TERM TSTP RTMIN], 8) = 0
    write(8, "\0\343=\10\0\0\0\0 R?\10p\301\7\10\10>z\10\7P\10\200\0"..., 148) = 148
    rt_sigprocmask(SIG_SETMASK, NULL, [HUP INT QUIT PIPE TERM TSTP RTMIN], 8) = 0
    rt_sigsuspend([HUP INT QUIT PIPE TERM TSTP] <unfinished ...>
    --- SIGRTMIN (Unknown signal 32) @ 0 (0) ---
    <... rt_sigsuspend resumed> ) = -1 EINTR (Interrupted system call)
    sigreturn() = ? (mask now [HUP INT QUIT PIPE TERM TSTP RTMIN])
    select(5, [3 4], NULL, NULL, NULL
    ===== RUN COMMAND within MYSQL: connect mysql =========================
    The rest appears :
    ============================
    = 1 (in [4])
    fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    accept(4, {sa_family=AF_FILE, path="ÿ¿I‰"}, [2]) = 11
    fcntl64(4, F_SETFL, O_RDWR) = 0
    getsockname(11, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
    fcntl64(11, F_GETFL) = 0x2 (flags O_RDWR)
    fcntl64(11, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    setsockopt(11, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
    time(NULL) = 1139946753
    rt_sigprocmask(SIG_SETMASK, NULL, [HUP INT QUIT PIPE TERM TSTP RTMIN], 8) = 0
    write(8, "\0\343=\10\0\0\0\0 R?\10p\301\7\10\10>z\10\7P\10\200\0"..., 148) = 148
    rt_sigprocmask(SIG_SETMASK, NULL, [HUP INT QUIT PIPE TERM TSTP RTMIN], 8) = 0
    rt_sigsuspend([HUP INT QUIT PIPE TERM TSTP] <unfinished ...>
    --- SIGRTMIN (Unknown signal 32) @ 0 (0) ---
    <... rt_sigsuspend resumed> ) = -1 EINTR (Interrupted system call)
    sigreturn() = ? (mask now [HUP INT QUIT PIPE TERM TSTP RTMIN])
    select(5, [3 4], NULL, NULL, NULL
     
  8. xixi

    xixi Active Member

    Joined:
    Mar 11, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    6
    I have the similar problem mysql is eating up almost whole server resources and doing strace on its pid get the same results:

    root@ [~]# strace -p 3826
    Process 3826 attached - interrupt to quit
    select(5, [3 4], NULL, NULL, NULL) = 1 (in [4])
    fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    accept(4, {sa_family=AF_FILE, path="¸SC\j"}, [2]) = 12
    fcntl64(4, F_SETFL, O_RDWR) = 0
    getsockname(12, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
    fcntl64(12, F_GETFL) = 0x2 (flags O_RDWR)
    fcntl64(12, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    setsockopt(12, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
    kill(3852, SIGRTMIN) = 0
    kill(3852, SIGRTMIN) = 0
    select(5, [3 4], NULL, NULL, NULL) = 1 (in [4])
    fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    accept(4, {sa_family=AF_FILE, path="¸SC\j"}, [2]) = 340
    fcntl64(4, F_SETFL, O_RDWR) = 0
    getsockname(340, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
    fcntl64(340, F_GETFL) = 0x2 (flags O_RDWR)
    fcntl64(340, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    setsockopt(340, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
    kill(4789, SIGRTMIN) = 0
    kill(4789, SIGRTMIN) = 0
    select(5, [3 4], NULL, NULL, NULL) = 2 (in [3 4])
    fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    accept(4, {sa_family=AF_FILE, path="¸SC\j"}, [2]) = 382
    fcntl64(4, F_SETFL, O_RDWR) = 0
    getsockname(382, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
    fcntl64(382, F_GETFL) = 0x2 (flags O_RDWR)
    fcntl64(382, F_SETFL, O_RDWR|O_NONBLOCK) = 0
    setsockopt(382, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)

    Any suggestions how to check what exactly is responsible for that error ?
     
  9. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    Where did you get your servers from? I got mine from serverbeach
     
  10. xixi

    xixi Active Member

    Joined:
    Mar 11, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    6
    From Servermatrix but it shouldnt have any influence.
     
  11. vivek

    vivek Well-Known Member

    Joined:
    Mar 2, 2004
    Messages:
    93
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    It is also possible that mysql queries written in the script may take time to execute, you can find those queries by this :

    To do this
    Edit /etc/init.d/mysql

    $bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file >/dev/null 2>&1 &

    Add --log-slow-queries=/var/log/slow-queries.log to above line as below.


    $bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file --log-slow-queries=/var/log/slow-queries.log
    >/dev/null 2>&1 &

    Let me know if that works.

    N'Joy :D
     
  12. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    Well it makes a difference since each NOC creates their own images..

    As for slow queries, if you look at the second post after the first thread starter i am not actually executing queries i am simply connecting to mysql from the prompt and getting the errors. I wish it was linked to slow queries :)

    Thanks for replying though, i am having a devil of a time trying to get anyone to run comparisons on their own servers to find out if it was standard output.. :)

    I now have 4 servers all showing the same exact thing.

    2 Dual proc AMD : RHEL 3
    1 Single Proc : RHEL 4
    1 Dual Core : RHEL 4

    All from the same company with their loaded images.

    All do the same thing. Straight after receiving the two rhel 4's before doing anything else i tested them machines for the same issue. trace a pid.. type mysql to connect and voila output shows the "operation not supported"
     
    #12 WestBend, Feb 16, 2006
    Last edited: Feb 16, 2006
  13. abubin

    abubin Well-Known Member

    Joined:
    Dec 7, 2004
    Messages:
    393
    Likes Received:
    1
    Trophy Points:
    18
    i am also having similar problems....someone please help. This happen to both of my servers. One server is only running one domain with ecommerce scripts. Didn't really do much external scripts changes to these servers...only cpanel upgrades.

    Anyone have any idea what's with this EOPNOTSUPP problem?
     
  14. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    I did find that if i run from the command line : mysql -h 127.0.0.1 and force a tcp/ip connect that the EOP error vanishes and I am just left with the Interrupted System Call (error?).

    There should be the following exactly in /var/lib/mysql :
    srwxrwxrwx 1 mysql mysql 0 Feb 15 17:21 mysql.sock=

    There should be the following exactly in /tmp :
    lrwxrwxrwx 1 root root 30 Feb 16 14:57 mysql.sock -> ../../var/lib/mysql/mysql.sock=
     
    #14 WestBend, Feb 17, 2006
    Last edited: Feb 17, 2006
  15. xixi

    xixi Active Member

    Joined:
    Mar 11, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    6
    What you exactly mean by "force tcp/ip connect ?
    Can you give me exactly command you enter in mysql>
     
  16. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    forcing a tcp/ip connection instead of a unix socket connection

    when you specifiy localhost as the server to connect to the client will automatically use a unix socket connection. if you put 127.0.0.1 (loopback) ip then it will connect via TCP/IP and not a unix socket.

    eg : mysql -h 127.0.0.1

    -h is to specify host/hostname
     
  17. xixi

    xixi Active Member

    Joined:
    Mar 11, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    6
    Okay, i see but it doesnt resolve problem on production server which i have because it generates connections via socket. So it should be some problem which is causing it and question is how to fix it ?
     
  18. WestBend

    WestBend Well-Known Member

    Joined:
    Oct 12, 2003
    Messages:
    173
    Likes Received:
    0
    Trophy Points:
    16
    Well if you put 127.0.0.1 in your scripts instead of localhost then the scripts should use a TCP/IP connection and not sockets.
     
  19. abubin

    abubin Well-Known Member

    Joined:
    Dec 7, 2004
    Messages:
    393
    Likes Received:
    1
    Trophy Points:
    18
    connecting through tcp/ip still doesn't solve the problem. I am also wondering why this problem is not as widespread as it should be. I mean, both my servers are as vanilla as it gets because I am very particular about installing external scripts or software but still have this problem.
     
  20. Brad

    Brad Well-Known Member

    Joined:
    Aug 16, 2001
    Messages:
    231
    Likes Received:
    0
    Trophy Points:
    16
    Having same problems here too ..
     
Loading...

Share This Page