Snapshot + medium sized userlists?

I finally managed to import my old vbulletin forums into my bbPress forums on my new network.

While we had managed to get snapshot to work prior to importing everything, I have noticed that now there is a fair amount of data that the plugin is getting stuck.

I only have 28k members so it's not large by any standards, but my usermeta table does have 886,443 rows, which even then isn't that large.

The problem is that snapshot hits this table to backup and just stalls out. The real issue is that it is SO SLOW doing the backup that you can virtually guarantee that there is going to be new usermeta added to the table during the download period.

Can we come up with a much faster way of backing up the tables for medium sized sites?

I can tar up the entire 256 dbs in under a second or two, yet this ran for 30 mins before dying.

*For Now I will simply skip the users and user meta tables and use snapshot for backing up the other stuff to dropbox. I do a cron tar for the real backups so I am covered.

suppose I am mostly just reporting another issue is all. Guessing this is not a tool used on edublogs.... so if they don't need it, I don't.

  • Paul

    @shawng, Greetings again. Are you running the Snapshot manual or schedule mode? How much memory are you allowing the WP system to use? Under Snapshot > Settings there is a Segment Size option. What is this set to?

    Last I looked at your site when navigating the wp-admin area it was taking 7-10 second per page load. If this is still the issue then you might have other issue effecting the performance of Snapshot.

    And really if you have the ability to run backups outside of a WordPress plugin environment you should always take it.

  • Paul

    @shawng, I just logged into your site. Forgot I still had access. I see the latest failed snapshot for the primary site. And I ran this manually. I see where it is stopping on the usermeta table. (See screen capture)

    I saw a similar issue related to the zip process when we initially released the files. In that initial release we were adding a single file at a time. Using the WordPress zip class there seems to be tremendous overhead doing this. So the code was change to process a set of the files at a time. So like themes, media, plugins, etc. Via a web interface we can't load all the files at once as there are some sites with thousands or more media files alone.

    Similar with tables. On most smallish sites we can probably dump all database tables at once and add to the zip file. But then there is the desire to show the working progress of what the plugin is doing. So that is why we have the Segment Size option. This literally translated to "Add this number of rows per table AJAX request'. So try updating the segment size to 10,000

  • Paul

    One more post on this thread before I cut out for my Saturday. I updated the Segment Size to 50,000. Then went to the latest snapshot for your primary site.

    I updated the table to just run the usermeta and user. The usermeta table finishes eventually. But then nothing. I ran this again under Firebug and appears there is a 500 error. See screenshot. Not sure if that was part of the original issue. Still investigating.

  • Paul

    @shawng, One final post. I'm actually on vacation, a short one.

    Seems there might be something with the users table. I've updated the latest failed snapshot item to just backup the jam_users table. When snapshot starts it performs the initialization ajax request. This is just like a form submit. Sends to the server the tables and files selected. The server will return the list of tables in segments per the segment size. This JSON data is what drive the UI part of snapshot to make the ajax calls back to the server for the various tables.

    Anyway, when attempting to just backup the users table I get the same 500 error every time. I'll try investigating later this evening. Didn't want to try anything in the code and leave you with a broken site. So sit tight. Will figure out a solution shortly.

  • shawng

    No worries
    I went ahead and deactivated the snapshot plugin for now as I was seeing a huge amount of errors in my system logs for running out of memory even though I assigned 256MB to the site. I could assign more but it seems pointless.

    If you need to activate it for further testing, then feel free to do what you need to do.

    I'm rebooting the server now to clear up some ram as I see I ran out of the 8GB somehow and was jumping into the swap memory which makes things really slow.

    *If you need direct server access just let me know (post your email address) and I can provide access. Just in case you need to look at either mysql or the lamp setup. I'm not much of a sysadmin, so there could be other factors involved, although I don't see any reason for having problems with the users table.

  • Paul

    @shawng, As mentioned in my prior post I've added some debug output to your version of Snapshot. Mainly this is in the table backup area. When Snapshot goes to backup a table it does this in segments. This is to prevent loading the memory of the server. At current I set the segment size to 25,000 rows per AJAX request. During each request the block of rows is pulled from the DB and appended to the end of a file. Once Snapshot has finished dumping the table it then will add the table to the zip file. Initially the zip file lives under the _backups/<item #>/ directory along with the .sql files from the DB dumps.

    During my debugging it seems to abort somewhere when adding the .sql file to the zip. For some reason this is not a trappable error. The PHP process just ends. The Snapshot plugin uses the PclZip library which is part of WordPress. I've looked through the library code and cannot yet identify where this is dying.

    Also as part of the debug output during the table segments dump, I'm writing the peak memory usage to the log files. Looks like

    2012-10-15 09:59:59: memory_limit: 256M: memory peak usage: 109.75M

    Curious that when the PclZip object is instantiated the memory used goes from 109.75M to 140.75M. This is just before the call on line 2788 os Snapshot to add the .sql file to the zip. Which never returns.

    I have a few tables on my test site (local laptop) which are close to 300M in size. Plus they have many more columns than the usermeta and/or users table. Was able to back these up using the same Snapshot code as is running on your site. Curious I then dumped your usermeta and users tables and added them to my local WP 3.5 Multisite system as non-core tables. Running the same Segment Size of 25,000 and the same exact plugin code downloaded from your site. And allowing 256M of memory I was able to backup the 2 tables without issue.

    Sorry to be so verbose on my debug. Just wanted you to know what I've ben trying to resolve. I'm about to get on a plain this afternoon. So will be out of pocket the rest of the day until the morning. Will pick up there.

  • shawng

    I appreciate the help and effort you are putting into this. Keep in mind that it could also be something server related. While I don't see any problems on the server, I do know that I have taken zero steps to optimize it as that is a bit out of my league.

    If having direct root access to the server would help just let me know. Then at the very least you could make sure it's not an external problem and you could also tar up the db to try on your local install if it would help.

    Have a safe flight.

  • Paul

    @shawng, Just poking around your MySQL/phpMyAdmin. I have two things for you.

    1. Go to Home in the left frame. Then in the right frame click on the 'Status' on the top tab bar. Then 'All Status Variables'. This shows basically how your MySQL engine is running. You will note some vars are in red. These are the ones you should investigate.

    2. On the same secondary tab bar click on the 'Advisor' tab. This is basically an analysis of what is running and suggestions on how to tune your MySQL configuration. some of these suggestions may not help since it requires you know about all the queries running on your site. Since most of the internal WordPress queries change you don't have much control.

  • shawng

    Thank you for the info. Like I said, I am a total mysql server admin rookie. I've contacted @aecnu about hiring him to tune the server. I'm guessing he will take a look into this part as well.

    I'm hoping to hear back from him and to get the job completed. I suppose it's not worth trying to troubleshoot any further until he tweaks my system. I'm guessing that after having an expert jump in and fix it that it will be night and day faster and might solve the snapshot backup problem.

    My bottom line is that I am just trying to hire a sysadmin to come in and tweak my server to get it working to its potential. I am open to hiring anyone qualified for the job and I know pretty much everyone here is more than qualified.

    I hate having such a big fully dedicated server and having it so doggedly slow. It drives me up a wall and I'm determined to get it fixed. :slight_smile:

  • aecnu

    Greetings shawng,

    Thank you for all the input and looking over this ticket and at the advice from Paul I understand some issues revolve around the MySQL server as well.

    I will certainly be addressing many items within the server of course, but outside the realm of WPMU Dev.

    It looks by my schedule that I will not get a start on this until the 24th but then I will be all over it due to I will be out of the country from the 18th thru the 23rd.

    I am closing this ticket for now at least until after we see what if any issues remain after the tweaking so to speak.

    Cheers, Joe

  • shawng

    No, actually it turns out I screwed up. I have the wrong server running my websites, meaning it's an older really slow server. I have so many servers sitting around the house, that when I lost a few hard drives on my webserver, I took the wrong server into the datacenter to replace it.

    Bottom line is the server itself is to old and slow to bother spending the time and resources to optimize it.

    I will be taking in a much faster server to replace the server there in the next week or so. My datacenter is local to me, it's just a matter of finding the time which I have very little of.

    Once that is done, then I'll have Aaron take a peek again and I'll get back to you.

  • shawng

    I am now on a VERY FAST dedicated server and the problem remains. Snapshot is an absolute waste of time and resources when used with medium-large sized websites. At least in its current state, it is totally 100% unusable. I was checking back to see if anything had changed to fix the problem, but it seems not... sad...

    In the meantime, I simply purchased backupbuddy and it works great. I'd prefer to stick with a wpmu plugin instead, but at this point I can't.

  • aecnu

    Greetings shawng,

    Thank you for the feedback in which I am certain that Paul is interested in knowing.

    For backups I have my servers send the backups off site to a server on a completely different continent using the facilities in the WHM/cPanel and it works like a charm.

    For specifically databases when needed, I use Navicat which in my opinion is the greatest database management software on the planet, and no that is not an affiliate link ... lol

    I will send a flag over to @Paul so he will be notified of your additional input.

    Cheers, Joe

  • aecnu

    Greetings shawng,

    Oh yes Navicat is amazing and I hope you have had a chance to work with it since it has been a few days since we have heard form you last :slight_smile:

    I love its drag and drop of databases from server to server and so much more - it is indeed an incredible piece of software and like nothing i have ever seen before.

    Let me know how you like it and if you enjoy it as much as I do.

    Look forward to hearing from you in any event.

    Sincerely, Joe

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.