Home » Performance » MySQL » Innodb high memory load make mysql crash [help]
Innodb high memory load make mysql crash [help] [message #3103] Tue, 20 May 2008 04:59 Go to next message
yahoon  is currently offline yahoon
Messages: 2
Registered: May 2008
Location: wuhan,china
Junior Member

OS: Red Hat Enterprise Linux ES release 3 (Taroon Update 5)
32bit
MYSQL 5.0.16
MEM: 8G

while a db miragtion, mysql eat up all the physical memory ,then crash and restart.

the SQL statement excuting is
REPLACE INTO pix_preproduct.od SELECT * FROM pix_enhance.od


the MySQL error log is
080507 21:19:47  InnoDB: Error: cannot allocate 1064960 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 2974884308 bytes. Operating system errno: 12
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: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
080507 21:20:47  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=67108864
read_buffer_size=8384512
max_used_connections=3
max_connections=50
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 884535 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=(nil)
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=0x288e55c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8150650
0xf08e48
(nil)
0x831b718
0x82f0664
0x824fe5d
0xf02de8
0x82693a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read [url]http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html[/url] 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
The manual page at [url]http://www.mysql.com/doc/en/Crashing.html[/url] contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
080507 21:20:47  mysqld restarted
080507 21:20:48  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible localhostf-written data pages from the doublewrite
InnoDB: buffer...
080507 21:20:48  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 646 1621436243.
InnoDB: Doing recovery: scanned up to log sequence number 646 1626678784
InnoDB: Doing recovery: scanned up to log sequence number 646 1631921664
InnoDB: Doing recovery: scanned up to log sequence number 646 1637164544
InnoDB: Doing recovery: scanned up to log sequence number 646 1642407424
InnoDB: Doing recovery: scanned up to log sequence number 646 1647650304
InnoDB: Doing recovery: scanned up to log sequence number 646 1652893184
InnoDB: Doing recovery: scanned up to log sequence number 646 1658136064
InnoDB: Doing recovery: scanned up to log sequence number 646 1663378944
InnoDB: Doing recovery: scanned up to log sequence number 646 1668621824
InnoDB: Doing recovery: scanned up to log sequence number 646 1673864704
InnoDB: Doing recovery: scanned up to log sequence number 646 1679107584
InnoDB: Doing recovery: scanned up to log sequence number 646 1684350464
InnoDB: Doing recovery: scanned up to log sequence number 646 1689593344
InnoDB: Doing recovery: scanned up to log sequence number 646 1694836224
InnoDB: Doing recovery: scanned up to log sequence number 646 1700079104
InnoDB: Doing recovery: scanned up to log sequence number 646 1705321984
InnoDB: Doing recovery: scanned up to log sequence number 646 1710564864
InnoDB: Doing recovery: scanned up to log sequence number 646 1715807744
InnoDB: Doing recovery: scanned up to log sequence number 646 1721050624
InnoDB: Doing recovery: scanned up to log sequence number 646 1726293504
InnoDB: Doing recovery: scanned up to log sequence number 646 1731536384
InnoDB: Doing recovery: scanned up to log sequence number 646 1736779264
InnoDB: Doing recovery: scanned up to log sequence number 646 1742022144
InnoDB: Doing recovery: scanned up to log sequence number 646 1747265024
InnoDB: Doing recovery: scanned up to log sequence number 646 1752507904
InnoDB: Doing recovery: scanned up to log sequence number 646 1757750784
InnoDB: Doing recovery: scanned up to log sequence number 646 1762993664
InnoDB: Doing recovery: scanned up to log sequence number 646 1768236544
InnoDB: Doing recovery: scanned up to log sequence number 646 1773479424
InnoDB: Doing recovery: scanned up to log sequence number 646 1778722304
InnoDB: Doing recovery: scanned up to log sequence number 646 1783965184
InnoDB: Doing recovery: scanned up to log sequence number 646 1789208064
InnoDB: Doing recovery: scanned up to log sequence number 646 1794450944
InnoDB: Doing recovery: scanned up to log sequence number 646 1799693824
InnoDB: Doing recovery: scanned up to log sequence number 646 1804936704
InnoDB: Doing recovery: scanned up to log sequence number 646 1807010978
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 4561694 row operations to undo
InnoDB: Trx id counter is 0 707924736
080507 21:21:09  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 4246, file name /var/lib/mysql/localhost-bin.000282
InnoDB: Starting in background the rollback of uncommitted transactions
080507 21:22:30  InnoDB: Rolling back trx with id 0 707924262, 4561694 rows to undo

InnoDB: Progress in percents: 1080507 21:22:30  InnoDB: Started; log sequence number 646 1807010978
080507 21:22:30 [Note] Recovering after a crash using /var/lib/mysql/localhost-bin
080507 21:22:30 [Note] Starting crash recovery...
080507 21:22:30 [Note] Crash recovery finished.
080507 21:22:30 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.16-standard-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53


here is a part from my.cnf file.
innodb_buffer_pool_size=640M
#innodb_buffer_pool_size=2G
thread_stack=128K
innodb_data_home_dir = 
innodb_data_file_path = /var/lib/mysql/ibdata1:10G;/var/lib/mysql/ibdata2:10G;/var/lib/mysql/ibdata3:10G:autoextend
set-variable = innodb_mirrored_log_groups=1  
set-variable = innodb_log_files_in_group=3  
set-variable = innodb_log_file_size=250M  
set-variable = innodb_log_buffer_size=64M
set-variable = max_connections=50
#key_buffer = 64M
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 32M
thread_cache = 8
query-cache-type = 1
query_cache_size = 32M
query_cache_limit = 16M
log-bin



during the entire processing ,the swap usage is 0 ,even when the free physical memory is only 20M.


I noticed that the file /var/lib/mysql/ibdata3 has reached 170G
but all the setting was made by my former fellow,I didn't know how to configure now.

even after we added the physical memory to 12G ,the status continues..

pls give me some advice ,thanks very much.
I am from china ,sorry for my poor english.

[Updated on: Wed, 21 May 2008 00:53]

Re: Innodb high memory load make mysql crash [help] [message #3137 is a reply to message #3103 ] Wed, 28 May 2008 01:12 Go to previous messageGo to next message
debug  is currently offline debug
Messages: 128
Registered: March 2008
Senior Member

It just eats all free memory and dies? Is your swap configured correctly?


Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Re: Innodb high memory load make mysql crash [help] [message #3142 is a reply to message #3137 ] Wed, 28 May 2008 09:36 Go to previous message
yahoon  is currently offline yahoon
Messages: 2
Registered: May 2008
Location: wuhan,china
Junior Member

debug wrote on Wed, 28 May 2008 01:12

It just eats all free memory and dies? Is your swap configured correctly?



I think so.
my developer told me that if use 'insert' statement ,everything goes well.but we need 'replace'.
this database run well for a long time,but recently the data amount grew a lot.
I didn't know why swap didnot cost,it looks configured well.

Previous Topic:"go to" command in an sql script
Next Topic:Community Edition max_connections limit?
Goto Forum:
  



Current Time: Thu Jan 8 23:41:34 EST 2009

Total time taken to generate the page: 0.02096 seconds
.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software

MySQL Performance | Forum authority Badge