This piece focuses on an incident that happened last week at work that I feel all developers should read. It’s super easy, in our industry, to miss things (You try looking at a screen for 9 hours a day and get everything right), so this particular incident really stuck with me. I want this to be a friendly reminder for all Magento users to be thorough, or you could cause yourself, your employer, or your clients next hosting agency (ME) a real headache!
Last week we were tasked to upgrade a Magento website, which was hosted by another agency. To begin I had to migrate the site over onto our servers... easy enough right? Once gaining access to the previous hosting companies servers, I created a zip of all the site files and downloaded it. Then I needed to get a copy of the sites current database.
This is when it got interesting.
Blissfully unaware of the size of the database, we connected to the server thinking that after a quick mysqldump and we would be ready to start setting the site up on our servers. However, to my surprise, 20 minutes into creating the mysql dump of the database, it put the site offline.
My first thought was to cancel the mysqldump, as this was the only factor that had changed since it was last online, thankfully that brought the site back online. Seeing that when I tried to run the mysqldump the site crashed, I decided to request that the hosting provider create the file and store it on the server rather than me connecting remotely and doing it. That's when I saw it.
All 5.5GB's of it.
For those of you aren't familiar with Magento databases, or even for those of you who are, let me give you some context. The average Magento site we deal with is about 200mb.
Staggered, I downloaded it onto my computer and being a relative newbee, began to try and import it onto my localhost to see why/how it was so big. After a full day of it running in the background, I thought I would take a look at the database to see how it was going.
It was up to a log_url table (about half way), and with no end in sight it was time to go home. I setup my computer to not go to sleep, hibernate or screen lock and left it running overnight.
When I got into work the next day I was slapped in the face by a windows update that had restarted my computer after I had left! *shoot me*. At this point, I had no idea whether it had been running for 5 hours or 5 minutes after I had left last night.
Hours later while working away, letting that nightmare run in the background, I thought I would take a look at the database to see how it was coming along, only to be greeted by the same table, log_url. It hadn't moved…. or at least that's what I thought.
A suggestion from a colleague lead me to download a large file reader to see the contents of the sql file. I opened it up to have a look and there it was, the cause of it all, line after line of insert statements, thousands of them, all massive in length.
7,000 lines. 700,000 characters each. Once again, to put that in context, the average length of a book is around 500,000 characters, so each line contained enough characters to write an entire book pretty comfortably.
After further inspection we discovered that they were all Magento Logs!
The whole time I'd been under the impression that the import was stalling. It wasn't; It was trying to import these enormous insert statements. So after doing a bit of research and poking around we discovered that the Magento log cleaning had never been turned on. Magento had been tracking all of the activity on the site and storing it.
After removing all of these insert statements and saving a new copy of the database, its size dropped from 5.5GB’s down to
Wait for it..
350 mb’s. *sigh*
This is a story I had to share, for all those in development and working with Magento, you will understand that the ramifications of something so small can eventually grow to be something so big. So as a reminder to all Magento users, remember to turn on log cleaning in the backend of your Magento admin panel, because for even those of you who love to read, 7,000 books is a bit of a stretch.