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 ofInnoDB: memory with malloc! Total allocated memoryInnoDB: by InnoDB 2974884308 bytes. Operating system errno: 12InnoDB: Check if you should increase the swap file orInnoDB: ulimits of your operating system.InnoDB: On FreeBSD check you have compiled the OS withInnoDB: a big enough maximum process size.InnoDB: Note that in most 32-bit computers the processInnoDB: 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 thatInnoDB: 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 binaryor 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 diagnosethe problem, but since we have already crashed, something is definitely wrongand this may fail.key_buffer_size=67108864read_buffer_size=8384512max_used_connections=3max_connections=50threads_connected=1It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 884535 Kbytes of memoryHope that’s ok; if not, decrease some variables in the equation.thd=(nil)Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong…Cannot determine thread, fp=0x288e55c, backtrace may not be correct.Stack range sanity check OK, backtrace follows:0x81506500xf08e48(nil)0x831b7180x82f06640x824fe5d0xf02de80x82693aNew 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. Resolvedstack trace is much more helpful in diagnosing the problem, so please do resolve itThe manual page at [url]MySQL :: MySQL Documentation containsinformation that should help you find out what is causing the crash.Number of processes running now: 0080507 21:20:47 mysqld restarted080507 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 doublewriteInnoDB: buffer…080507 21:20:48 InnoDB: Starting log scan based on checkpoint atInnoDB: log sequence number 646 1621436243.InnoDB: Doing recovery: scanned up to log sequence number 646 1626678784InnoDB: Doing recovery: scanned up to log sequence number 646 1631921664InnoDB: Doing recovery: scanned up to log sequence number 646 1637164544InnoDB: Doing recovery: scanned up to log sequence number 646 1642407424InnoDB: Doing recovery: scanned up to log sequence number 646 1647650304InnoDB: Doing recovery: scanned up to log sequence number 646 1652893184InnoDB: Doing recovery: scanned up to log sequence number 646 1658136064InnoDB: Doing recovery: scanned up to log sequence number 646 1663378944InnoDB: Doing recovery: scanned up to log sequence number 646 1668621824InnoDB: Doing recovery: scanned up to log sequence number 646 1673864704InnoDB: Doing recovery: scanned up to log sequence number 646 1679107584InnoDB: Doing recovery: scanned up to log sequence number 646 1684350464InnoDB: Doing recovery: scanned up to log sequence number 646 1689593344InnoDB: Doing recovery: scanned up to log sequence number 646 1694836224InnoDB: Doing recovery: scanned up to log sequence number 646 1700079104InnoDB: Doing recovery: scanned up to log sequence number 646 1705321984InnoDB: Doing recovery: scanned up to log sequence number 646 1710564864InnoDB: Doing recovery: scanned up to log sequence number 646 1715807744InnoDB: Doing recovery: scanned up to log sequence number 646 1721050624InnoDB: Doing recovery: scanned up to log sequence number 646 1726293504InnoDB: Doing recovery: scanned up to log sequence number 646 1731536384InnoDB: Doing recovery: scanned up to log sequence number 646 1736779264InnoDB: Doing recovery: scanned up to log sequence number 646 1742022144InnoDB: Doing recovery: scanned up to log sequence number 646 1747265024InnoDB: Doing recovery: scanned up to log sequence number 646 1752507904InnoDB: Doing recovery: scanned up to log sequence number 646 1757750784InnoDB: Doing recovery: scanned up to log sequence number 646 1762993664InnoDB: Doing recovery: scanned up to log sequence number 646 1768236544InnoDB: Doing recovery: scanned up to log sequence number 646 1773479424InnoDB: Doing recovery: scanned up to log sequence number 646 1778722304InnoDB: Doing recovery: scanned up to log sequence number 646 1783965184InnoDB: Doing recovery: scanned up to log sequence number 646 1789208064InnoDB: Doing recovery: scanned up to log sequence number 646 1794450944InnoDB: Doing recovery: scanned up to log sequence number 646 1799693824InnoDB: Doing recovery: scanned up to log sequence number 646 1804936704InnoDB: Doing recovery: scanned up to log sequence number 646 1807010978InnoDB: 1 transaction(s) which must be rolled back or cleaned upInnoDB: in total 4561694 row operations to undoInnoDB: Trx id counter is 0 707924736080507 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 completedInnoDB: Last MySQL binlog file position 0 4246, file name /var/lib/mysql/localhost-bin.000282InnoDB: Starting in background the rollback of uncommitted transactions080507 21:22:30 InnoDB: Rolling back trx with id 0 707924262, 4561694 rows to undoInnoDB: Progress in percents: 1080507 21:22:30 InnoDB: Started; log sequence number 646 1807010978080507 21:22:30 [Note] Recovering after a crash using /var/lib/mysql/localhost-bin080507 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=2Gthread_stack=128Kinnodb_data_home_dir = innodb_data_file_path = /var/lib/mysql/ibdata1:10G;/var/lib/mysql/ibdata2:10G;/var/lib/mysql/ibdata3:10G:autoextendset-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=64Mset-variable = max_connections=50#key_buffer = 64Mkey_buffer = 384Mmax_allowed_packet = 1Mtable_cache = 512sort_buffer_size = 8Mread_buffer_size = 8Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 32Mthread_cache = 8query-cache-type = 1query_cache_size = 32Mquery_cache_limit = 16Mlog-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.