20
ErvinSabic
222d

I had spent the last year working on a online store power by woocommerce with over 100k products from various suppliers. This online store utilized a custom API that would take the various formats that suppliers offer their inventory in and made them consistent. Now everything was going swimmingly initially, but then I began adding more and more products using a plug-in called WP all import. I reached around 100k products and the site would take up to an entire minute to load sometimes timing out. I got desperate so I installed several caching plugins, but to no avail this did not help me. The site was originally only supposed to take three to four months but ended up taking an entire year. Then, just yesterday I found out what went wrong and why this woocommerce website with all of these optimizations was still taking anywhere from 60 to 90 seconds to load, or just timing out entirely. I had initially thought that I needed a beefier server so I moved it to a high CPU digitalocean VM. While this did help a little bit, the site was still very slow and now I had very high CPU usage RAM usage and high disk IO. I was seriously stumped the Apache process was using a high amount of CPU and IO along with MYSQL as well. It wasn't until I started digging deeper into the database that I actually found out what the issue was. As I was loading the site I would run 'show process list' in the SQL terminal, I began to notice a very significant load time for one of the tables, so I went to go and check it out. What I did was I ran a select all query on that particular table just to see how full it was and SQL returned a error saying that I had exceeded the maximum packet size. So I was like okay what the fuck...
So I exited my SQL and re-entered it this time with a higher packet size. I ran a query that would count how many rows were in this particular table and the number came out to being in the millions. I was surprised, and what's worse is that this table belong to a plugin that I had attempted to use early in the development process to cache the site. The plugin was deactivated but apparently it had left PHP files within the wp content directory outside of the actual plugin directory, so it's still executing scripts even though the plugin itself was disabled. Basically every time I would change anything on the site, it would recache the whole thing, and it didn't delete any old records. So 100k+ products caching on saves with no garbage collection... You do the math, it's gonna be a heavy ass database. Not only that but it was serialized data, so when it did pull this metric shit ton of spaghetti from the database, PHP then had to deserialize it. Hence the high ass CPU load. I had caching enabled on the MySQL end of things so that ate the ram. I was really desperate to get this thing running.
Honest to God the main reason why this website took so long was because the load times made it miserable to work on. I just thought that the hardware that I had the site on was inadequate. I had initially started the development on a small Linux VM which apparently wasn't enough, which is why I moved it to digitalocean which also seemed to not be enough, so from there I moved to a dedicated server which still didn't seem to be enough. I was probably a few more 60-second wait times or timeouts from recommending a server cluster to my client who I know would not be willing to purchase it. The client who I promised this site to have completed in 3 months and has waited a year. Seriously, I would tell people the struggles that I would go through with this particular site and they would just tell me to just drop the site; just take the money, just take the loss. I refused to, this was really the only thing that was kicking my ass. I present myself as this high-and-mighty developer like I'm just really good at what I do but then I have this WordPress site that's just beating the shit out of me for a year. It was a very big learning experience and it was also very humbling as well, it made me realize that I really don't know as much as I think I might. It was evidence that there is still so much more to learn out there, I did learn a lot from that experience especially about optimizing websites the different types of methods to do that particular lonely on the server side and I'll be able to utilize this knowledge in the future.
I guess the moral of the story is, never really give up. Ultimately things might get so bad that you're running on hopes and dreams. Those experiences are generally the most humbling. Now I can finally present the site that I am basically a year late on to the client who will be so happy that I did not give up on the project entirely. I'll have experienced this feeling of pure euphoria, and help the small business significantly grow their revenue. Helping others is very fulfilling for me, even at my own expense.

Anyways, gonna stop ranting. Running out of characters. If you're still here... Ty for reading :')

Comments
  • 7
    As long as we learn its never a complete loss.

    And when something is slow and there is a database involved, start looking at it, its very often involved.

    And finally, always keep track on any third party plugins, some are very good but some are not worth the space on the harddrive.

    And to often some do mot work well with other plugins.
  • 0
    @Voxera well the issue here was that this plugin probably would have been fantastic on a much smaller scale. Ya know, when it doesn't hurt to recache a whole site cause it's like 6 pages.

    What threw me off was the fact that I had never worked with this large of a site before. So I just kinda expected these things eat all of the CPU and RAM. I didn't look into the disk IO untill much much later...
    Inexperience I guess. After all these years, I'm still a noob.
  • 5
    And this kids is why WordPress and PHP are evil.
  • 3
    Also kudos to you for pushing through and solving the issue. We've all been there.
  • 0
    Great story and valuable wisdom shared.

    FX [ Tries to work out how big a database he would need for 32794.95 to the power of 545 records.. ]

    Google says first it can't do higher than 500, and then:

    > According to my calculations, 32794.95

    > raised to the power of 500 would equal Infinity.

    So, I'll need a bigger HD then..
  • 3
    Code that store serialized data in DB is evil.

    CMS have too many plugins doing just that. I die a bit inside every time I see that.
  • 0
    Which caching plugin was the issue? I might have the same problem. One day my app server went from 500ms per request on average to 3s with the majority of slowness centered on WooCommerce. I suspect it’s the WP Rocket plugin but I can’t seem to prove it. And it does have files outside its plugin directory.
  • 3
    The moral of the story is not "never give up" it's "stop using WP"
Your Job Suck?
Get a Better Job
Add Comment