Slow Import Speed with Scalability Pro

WP Intense Q & ACategory: Scalability ProSlow Import Speed with Scalability Pro
LDN asked 1 month 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.

2 Answers
Dave Hilditch Staff answered 1 month 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:

  1. Confirm you have the indexes created
  2. Tell me which import tool you’re using
  3. Provide wp-admin access details in the Edit Question hidden area
  4. 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:

Dan N. replied 1 month 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 H. Staff replied 1 month ago

If you install the plugin mentioned here, how many image sizes does it tell you that you have? If you reduce the active image sizes to just the 3 or 4 you really need, how fast does your import proceed?

Dave H. Staff replied 1 month 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.

Dan N. replied 1 month 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 1 month ago

Ok – send the credentials to [email protected] – 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.

LDN replied 4 weeks ago

Sent information on how to access to the email address you left in your last comment.

Dan N. replied 4 weeks 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 3 weeks 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 !

Dave H. Staff replied 3 weeks ago

The login you provided doesn’t work

LDN replied 3 weeks 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.

LDN replied 3 weeks 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.

Dave Hilditch Staff answered 3 weeks 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:

There is a guide for fixing this here:

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:

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%’;

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:

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:

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 2 weeks 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: [email protected] ?

Dave H. Staff replied 2 weeks 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 2 weeks ago

Sorry Dave, I didn’t even realize you had responded. I just sent you an email with everything you need.

LDN replied 1 week ago

Hey Dave, just checking in to see if there are any additional updates. Thanks 🙂

Dave H. Staff replied 5 days 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 3 days 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 2 days 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:

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.

Dave H. Staff replied 2 days 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.

We use cookies

We use cookies for various things on our site, including our on-site chat bubble (if you use it), our comment forms (if you use them) and for session handling (if you log in).

Other than that, we use cookies to identify where traffic came from to help us understand which traffic turns into sales, we use a cookie for Google Analytics traffic analysis and we use a cookie to customise adverts for our own products we think you'll be interested in.

You can read more detail in our privacy policy page. Please click 'Accept' or 'Decline' to continue.