By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,838 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,838 IT Pros & Developers. It's quick & easy.

innodb keeps crashing due to out-of-memory errors

P: n/a
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 shows mysql steadily consuming more resourses over a period of 2
hours, up to 1.2 Gb, before it crashes.

Any clues?

------------------------------

[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/Us...ack_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

....

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Eddie,

what does

ulimit -a

print?

If you monitor the mysqld process size with the 'top' command, how big is
it?

Clearly, InnoDB is not the root of the problem, as it has only allocated 600
MB of memory.

You can try setting tmp table size even lower.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/
"Eddie" <e.******@chello.nl> kirjoitti
viestissä:11**********************@z14g2000cwz.goo glegroups.com...
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 shows mysql steadily consuming more resourses over a period of 2
hours, up to 1.2 Gb, before it crashes.

Any clues?

------------------------------

[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/Us...ack_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

...

Jul 23 '05 #2

P: n/a
Eddie,

oops, I did not notice this:
Top shows mysql steadily consuming more resourses over a period of 2
hours, up to 1.2 Gb, before it crashes.

Please monitor also SHOW PROCESSLIST. Do connections pile up? Are they doing
sorting?

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/

"Heikki Tuuri" <He**********@innodb.com> kirjoitti
viestissä:lf*************@read3.inet.fi... Eddie,

what does

ulimit -a

print?

If you monitor the mysqld process size with the 'top' command, how big is
it?

Clearly, InnoDB is not the root of the problem, as it has only allocated
600 MB of memory.

You can try setting tmp table size even lower.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/
"Eddie" <e.******@chello.nl> kirjoitti
viestissä:11**********************@z14g2000cwz.goo glegroups.com...
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 shows mysql steadily consuming more resourses over a period of 2
hours, up to 1.2 Gb, before it crashes.

Any clues?

------------------------------

[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/Us...ack_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

...


Jul 23 '05 #3

P: n/a
Hi!

The problem might indeed be a too big tmp_table_size. Try decreasing it to
16M.

Regards,

Heikki

"Heikki Tuuri" <He**********@innodb.com> kirjoitti
viestissä:Yh*************@read3.inet.fi...
Eddie,

oops, I did not notice this:
Top shows mysql steadily consuming more resourses over a period of 2
hours, up to 1.2 Gb, before it crashes.


Please monitor also SHOW PROCESSLIST. Do connections pile up? Are they
doing sorting?

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/

"Heikki Tuuri" <He**********@innodb.com> kirjoitti
viestissä:lf*************@read3.inet.fi...
Eddie,

what does

ulimit -a

print?

If you monitor the mysqld process size with the 'top' command, how big is
it?

Clearly, InnoDB is not the root of the problem, as it has only allocated
600 MB of memory.

You can try setting tmp table size even lower.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/
"Eddie" <e.******@chello.nl> kirjoitti
viestissä:11**********************@z14g2000cwz.goo glegroups.com...
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 shows mysql steadily consuming more resourses over a period of 2
hours, up to 1.2 Gb, before it crashes.

Any clues?

------------------------------

[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/Us...ack_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

...



Jul 23 '05 #4

P: n/a
Hi Heikki,

Thanks for your instructive response.

SHOW PROCESSLIST shows 10~20 connection threads,
_dead_ threads are being removed after 60s. (my.cnf: wait_timeout = 60)

The system is for the most part doing small inserts, and usually no
queries are caught by SHOW PROCESSLIST.

Batch sql statements are doing summary tables (MYISAM), some do
sorting, but havnt catched any in SHOW PROCESSLIST.

Just before writing this (didnt read your response until just now), Ive
changed all HEAP tables (small) to MYISAM tables to see if thats the
problem. I was thinking that maybe some HEAP tables are munching
memory.

If this doesnt work, I will reduce tmp_table_size.

I will post my findings either way.

Thank you,

Eddie
..............

[root@s10 logs]# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited

.........

And in my.cnf
open_files_limit=65535

.......................

Jul 23 '05 #5

P: n/a
posting to forum.mysql.com -> innodb

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.