RE: Importing 3Gb File
At 04:41 PM 6/11/2006, Ian Barnes wrote: Hi, Then after that it the following happened for 60 seconds and then it timed out: I have put more RAM in (I now have 768Mb's, as well as 3Gig of SWAP). Thanks for the suggestions! Cheers Ian Ian, Timed out? Wasn't there an error? Have you looked at the MySQL Log files? http://dev.mysql.com/doc/refman/5.0/en/error-log.html You're trying to load 3.2gb of data with only 768mb of ram and 3gb of swap and that may not be enough. Also make sure your MySQL data directory has enough disk space. You will need more than 3gb of space free in your data directory (I'm assuming you're using MyISAM tables and not InnoDb). With indexes you could easily be looking at double or triple that size (6-9gb of disk space). Mike > -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 = 1 > > > ># 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]
RE: Importing 3Gb File
Hi, Yes, I don't actually know if I have very large blogs, but the possibility exists, and is quite large. I am running FreeBSD so I don't have the ulimit program, the only program I have is called limits and these are what I get when running it: [EMAIL PROTECTED] /home # limits Resource limits (current): cputime infinity secs filesize infinity kb datasize 524288 kb stacksize 65536 kb coredumpsize infinity kb memoryuseinfinity kb memorylocked infinity kb maxprocesses 5446 openfiles 10893 sbsize infinity bytes vmemoryuse infinity kb [EMAIL PROTECTED] /home # I have upped my RAM in the unit with 512Mb to 768Mb, so I think I should have enough RAM now. Any other ideas? Thanks, Ian > -Original Message- > From: Jeremy Cole [mailto:[EMAIL PROTECTED] > Sent: 09 June 2006 05:01 PM > To: Ian Barnes > Cc: mysql@lists.mysql.com > Subject: Re: Importing 3Gb File > > Hi Ian, > > > 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 message comes from some single place trying to allocate 178MB > at a single time. Do you have large BLOBs in your data? > > This error message means that mysqld is beind denied memory by the OS, > either because you are actually out of memory, or because your ulimit > has been reached (more likely). > > Check your ulimits for your system with ulimit -a, and adjust if > necessary in the mysql.server or mysqld_safe script (those both run as > root, so can adjust ulimits upwards). > > Regards, > > Jeremy > > -- > Jeremy Cole > MySQL Geek, Yahoo! Inc. > Desk: 408 349 5104 > > -- > 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]
RE: Importing 3Gb File
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 = 1 > > > ># 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
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. > If indexing is the problem when loading the dump, are there not options to have mysqldump not disable the indexes until the import is complete, so that the index is generated as records are inserted, instead of all in one shot? Ah, here it is... I've never used this before, but a quick test shows it does what I assumed it does. (I'm running 5.0, so mileage may vary in 4.1) mysqldump --disable-keys=false [table] Of course, this is assuming that you have the luxury of re-exporting this data from the original source. Dan. --- This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 = 1 # 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]
Re: Importing 3Gb File
Hi Ian, 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 message comes from some single place trying to allocate 178MB at a single time. Do you have large BLOBs in your data? This error message means that mysqld is beind denied memory by the OS, either because you are actually out of memory, or because your ulimit has been reached (more likely). Check your ulimits for your system with ulimit -a, and adjust if necessary in the mysql.server or mysqld_safe script (those both run as root, so can adjust ulimits upwards). Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing 3Gb File
On 6/8/06, Ian Barnes <[EMAIL PROTECTED]> 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. 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 = 1 # 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] Assuming that you have dumped your databases using mysqldump, what options did you give to mysqldump, as of 4.1, "--opt" is enabled by default, and this enables the "--quick" option which basically forces mysqldump to retrieve one row at a time instead of buffering the whole table into memory and then writing out the result. So if you have the --quick option enabled in myslqdump, you should not be getting the out of memory errors, also I see you are using the -n option with mysql CLT, which does not buffer sql statements/queries into memory before flushing them, but if the dump itself consists of large rows of table data flushed into one large sql statement, then mysql CLT would still treat it as one query, so i am sure you have to change the way you dump your tables... Kishore Jalleda http://kjalleda.googlepages.com/projects
