SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The RAM problem about MySql

    I currently run a Mysql forum on my dedicate server.
    The forum is just like this forum.
    My server has 1gb Ram.
    Via the top command under the unix system, I found that the mysql always users lots of memory.
    The usage of the Ram is always between 600-800mb.(At those time 100-200 people are online at sametime.)
    I think this is not normal.

    Could anyone can help me? and help me solve this problem?
    Thank you!

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, that's a bit high. you probably need some things adjusted in your configuration options. post either the contents of your my.cnf file (i think i was helping you with your connections before? ), the output from SHOW VARIABLES, or the output from `mysqladmin variables'. 1 of those 3 so i can see the current settings. also, post your MySQL status from SHOW STATUS or `mysqladmin extended-status', preferably after MySQL has been running for at least a few hours. then i can hopefully give you some better settings to reduce MySQL's memory usage.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I pm you. did you see my message?

  4. #4
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I used the file you give me, but
    The memory usage is s very large right now. around 900mb(220 people on line).
    Please Help.

    Thank you

  5. #5
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DR_LaRRY_PEpPeR,
    I find another problem is that my server can not stop the mysql service.

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't see a problem with it if it is a dedicated MySQL box and you aren't having swapping problems. In Sybase ASE, for instance, I explicitly tell it how much ram to use (2.2GB). I don't think there is an analagous control stucture in MySQL to do this, but I'd say as long as it is a dedicated box and you aren't swapping or having other low-memory issues let MySQL cache all that it wants to. Remember that to be most efficient you need to cache as much disk-based data as you can.

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm just posting the modified my.cnf i gave you here. his key_buffer was 256M! also lowered max_connections from 500. log-bin was turned on. i can't remember what that does exactly. i think it may log a lot of unnecessary stuff, which would increase disk access (maybe RAM usage too?).

    Code:
    [client]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    
    [mysqld]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    skip-locking
    set-variable = max_connections=400
    set-variable = key_buffer=16M
    set-variable = max_allowed_packet=1M
    set-variable = myisam_sort_buffer_size=64M
    set-variable = join_buffer=1M
    set-variable = record_buffer=1M
    set-variable = sort_buffer=2M
    set-variable = table_cache=1024
    set-variable = thread_cache=64
    set-variable = wait_timeout=3600
    
    [mysqldump] 
    quick 
    set-variable = max_allowed_packet=16M 
    
    [mysql] 
    no-auto-rehash
    
    [mysqlcheck]
    set-variable = key_buffer=128M
    set-variable = sort_buffer=128M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M
    
    [myisamchk]
    set-variable = key_buffer=128M
    set-variable = sort_buffer=128M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout

    it's weird that the memory usage is still so high. DID you restart MySQL after the new file, or weren't you able to? if it hasn't been restarted, then you're not using the new settings. i thought the memory usage should be down below 200M or so, from what i've seen, depending on how many threads (connections) are running.

    like MattR said, what's your swap usage like in top? is this a dedicated MySQL server? i assumed the Web server was on it too.

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    p.s. also how big are the databases + indexes? If you have a multi-GB DB on a dedicated box then using all the ram is a GOOD THING. If you have a 20MB DB then we have a problem.

  9. #9
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks you guys.
    I reboot my server yesterday after modify the my.cnf. My mysql service can not stop. it always said
    Stoppint: [Failed]
    Restarting: [Success]
    I have to reboot the server to make the change to take effect.
    After that, the usage is really good(around 200mb),
    But this morning i checked back, the usage was back to 800-900mb. In Swap, just very few memory used, like 230kb

  10. #10
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if your swap usage stays low like that, then i'd say there's nothing to worry about. again i ask, is this your Web server too or JUST a MySQL server?

  11. #11
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes,This is my web server.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •