Slow Import Speed with Scalability Pro
. asked 4 years ago
I am over 50,000 products now and it takes hours to upload 100 products.. we\'re talking nearly 30 seconds or more for one import. It used to only take a couple seconds to import a product.
I am running a dedicated server, tons of space, 6 cores so the issue is not my server or server speed. The database has increased but I thought that with Scalability that even as the site got larger, I would not see a decrease in speed.
When I migrated over to the new server it was not like this but as the database has grown, import time has gotten worse and worse and worse.Importing is a MAJOR drag and it just plain sucks. I have aspirations to continue to add items to the database but adding items just leads to extreme load times and sometimes timing the import out as well.
I need some help. Please help.
Dave Hilditch Staff answered 4 years ago
Hi – yes, Scalability Pro should mean that your imports don’t slow down – it doesn’t technically speed them up, it stops them from slowing down the more products you add.
If you’re not seeing that behaviour:
- Confirm you have the indexes created
- Tell me which import tool you’re using
- Provide wp-admin access details in the Edit Question hidden area
- Provide a URL for your import job so I can take a look
Another thing that could be causing your imports to get slower over time is this – if you have been adding third-party plugins or theme features, there’s a chance that additional ‘image sizes’ have been created.
I’ve seen installs with 20 or 30+ image sizes. For products, you need 2 sizes – the full size image and the thumbnail.
I’ll soon be adding a feature to Scalability Pro to restrict image sizes when running product imports to automatically gain this boost, but in the meantime check this article:
https://www.superspeedyplugins.com/2017/05/31/speeding-wordpress-imports-datafeedr-wp-import/
LDN replied 4 years ago Hey Dave, just looking for an update on resolving my import issue. As mentioned before, I apologize that you weren’t able to get in initially, but I believe I have fixed that with the last email I sent your way. Let me know if that works for you. Thanks for reviewing this.
LDN replied 4 years ago Sorry, just corrected it. Hadn’t sent you the token. It should work fine now. Thanks for the reply ! š Emailed it back to the email you provided.
Dave H. Staff replied 4 years ago The login you provided doesn’t work
LDN replied 4 years ago Hey, Dave.. following up again since my login records do not show that you’ve visited the site yet with your access to it. I have no doubt that you’re very busy so please accept this as a kindly reminder. Just looking forward to hopefully getting things back on track with better importing speed with your assistance.
Can you provide an estimate on when you will be able to take a look ?
Thanks !
Dan N. replied 4 years ago Hey Dave, just wanted to follow up since there hasn’t been a follow up since about 5 days ago. Let me know if you have any issues with the access information I provided that was sent to the email you gave me in this thread. Thanks !
LDN replied 4 years ago Sent information on how to access to the email address you left in your last comment.
Dave H. Staff replied 4 years ago Ok – send the credentials to credentials@superspeedyplugins.com – they get deleted from there after a month. Let me know when you’ve sent them. I’m not working tomorrow, but I’ll take a look Thursday and figure this out for you.
Dan N. replied 4 years ago Hey Dave:
I have uploaded the plugin and this is what it tells me and I think it makes sense since basically all of the images are remote: You currently have 4 thumbnails registered. It means, if you upload an image, it’ll generate 4 duplicates of that image. Select the sizes from the list below that you want to disable from generating.
As for wp-admin, there is no area where I can edit the original question. I’ve gone back to the section where I can see my questions and this one does not show up. However, on the first question I asked when I was very new here I did see an edit button but when I clicked it, nothing happened.
As for the new question, regarding the import speed.. I can’t edit anything unfortunately so there’s no way to get you the access that you’re requesting privately.
Dave H. Staff replied 4 years ago And failing that, can you provide credentials (FTP and wp-admin) so I can take a look? You can answer with a private answer here, or edit the original question and enter the info in the hidden area.
Dave H. Staff replied 4 years ago If you install the plugin mentioned here, how many image sizes does it tell you that you have? https://www.superspeedyplugins.com/2017/05/31/speeding-wordpress-imports-datafeedr-wp-import/ If you reduce the active image sizes to just the 3 or 4 you really need, how fast does your import proceed?
Dan N. replied 4 years ago Hi Dave, thanks for your reply.
I just created the indexes today after your suggestion.
I’m using the woozone plugin (also known as Wzone).
I do not see the “edit question” hidden area. (I think I remember seeing it when I started this question but the only thing I can see now is post comment and a “your answer” box beneath it with a submit button.
After I created the indexes in Scalability Pro, one product import took 50 seconds and clearly that is still way too long. According to the woozone direct import log, there were 2 products that took 33 seconds each to import but the other 5 I also imported took as I mentioned before 50 seconds or more each to import.
Importing right now is painfully long.
Dave Hilditch Staff answered 4 years ago
I’ll list here the things I found and how I fixed this.
First thing I noticed when logging in was you have this warning in Scalability Pro:
https://i.imgur.com/DawJ4Xs.png
There is a guide for fixing this here: https://www.superspeedyplugins.com/knowledgebase/how-do-i-fix-the-performance-warning-of-too-many-options-set-to-autoload/
I installed the Query Executioner plugin for you and ran this SQL to investigate your options table (SQL pulled directly from article above – and $options = wp_options but SQL Executioner will change the prefix for your site config accordingly):
select left(option_name,15) option_name, count( * ) total
from $options where autoload = ‘yes’
group by left(option_name,15)
order by total desc;
From that, I see this:
https://i.imgur.com/UUAFL4Q.png
You can see in that image, 52000 entries in your options table with a key starting: _transient_wc_p
I then ran this:
select option_name, option_value
from $options where autoload = ‘yes’
and option_name like ‘_transient_wc_p%’;
https://i.imgur.com/EG0LB4x.png
So – something is creating an option in your options table for every product in your database it seems. Not ideal, since this info is loading not just on every front-end page, but on every back-end load of WordPress – this includes cron and ajax, so it includes every sub-batch in your imports.
I then took a look at your image sizes, as referenced in this article: https://www.superspeedyplugins.com/2017/05/31/speeding-wordpress-imports-datafeedr-wp-import/
But you’ve dealt with this already, you only have 4 image sizes.
Then I tried running an actual import using WooZone. I see this error message when I searched for ‘Prams’ with the intent of adding some prams to your baby section:
https://i.imgur.com/uPFBgVj.png
That error is indicating you’ve exceeded your Amazon API limits. It may well be this causing your slowdown.
Could you please help me further in understanding this?
Can you give me a URL where there is an import I can attempt?
What I want to do is run the import, and while it’s running I’ll run "show full processlist;" against your database to find the queries that WooZone is running.
It may well be that there is another index I need to add to Scalability Pro, specifically for WooZone, so if you could provide a URL where I can run an import then I’ll discover that.
LDN replied 4 years ago Hey Dave, thanks for your work and for the update. Let me know when you’re ready for me to try another run on my end using the plugin.
Dave H. Staff replied 4 years ago Ok – I’ll have to alter the stats function to return something I guess. It checks its import speed after every row – this is why it’s running so slowly, it counts how many it has left to do and how many are done after every item imported. This calculation should ideally be done when the import is finished, not per row.
LDN replied 4 years ago Hey Dave, no it’s not working now. Tried to import 5 products and it says they are all invalid. Same method used before to import products. But wanted to confirm that it is “not” working now to import.
LDN replied 4 years ago Thanks Dave. I just saw this message. I will check this out later tonight and see if things are working smoothly, faster etc.
Dave H. Staff replied 4 years ago Dave H. Staff replied 4 years ago Ok – can you please test your WooZone/AZone Imports now and let me know if: A) They still work, B) They are far faster
I discovered that WooZone is doing a whole lot of unnecessary work every row it imports – basically it recalculates everything to tell you how fast it’s done it, and yet calculating this stuff is what is slowing it down…
I had to alter the actual WooZone code – I have a note of the alterations I made and will write these up into the case study. If this works for you and you like the speed, we can send the optimisation instructions from the case study to the Azone plugin authors so they can improve their speed.
LDN replied 4 years ago I think I’ve successfully added the key. You’ll see my response by email. Thanks so much for your patience !
Dave H. Staff replied 4 years ago I did see some slow queries before with query monitor and show processlist, in particular WooZone has some very badly written SQL queries. It may be possible to optimise them a bit using indexes, but really it looked like I need to rewrite them to run fast.
Dave H. Staff replied 4 years ago No unfortunately not! You sent me your public key, that’s not how SSH works – if you can’t make an SSH key, I just need SSH access to be able to log onto the server – there’s a guide here: https://www.superspeedyplugins.com/2019/02/22/how-to-add-the-wp-intense-ssh-key-to-your-server/
But when I say SSH access, I mean to be able to log onto your server, not just FTP, but to be able to see and interact with the services – mysql, php, nginx, apache, redis, whatever you’ve got – in particular I need to access mysql to enable the slow query log. It might be your host doesn’t provide SSH access? If so, I’ll need to clone your environment to my own so I can actually work on this.
LDN replied 4 years ago Hey Dave, hoping the new information on SSH access is what you need. Let me know if it works for you or not.
Dave H. Staff replied 4 years ago Replied to your email, waiting on SSH access so I can enable the slow query log and find out the query groups that are performing badly here.
LDN replied 4 years ago Hey Dave, just checking in to see if there are any additional updates. Thanks š
LDN replied 4 years ago Sorry Dave, I didn’t even realize you had responded. I just sent you an email with everything you need.
Dave H. Staff replied 4 years ago Yes please – or you can add the screenshots here with Imgur. I specifically need an import I can run and ideally re-run multiple times so I can spot what is slowing down WooZone.
LDN replied 4 years ago Thanks for your help and looking into this. I can give you a url but I’d want to specify the category where it will go. Can I send you some screenshots through so that the products imported aren’t trying to match up with Amazon. I try as much to situate the imports so that they go into the category I have predefined. Awaiting our reply. Should I send the screenshots to this email: credentials@superspeedyplugins.com ?
