Bug #31100 5.0.45 crashing when doing SHOW CREATE PROCEDURE, or a call to a procedure
Submitted: 19 Sep 2007 20:44 Modified: 27 Sep 2007 4:37
Reporter: Bruce Bristol Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.45 OS:Linux (Linux hostname 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: crash, innodb, out of memory, stored procedure, stored routine

[19 Sep 2007 20:44] Bruce Bristol
Description:
I have an InnoDB database running MySQL 5.0.45 with 8Gb RAM, Red Hat Linux 32-bit.

Currently have innodb_buffer_pool_size = 768M and key_buffer_size = 32M (not using too much MyISAM at this time).

070919 10:26:41 [Note] Slave I/O thread: connected to master 'repl@10.10.1.210:3306', replication started in log 'mysql-bin.000812' at position 376952492
070919 10:47:34 [ERROR] /usr/local/mysql/bin/mysqld: Out of memory (Needed 2097116 bytes)
070919 10:48:11 [ERROR] /usr/local/mysql/bin/mysqld: Out of memory (Needed 2097116 bytes)
070919 10:48:18 [ERROR] /usr/local/mysql/bin/mysqld: Out of memory (Needed 114296 bytes)
070919 10:48:18 [ERROR] /usr/local/mysql/bin/mysqld: Out of memory (Needed 114296 bytes)
070919 10:48:18 - 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=33554432
read_buffer_size=1044480
max_used_connections=220
max_connections=500
threads_connected=220
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1054764 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x34e5f670
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=0xb, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0xb, stack_bottom=0x4c8d0000, thread_stack=196608, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xb8299d0 = SHOW CREATE PROCEDURE `adv_search`.`get_adv_browse_members`
thd->thread_id=245
The manual page at [www.mysql.com] contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
070919 10:48:18 mysqld restarted

Any ideas?

Thank you!

-Bruce

How to repeat:
It crashes 30 times a day on average.
[20 Sep 2007 18:57] Valeriy Kravchuk
Thank you for a problem report. Please, send your my.cnf file content and

free

command results. Does that SHOW CREATE PROCEDURE crash each and every time?
[20 Sep 2007 19:00] Bruce Bristol
Hi Valeriy,

No, it's not consistent.  A call to a stored procedure is often what's displayed in the error message for other crashes.

Right now we're crashing about 2 times an hour.
[21 Sep 2007 16:13] Valeriy Kravchuk
Still, please, send your my.cnf file content and

free

Linux command results.
[21 Sep 2007 16:32] Bruce Bristol
Our my.cnf file

Attachment: my.cnf (application/octet-stream, text), 21.03 KiB.

[21 Sep 2007 16:32] Bruce Bristol
Here our our free -t output:

             total       used       free     shared    buffers     cached
Mem:       8310624    8306880       3744          0      25252    6216708
-/+ buffers/cache:    2064920    6245704
Swap:      8093664       3792    8089872
Total:    16404288    8310672    8093616
[23 Sep 2007 8:58] Valeriy Kravchuk
With these values in my.cnf:

max_connections = 500

max_heap_table_size = 32M
tmp_table_size = 32M

sort_buffer_size = 1M
join_buffer_size = 1M
thread_stack = 192K

query_cache_size = 64M

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 768M
innodb_log_buffer_size = 8M

you may use from:

768+64+16+8 + 500*(1+1+0.192+...) > 856+1096 = 1952M

of memory (even this is likely more than you can get with 32-bit process on 32-bit OS)

up to:

768+64+16+8 + 500*(1+1+0.192+in_memory_temporary_tables+...) > 856+1096+32M*500*N

So, out of memory situation is surely possible with many connections using temporary tables. Please, send the results of:

show global status like 'max%';
[24 Sep 2007 19:34] Bruce Bristol
Max_used_connections = 111

It's been up over 300 in the past.

Sounds like reducing tmp_table_size may solve the memory issue, though it may cost us in performance.  Is there a way to estimate the memory table size that MySQL is using for any of our queries?  I it simple number_of_rows * aggregate_column_size?

Thanks!

-Bruce
[25 Sep 2007 23:33] Bruce Bristol
Can we move this bug under our support contract or should I open a new support ticket there?

Thank you!
[26 Sep 2007 5:42] Valeriy Kravchuk
I had created a new support issue for you, about temporary table size estimation. As for this bug report, do you agree that we may just have an out-of-memory problem here?
[26 Sep 2007 17:03] Bruce Bristol
Thank you.

Yes, it's quite possible.  You can close this and I'll re-open it if the problem persists after adjusting tmp_table_size.
[27 Sep 2007 4:37] Valeriy Kravchuk
So, let's assume this is not a bug for now.