Have you considered using the archive storage engine? I have gotten 30:1 compression using it.
Create table archive_multimedia engine=Archive as select * from <multimedia table> John Mancuso Linux Administrator/MySQL DBA IT Infrastructure American Home Mortgage w: 631-622-6382 c: 516-652-2475 -----Original Message----- From: Dušan Pavlica [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 19, 2007 5:08 AM To: Hartleigh Burton Cc: MySql Subject: {Spam?} Re: mysqldump problem with large innodb tables... 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]