I have a MySQL-server running Innodb. We have installed ~ 2GB of
memory in the server. In spite of this MySQL keeps crashing due to
out-of-memory errors.
The server is a dual xeon i686 running kernel Linux 2.4.20-8smp redhat stock.
Top/ graphs shows mysql steadily consuming more resources over a period of 2
hours, up to 1.2 Gb, before it crashes.
Lessening memory parameters lengthens the 2 hs but innodb still crashes.
Restarting mysql is the only way to keep innodb up and running!
Any clues as to what could be going on?
------------------------------
[root@s10 mysql]# uname -a
Linux s10.xxx.net 2.4.20-8smp #1 SMP Thu Mar 13 17:45:54 EST 2003 i686
i686 i386 GNU/Linux
------------------------------
[root@s10 mysql]# mysql --version
mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686)
------------------------------
The error log shows:
[root@s10 mysql]# tail -n 98 /var/lib/mysql/s10.xxx.net.err
050303 19:26:40 [Warning] Asked for 196608 thread stack, but got 126976
050303 19:26:41 InnoDB: Started; log sequence number 1 2349435259
/usr/sbin/mysqld: ready for connections.
Version: '4.1.9-standard-log' socket: '/var/lib/mysql/mysql.sock'
port: 3306 Official MySQL RPM
050303 22:31:43 InnoDB: Fatal error: cannot allocate 8404992 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 598183234 bytes. Operating system errno: 12
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=268435456
read_buffer_size=2093056
max_used_connections=16
max_connections=100
threads_connected=13
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 671343 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x341dfa78
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfe5d298, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x808e1b7
0x82e5a08
0x827868b
0x8278716
0x827c2b9
0x82310bd
0x81452d2
0x814681b
0x81601fe
0x817ac7b
0x8104933
0x8104e98
0x80fb593
0x810d359
0x809e6fe
0x80a2109
0x809c718
0x809c0e4
0x809b797
0x82e31bc
0x830ca8a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read
http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html
and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please
do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xb0da9078 is invalid pointer
thd->thread_id=628
The manual page at
http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
------------------------------
Relevent my.cnf parameters (after downsizing mem usage) are:
...
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
...
#testing 20050131
key_buffer = 256M #was 512M 19:04 2005-03-03
max_allowed_packet = 1M
table_cache = 256 # 2005-03-03 10:58 from 2048 (works)
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M #was 8M 19:04 2005-03-03
myisam_sort_buffer_size = 32M # was 64M 19:04 2005-03-03
thread_cache = 8
query_cache_size = 32M
tmp_table_size=126M # 2005-03-01 10:58 from 126 2005-03-03
... innodb ....
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
#If you can afford the loss of some latest committed transactions,
#you can set the `my.cnf' parameter innodb_flush_log_at_trx_commit to 0
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
...