HELP. Mysql overloading

Discussion in 'Site & Server Administration' started by ryuchix, Jun 12, 2012.

  1. #1
    Can someone help me, i have problem with mysql processes. It eating a lot of my CPU and memory. I have 12MB ram Intel XEOn server. The load doesnt go down.

    Please help
     
    Solved! View solution.
    ryuchix, Jun 12, 2012 IP
  2. SolidShellSecurity

    SolidShellSecurity Banned

    Messages:
    262
    Likes Received:
    3
    Best Answers:
    1
    Trophy Points:
    45
    #2
    We need more information such as top, iotop, my.cnf, need to know whats going on with the server and processes.
     
    SolidShellSecurity, Jun 12, 2012 IP
  3. ryuchix

    ryuchix Active Member

    Messages:
    127
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    88
    #3
    df -h

    Filesystem Size Used Avail Use% Mounted on
    /dev/sda3 66G 52G 10G 84% /
    tmpfs 5.9G 0 5.9G 0% /dev/shm
    /dev/sda1 485M 58M 402M 13% /boot


    top

    top - 02:44:21 up 2:18, 2 users, load average: 6.54, 3.42, 24.52
    Tasks: 416 total, 67 running, 346 sleeping, 2 stopped, 1 zombie
    Cpu(s): 93.4%us, 6.3%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
    Mem: 12186620k total, 12052508k used, 134112k free, 14836k buffers
    Swap: 8191992k total, 743928k used, 7448064k free, 5517184k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    15904 mysql 20 0 4141m 99m 3640 S 19.5 0.8 0:05.24 mysqld
    13710 nobody 20 0 269m 49m 14m R 13.6 0.4 19:45.03 httpd
    15367 nobody 20 0 296m 67m 7436 R 13.6 0.6 0:00.59 httpd
    15516 nobody 20 0 296m 67m 7392 R 13.6 0.6 0:00.57 httpd
    15659 nobody 20 0 312m 83m 7440 R 13.6 0.7 0:00.68 httpd
    15933 nobody 20 0 319m 100m 14m R 13.6 0.8 0:01.25 httpd
    15939 nobody 20 0 321m 96m 5804 R 13.6 0.8 0:03.67 httpd
    15962 nobody 20 0 313m 83m 6400 R 13.6 0.7 0:00.63 httpd
    15964 nobody 20 0 316m 87m 5668 R 13.6 0.7 0:00.66 httpd
    15998 nobody 20 0 288m 64m 5820 R 13.6 0.5 0:01.26 httpd
    15624 nobody 20 0 324m 99m 7532 R 13.2 0.8 0:05.13 httpd
    15653 nobody 20 0 297m 71m 6536 R 13.2 0.6 0:01.39 httpd
    15684 nobody 20 0 291m 68m 7780 R 13.2 0.6 0:01.22 httpd
    15748 nobody 20 0 320m 90m 6616 R 13.2 0.8 0:00.72 httpd
    15927 nobody 20 0 318m 89m 5684 R 13.2 0.7 0:00.73 httpd
    15984 nobody 20 0 320m 92m 5744 R 13.2 0.8 0:00.79 httpd
    16009 nobody 20 0 291m 63m 6504 S 13.2 0.5 0:00.94 httpd
    16015 nobody 20 0 287m 64m 5804 R 13.2 0.5 0:03.08 httpd
    15716 nobody 20 0 292m 63m 5724 R 12.9 0.5 0:00.49 httpd
    15739 nobody 20 0 291m 67m 7512 R 12.9 0.6 0:01.25 httpd
    15743 nobody 20 0 289m 65m 5840 R 12.9 0.6 0:01.16 httpd
    15923 nobody 20 0 314m 97m 13m R 12.9 0.8 0:01.14 httpd
    15936 nobody 20 0 297m 73m 5804 R 12.9 0.6 0:04.17 httpd


    The original my.cnf was empty. So i tried googling on how to optimize my mysql and found many threads and copy paste others .my.cnf
    taken from this thread http://forums.cpanel.net/f5/cpu-memory-mysql-usage-48398.html

    my.cnf

    [mysqld]
    safe-show-database
    skip-locking
    skip-innodb
    max_connections = 650
    key_buffer = 16M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 1280
    thread_cache_size = 64
    wait_timeout = 1200
    connect_timeout = 10
    tmp_table_size = 64M
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 32M
    query_cache_type = 1
    query_prealloc_size = 16384
    query_alloc_block_size = 16384

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M

    I dont know how to get iotop.
     
    ryuchix, Jun 12, 2012 IP
  4. SolidShellSecurity

    SolidShellSecurity Banned

    Messages:
    262
    Likes Received:
    3
    Best Answers:
    1
    Trophy Points:
    45
    #4
    Increase Key buffer to 512MB 16MB is way to low.

    Give the following a try:
    mysqltuner.pl
    www.day32.com/MySQL/

    That should help you with my.cnf tuning.

    How many CPUs? The MySQL service does not look to be your problem its only at 19% CPU from the top. However, Apache looks to be overloading the system more. What is your apache/php configuration and settings?

    The I/O looks like it might be coming from SWAPiness which is probably from Apache using up all the RAM.
     
    SolidShellSecurity, Jun 12, 2012 IP
  5. linux7802

    linux7802 Active Member

    Messages:
    110
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #5
    As you mentioned in the subject "Mysql overloading" therefore I would like to recommend you, do not make any changes in the my.cnf without checking servers available resources, use the "MySQLTuner" script it will provide you the correct settings for your server.
     
    linux7802, Jun 13, 2012 IP
  6. ryuchix

    ryuchix Active Member

    Messages:
    127
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    88
    #6
    Thank you guys for the replies. Looks like i fixed it by installing litespeed. Everything is stable now


    top - 18:59:27 up 18:33, 1 user, load average: 1.22, 0.77, 0.63
    Tasks: 227 total, 3 running, 224 sleeping, 0 stopped, 0 zombie
    Cpu(s): 11.4%us, 0.9%sy, 0.0%ni, 87.6%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st
    Mem: 12186620k total, 3343272k used, 8843348k free, 45144k buffers
    Swap: 8191992k total, 105100k used, 8086892k free, 2410684k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    6884 panime 20 0 138m 46m 23m S 21.6 0.4 0:16.45 lsphp5
    6994 panime 20 0 139m 47m 23m S 14.6 0.4 0:03.77 lsphp5
    6993 panime 20 0 139m 47m 23m S 14.3 0.4 0:05.16 lsphp5
    6998 fave 20 0 136m 44m 22m S 13.3 0.4 0:01.43 lsphp5
    6982 biker 20 0 133m 42m 22m R 11.0 0.4 0:01.46 lsphp5
    6995 fave 20 0 136m 44m 22m S 8.3 0.4 0:01.82 lsphp5
    6883 panime 20 0 138m 46m 23m S 7.3 0.4 0:11.42 lsphp5
    19891 mysql 20 0 1875m 175m 4228 S 5.0 1.5 28:59.99 mysqld
    6976 panime 20 0 139m 47m 23m S 1.7 0.4 0:09.56 lsphp5
    2323 nobody 1 -19 129m 20m 5764 S 1.0 0.2 0:54.86 litespeed
    2324 nobody 1 -19 49888 5360 516 S 0.3 0.0 0:02.64 litespeed
    7000 fave 20 0 136m 44m 22m S 0.3 0.4 0:02.25 lsphp5
    1 root 20 0 19328 812 588 S 0.0 0.0 0:01.47 init
    2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd
    3 root RT 0 0 0 0 S 0.0 0.0 0:06.72 migration/0
    4 root 20 0 0 0 0 S 0.0 0.0 0:03.30 ksoftirqd/0
    5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0


    I enable memcache, eaccelator but the problem is usually the cached is eating my memory slowly, is it normal?

    i had to flushed it every time my free memory reaches 2MB. So i have to monitor it every 2 hours. Can cron job do that automatically?
    How to do that?
     
    ryuchix, Jun 13, 2012 IP
  7. #7
    Linux by default will use up all the memory it can possible to enhance performance. If you think those two service are eating up too much ram you can tweak their values.
     
    SolidShellSecurity, Jun 13, 2012 IP
  8. xengine

    xengine Greenhorn

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #8
    I use php script on the server to periodically stop processes which have been running over 1 hour, something likes:

    <?php
    $queryProcessTimeLimit = 3600;

    require_once 'config.php';
    ini_set('display_startup_errors', 1);
    ini_set('display_errors', 1);
    require_once FAST_PATH . '/Db.php';
    $db = Fast_Db::getInstance();

    $sql = 'SHOW FULL PROCESSLIST';
    $processes = $db->fetchAll($sql);

    foreach($processes as $process) {
    if ($process['Time'] > $queryProcessTimeLimit ) {
    $processId = $process['Id'];
    $sql = 'KILL ' . $processId;
    $db->exec($sql);
    }
    }
    ?>

    Fast_Db is a PHP class, you can replace it with your favorite lib.

    If you need cron job service, try easycron.com.
     
    xengine, Jul 22, 2012 IP
  9. linux7802

    linux7802 Active Member

    Messages:
    110
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #9
    I would like to recommend you please do not use the "memcache, eaccelator" on the same server, use any one and please copy and paste the output of following command.

    free -m
     
    linux7802, Jul 23, 2012 IP