Thread: [Automysqlbackup-users] Mysqlbackup fails an brings the server down
Automatic MySQL Backup
Brought to you by:
wipe_out
From: Ward L. <wa...@lo...> - 2011-06-02 07:44:47
|
Hello, I currently use automysqlbackup to backup 10databases. There is one big database that contains a table with +- 780.000records. When mysqlbackup executes it fails (on that table) and brings my websites down (maybe the load is so high that apache processes can't answer in time). Looks like there are a lot of query's executed at the moment of backup and maybe the query's are locking each other. I also get the following by email: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `video` at row: 745740 I think this are 2seperate problems: - Mysql backup script failing - Website's are down (because the use of too many resources?) Do you guys have any ideas how to fix this? Can you let the script run in low priority mode, so that it doesn't eat the resources and does not lock any tables ? Thanks! Ward |
From: Wipe_Out <wip...@us...> - 2011-06-02 17:07:50
|
Hi, It could be an issue with the server running out of memory while dumping that large table.. The --opt switch is used which should disable memory buffering when its dumping the database but perhaps its something else.. It could also be that the table is being locked for a long time which is causing a problem for your website.. Are you able to change the MySQL server settings? eg buffers etc.. An alternative would be to not use the script to backup that database and instead use the mysqlhotcopy command which may work better for a larger database thats always active.. Does seem strange because On 2 June 2011 08:44, Ward Loockx <wa...@lo...> wrote: > Hello, > > I currently use automysqlbackup to backup 10databases. There is one big > database that contains a table with +- 780.000records. When mysqlbackup > executes it fails (on that table) and brings my websites down (maybe the > load is so high that apache processes can't answer in time). Looks like > there are a lot of query's executed at the moment of backup and maybe the > query's are locking each other. > > I also get the following by email: > > mysqldump: Error 2013: Lost connection to MySQL server during query when > dumping table `video` at row: 745740 > > I think this are 2seperate problems: > > - Mysql backup script failing > - Website's are down (because the use of too many resources?) > > > Do you guys have any ideas how to fix this? Can you let the script run in > low priority mode, so that it doesn't eat the resources and does not lock > any tables ? > > Thanks! > Ward > > > > > ------------------------------------------------------------------------------ > Simplify data backup and recovery for your virtual environment with > vRanger. > Installation's a snap, and flexible recovery options mean your data is > safe, > secure and there when you need it. Data protection magic? > Nope - It's vRanger. Get your free trial download today. > http://p.sf.net/sfu/quest-sfdev2dev > _______________________________________________ > Automysqlbackup-users mailing list > Aut...@li... > https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users > > |
From: Ward L. <wa...@lo...> - 2011-06-02 17:25:50
|
Hello, Thanks for the reply. I have control over the mysql settings. I'll also take a look at mysqlhotcopy. 2011/6/2 Wipe_Out <wip...@us...> > Hi, > > It could be an issue with the server running out of memory while dumping > that large table.. The --opt switch is used which should disable memory > buffering when its dumping the database but perhaps its something else.. It > could also be that the table is being locked for a long time which is > causing a problem for your website.. > > Are you able to change the MySQL server settings? eg buffers etc.. > > An alternative would be to not use the script to backup that database and > instead use the mysqlhotcopy command which may work better for a larger > database thats always active.. > > > > Does seem strange because > > On 2 June 2011 08:44, Ward Loockx <wa...@lo...> wrote: > >> Hello, >> >> I currently use automysqlbackup to backup 10databases. There is one big >> database that contains a table with +- 780.000records. When mysqlbackup >> executes it fails (on that table) and brings my websites down (maybe the >> load is so high that apache processes can't answer in time). Looks like >> there are a lot of query's executed at the moment of backup and maybe the >> query's are locking each other. >> >> I also get the following by email: >> >> mysqldump: Error 2013: Lost connection to MySQL server during query when >> dumping table `video` at row: 745740 >> >> I think this are 2seperate problems: >> >> - Mysql backup script failing >> - Website's are down (because the use of too many resources?) >> >> >> Do you guys have any ideas how to fix this? Can you let the script run in >> low priority mode, so that it doesn't eat the resources and does not lock >> any tables ? >> >> Thanks! >> Ward >> >> >> >> >> ------------------------------------------------------------------------------ >> Simplify data backup and recovery for your virtual environment with >> vRanger. >> Installation's a snap, and flexible recovery options mean your data is >> safe, >> secure and there when you need it. Data protection magic? >> Nope - It's vRanger. Get your free trial download today. >> http://p.sf.net/sfu/quest-sfdev2dev >> _______________________________________________ >> Automysqlbackup-users mailing list >> Aut...@li... >> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >> >> > > > ------------------------------------------------------------------------------ > Simplify data backup and recovery for your virtual environment with > vRanger. > Installation's a snap, and flexible recovery options mean your data is > safe, > secure and there when you need it. Discover what all the cheering's about. > Get your free trial download today. > http://p.sf.net/sfu/quest-dev2dev2 > _______________________________________________ > Automysqlbackup-users mailing list > Aut...@li... > https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users > > |
From: Ward L. <wa...@lo...> - 2011-06-06 06:32:35
|
I see that mysqlhotcopy is only available for myIsam and currently we use innoDB tables.. Do you have another solution? 2011/6/2 Ward Loockx <wa...@lo...> > Hello, > > Thanks for the reply. I have control over the mysql settings. I'll also > take a look at mysqlhotcopy. > > > 2011/6/2 Wipe_Out <wip...@us...> > >> Hi, >> >> It could be an issue with the server running out of memory while dumping >> that large table.. The --opt switch is used which should disable memory >> buffering when its dumping the database but perhaps its something else.. It >> could also be that the table is being locked for a long time which is >> causing a problem for your website.. >> >> Are you able to change the MySQL server settings? eg buffers etc.. >> >> An alternative would be to not use the script to backup that database and >> instead use the mysqlhotcopy command which may work better for a larger >> database thats always active.. >> >> >> >> Does seem strange because >> >> On 2 June 2011 08:44, Ward Loockx <wa...@lo...> wrote: >> >>> Hello, >>> >>> I currently use automysqlbackup to backup 10databases. There is one big >>> database that contains a table with +- 780.000records. When mysqlbackup >>> executes it fails (on that table) and brings my websites down (maybe the >>> load is so high that apache processes can't answer in time). Looks like >>> there are a lot of query's executed at the moment of backup and maybe the >>> query's are locking each other. >>> >>> I also get the following by email: >>> >>> mysqldump: Error 2013: Lost connection to MySQL server during query when >>> dumping table `video` at row: 745740 >>> >>> I think this are 2seperate problems: >>> >>> - Mysql backup script failing >>> - Website's are down (because the use of too many resources?) >>> >>> >>> Do you guys have any ideas how to fix this? Can you let the script run in >>> low priority mode, so that it doesn't eat the resources and does not lock >>> any tables ? >>> >>> Thanks! >>> Ward >>> >>> >>> >>> >>> ------------------------------------------------------------------------------ >>> Simplify data backup and recovery for your virtual environment with >>> vRanger. >>> Installation's a snap, and flexible recovery options mean your data is >>> safe, >>> secure and there when you need it. Data protection magic? >>> Nope - It's vRanger. Get your free trial download today. >>> http://p.sf.net/sfu/quest-sfdev2dev >>> _______________________________________________ >>> Automysqlbackup-users mailing list >>> Aut...@li... >>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>> >>> >> >> >> ------------------------------------------------------------------------------ >> Simplify data backup and recovery for your virtual environment with >> vRanger. >> Installation's a snap, and flexible recovery options mean your data is >> safe, >> secure and there when you need it. Discover what all the cheering's about. >> >> Get your free trial download today. >> http://p.sf.net/sfu/quest-dev2dev2 >> _______________________________________________ >> Automysqlbackup-users mailing list >> Aut...@li... >> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >> >> > |
From: Wipe_Out <wip...@us...> - 2011-06-06 09:21:24
|
What data is in the table thats failing? --Just alphanumeric text fields? --or something else? How big is the table? (MB) How big are the individual rows of data? On 6 June 2011 07:32, Ward Loockx <wa...@lo...> wrote: > I see that mysqlhotcopy is only available for myIsam and currently we use > innoDB tables.. > > Do you have another solution? > > > 2011/6/2 Ward Loockx <wa...@lo...> > >> Hello, >> >> Thanks for the reply. I have control over the mysql settings. I'll also >> take a look at mysqlhotcopy. >> >> >> 2011/6/2 Wipe_Out <wip...@us...> >> >>> Hi, >>> >>> It could be an issue with the server running out of memory while dumping >>> that large table.. The --opt switch is used which should disable memory >>> buffering when its dumping the database but perhaps its something else.. It >>> could also be that the table is being locked for a long time which is >>> causing a problem for your website.. >>> >>> Are you able to change the MySQL server settings? eg buffers etc.. >>> >>> An alternative would be to not use the script to backup that database and >>> instead use the mysqlhotcopy command which may work better for a larger >>> database thats always active.. >>> >>> >>> >>> Does seem strange because >>> >>> On 2 June 2011 08:44, Ward Loockx <wa...@lo...> wrote: >>> >>>> Hello, >>>> >>>> I currently use automysqlbackup to backup 10databases. There is one big >>>> database that contains a table with +- 780.000records. When mysqlbackup >>>> executes it fails (on that table) and brings my websites down (maybe the >>>> load is so high that apache processes can't answer in time). Looks like >>>> there are a lot of query's executed at the moment of backup and maybe the >>>> query's are locking each other. >>>> >>>> I also get the following by email: >>>> >>>> mysqldump: Error 2013: Lost connection to MySQL server during query when >>>> dumping table `video` at row: 745740 >>>> >>>> I think this are 2seperate problems: >>>> >>>> - Mysql backup script failing >>>> - Website's are down (because the use of too many resources?) >>>> >>>> >>>> Do you guys have any ideas how to fix this? Can you let the script run >>>> in low priority mode, so that it doesn't eat the resources and does not lock >>>> any tables ? >>>> >>>> Thanks! >>>> Ward >>>> >>>> >>>> >>>> >>>> ------------------------------------------------------------------------------ >>>> Simplify data backup and recovery for your virtual environment with >>>> vRanger. >>>> Installation's a snap, and flexible recovery options mean your data is >>>> safe, >>>> secure and there when you need it. Data protection magic? >>>> Nope - It's vRanger. Get your free trial download today. >>>> http://p.sf.net/sfu/quest-sfdev2dev >>>> _______________________________________________ >>>> Automysqlbackup-users mailing list >>>> Aut...@li... >>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>> >>>> >>> >>> >>> ------------------------------------------------------------------------------ >>> Simplify data backup and recovery for your virtual environment with >>> vRanger. >>> Installation's a snap, and flexible recovery options mean your data is >>> safe, >>> secure and there when you need it. Discover what all the cheering's >>> about. >>> >>> Get your free trial download today. >>> http://p.sf.net/sfu/quest-dev2dev2 >>> _______________________________________________ >>> Automysqlbackup-users mailing list >>> Aut...@li... >>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>> >>> >> > > > ------------------------------------------------------------------------------ > Simplify data backup and recovery for your virtual environment with > vRanger. > Installation's a snap, and flexible recovery options mean your data is > safe, > secure and there when you need it. Discover what all the cheering's about. > Get your free trial download today. > http://p.sf.net/sfu/quest-dev2dev2 > _______________________________________________ > Automysqlbackup-users mailing list > Aut...@li... > https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users > > |
From: Ward L. <wa...@lo...> - 2011-06-06 09:41:36
|
It contains VARCHARS, INT, timestamp, DATETIME. This is the size info about the table Data 815,0 MB Index 226,0 MB Total 1,041,0 MB The individual size of a row = 815.000Kb / 777263 records = 1.05kb / record 2011/6/6 Wipe_Out <wip...@us...> > What data is in the table thats failing? > --Just alphanumeric text fields? > --or something else? > > How big is the table? (MB) > How big are the individual rows of data? > > > > On 6 June 2011 07:32, Ward Loockx <wa...@lo...> wrote: > >> I see that mysqlhotcopy is only available for myIsam and currently we use >> innoDB tables.. >> >> Do you have another solution? >> >> >> 2011/6/2 Ward Loockx <wa...@lo...> >> >>> Hello, >>> >>> Thanks for the reply. I have control over the mysql settings. I'll also >>> take a look at mysqlhotcopy. >>> >>> >>> 2011/6/2 Wipe_Out <wip...@us...> >>> >>>> Hi, >>>> >>>> It could be an issue with the server running out of memory while dumping >>>> that large table.. The --opt switch is used which should disable memory >>>> buffering when its dumping the database but perhaps its something else.. It >>>> could also be that the table is being locked for a long time which is >>>> causing a problem for your website.. >>>> >>>> Are you able to change the MySQL server settings? eg buffers etc.. >>>> >>>> An alternative would be to not use the script to backup that database >>>> and instead use the mysqlhotcopy command which may work better for a larger >>>> database thats always active.. >>>> >>>> >>>> >>>> Does seem strange because >>>> >>>> On 2 June 2011 08:44, Ward Loockx <wa...@lo...> wrote: >>>> >>>>> Hello, >>>>> >>>>> I currently use automysqlbackup to backup 10databases. There is one big >>>>> database that contains a table with +- 780.000records. When mysqlbackup >>>>> executes it fails (on that table) and brings my websites down (maybe the >>>>> load is so high that apache processes can't answer in time). Looks like >>>>> there are a lot of query's executed at the moment of backup and maybe the >>>>> query's are locking each other. >>>>> >>>>> I also get the following by email: >>>>> >>>>> mysqldump: Error 2013: Lost connection to MySQL server during query >>>>> when dumping table `video` at row: 745740 >>>>> >>>>> I think this are 2seperate problems: >>>>> >>>>> - Mysql backup script failing >>>>> - Website's are down (because the use of too many resources?) >>>>> >>>>> >>>>> Do you guys have any ideas how to fix this? Can you let the script run >>>>> in low priority mode, so that it doesn't eat the resources and does not lock >>>>> any tables ? >>>>> >>>>> Thanks! >>>>> Ward >>>>> >>>>> >>>>> >>>>> >>>>> ------------------------------------------------------------------------------ >>>>> Simplify data backup and recovery for your virtual environment with >>>>> vRanger. >>>>> Installation's a snap, and flexible recovery options mean your data is >>>>> safe, >>>>> secure and there when you need it. Data protection magic? >>>>> Nope - It's vRanger. Get your free trial download today. >>>>> http://p.sf.net/sfu/quest-sfdev2dev >>>>> _______________________________________________ >>>>> Automysqlbackup-users mailing list >>>>> Aut...@li... >>>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>>> >>>>> >>>> >>>> >>>> ------------------------------------------------------------------------------ >>>> Simplify data backup and recovery for your virtual environment with >>>> vRanger. >>>> Installation's a snap, and flexible recovery options mean your data is >>>> safe, >>>> secure and there when you need it. Discover what all the cheering's >>>> about. >>>> >>>> Get your free trial download today. >>>> http://p.sf.net/sfu/quest-dev2dev2 >>>> _______________________________________________ >>>> Automysqlbackup-users mailing list >>>> Aut...@li... >>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>> >>>> >>> >> >> >> ------------------------------------------------------------------------------ >> Simplify data backup and recovery for your virtual environment with >> vRanger. >> Installation's a snap, and flexible recovery options mean your data is >> safe, >> secure and there when you need it. Discover what all the cheering's about. >> Get your free trial download today. >> http://p.sf.net/sfu/quest-dev2dev2 >> _______________________________________________ >> Automysqlbackup-users mailing list >> Aut...@li... >> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >> >> > > > ------------------------------------------------------------------------------ > Simplify data backup and recovery for your virtual environment with > vRanger. > Installation's a snap, and flexible recovery options mean your data is > safe, > secure and there when you need it. Discover what all the cheering's about. > Get your free trial download today. > http://p.sf.net/sfu/quest-dev2dev2 > _______________________________________________ > Automysqlbackup-users mailing list > Aut...@li... > https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users > > |
From: Wipe_Out <wip...@us...> - 2011-06-06 11:09:46
|
Try adding something like --max_allowed_packet=100M to the OPT= line in the backup script and see if that helps things along.. Also is there anything being reported in the logs? On 6 June 2011 10:41, Ward Loockx <wa...@lo...> wrote: > It contains VARCHARS, INT, timestamp, DATETIME. > > This is the size info about the table > Data 815,0 MB Index 226,0 MB Total 1,041,0 MB > The individual size of a row = 815.000Kb / 777263 records = 1.05kb / record > > > 2011/6/6 Wipe_Out <wip...@us...> > >> What data is in the table thats failing? >> --Just alphanumeric text fields? >> --or something else? >> >> How big is the table? (MB) >> How big are the individual rows of data? >> >> >> >> On 6 June 2011 07:32, Ward Loockx <wa...@lo...> wrote: >> >>> I see that mysqlhotcopy is only available for myIsam and currently we use >>> innoDB tables.. >>> >>> Do you have another solution? >>> >>> >>> 2011/6/2 Ward Loockx <wa...@lo...> >>> >>>> Hello, >>>> >>>> Thanks for the reply. I have control over the mysql settings. I'll also >>>> take a look at mysqlhotcopy. >>>> >>>> >>>> 2011/6/2 Wipe_Out <wip...@us...> >>>> >>>>> Hi, >>>>> >>>>> It could be an issue with the server running out of memory while >>>>> dumping that large table.. The --opt switch is used which should disable >>>>> memory buffering when its dumping the database but perhaps its something >>>>> else.. It could also be that the table is being locked for a long time which >>>>> is causing a problem for your website.. >>>>> >>>>> Are you able to change the MySQL server settings? eg buffers etc.. >>>>> >>>>> An alternative would be to not use the script to backup that database >>>>> and instead use the mysqlhotcopy command which may work better for a larger >>>>> database thats always active.. >>>>> >>>>> >>>>> >>>>> Does seem strange because >>>>> >>>>> On 2 June 2011 08:44, Ward Loockx <wa...@lo...> wrote: >>>>> >>>>>> Hello, >>>>>> >>>>>> I currently use automysqlbackup to backup 10databases. There is one >>>>>> big database that contains a table with +- 780.000records. When mysqlbackup >>>>>> executes it fails (on that table) and brings my websites down (maybe the >>>>>> load is so high that apache processes can't answer in time). Looks like >>>>>> there are a lot of query's executed at the moment of backup and maybe the >>>>>> query's are locking each other. >>>>>> >>>>>> I also get the following by email: >>>>>> >>>>>> mysqldump: Error 2013: Lost connection to MySQL server during query >>>>>> when dumping table `video` at row: 745740 >>>>>> >>>>>> I think this are 2seperate problems: >>>>>> >>>>>> - Mysql backup script failing >>>>>> - Website's are down (because the use of too many resources?) >>>>>> >>>>>> >>>>>> Do you guys have any ideas how to fix this? Can you let the script run >>>>>> in low priority mode, so that it doesn't eat the resources and does not lock >>>>>> any tables ? >>>>>> >>>>>> Thanks! >>>>>> Ward >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> ------------------------------------------------------------------------------ >>>>>> Simplify data backup and recovery for your virtual environment with >>>>>> vRanger. >>>>>> Installation's a snap, and flexible recovery options mean your data is >>>>>> safe, >>>>>> secure and there when you need it. Data protection magic? >>>>>> Nope - It's vRanger. Get your free trial download today. >>>>>> http://p.sf.net/sfu/quest-sfdev2dev >>>>>> _______________________________________________ >>>>>> Automysqlbackup-users mailing list >>>>>> Aut...@li... >>>>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>>>> >>>>>> >>>>> >>>>> >>>>> ------------------------------------------------------------------------------ >>>>> Simplify data backup and recovery for your virtual environment with >>>>> vRanger. >>>>> Installation's a snap, and flexible recovery options mean your data is >>>>> safe, >>>>> secure and there when you need it. Discover what all the cheering's >>>>> about. >>>>> >>>>> Get your free trial download today. >>>>> http://p.sf.net/sfu/quest-dev2dev2 >>>>> _______________________________________________ >>>>> Automysqlbackup-users mailing list >>>>> Aut...@li... >>>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>>> >>>>> >>>> >>> >>> >>> ------------------------------------------------------------------------------ >>> Simplify data backup and recovery for your virtual environment with >>> vRanger. >>> Installation's a snap, and flexible recovery options mean your data is >>> safe, >>> secure and there when you need it. Discover what all the cheering's >>> about. >>> Get your free trial download today. >>> http://p.sf.net/sfu/quest-dev2dev2 >>> _______________________________________________ >>> Automysqlbackup-users mailing list >>> Aut...@li... >>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>> >>> >> >> >> ------------------------------------------------------------------------------ >> Simplify data backup and recovery for your virtual environment with >> vRanger. >> Installation's a snap, and flexible recovery options mean your data is >> safe, >> secure and there when you need it. Discover what all the cheering's about. >> Get your free trial download today. >> http://p.sf.net/sfu/quest-dev2dev2 >> _______________________________________________ >> Automysqlbackup-users mailing list >> Aut...@li... >> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >> >> > > > ------------------------------------------------------------------------------ > Simplify data backup and recovery for your virtual environment with > vRanger. > Installation's a snap, and flexible recovery options mean your data is > safe, > secure and there when you need it. Discover what all the cheering's about. > Get your free trial download today. > http://p.sf.net/sfu/quest-dev2dev2 > _______________________________________________ > Automysqlbackup-users mailing list > Aut...@li... > https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users > > |
From: Ward L. <wa...@lo...> - 2011-06-08 06:31:28
|
I added the max-allowed-packet line, but looks like it fails again (not sure,because I didn't receive the log mail yet). My server blocks because it has a load of 13,94 during the backup. Is there a way to do the backup in low-priority to minimize the load? 2011/6/6 Wipe_Out <wip...@us...> > Try adding something like --max_allowed_packet=100M to the OPT= line in > the backup script and see if that helps things along.. > > Also is there anything being reported in the logs? > > > > On 6 June 2011 10:41, Ward Loockx <wa...@lo...> wrote: > >> It contains VARCHARS, INT, timestamp, DATETIME. >> >> This is the size info about the table >> Data 815,0 MB Index 226,0 MB Total 1,041,0 MB >> The individual size of a row = 815.000Kb / 777263 records = 1.05kb / >> record >> >> >> 2011/6/6 Wipe_Out <wip...@us...> >> >>> What data is in the table thats failing? >>> --Just alphanumeric text fields? >>> --or something else? >>> >>> How big is the table? (MB) >>> How big are the individual rows of data? >>> >>> >>> >>> On 6 June 2011 07:32, Ward Loockx <wa...@lo...> wrote: >>> >>>> I see that mysqlhotcopy is only available for myIsam and currently we >>>> use innoDB tables.. >>>> >>>> Do you have another solution? >>>> >>>> >>>> 2011/6/2 Ward Loockx <wa...@lo...> >>>> >>>>> Hello, >>>>> >>>>> Thanks for the reply. I have control over the mysql settings. I'll also >>>>> take a look at mysqlhotcopy. >>>>> >>>>> >>>>> 2011/6/2 Wipe_Out <wip...@us...> >>>>> >>>>>> Hi, >>>>>> >>>>>> It could be an issue with the server running out of memory while >>>>>> dumping that large table.. The --opt switch is used which should disable >>>>>> memory buffering when its dumping the database but perhaps its something >>>>>> else.. It could also be that the table is being locked for a long time which >>>>>> is causing a problem for your website.. >>>>>> >>>>>> Are you able to change the MySQL server settings? eg buffers etc.. >>>>>> >>>>>> An alternative would be to not use the script to backup that database >>>>>> and instead use the mysqlhotcopy command which may work better for a larger >>>>>> database thats always active.. >>>>>> >>>>>> >>>>>> >>>>>> Does seem strange because >>>>>> >>>>>> On 2 June 2011 08:44, Ward Loockx <wa...@lo...> wrote: >>>>>> >>>>>>> Hello, >>>>>>> >>>>>>> I currently use automysqlbackup to backup 10databases. There is one >>>>>>> big database that contains a table with +- 780.000records. When mysqlbackup >>>>>>> executes it fails (on that table) and brings my websites down (maybe the >>>>>>> load is so high that apache processes can't answer in time). Looks like >>>>>>> there are a lot of query's executed at the moment of backup and maybe the >>>>>>> query's are locking each other. >>>>>>> >>>>>>> I also get the following by email: >>>>>>> >>>>>>> mysqldump: Error 2013: Lost connection to MySQL server during query >>>>>>> when dumping table `video` at row: 745740 >>>>>>> >>>>>>> I think this are 2seperate problems: >>>>>>> >>>>>>> - Mysql backup script failing >>>>>>> - Website's are down (because the use of too many resources?) >>>>>>> >>>>>>> >>>>>>> Do you guys have any ideas how to fix this? Can you let the script >>>>>>> run in low priority mode, so that it doesn't eat the resources and does not >>>>>>> lock any tables ? >>>>>>> >>>>>>> Thanks! >>>>>>> Ward >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> ------------------------------------------------------------------------------ >>>>>>> Simplify data backup and recovery for your virtual environment with >>>>>>> vRanger. >>>>>>> Installation's a snap, and flexible recovery options mean your data >>>>>>> is safe, >>>>>>> secure and there when you need it. Data protection magic? >>>>>>> Nope - It's vRanger. Get your free trial download today. >>>>>>> http://p.sf.net/sfu/quest-sfdev2dev >>>>>>> _______________________________________________ >>>>>>> Automysqlbackup-users mailing list >>>>>>> Aut...@li... >>>>>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> ------------------------------------------------------------------------------ >>>>>> Simplify data backup and recovery for your virtual environment with >>>>>> vRanger. >>>>>> Installation's a snap, and flexible recovery options mean your data is >>>>>> safe, >>>>>> secure and there when you need it. Discover what all the cheering's >>>>>> about. >>>>>> >>>>>> Get your free trial download today. >>>>>> http://p.sf.net/sfu/quest-dev2dev2 >>>>>> _______________________________________________ >>>>>> Automysqlbackup-users mailing list >>>>>> Aut...@li... >>>>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>>>> >>>>>> >>>>> >>>> >>>> >>>> ------------------------------------------------------------------------------ >>>> Simplify data backup and recovery for your virtual environment with >>>> vRanger. >>>> Installation's a snap, and flexible recovery options mean your data is >>>> safe, >>>> secure and there when you need it. Discover what all the cheering's >>>> about. >>>> Get your free trial download today. >>>> http://p.sf.net/sfu/quest-dev2dev2 >>>> _______________________________________________ >>>> Automysqlbackup-users mailing list >>>> Aut...@li... >>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>> >>>> >>> >>> >>> ------------------------------------------------------------------------------ >>> Simplify data backup and recovery for your virtual environment with >>> vRanger. >>> Installation's a snap, and flexible recovery options mean your data is >>> safe, >>> secure and there when you need it. Discover what all the cheering's >>> about. >>> Get your free trial download today. >>> http://p.sf.net/sfu/quest-dev2dev2 >>> _______________________________________________ >>> Automysqlbackup-users mailing list >>> Aut...@li... >>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>> >>> >> >> >> ------------------------------------------------------------------------------ >> Simplify data backup and recovery for your virtual environment with >> vRanger. >> Installation's a snap, and flexible recovery options mean your data is >> safe, >> secure and there when you need it. Discover what all the cheering's about. >> Get your free trial download today. >> http://p.sf.net/sfu/quest-dev2dev2 >> _______________________________________________ >> Automysqlbackup-users mailing list >> Aut...@li... >> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >> >> > > > ------------------------------------------------------------------------------ > Simplify data backup and recovery for your virtual environment with > vRanger. > Installation's a snap, and flexible recovery options mean your data is > safe, > secure and there when you need it. Discover what all the cheering's about. > Get your free trial download today. > http://p.sf.net/sfu/quest-dev2dev2 > _______________________________________________ > Automysqlbackup-users mailing list > Aut...@li... > https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users > > |
From: Wipe_Out <wip...@us...> - 2011-06-10 21:57:13
|
Try the "nice" command to change the priority when you start the script.. Although I'm not sure this will solve it, its worth a try.. On 8 June 2011 07:31, Ward Loockx <wa...@lo...> wrote: > I added the max-allowed-packet line, but looks like it fails again (not > sure,because I didn't receive the log mail yet). My server blocks because it > has a load of 13,94 during the backup. Is there a way to do the backup in > low-priority to minimize the load? > > 2011/6/6 Wipe_Out <wip...@us...> > >> Try adding something like --max_allowed_packet=100M to the OPT= line in >> the backup script and see if that helps things along.. >> >> Also is there anything being reported in the logs? >> >> >> >> On 6 June 2011 10:41, Ward Loockx <wa...@lo...> wrote: >> >>> It contains VARCHARS, INT, timestamp, DATETIME. >>> >>> This is the size info about the table >>> Data 815,0 MB Index 226,0 MB Total 1,041,0 MB >>> The individual size of a row = 815.000Kb / 777263 records = 1.05kb / >>> record >>> >>> >>> 2011/6/6 Wipe_Out <wip...@us...> >>> >>>> What data is in the table thats failing? >>>> --Just alphanumeric text fields? >>>> --or something else? >>>> >>>> How big is the table? (MB) >>>> How big are the individual rows of data? >>>> >>>> >>>> >>>> On 6 June 2011 07:32, Ward Loockx <wa...@lo...> wrote: >>>> >>>>> I see that mysqlhotcopy is only available for myIsam and currently we >>>>> use innoDB tables.. >>>>> >>>>> Do you have another solution? >>>>> >>>>> >>>>> 2011/6/2 Ward Loockx <wa...@lo...> >>>>> >>>>>> Hello, >>>>>> >>>>>> Thanks for the reply. I have control over the mysql settings. I'll >>>>>> also take a look at mysqlhotcopy. >>>>>> >>>>>> >>>>>> 2011/6/2 Wipe_Out <wip...@us...> >>>>>> >>>>>>> Hi, >>>>>>> >>>>>>> It could be an issue with the server running out of memory while >>>>>>> dumping that large table.. The --opt switch is used which should disable >>>>>>> memory buffering when its dumping the database but perhaps its something >>>>>>> else.. It could also be that the table is being locked for a long time which >>>>>>> is causing a problem for your website.. >>>>>>> >>>>>>> Are you able to change the MySQL server settings? eg buffers etc.. >>>>>>> >>>>>>> An alternative would be to not use the script to backup that database >>>>>>> and instead use the mysqlhotcopy command which may work better for a larger >>>>>>> database thats always active.. >>>>>>> >>>>>>> >>>>>>> >>>>>>> Does seem strange because >>>>>>> >>>>>>> On 2 June 2011 08:44, Ward Loockx <wa...@lo...> wrote: >>>>>>> >>>>>>>> Hello, >>>>>>>> >>>>>>>> I currently use automysqlbackup to backup 10databases. There is one >>>>>>>> big database that contains a table with +- 780.000records. When mysqlbackup >>>>>>>> executes it fails (on that table) and brings my websites down (maybe the >>>>>>>> load is so high that apache processes can't answer in time). Looks like >>>>>>>> there are a lot of query's executed at the moment of backup and maybe the >>>>>>>> query's are locking each other. >>>>>>>> >>>>>>>> I also get the following by email: >>>>>>>> >>>>>>>> mysqldump: Error 2013: Lost connection to MySQL server during query >>>>>>>> when dumping table `video` at row: 745740 >>>>>>>> >>>>>>>> I think this are 2seperate problems: >>>>>>>> >>>>>>>> - Mysql backup script failing >>>>>>>> - Website's are down (because the use of too many resources?) >>>>>>>> >>>>>>>> >>>>>>>> Do you guys have any ideas how to fix this? Can you let the script >>>>>>>> run in low priority mode, so that it doesn't eat the resources and does not >>>>>>>> lock any tables ? >>>>>>>> >>>>>>>> Thanks! >>>>>>>> Ward >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> ------------------------------------------------------------------------------ >>>>>>>> Simplify data backup and recovery for your virtual environment with >>>>>>>> vRanger. >>>>>>>> Installation's a snap, and flexible recovery options mean your data >>>>>>>> is safe, >>>>>>>> secure and there when you need it. Data protection magic? >>>>>>>> Nope - It's vRanger. Get your free trial download today. >>>>>>>> http://p.sf.net/sfu/quest-sfdev2dev >>>>>>>> _______________________________________________ >>>>>>>> Automysqlbackup-users mailing list >>>>>>>> Aut...@li... >>>>>>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> ------------------------------------------------------------------------------ >>>>>>> Simplify data backup and recovery for your virtual environment with >>>>>>> vRanger. >>>>>>> Installation's a snap, and flexible recovery options mean your data >>>>>>> is safe, >>>>>>> secure and there when you need it. Discover what all the cheering's >>>>>>> about. >>>>>>> >>>>>>> Get your free trial download today. >>>>>>> http://p.sf.net/sfu/quest-dev2dev2 >>>>>>> _______________________________________________ >>>>>>> Automysqlbackup-users mailing list >>>>>>> Aut...@li... >>>>>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>>> ------------------------------------------------------------------------------ >>>>> Simplify data backup and recovery for your virtual environment with >>>>> vRanger. >>>>> Installation's a snap, and flexible recovery options mean your data is >>>>> safe, >>>>> secure and there when you need it. Discover what all the cheering's >>>>> about. >>>>> Get your free trial download today. >>>>> http://p.sf.net/sfu/quest-dev2dev2 >>>>> _______________________________________________ >>>>> Automysqlbackup-users mailing list >>>>> Aut...@li... >>>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>>> >>>>> >>>> >>>> >>>> ------------------------------------------------------------------------------ >>>> Simplify data backup and recovery for your virtual environment with >>>> vRanger. >>>> Installation's a snap, and flexible recovery options mean your data is >>>> safe, >>>> secure and there when you need it. Discover what all the cheering's >>>> about. >>>> Get your free trial download today. >>>> http://p.sf.net/sfu/quest-dev2dev2 >>>> _______________________________________________ >>>> Automysqlbackup-users mailing list >>>> Aut...@li... >>>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>>> >>>> >>> >>> >>> ------------------------------------------------------------------------------ >>> Simplify data backup and recovery for your virtual environment with >>> vRanger. >>> Installation's a snap, and flexible recovery options mean your data is >>> safe, >>> secure and there when you need it. Discover what all the cheering's >>> about. >>> Get your free trial download today. >>> http://p.sf.net/sfu/quest-dev2dev2 >>> _______________________________________________ >>> Automysqlbackup-users mailing list >>> Aut...@li... >>> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >>> >>> >> >> >> ------------------------------------------------------------------------------ >> Simplify data backup and recovery for your virtual environment with >> vRanger. >> Installation's a snap, and flexible recovery options mean your data is >> safe, >> secure and there when you need it. Discover what all the cheering's about. >> Get your free trial download today. >> http://p.sf.net/sfu/quest-dev2dev2 >> _______________________________________________ >> Automysqlbackup-users mailing list >> Aut...@li... >> https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users >> >> > > > ------------------------------------------------------------------------------ > EditLive Enterprise is the world's most technically advanced content > authoring tool. Experience the power of Track Changes, Inline Image > Editing and ensure content is compliant with Accessibility Checking. > http://p.sf.net/sfu/ephox-dev2dev > _______________________________________________ > Automysqlbackup-users mailing list > Aut...@li... > https://lists.sourceforge.net/lists/listinfo/automysqlbackup-users > > |