For those of you not familiar with the concept of underclocking: it's the opposite of overclocking, that is, you don't speed up CPU but instead slow it down..
What for?
Ask the underclockers, I'm totally not sure. Actually, hanging around the Web these days leaves a feeling that nearly every website out there must have been underclocked, but most of the time it's about tons of unnecessary images, megabytes of javascript (of which hardly a hundred kilobytes gets actually executed), and all that. In this post I will, however, tell you about a server-side approach to underclocking, with a help of our good old friend - the MySQL Database Server.
Today I had a nice chat in my client's development telegram channel. The two other devs, R** and V**, were making a switch of the old image API app to a new MySQL server. A couple of days before that, we have discussed a plan, it was as dumb as possible, just as I like it. Super-simple clear steps that a five-year-old can make. Switch to readonly mode (stop uploads), dump DB, restore the dump on the new server, update database connection details, turn off readonly mode. What could possibly go wrong?
Nothing. Except that it did go wrong. The app that I'm talking about, is a really ancient piece of what is gently called "legacy". Once the app was back to normal again, we noticed a significant slowdown on every page that made use of images. Before that point, I never got to that app and/or its database. I logged in to the MySQL console, and started investigating, at the same point chatting with colleagues.
Me: Is that really important that the tables are MyISAM? It's 2018, you know.. There are dozens of queries in queue waiting for table-level locks.
R**: Are they MyISAM? Really?
Me: Yes.. Any objection against converting them to InnoDB? With the current state of the website, with all those tons of Gateway Timeouts, it's not going to make it worse if I do it right now..
. minutes later:
Me: Nah, it didn't help a lot.. But, looking at the SHOW PROCESSLIST output, I see something weird. What, do you think, this query does? SELECT LAST_INSERT_ID() FROM images?
R**: ehh... Gets you the last AUTO_INCREMENT id from images table?
Me: Let's play another good news bad news joke.. Good news: you're right, it gets you the last AUTO_INCREMENT id. Bad news: it's not for table, it's for the session. Worse news: this query gets you the last AUTO_INCREMENT id and does it exactly as many times as there are rows in the images table. how many are there?
R**: about 8mln. #@%&! It's sending 8mln rows on every image upload, through the network!
Me: Bingo! 8mln rows, with one and the same integer value in all of them.
R**: Ouch... Aaaand... Before today, it was not an issue. Because the database was on the same server as the application..
Me: Exactly, it used the loopback interface, and now it's using ehternet, which, apparently, doesn't have a super good bandwidth. We don't have a gigabit channel between servers, do we?
R**: No, it's 100 Mbit
Me: Are you fixing the query, BTW?
R**: yeah, man, deploying it...
Another 10 minutes later, problem is gone, performance is back to normal.
What conclusions can one make from this story?
I can think of two at least:
First: never underestimate legacy code. The ways it can move to bite you in the ass, are mysterious.
Second: if you're working with MySQL or another RDBMS, learn SQL, learn the specific SQL dialect you're using and learn how to trouble shoot issues. In this case, I did not need to look in the PHP code at all in order to help my fellow colleagues out. You can also generalize this principle as "you have to know the tools you're using".
Happy optimizing, folks! Comments appreciated!
Nomad PHP
