Hi.
On Tue, Feb 06, 2001 at 10:05:07AM +0800, [EMAIL PROTECTED] wrote:
> why mysql eat my memory over 1G?Please help me!Thank you!!!
> --------------------------------------------------------------------------------
> server1:DELL6300,4cpu,4G memory.solaris5.7 for X86 .
> mysql start command:nohup /usr/local/bin/safe_mysqld -O key_buffer=128M -O
>table_cache=512 -O sort_buffer=128M -O record_buffer=128M -O max_connections=999 -O
>wait_timeout=5000 &
You can get some info about MySQL's usage by "mysqladmin
extended-status". My comments are a bit vague, because all depends on
the database size and usage.
key_buffer is okay, but may be larger for a machine with 4GB,
depending on your database size. If Key_blocks_used*1024 about 128MB,
you may want to increase it. If it is far lower, decrease
it. Key_read_requests/Key_reads should be a large number (for my
system it is about 500). Btw, this is all documented in the manual,
too.
table_cache: have a look at open_tables. If it is far lower than 512,
you may want to decrease table_cache accordingly.
You know, sort_buffer is used on a per-connection basis? I.e. if you
have 10 connections at a time which need to use a sort buffer,
10*128=1280MB are in use. That probably explains why MySQL uses so
much memory: You have told it to do so. You should probably decrease
this value a lot (depending on the number of concurrent connections
you have to the database... how about 32MB).
record_buffer: also on a per-connection basis. Each connection which
has to do a full scan (i.e. cannot use indexes) will allocate this
memory. Well, idially all your queries use indexes, aren't they? Well,
you problably don't want to have it such large.
The manual suggest the following values for a server with at least
256MB, only running MySQL and a _moderate_ number of clients at the
same time, configured for maximum speed (at expense of memory usage):
key_buffer=64M table_cache=256 sort_buffer=4M record_buffer=1M
Simply multiplying for 4GB gives:
key_buffer=1024M table_cache=4096 sort_buffer=64M record_buffer=16M
Of course, key_buffer and table_cache are a bit high (I wouldn't set
them blindly to these values, but tune the parameters on the usage
shown by extended-status), but you see, that sort_buffer is below
and record_buffer is far below of what you specified.
> use "top" show:
> --------------------------------------------------------------------------------
> last pid: 26041; load averages: 6.89, 9.39, 10.01 13:27:36
> 64 processes: 59 sleeping, 2 running, 3 on cpu
> CPU states: 0.8% idle, 68.1% user, 31.1% kernel, 0.0% iowait, 0.0% swap
> Memory: 4032M real, 557M free, 1787M swap in use, 3602M swap free
>
> PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
> 2838 root 147 20 0 1432M 1317M cpu1 62.1H 53.15% mysqld
> 3057 publish 1 5 2 18M 12M sleep 67:32 2.08% perl
> 23734 publish 1 22 0 17M 14M run 0:32 5.10% httpd
> 16703 publish 1 58 0 17M 11M sleep 0:38 0.00% httpd
> --------------------------------------------------------------------------------
> server2:SunUltra-4,1cpu,1G memory,solaris5.7.
> mysql start command:nohup /usr/local/bin/safe_mysqld -O key_buffer=128M -O
>table_cache=512 -O sort_buffer=128M -O record_buffer=128M -O max_connections=999 -O
>wait_timeout=5000 &
Same game here. Additionally, you have only a forth of the memory, but
specify the same parameters... that can't go well. To take the example
from the manual again, multiplied by 4 (256MB * 4 = 1GB):
key_buffer=256M table_cache=1024 sort_buffer=16M record_buffer=4M
So, key_buffer and table_cache are in the correct magnitude (tune them
by hand looking at extended-status), but sort_buffer and record_buffer
are far off. You only need one connection doing a full table scan for
and ordered output and 256MB are in use... that's not good with only
1GB available.
> use "top" show:
> --------------------------------------------------------------------------------
> load averages: 0.98, 1.09, 1.17 13:32:41
> 159 processes: 157 sleeping, 1 running, 1 on cpu
> CPU states: 0.0% idle, 57.3% user, 42.7% kernel, 0.0% iowait, 0.0% swap
> Memory: 1024M real, 16M free, 870M swap in use, 894M swap free
>
> PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
> 332 root 9 20 0 779M 400M run 80.6H 89.60% mysqld
> 370 root 1 59 0 18M 13M sleep 0:15 0.00% Xsun
> 392 root 1 59 0 7136K 1248K sleep 0:09 0.00% dtgreet
Btw, the difference in memory usage comes from the fact, that MySQL
tries to only allocate memory as it needs it, so the second server
just was used in a more memory hungry way.
Bye,
Benjamin.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
