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 Deadly

Discussion in 'General Discussion' started by jaymc, Sep 7, 2005.

  1. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    As you can MYSQL is eating all the memory of the server, 1.5GB of it

    [​IMG]

    Any suggestions of how to suffice this problem, possible things to try or luck into to minimize ?

    you could say its because it has heavy sites on it

    but at 4am in the mornin, wen their is no more than 15 active people on the site compared to a day when thier is 700, the memory used is still 1.5GB
     
  2. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    What's in /etc/my.cnf?
     
  3. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    #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=4M
    query_cache_size=128M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=500
    max_connections=600
    interactive_timeout=5
    wait_timeout=20
    connect_timeout=20
    thread_cache_size=128
    key_buffer=128M ## 32MB 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=2M ## 1MB for every 1GB of RAM
    read_buffer_size=2M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=2M ## 1MB for every 1GB of RAM
    thread_concurrency=2 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1
    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]
    [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
     
  4. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    OK, well the config is familiar :D

    What do you get with these two commands in the MySQL command line?
    status
    show status like 'Qcache%';
     
  5. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    mysql> status
    --------------
    mysql Ver 14.7 Distrib 4.1.13, for pc-linux-gnu (i686) using readline 4.3

    Connection id: 69352
    SSL: Not in use
    Current pager: stdout
    Using outfile: ''
    Using delimiter: ;
    Server version: 4.1.13-standard
    Protocol version: 10
    Connection: Localhost via UNIX socket
    Client characterset: latin1
    Server characterset: latin1
    UNIX socket: /var/lib/mysql/mysql.sock
    --------------


    show status like 'Qcache%'; = command not found, i ran them in SSH
     
  6. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    The first one didn't show anything like how many slow queries you had or how many questions? And the second command should have worked in the MySQL command line as the one above.

    Also, I looked some more at your top screen. You have virtually 0 swap usage. Is the memory usage actually causing high load issues with your server? If it's not, you should really not worry about MySQL's RAM usage (atleast based on what I see). Linux usually doesn't clear the memory used by a program when it's not needed anymore as it just stays cached (this is another reason I love Linux over Windows :) ). You will probobly see free RAM drop down to under 100MB even if you have 2 or 3GB of RAM. That's because everything is being cached, which is a good thing. And if another program did need the memory, Linux would give up some of the cache'd data and then the program can use that space.
     
  7. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    ahh well that makes more sense then

    i thought i was just dead on luckly that it was basically 200MB aaway from swapping which would send the machine crazy

    with that in mind i suppose their is no real problem

    as a side note, their has been 60k of SWAP for the past 2 days, it hasnt gone up or down, its just set on 60k

    any explination for that ?
     
  8. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    The 60k in swap is pretty much nothing in hindsight, really. I can't tell you for sure what it could be, but most likely a process that hasn't even been accessed since the server's startup or soon after. I wouldn't worry about 60kb. Once swap starts getting in the MB, then I'd start looking at what is using up the RAM and consider if it's a script problem or just time to add more RAM.
     
Loading...

Share This Page