Hi, This is all I could see just before it happened:
mysql> show processlist; +----+------+-----------+---------+---------+------+----------------+------- ---------------------------------------------------------------------------- -------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+---------+---------+------+----------------+------- ---------------------------------------------------------------------------- -------------------+ | 11 | root | localhost | testing | Query | 0 | creating table | CREATE TABLE ` upgrade_history` ( `upgrade_id` int(10) NOT NULL auto_increment, `upgrade_vers | | 12 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+---------+---------+------+----------------+------- ---------------------------------------------------------------------------- -------------------+ 2 rows in set (0.00 sec) Then after that it the following happened for 60 seconds and then it timed out: mysql> show processlist; +----+------+-----------+---------+---------+------+-------+---------------- --+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+---------+---------+------+-------+---------------- --+ | 11 | root | localhost | testing | Sleep | 0 | | | | 12 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+---------+---------+------+-------+---------------- --+ 2 rows in set (0.00 sec) I have put more RAM in (I now have 768Mb's, as well as 3Gig of SWAP). Thanks for the suggestions! Cheers Ian > -----Original Message----- > From: mos [mailto:[EMAIL PROTECTED] > Sent: 09 June 2006 07:15 PM > To: mysql@lists.mysql.com > Subject: Re: Importing 3Gb File > > At 10:20 AM 6/8/2006, you wrote: > >Hi, > > > >I am trying to import a 3.2Gb sql dump file back into my sql server > (4.1.12) > >and im coming across the following error: > > > >mysql: Out of memory (Needed 178723240 bytes) > >mysql: Out of memory (Needed 178719144 bytes) > > > >That error comes up after about 30 minutes worth of import and I would > guess > >about half way through the import. > > What does "Show Processlist" say its doing just before the crash? I've had > problems with Load Data on a very large table 500 million rows because the > machine did not have enough memory to build the index. The data was loaded > just fine, it's building the index that hung it out to dry because that > eats up memory like crazy. How much memory do you have on your machine? > The > cheapest solution might be to go out and get a few more gb of RAM. > > Mike > > > > >The file in question is a mysqldump > >-all-databases file from another server that im trying to import onto my > >desktop machine. I have tried to alter the my.cnf file a bit, and this is > >what it looks like: > > > >[client] > >#password = your_password > >port = 3306 > >socket = /tmp/mysql.sock > > > >[mysqld] > >port = 3306 > >socket = /tmp/mysql.sock > >skip-locking > >key_buffer = 64M > >max_allowed_packet = 8M > >table_cache = 512 > >sort_buffer_size = 8M > >net_buffer_length = 8M > >myisam_sort_buffer_size = 45M > >set-variable=max_connections=300 > > > ># Replication Master Server (default) > ># binary logging is required for replication > >#log-bin > > > ># required unique id between 1 and 2^32 - 1 > ># defaults to 1 if master-host is not set > ># but will not function as a master if omitted > >server-id = 1 > > > >#bdb_cache_size = 4M > >#bdb_max_lock = 10000 > > > ># Uncomment the following if you are using InnoDB tables > >#innodb_data_home_dir = /var/db/mysql/ > >#innodb_data_file_path = ibdata1:10M:autoextend > >#innodb_log_group_home_dir = /var/db/mysql/ > >#innodb_log_arch_dir = /var/db/mysql/ > ># You can set .._buffer_pool_size up to 50 - 80 % > ># of RAM but beware of setting memory usage too high > >#innodb_buffer_pool_size = 16M > >#innodb_additional_mem_pool_size = 2M > ># Set .._log_file_size to 25 % of buffer pool size > >#innodb_log_file_size = 5M > >#innodb_log_buffer_size = 8M > >#innodb_flush_log_at_trx_commit = 1 > >#innodb_lock_wait_timeout = 50 > > > >[mysqldump] > >quick > >max_allowed_packet = 16M > > > >[mysql] > >no-auto-rehash > ># Remove the next comment character if you are not familiar with SQL > >#safe-updates > > > >[isamchk] > >key_buffer = 10M > >sort_buffer_size = 20M > >read_buffer = 2M > >write_buffer = 2M > > > >[myisamchk] > >key_buffer = 10M > >sort_buffer_size = 20M > >read_buffer = 2M > >write_buffer = 2M > > > >[mysqlhotcopy] > >interactive-timeout > > > > > >Does anyone have any advice as to what I could change to make it import, > and > >not break half way through. The command im running to import is: mysql -n > -f > >-p < alldb.sql > > > >Thanks in advance, > >Ian > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
