mysql crashes so offen 1

[greenpee]

Hi,

This is mysql error message

-e
Number of processes running now: 0
051216 18:59:11 mysqld restarted
InnoDB: Fatal error: cannot allocate 2097152 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 17536224 bytes. Operating system errno: 11
InnoDB: Cannot continue operation!
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: We now intentionally generate a seg fault so that
InnoDB: on Linux we get a stack trace.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_connections=100
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

051216 18:59:12 mysqld ended

I have about 2G memory. Can someone help?

Thanks,

 

[KarveR]

have you got enough disk space for swap file to query your database ona full (select * ) resultset ?


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[greenpee]

I think I usually have more than 1G swap free. I couldn't catch the situation when mysql crashes. Is there any way I can see the more situation info at the crashing moment?

 

[KarveR]

It should write all eeror output to the log file, is it windows or linux?

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[greenpee]

Yes, I found

out of per-user processes for uid 60001
and
WARNING: Sorry, no swap space to grow stack for pid 3223 (httpd)
and
last message repeated 8 times

 

[KarveR]

self explanatory then, you need more.

heres a sampple of my my.cnf


set-variable = key_buffer_size=512M
set-variable = sort_buffer=32M

set-variable = max_connections=200
set-variable = thread_cache_size=100
set-variable = max_allowed_packet=2000000
set-variable = wait_timeout=300


and memory config:
Mem: 3943852K av, 3844448K used, 99404K free, 200K shrd, 163628K buff
Swap: 2044072K av, 517960K used, 1526112K free 2888104K cached

Not sure what your requirements are, but you definately need more swap space.


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[greenpee]

Thanks. Here is my my.cnf

key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M

can you see any problem?

What does the error message tell me?
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K
bytes of memory

Do I only have 80M allowed in mysqld?

my machine memory
Memory: 2048M real, 1479M swap in use, 963M swap free

 

[KarveR]

well, depending on the size of your db, you tweek these values accordingly - how big are your tables (no of records and physical size).

Also the error you posted says:

WARNING: Sorry, no swap space to grow stack for pid 3223 (httpd)

this means you *really* need more swap.

With mySQL the more physical memory you have the better, your key_buffer is a tad low (this bit is primarily used for indexes), and more here makes queries much faster.

table_cache isn't defined correctly ( 64 what, apples, orages mb tb kb ...)

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[greenpee]

Thanks very much for pointing the problems. Physical size of the databases in total is 296M. Too new to figure out total table rows

 

[KarveR]

select count(*) from table;

will tell you how many, if its linux, (probably) cd /var/lib/mysql/databasename
ls -l or du -H


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.