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 memory use

Discussion in 'General Discussion' started by S-Combs, Jan 26, 2006.

  1. S-Combs

    S-Combs Well-Known Member

    Joined:
    Jun 10, 2004
    Messages:
    78
    Likes Received:
    0
    Trophy Points:
    6
    Mysql memory usage

    I have been through many other posts and haven't found anything that works so far.

    Mysql memory use is out of control on my box and I really don't know what else to try..

    Whenever restarting MYSQL the %mem is around 1 per process

    That % starts growing and gradually increases each day to a point the server grinds to a halt when it reaches around 7 (5-6 days later).. I then need to restart MYSQL again..

    I broke down yesterday and upgraded almost everything but now it seems to require even more memory per process (running at 5.6 already after only 12 hours)..

    I used this tool this time for the upgrading and everything seemed to go fine except ZEND is no longer working (I'll worry about that later). http://forums.cpanel.net/showpost.php?p=202971&postcount=3


    The server only has 10 domains (all of which my own). On one domain we have an updated vbulletin forum but it rarely has more than 25 members on at once.


    Any help with this would be greatly appreciated..


    Here is what I'm running

    P4 3.2
    1GB PC3200
    2 x 160GB SATA Drives

    CentOS 3.6
    cPanel 10.8.1-E111

    PHP 5.1.2
    './configure' '--with-apxs=/usr/local/apache/bin/apxs' '--prefix=/usr/local' '--with-xml' '--enable-bcmath' '--enable-calendar' '--with-curl' '--enable-ftp' '--with-mhash' '--enable-magic-quotes' '--with-mysqli' '--with-mysql=/usr' '--enable-discard-path' '--with-pear' '--enable-sockets' '--enable-track-vars' '--enable-versioning' '--with-zlib'

    Apache/1.3.34 (Unix) PHP/5.1.2 mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 FrontPage/5.0.2.2635 mod_ssl/2.8.25 OpenSSL/0.9.7a

    MYSQL 4.1.14




    Other Info
    ____________________________________________

    PS AUX
    ____________________________________________

    Mysql

    Code:
    mysql    19876  0.1  5.6 112748 57600 ?      S    Jan25   1:41 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql    29500  0.1  5.6 112748 57600 ?      S    Jan25   1:17 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql     6960  0.1  5.6 112748 57600 ?      S    Jan25   0:56 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql     6962  0.1  5.6 112748 57600 ?      S    Jan25   0:45 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql     6963  0.0  5.6 112748 57600 ?      S    Jan25   0:39 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql    16876  0.0  5.6 112748 57600 ?      S    Jan25   0:19 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/

    Apache

    Code:
    nobody   17562  2.2  1.6 24988 17304 ?       S    Jan25  22:19 /usr/local/apache/bin/httpd -DSSL
    nobody   17563  2.2  1.6 24708 17020 ?       S    Jan25  22:15 /usr/local/apache/bin/httpd -DSSL
    nobody   17564  2.4  1.7 26152 18300 ?       S    Jan25  24:41 /usr/local/apache/bin/httpd -DSSL
    nobody   17565  2.2  1.7 26112 18416 ?       S    Jan25  22:58 /usr/local/apache/bin/httpd -DSSL
    nobody   17566  2.1  1.7 25560 17988 ?       S    Jan25  21:17 /usr/local/apache/bin/httpd -DSSL
    nobody   17877  2.2  1.6 24728 17028 ?       S    Jan25  22:49 /usr/local/apache/bin/httpd -DSSL
    nobody   17878  1.9  1.7 25232 17520 ?       S    Jan25  19:57 /usr/local/apache/bin/httpd -DSSL
    nobody   17879  2.2  1.7 25240 17472 ?       S    Jan25  22:12 /usr/local/apache/bin/httpd -DSSL
    nobody   17880  1.8  1.6 24728 17132 ?       S    Jan25  18:43 /usr/local/apache/bin/httpd -DSSL
    nobody   17881  2.5  1.6 24748 17012 ?       S    Jan25  25:43 /usr/local/apache/bin/httpd -DSSL

    (Just noticed that Apache is using more memory and cpu than before the upgrade also)


    ____________________________________________

    Top
    ____________________________________________


    Code:
     02:08:24  up 17:48,  2 users,  load average: 0.47, 0.35, 0.29
    145 processes: 143 sleeping, 2 running, 0 zombie, 0 stopped
    CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
               total   50.6%    0.0%    0.5%   0.0%     0.0%    0.0%   48.6%
               cpu00   34.2%    0.0%    0.3%   0.1%     0.1%    0.0%   65.0%
               cpu01   67.0%    0.0%    0.7%   0.0%     0.0%    0.0%   32.2%
    Mem:  1025348k av, 1004560k used,   20788k free,       0k shrd,   29920k buff
           387140k active,             587692k inactive
    Swap: 2096440k av,       0k used, 2096440k free                  682040k cached
    
      PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
    17563 nobody    15   0 24708  16M  3920 S    31.7  1.6  22:26   0 httpd
    17562 nobody    16   0 26208  17M  3924 R    11.6  1.7  22:22   1 httpd
    15791 root      17   0  5324 1224   920 R     3.0  0.1   0:08   0 top
    15385 root      17   0  5324 1228   920 S     2.8  0.1   0:25   1 top
    29500 mysql     16   0  110M  56M  2060 S     0.1  5.6   1:17   1 mysqld
    16876 mysql     15   0  110M  56M  2060 S     0.1  5.6   0:19   0 mysqld
        1 root      16   0  1620  540   472 S     0.0  0.0   0:01   0 init
        2 root      RT   0     0    0     0 SW    0.0  0.0   0:00   0 migration/0
        3 root      34  19     0    0     0 SWN   0.0  0.0   0:00   0 ksoftirqd/0
        4 root      RT   0     0    0     0 SW    0.0  0.0   0:00   1 migration/1
        5 root      34  19     0    0     0 SWN   0.0  0.0   0:00   1 ksoftirqd/1
        6 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   0 events/0
        7 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   1 events/1
        8 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   0 khelper
       13 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   1 kthread
       22 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   1 kacpid
      104 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   0 kblockd/0
      105 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   1 kblockd/1
      118 root      15   0     0    0     0 SW    0.0  0.0   0:00   1 khubd
      191 root      20   0     0    0     0 SW    0.0  0.0   0:00   0 pdflush
      192 root      15   0     0    0     0 SW    0.0  0.0   0:01   0 pdflush
      193 root      15   0     0    0     0 SW    0.0  0.0   0:09   1 kswapd0
      194 root      11  -5     0    0     0 SW<   0.0  0.0   0:00   0 aio/0
      195 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   1 aio/1
      784 root      16   0     0    0     0 SW    0.0  0.0   0:00   0 kseriod
      860 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   0 ata/0
      861 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   1 ata/1
      863 root      16   0     0    0     0 SW    0.0  0.0   0:00   1 scsi_eh_0
      864 root      16   0     0    0     0 SW    0.0  0.0   0:00   0 scsi_eh_1
      889 root      16   0     0    0     0 SW    0.0  0.0   0:00   0 khpsbpkt
      922 root      15   0     0    0     0 SW    0.0  0.0   0:00   1 kirqd


    _______________________________________

    MY.cnf
    _______________________________________


    DO NOT MODIFY THE FOLLOWING COMMENTED LINES!
    #Created with ELS from www.nsonetworks.com
    #els-build=4.1
    [mysqld]
    datadir=/var/lib/mysql
    skip-locking
    skip-innodb
    skip-networking
    safe-show-database
    query_cache_limit=1M
    query_cache_size=32M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=200
    max_connections=500
    interactive_timeout=10
    wait_timeout=20
    connect_timeout=20
    thread_cache_size=128
    key_buffer=64M ## 64MB for every 1GB of RAM
    join_buffer=1M
    max_connect_errors=20
    max_allowed_packet=16M
    table_cache=1024
    record_buffer=1M
    sort_buffer_size=1M ## 1MB for every 1GB of RAM
    read_buffer_size=1M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
    thread_concurrency=4 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1
    log_slow_queries=/var/log/mysql-slow-queries.log
    long_query_time=2
    collation-server=latin1_general_ci
    old-passwords

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192

    [mysqldump]
    quick
    max_allowed_packet=16M

    [mysql]
    no-auto-rehash
    #safe-updates

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

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

    [mysqlhotcopy]
    interactive-timeout



    ______________________________________

    PHPinfo
    _______________________________________


    http://www.cyberlnc.com/phpinfo.php
     
    #1 S-Combs, Jan 26, 2006
    Last edited: Jan 30, 2006
  2. S-Combs

    S-Combs Well-Known Member

    Joined:
    Jun 10, 2004
    Messages:
    78
    Likes Received:
    0
    Trophy Points:
    6
    Mysql demands seem to be getting much worse now after upgrading. It is now the highest I've seen so far 9.4


    Code:
    mysql    19876  0.1  9.4 114980 96692 ?      S    Jan25   2:09 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql    29500  0.1  9.4 114980 96692 ?      S    Jan25   2:23 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql     6960  0.1  9.4 114980 96692 ?      S    Jan25   1:13 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql     6962  0.0  9.4 114980 96692 ?      S    Jan25   1:03 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql     6963  0.0  9.4 114980 96692 ?      S    Jan25   1:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql    16876  0.0  9.4 114980 96692 ?      S    Jan25   0:51 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/

    After restarting mysql it's back down to 1.2 again

    Code:
    mysql    13597  0.3  1.2 106624 12492 ?      S    10:35   0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql    13604  0.0  1.2 106624 12492 ?      S    10:35   0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql    13605  0.0  1.2 106624 12492 ?      S    10:35   0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql    13613  1.2  1.2 106624 12492 ?      S    10:35   0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
    mysql    13652  0.0  1.2 106624 12492 ?      S    10:35   0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/
     
  3. CliraNET

    CliraNET Registered

    Joined:
    Mar 27, 2004
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    I've exactly the same problem, I've got 6 servers:
    4 Celeron 2.4GHz with 1 Gb of memory
    2 Pentium 3.2 GHz HT with 1 Gb of memory

    I've updated all servers this week:
    PHP 4.3.11 => PHP 4.4.2
    PHP 5.0.5 => 5.1.2 (PHP5 is compiled in CGI mod)

    MySQL 4.0.25 => MySQL 4.1.14

    The memory is OK but the CPU has gone from about 0.25 to 2 or 3...

    (sorry for my bad english but I'm french ;) )
     
  4. S-Combs

    S-Combs Well-Known Member

    Joined:
    Jun 10, 2004
    Messages:
    78
    Likes Received:
    0
    Trophy Points:
    6
    No ideas anyone?

    I've had to restart mysql 3 times since my last reply

    I can't understand why the memory demands for mysql grow each hour/day until the server chokes with only 15 to 20 mysql connections. Also; Is it normal for each process to consume the same exact amount of memory?

    I'm tempted to try downgrading again to see if at least the memory draw drops down to where it was before but I'm scared of what might happen.

    I'm really in a bind with this thing and would appreciate any advice you might have.

    Thanks
     
  5. bigjohntoday

    bigjohntoday Registered

    Joined:
    Mar 19, 2004
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    I have noticed the exact same issue with my server and I am running DUAL XEON PROCCESSORS at 3.2 GHZ HT each and 2 GIGs of RAM

    MySQL USAGE IS THROUGH THE ROOF ..... MY SERVER CHOKES EVERY WEEK IF I FORGET TO RESTART MySQL ..... if someone knows an answer to this that would be awesome thnx
     
Loading...

Share This Page