8G的内存
系统是redhat5
mysql5
在执行一个数据库之间数据迁移的sql语句时,耗费大量内存,导致这个查询中断

在mysql的日志里面 内容如下

复制内容到剪贴板

代码:

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
[root@localhost mysqldata1]#

执行的这个mysql语句是

复制内容到剪贴板

代码:

REPLACE INTO pix_preproduct.od SELECT * FROM pix_enhance.od

除了加内存之外,有什么方法解决吗?

而且我观察在执行过程中,swap的使用一直是0..为什么在内存不足的情况下mysql没使用swap呢??

[ 本帖最后由 yahoon 于 2008-5-8 11:07 编辑 ]

不是很了解，你用的是InnoDB,试着调整一下相关参数看看？

恩好好研究下

mysql 在内存不足的情况下 宁可爆掉 也不用swap..
这种现象着实奇怪啊

这个应该是innobase DB的配置问题

mysql.cnf 中的部分配置如下
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

还是没人提个方案?

能不能分批迁移，原来看oracle时是这么建议的

就算你的内存够大,swap一点不用,应该不太会

不知道 顶一下吧 呵呵

关于不使用swap的问题
是2.6内核中 内存使用机制的一种
你可以通过调整
echo 1 > /proc/sys/vm/overcommit_memory

来关闭 oom-killer 机制
具体的我解释不太清楚,你查查看

楼主,最后如何解决的呀,我倒是很想知道,是调参数还是做分表,还是做了分区呀,说说看呀,也给我们一个提醒

一直没解决 不知道具体开发的那边怎么弄的 反正task打回去了

截断日志看看？