The RAM problem about MySql

[ttz]

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!

[DR_LaRRY_PEpPeR]

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.

[ttz]

I pm you. did you see my message?

[ttz]

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

[ttz]

DR_LaRRY_PEpPeR,
I find another problem is that my server can not stop the mysql service.

[MattR]

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.

[DR_LaRRY_PEpPeR]

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?).

[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.

[MattR]

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.

[ttz]

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

[DR_LaRRY_PEpPeR]

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?

[ttz]

yes,This is my web server.