From 0 to 5.000.000 monthly views

#php #mysql #javascript #optimisation

This is not an article about how to reach 5 000 000 monthly views. This is an article on how we managed to design, develop, optimize and maintain (3.5 years) such a website.

I have published this article on my small blog: webinuse.com. I am trying to be more useful to society by publishing articles, based on my knowledge and my experience.

Beginning

My company (I was not employed there yet) was approached by a client who asked us to build a simple news/magazine portal that will employ 1-2 people and will be self-sustained (bootstrapped).

After initial talks they agreed to start that project and a colleague of mine was assigned to this project. The client had only one demand: it had to be done from scratch using PHP, MySQL, and “simple JavaScript.” The reason for this was that some client’s cousin knew “some of those” so he will be able to help them if they need to change anything.

This colleague, let’s call him John, started from scratch and finished around 60-70% of the project when I joined the team.

After few weeks we were able to present them (he already employed 2 people) with simple CMS and portal and after few more tweaks, we were up and running. (We had a bunch of meetings and corrections before that, but that is irrelevant to the story.)

This was a simple website, simple CMS for creating posts and galleries. They were able to insert images, change users and manage photos. As per the client’s request (cousin’s advice), we hosted that website on OVH and they started to publish articles and do their job.

First problems

After several months they noticed that website is pretty slow so they called us again (this was out of scope for his cousin and it was becoming a really serious project), and ask us to optimize the website a little bit, regarding loading times. Due to their background, they were able to gain a pretty good audience at that time and they over-grown their server’s capacity. They were around 150-200 visitor at any time reading articles.

Naturally, we decided that the best way to optimize it is to scale it vertically, so we migrated to a much better server. And everything worked well for a while. They also asked us to add a bunch of new functionalities like:

they needed to control every position, 62 positions, on homepage (which article and how long will it stay on certain position);
they wanted to be able to drag and drop article where they wanted it to be
they wanted to see how many users are there at any given time in their dashboard
they wanted to add a bunch of shortcodes for different types of content, etc.

Since my colleague was a lead dev and CEO and co-founder I was, kind of, forced to listen to him and we developed as he required. He would listen to my “advice” sometimes, but most of the time he would ask me to do as he was planning. Honestly, I needed that job so I never objected too much. Also, he was doing 90% of backend and I was doing 100% frontend and 10% backend.

After we have finished all those functionalities, I just felt like every functionality is made of glass and it will not hold that long. So, I was right.

No. 1 mistake was that for every functionality he used an infinite number of DB queries which made the application pretty slow. Instead of manipulating some data using PHP or JS he just pulled it from DB.

No. 2 mistake was that, due to all those positions on the homepage, we had around 30 DB queries just to load the homepage, which was awful.

No. 3 mistake was that he used PDO but he never actually used prepared statements, check the code below.

//Instead of using PDO like this

    $stmt = $pdo->prepare("SELECT * FROM post WHERE slug = ?");
    $stmt->execute([$slugString]);

    //He used it like this
    $stmt = $pdo->query("SELECT * FROM post WHERE slug = $slugString")

Because of this last mistake, our application was prone to hackers’ attacks and we really struggled with it.

No. 4 mistake was that we did not really pay attention to any type of loading times, so you can imagine how satisfied the client was.

At this point we were homepage was loading for ~5-7secs. But the real problem emerged when this portal was reporting on a big event in the whole country and reading numbers spiked in few seconds, we went from 500-600 to 1300 in like few minutes. Everything came to a halt, literally. The client called, he was furious, but there was really nothing we could do at this point in time.

Middle phase

Fast forward a few months later, my colleague left the company and sold it to me and my friend so we went to our client and we promised to make up for his mistakes and improve the app as much as we could, we even accepted to do a pro-bono project for a client (project value was pretty high, but we did not care that much, we just wanted to continue our relationship with this client because he was high paying, really nice guy, it was/is a pleasure to work with him).

So we started working. My first goal was to make the homepage load everything with only one or two queries. In order to do that I created a new table where only the homepage was stored in order of those positions that I’ve mentioned earlier. Instantly loading times were down to 2 seconds and the client was super satisfied. After that, I implemented cache for everything on the website, except for queries. So, I managed to lower loading times to 800ms-1.5secs.

Also, I had to implement better image compression and everything, but now front was working as expected, now I had to deal with CMS. The problem was this backend code was not commented at all, even if it was commented it was something like:

//Get everything from gallery table
    $stmt = $pdo->query("SELECT * FROM gallery");

As you can see it was nothing really useful, so I had to go line by line and try to figure out what was happening. I was lucky that I’ve been there from the beginning so most of the code I could figure out pretty quickly.

Loading times were a problem on the backend, so I had to deal with that also. At the time he said we should use AJAX for everything to load, so basically you would get skeleton then we would query for data. And most of the times data was dependent on some other data, so we had a loading queue and it was slow as hell. So instead of that, I used PHP to load everything (server-side rendering) and now the application was loading pretty fast.

Most of the “advanced” functionalities had to be overwritten, but everything worked, eventually.

Today

After all these improvements, we changed hosting, again, and now we have a pretty powerful server. Also, now, we have a backup server with the same specification as the main server, which can be used either as backup or we can use DB on one server and files on the other so we distribute the load better.

A few weeks ago we had one event that lead to 5000 concurrent users on the server for several hours. Everything worked pretty well and without serious “hiccups”, but we saw that 5000 is nearly our limit with current infrastructure, so we had a meeting and we decided to improve everything.

According to statistics every hour we have around 7000-8000 page views and 5 000 000 – 5 500 000 page views per month.

We did some calculations and we decided that we will implement Redis, first. We already did some testing with the homepage and load times are not really different on a lower number of visitors but when numbers are high we can see differences in loading times and server performance regarding CPU, I/O, and RAM usage.

Even though RAM is more “occupied” with Redis, CPU and I/O are performing much better, so I think we will continue with Redis. Also, we are considering re-writing everything to something more performant, maybe MERN or Laravel with MySQL and MongoDB. MongoDB would be used for articles. We would store the entire article and accompanying info for easier fetching.

As I said I would really like to hear your opinion on anything regarding my article. Thank you for reading.

If you have any questions or anything you can find me on my Twitter, or you can read some of the older articles like console.log in JavaScript – Tips & Tricks