List:General Discussion« Previous MessageNext Message »
From:Hartleigh Burton Date:June 20 2007 1:28am
Subject:Re: mysqldump problem with large innodb tables...
View as plain text  
Hi Dusan,

You replied to a forum post of mine on mysql.com yeah? ;)

I have tried adjusting the max_allowed_packet on both the server and  
client. Both are set to 1G now (apparently the highest value  
accepted) even though each row is no larger than 100M at very most.

I am thinking this may have something to do with --extended-insert.  
So rather than having all of data in an extended insert I have tried  
disabling this feature with --extended-insert=0, --extended- 
insert=false, --skip-extended-insert (not all at once obviously)...  
am I doing this correctly? Mixed results when I search google for  
answers. I don't really care at this stage if backup/restore times  
are reduced with this feature disabled, as long as I can get an  
accurate backup.

I also set --net_buffer_length=800M; in theory --extended-insert will  
only create queries up to this value, so if I keep it lower than the  
--max_allowed_packet value it should all be sweet. Still no cigar  
unfortunately.



On 19/06/2007, at 7:08 PM, Dušan Pavlica wrote:

> Try to look for Lost connection error in MySQL manual and it can  
> give your some hints like
> http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html
>
> Dusan
>
>
> Hartleigh Burton napsal(a):
>> Hi All,
>>
>> I have a database which is currently at ~10GB in it's test phase.  
>> It is containing uncompressed audio and is expected to reach 1.5TB  
>> in no time at all. I am just running some backup tests and I have  
>> been having lots of problems creating an accurate backup.
>>
>> I have tried both MySQL Administrator & mysqldump, both  
>> applications drop out on the same table, the table `trackdata`  
>> which contains ~9GB worth of data. There is no single row any  
>> larger than 50MB, most average around 40MB. Windows 2000 Server,  
>> MySQL v5.0.37-community-nt and all tables are InnoDB.
>>
>> If anyone can help me out with this problem the assistance is  
>> greatly appreciated. I have scoured google and various other  
>> sources and not found much information that has been useful to me.  
>> I hope I have enough info below... if more is required let me know.
>>
>> mysqldump example
>>
>> P:\>mysqldump -u username -p mraentertainment >  
>> mraentertainment.sql --opt
>> --verbose --max_allowed_packet=500M --hex-blob --single_transaction
>> --net_buffer_length=100M
>> Enter password: ******
>> -- Connecting to localhost...
>> -- Retrieving table structure for table albums...
>> -- Sending SELECT query...
>>
>> ...
>>
>> -- Retrieving rows...
>> -- Retrieving table structure for table trackdata...
>> -- Sending SELECT query...
>> -- Retrieving rows...
>> mysqldump: Error 2013: Lost connection to MySQL server during  
>> query when dumping
>>  table `trackdata` at row: 1
>>
>>
>> my.ini configuration file
>>
>> [client]
>>
>> port=3306
>>
>> [mysql]
>>
>> default-character-set=latin1
>>
>> [mysqld]
>>
>> log-bin=itd002-bin
>> server-id=1
>>
>> port=3306
>>
>> wait_timeout=86400
>>
>> max_allowed_packet=100M
>>
>>
>> basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
>>
>> datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
>>
>> default-character-set=latin1
>>
>> default-storage-engine=INNODB
>>
>> sql- 
>> mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
>>
>> max_connections=100
>>
>> query_cache_size=0
>>
>> table_cache=256
>>
>> tmp_table_size=77M
>>
>> thread_cache_size=8
>>
>> #*** MyISAM Specific options
>>
>> myisam_max_sort_file_size=100G
>>
>> myisam_max_extra_sort_file_size=100G
>>
>> myisam_sort_buffer_size=154M
>>
>> key_buffer_size=130M
>>
>> read_buffer_size=64K
>> read_rnd_buffer_size=256K
>>
>> sort_buffer_size=256K
>>
>> #skip-innodb
>>
>> innodb_additional_mem_pool_size=6M
>>
>> innodb_flush_log_at_trx_commit=1
>>
>> innodb_log_buffer_size=3M
>>
>> innodb_buffer_pool_size=252M
>>
>> innodb_log_file_size=126M
>>
>> innodb_thread_concurrency=8
>>
>>
>>
>>
>>
>> Regards,
>> Hartleigh Burton
>> Resident Geek
>>
>> MRA Entertainment Pty Ltd
>> 5 Dividend St | Mansfield | QLD 4122 | Australia
>> Phone: (07) 3457 5041
>> Fax: (07) 3349 8806
>> Mobile: 0421 646 978
>>
>> www.mraentertainment.com
>>
>>
>>
>> Internal Virus Database was built: Never
>> Checked by MAC OSX... we don't get viruses!
>>
>>
>>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql? 
> unsub=hburton@stripped
>





Regards,
Hartleigh Burton
Resident Geek

MRA Entertainment Pty Ltd
5 Dividend St | Mansfield | QLD 4122 | Australia
Phone: (07) 3457 5041
Fax: (07) 3349 8806
Mobile: 0421 646 978

www.mraentertainment.com



Internal Virus Database was built: Never
Checked by MAC OSX... we don't get viruses!



Thread
mysqldump problem with large innodb tables...Hartleigh Burton18 Jun
  • Re: mysqldump problem with large innodb tables...Baron Schwartz18 Jun
    • Re: mysqldump problem with large innodb tables...Hartleigh Burton18 Jun
      • Re: mysqldump problem with large innodb tables...Baron Schwartz18 Jun
        • Re: mysqldump problem with large innodb tables...Hartleigh Burton18 Jun
          • Re: mysqldump problem with large innodb tables...Baron Schwartz18 Jun
            • Re: mysqldump problem with large innodb tables...Hartleigh Burton18 Jun
              • Re: mysqldump problem with large innodb tables...Baron Schwartz18 Jun
                • Re: mysqldump problem with large innodb tables...Hartleigh Burton18 Jun
                  • Re: mysqldump problem with large innodb tables...Baron Schwartz18 Jun
  • Re: mysqldump problem with large innodb tables...DuĊĦan Pavlica19 Jun
    • RE: {Spam?} Re: mysqldump problem with large innodb tables...John Mancuso19 Jun
    • Re: mysqldump problem with large innodb tables...Hartleigh Burton20 Jun