Akeeba Backup for Joomla!

Saturday, 16 October 2010 03:55 CDT

rbradbury

The backup for my site is failing due to the number of rows in one database table - it has 5,000,000 rows. My host (Heart Internet) limits max_join_size to 900,000 rows. How do I modify the Akeeba Backup script to include $db->sql_query("SET OPTION SQL_BIG_SELECTS=1"

Thanks!

Bob

Sunday, 17 October 2010 05:40 CDT

nicholas

Thank you for the heads up! I will include this query to be automatically executed upon starting the dump in version 3.1.1 due to release in a few days. Meanwhile, open administrator/components/com_akeeba/akeeba/engines/dump/native.php and add this block of code after line 580:

		// Try to enforce SQL_BIG_SELECTS option
		$db->setQuery('SET OPTION SQL_BIG_SELECTS=1');
		$db->query();

Save the file and retry backing up. It should be now able to back up your database without any problem.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Sunday, 17 October 2010 22:13 CDT

rbradbury

Nicolas - thanks very much for your reply. Have edited the script as you suggest and got the following error -

syntax error, unexpected T_VARIABLE, expecting T_FUNCTION in xxxx/joomla/administrator/components/com_akeeba/akeeba/engines/dump/native.php on line 583

Bob

Monday, 18 October 2010 06:37 CDT

nicholas

I think you had the wrong version installed. I did my modification on the files of the current developer's release. You can try installing the latest developer's release and apply the mod in there.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Wednesday, 20 October 2010 00:08 CDT

rbradbury

Thanks for the suggestion. Have used Akeeba Backup Pro 271 and still get the same error message. Can you confirm this is the latest version?

Thanks & regards

Wednesday, 20 October 2010 07:51 CDT

nicholas

I've just uploaded SVN276 which contains the fix and you won't have to edit it. Just install and use :)

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Wednesday, 20 October 2010 10:17 CDT

rbradbury

Nicholas - many thanks. That's service with a capital 'S' :cool: Will try it again.

edit - can now confirm the backup was successful. :D Just one observation - the backup was run as a CRON job. However, the completion email reports "Akeeba Backup has completed backing up your site using the front-end backup feature...."

Bob

Thursday, 21 October 2010 06:26 CDT

nicholas

You're welcome!

Regarding the email, all backups which do not come from the traditional back-end interface are technically front-end backups (they run outside of the Joomla! back-end application), hence the body text of the email message :)

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Thursday, 21 October 2010 23:57 CDT

user8399

Nicholas,

I've just purchased Backup Pro and my web host is also Heart Internet. I noticed during a test restoration that my SQL database dump was only a few hundred kilobytes, when it should be around 144MB. Based on this thread, I installed SVN276 over the top of the latest stable version and started a backend backup. I still don't seem to have a full copy of the database within the /installation directory. Please could you help me to examine my log to see where the problem might be.

Cheers.

Friday, 22 October 2010 07:27 CDT

nicholas

Can you ZIP and attach the backup log? Without it I can't know what is going on :)

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Sunday, 24 October 2010 22:23 CDT

user8399

Sorry, I thought I had.##Download Log##

Monday, 25 October 2010 09:55 CDT

nicholas

You have a lot of older backup archives on a directory named "backup" on your site's root.I'd suggest excluding that. Go to Akeeba Backup's Control Panel page, click on the File and Directories Exclusion button. When the page loads, find the "backup" entry in the left hand pane and click the second and third icon on its left. Then click Back and run a new backup. That should do the trick.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Thursday, 28 October 2010 01:17 CDT

user8399

Thanks for the reply but I can't see how that would be included in the backup anyway.Hearts file structure goes like this:

FTP Root Parent
[ul]cgi-bin
backup
public_html (Siteroot)
[indent]images
modules
components
.
.
.[/ul][/indent]
Joomla sits in the public_html folder, which is the siteroot, with its subfolders underneath. Anything in public_html is served via the web browser. Anything above public_html is inaccessible except via FTP or a web application.

My Akeeba Backup profile is configured to store backups in the Rootparent/backup directory but not to include that directory in the backup itself. It automatically includes everything under the public_html siteroot folder (although I've excluded another large directory from there). I've also included a couple of other folders like cgi-bin from the Rootparent folder.

So, I'm not sure how you meant for me to exclude a folder from the backup that is not a part of the backup profile. If it is trying to backup that folder, why?

Thanks for your continuing help. I'll just add that I used the last version of Joomlapack on this site (uninstalled before installing Akeeba Backup Pro) and it backed up the database without any problems.

Thursday, 28 October 2010 12:42 CDT

nicholas

I think I was looking at someone else's backup log... :/ Your last backup log file indicates a successful backup without any error occuring.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Monday, 01 November 2010 23:31 CDT

user8399

Even so, there was something wrong because the database was not all there. I fiddled around with some more settings and I had some success. The database backed up correctly.

So for those of you on Heart Internet, using Akeeba Backup Pro with large SQL databases, here's my settings for making it work. I'm using the SVN release that Nicholas mentioned earlier in this thread that supports Big Selects (I've had this issue on another component). Nicholas, when do you think this will reach a stable release?

Here are the config settings:
MySQL Compatability >> Default
Generate extended INSERTs >> tick
Max packet size for extended INSERTs >> 128kb
Dump PROCEDUREs, FUNCTIONs and TRIGGERs >> tick
Size for split SQL dump files >> 2MB
Number of rows per batch >> 100
Large directory threshold >> 250
Dereference symlinks >> tick
Part size for split archives >> 149.10MB
Chunk size for large files processing >> 2MB
Big file threshold >> 3MB
Minimum execution time >> 2secs
Maximum execution time >> 10secs
Execution time bias >> 75%

Tuesday, 02 November 2010 00:17 CDT

nicholas

It has reached production yesterday :) It's version 3.1.2. Enjoy!

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Tuesday, 02 November 2010 01:55 CDT

rbradbury

Here are the config settings:

MySQL Compatability >> Default

Generate extended INSERTs >> tick

Max packet size for extended INSERTs >> 128kb

Dump PROCEDUREs, FUNCTIONs and TRIGGERs >> tick

Size for split SQL dump files >> 2MB

Number of rows per batch >> 100

Large directory threshold >> 250

Dereference symlinks >> tick

Part size for split archives >> 149.10MB

Chunk size for large files processing >> 2MB

Big file threshold >> 3MB

Minimum execution time >> 2secs

Maximum execution time >> 10secs

Execution time bias >> 75%

For what it's worth - my settings are as above except -
Size for split SQL dump files >> 5MB
Number of rows per batch >> 1000
Dereference symlinks >> untick
Chunk size for large files processing >> 5MB
Big file threshold >> 10MB
The default settings also worked...the above was an attempt to speed up the archive. Peak memory usage now shows 23MB instead of 6MB. Archive time was reduced by 40%

Tuesday, 02 November 2010 13:17 CDT

nicholas

Yup, it's a trial and error thing. Increasing the part size for SQL dump will make the database backup faster as it has to spend much less time creating new files. Touching up the large file threshold allows compressing even more files, reducing the archive size and writing it faster. As you discovered, all of these have a huge impact in the memory consumption, which is why you won't find a lot of detail about them in the manual.

If you want to further optimize the backup time you can try decreasing the minimum execution time (on most servers you can get it to 0.75 seconds and on some servers even down to 0) and increasing the maximum execution time and the bias. Ideally, the maximum execution time should be about 5 seconds less than PHP's max_execution_time setting and the bias should be able to be upped to 90% or even 95%.

Of course, touching these settings may break your backup. I'd suggest creating a copy of the backup profile every time you have found a faster, working setting so that you can roll back to those "safe values" if you try a different set of options which cause the backup to fail :)

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Tuesday, 02 November 2010 14:36 CDT

rbradbury

Thanks for the pointers, Nicholas - I'll give them a try. Incidentally, your new release works fine in coping with the large number of table rows in mySQL dB

Wednesday, 03 November 2010 01:41 CDT

nicholas

I am glad I got that fixed :) The huge table backup was one of my priorities.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Support