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: | |
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
[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.