MySQL Forums
Forum List  »  Backup

InnoDB backups take over 8 hours
Posted by: Ken Hanks
Date: July 23, 2006 04:48PM

I have a number of InnoDB tables that back up fine, in less than an hour. But I have one table consisting of a key (Integer) and a longblob data piece that takes over 8 hours and then bombs out because of space. The table size listed in MySQL Administrator is about 17GB but of course it writes text files out (with Inserts and all the data values), so it ends up needing about 25GB of space. Now, we can deal with the space -- just buy another drive -- but the 8 hour time frame is not acceptable. Has no one else run into this problem? How can I speed this backup up?
I'm on Windows Server 2003 with 3 GB of memory, using MySQL 4.1.15 - and no, I don't want to upgrade to ver 5.x until I'm ready to deal with those bugs as well.
Thanks!
Below is an "excerpt" of what one record looks like:
DROP TABLE IF EXISTS `pcrm_filedata`;
CREATE TABLE `pcrm_filedata` (
`dataid` int(10) unsigned NOT NULL auto_increment,
`data` longblob,
PRIMARY KEY (`dataid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=4294967295 AVG_ROW_LENGTH=150000;

--
-- Dumping data for table `pcrm_filedata`
--
-- WHERE: dataid = 10000


/*!40000 ALTER TABLE `pcrm_filedata` DISABLE KEYS */;
LOCK TABLES `pcrm_filedata` WRITE;
INSERT INTO `pcrm_filedata` VALUES (10000,0x49492A008C4B00000028C341D741010000B81113544DBC5C9D89789920E0393126480139089F7F6FFE328314238F6DC2A6E8DFE4F97BF906A30ADAC4C4F297ABABF9CBD55B13000000B8E5FBF97B3913E1EAFBCEFB7C3F7F2B287F2BF6F33779BE7CFEBDF9D4DFE091077ED994FA9E317F2FDFE06FF29B98A3C43EDFE7549DF7F93EA7AD0900000000B8ABEFFB265EE6FBCBF9FBBEEFFBBE19E76F3EFFBE6FF2FC6A9FEF2BEC73E2BB0F724450BE7DFBFEA19C322413F132833865FB26C4E47D8232E450BEFBBE6FB7C2EE4CF93B1331417937F5997CFE7DDFC4CBF9FBBE89B72600000000B8ABEF65E265BEBF5C7DDFF77DA39E07FE5E26CFAFF6F9BE62DFBFEFECE565FE7DFBFEEDFBF77DDF7CFE7D908380BFEFFBF67D136285896F3EFFCEE6DFE71E6CF77DDFF77DF3F9F7B9A7FCECFBFCFBBE8997F3F77D136F4D00000000B8AB6F62E2EA7C4F5C9DBFEFFB50E033AAF82666CC51BE4F4CDEF709E7AB97F9F7EDFBB7EFDFF72DBF327F99FCF27DFBBEC941C0969BCFBFF3CBFD7D83EDBE17347FF32BDF37E56DBFE7DFF74D4CCCBFEF9B786B02000000B8AB6FFE7275BE275EE6DFF77DDF8CF337F89B0F3CBF22BEAFD8F7B3FD7CF53287DCBE7FFBFE7D672226ECF397C92FDFB7EF9B102BF609FB7CFE9D5FF6F33778DFBF97976F3E61FFBE2977F73DFFBE6F6262FE7DDFC45B1300000000B8ABEFFCB2FC0CC92F13DFF77DDF8CF337F83B8F3D7FF9CEFB8A7DFFBEF3D5CBFCE576DECFDFCB7CFECD5F26BF7CDFBE6FC227DFE76F3EFFCE2FFBF91BBCEFDFCBCB379F7FDF37E57EBBE7DFF74DCC27BEEF9B786B020000B8E5FBF96AF9CB7CE26A9FEFE76FC6F95BB19F479EBF7CDF3778DFBFDBF9EA65FE7D57FBF9FBBEF9FC367F99FC7DDFB7EF9BA07FDF6D3EFFCE57CEDFE07DFFBEEFE59BCF6FDF37E5BD7F13FB7C7F99BFECF3FDE5AD090000B81173C88D7839BF40F8CB0401CF89789943C0907BF9BE6FF0BE9FEDE7E53E48264E7C3F7F675A9CEDF3978917E294F77D13F4EF6C9FF07DE7ABF937F83B53BEA20CC9F6EF9B12EEFBCBCBD579F9CBD55B130000B8A107DD1AF23AEBC4DA2750000000006893D5BA83AE530300000000289B1500289B1500289B1500289B1500289B1500289B1500289B150068AFB70D02000000289B1500289B1500289B1500B88197850D000000289B1500289B1500289B1500B819CB4206000000B8D7654101000000289B1500289B1500289B1500289B1500289B1500289B1500681B693B10000000289B1500289B1500289B15006801BC356C000000289B15006801B2B51A000000289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B15006893797656000000289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B150068567BCB0D000000289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B150068969065150000006896906515000000289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B150068A1DE1A1A000000289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500280BE88D04000000289B1500289B1500289B1500289B1500289B1500289B1500288B65160000000068136AF615000000289B1500289B1500289B15006869B6A405000000686964491B000000289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B15002893F4B54D0A00002893F49401000000289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B150068464B560E00000068464B560E000000289B1500281345DA6F6C0000680D7B135C890100289B150028935909070000002893590907000000289B1500289B1500289B150068805E0100000000289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B15006891B7460A000000289B1500681B753B1900000068AAB2750100000068932E766A00000068932E766A000000289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B1500289B150068535BEC0300000068535BEC03000000289B1500289B1500289B150068AB63D3020000006853C5B0D9340100685399F03F3629006853C9EC02000000289B1500289B1500289B1500685DBE292D000000681D23535A000000289B1500289B150068937A7604000000C896D729510C590301000000680A0ACA4E783EDE40ADA24E07000000684A0DA2343EBEC302E1925530000000684A8537B51C9AB7BA000000684A81134D386A4502000000688A4E3AF6F0965100000000688AC6D6C1000000688A7965EB100000688A9A7535000000688A6CEB64000000688ABC7506000000688A947506000000688A947506000000A82FFB6C4416902D1A000000A857F2DB6C911D9F8DA2911B7F1C99CA01000000A8B386A706DD0E9D8592FAF7D021A6AC00000000A813BEC040D881FD5B68C1DC2126D200A8636EB0C33B4CDBDB1B755334A09B6803000000A88300E1AE76D8E8D036AF5E6DA20500A833C13C2CCF81C0D725DE6B166BB84C34010000685FC6C1EE438423CE8124E6CC91C9440300000068C7CEC11DF2B7B87BDF85EFEA1375E648A809290000000068875A40F1F03DDC7FE772F57E42CF1C8D4D480000000000C856E99FFC908FBF60C7B19F6E08627ED8440500C8A656D6C977C525041EC209F3560701310F325101000000C8C654F1ECF92E171F1F6F606CCC0ABFA5B2083251010000C88E911BDF85873B6A1EE3EA719076A7B1C26FA84AC8360566131500C80ECD0E8FFE112E2FEF8413DA0A et al ad nauseum .....

Options: ReplyQuote


Subject
Views
Written By
Posted
InnoDB backups take over 8 hours
3030
July 23, 2006 04:48PM
1867
July 25, 2006 02:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.