DEV Community: Taufiq Abdullah

When Disk Space is Not Enough: A Lesson in Challenging MySQL Database Cleansing

Taufiq Abdullah — Wed, 25 Feb 2026 21:31:11 +0000

Database maintenance is rarely a walk in the park, especially when dealing with high-volume transactional data. Recently, We was tasked with cleansing a MySQL database that had ballooned to 220GB. The mission was simple: remove data from 2018 to 2023 across four tables to reclaim space and improve performance.

However, as any Dev knows, "simple" tasks often hide complex traps. Here is how we navigated storage limits and technical hurdles to get the job done.

The Challenge: The 200GB "Elephant" in the Room

Out of the 220GB total size, one single table accounted for 200GB. This table was heavy with transactional records and—the real culprit—BLOB attachments. Our goal was to keep only the data from 2024 onwards.

Phase 1: The "Clone and Swap" Strategy (The Ideal Plan)

Our first approach was a classic "blue-green" table migration:

Clone: Create a clone of the existing tables with a new name.
Filter: Insert only the 2024–present data into the new tables.
Swap: Rename the existing tables and swap them with the new ones.
Cleanup: Drop the old tables once verified.

The Pros: It’s safe. If something goes wrong, the original data remains untouched as a "checkpoint."
The Result in Dev: Flawless. Despite the time required for backup and restore, it worked exactly as expected.

The Production Reality Check

When we moved to production, we hit a brick wall. The production server had a 500GB total capacity, but only 100GB of free space remained. Trying to clone a 200GB table into a 100GB hole is mathematically impossible. We needed a Plan B.

Phase 2: The "Chunked Delete" Strategy (The Safe Bet?)

We pivoted to a more granular approach: deleting data directly from the existing tables. To prevent long-running queries or locking the database, we followed these steps:

Deleted data in year-by-year ranges (2018, 2019, etc.).
Used Bash scripts to automate chunked deletions with LIMIT clauses to keep the system responsive and avoid "hanging" the DB.

The Trap: The `OPTIMIZE TABLE` Paradox

The deletion worked, but MySQL doesn't automatically shrink the file size on disk after a DELETE (due to the "High Water Mark" and data fragmentation). To actually reclaim the 100GB+ of space, we needed to run OPTIMIZE TABLE.

The Critical Oversight: OPTIMIZE TABLE works by creating a temporary copy of the table. Even though we had deleted half the data, the operation still required enough free space to rewrite the entire table. Once again, our 100GB of free space was the bottleneck. We were stuck.

Phase 3: The "Off-Site Surgery" (The Final Solution)

Desperate times call for creative architecture. We decided to perform the "heavy lifting" on a separate environment.

Export: We dumped the massive 200GB table and moved it to a secondary server with enough storage.
Cleanse & Shrink: On the secondary server, we performed the deletions and ran OPTIMIZE TABLE.
The Result: The optimized table dropped significantly below 100GB.
Import: We transferred the now-compacted table back to the production server under a new name.
The Final Swap: Since the new table was now small enough to fit in the 100GB free space, we successfully swapped it with the original and dropped the old 200GB giant.

Key Takeaways for us

Free Space is Your Best Friend: Always check your df -h before planning a migration. Most MySQL maintenance operations (like OPTIMIZE or ALTER) require at least 2x the size of your largest table in free space.
Optimize with Caution: Remember that OPTIMIZE TABLE is not an "in-place" fix; it is a rebuild.
Think Outside the Box: If your local disk is full, use a secondary staging server to process the data before bringing it back home.

Database cleansing is as much about managing infrastructure as it is about writing SQL. Have you ever faced a storage "deadlock" like this? I’d love to hear how you handled it in the comments!

'No More Space' and Can't connect SSH to the Server

Taufiq Abdullah — Tue, 01 Mar 2022 05:07:06 +0000

Hello, I want to share my Experiment of this 'Little Nightmare' about insufficient Storage Space in running live Server, in this case I use Google Cloud Services : Compute Engine and Ubuntu as the Operating System.

Note :
this post contain some Story, Trial & Error, Skip to the Solution Section if you want to see the Answer.

The Problem

I've got message from my friend, he sent me this Error Message :

and he also can't execute some ssh command on the Server, it always return this :

cd /va-bash: cannot create temp file for here-document: No space left on device

then, i told him to run this command for check directory file size :

du -h --max-depth=1

this is example of the output from command above, picture taken from Twitter

at this moment, he can running rm -rf to cleanup some Files / Directory and free up some space.

now the Server running well and the website up again.

The next day, he sent message to me, the disk on the server running out of space again, and he asked me to increase the size of the disk.

Everything getting worse here, and I've made my mistake, I'm in rush and ask him permission to shutdown the instance for a while, then I increase the size of the Disk from Google Cloud Console page.

after I boot up the instance again, we can't connect to the SSH (using Putty / WinSCP), the server refuse our public key and user profile :

the journey begin...

Attempt to Solve

1. Restart the Instance

The first thing I do is Restart the server, and hope with this little magic will fix the SSH Connect issue, but unfortunately everything still not working.

2. Connect to SSH in Google Cloud Console

Then I try to connect the SSH from Google Cloud Console Instance Page, and Still no luck, Connection Failed

3. Connect to Serial Console

So I start to Google my problem, and found some answer from Serverfault and Stackoverflow to turn on the Serial Console and try to login from there using this Startup Script :

#!/bin/bash
adduser USERNAME
echo USERNAME:PASSWORD | chpasswd
usermod -aG google-sudoers USERNAME

Unfortunately, the startup script Failed to Running, and I see this error from Serial Console :

GCEGuestAgent[1121]: 2022-02-28T14:40:50.0037+07:00 GCEGuestAgent Error non_windows_accounts.go:144: Error creating user: useradd: /etc/passwd.1520: No space left on device#012useradd: cannot lock /etc/passwd; try again later.#012.

Start to PANIC... then I think that we locked to the Server and almost can't do anything.

Moment of Silent...

4. Create new 'restore' disk with old disk snapshoot

After read some related answer on the forum that I've got from google, I try to create new Disk with bigger Storage Size (from 10GB to 20GB) and using old disk snapshoot as the source

I hope with this new Disk and bigger size everything will run smoothly when I attach this new disk as Boot disk to the Instance without losing any existing data. But Unfortunately, when the server run, we still Refused from the Server by using Putty / WinSCP and also Google Cloud Console SSH.

Solution

Almost gave up, but I will try my last Shoot. and YEAH!! it's working.

here is the step that I do to solve this problem

1. Create new Instance with new Disk and new OS Image

Creating new Instance with new Disk with Ubuntu and start it, make sure I can connect using SSH.

the idea is to Attach the old disk to this new Instance as additional disk

2. Attach old 'fully' disk as additional disk to the new Instance

go to Compute Engine Instance List, and choose the new instance then click the edit button.

find the Additional Disk Section and Click Attach Existing Disk, then choose the old disk.

Make sure to detach the old disk from Boot Disk at the main instance

3. Mount disk to filesystem folder

After attach the old disk, run this command to check if the disk Exist / loaded in the instance :

sudo lsblk

and it will show the list of the disk partition

in my case, the old disk identified by sdb and the partition that we need to fix is at sdb1 to filesystem, let's say we mount it to /myOldDisk in the new instance

create myOldDisk folder by execute this command

sudo mkdir -p /myOldDisk

mount sdb1 to /myOldDisk

sudo mount -o discard,defaults /dev/sdb1 /myOldDisk

configure permission

sudo chmod a+w /myOldDisk

run sudo lsblk again, now I can see my sdb1 mounted to /myOldDisk

4. Clean up or backup to local computer some files for free up more space in the mounted folder

since our old Disk mounted to /myOldDisk I can easily go to the directory then cleanup some files / folder to get more free space

cd /myOldDisk

then I can find some big files to delete or archive it as zip and download it to my local computer

I get 500MB+ free space and I thing it's save to go

5. Umount the old disk

unmount the sdb1 from filesystem by running this command :

umount /dev/sdb1

detach my old disk using Google Cloud Console at this new Instance Detail page, then we can shutdown and delete this new Instance & new Disk, we don't need that anymore.

6. Increase the old disk size at Google Cloud Console

Go to Google Cloud Console Disk Page, and select the old Disk, and increase size, in my case is from 10GB to 30GB, and save it.

7. Attach the old disk to the old Instance

I need to Attach my old Disk to my old Instance as Boot Disk, go to Compute Engine Instance Detail, edit the instance and add the Boot Disk, select my old Disk.

8. Run the instance and Growpart the Partition

Turn on the Instance, Finally now I can login SHH to the Server,
when I check the free storage by running df -h command, the storage space still 10GB.

my next step is to Update the disk partition by using Growpart from 10GB (existing) to the new size 30GB

I can check the new size by running :

sudo lsblk

It will show new dev/sda storage size, all i Need to do is Grow the Partition by running this command :

growpart /dev/sda 1
CHANGED: partition=1 start=2048 old: size=419428319 end=419430367 new: size=2097149919
,end=2097151967

then I need to Resize the Partition by execute this command :

sudo resize2fs /dev/sda1

it will return something like this :

resize2fs 1.42.13 (28-Feb-2022)
Filesystem at /dev/sda1 is mounted on /; on-line resizing required
old_desc_blocks = 13, new_desc_blocks = 63
The filesystem on /dev/sda1 is now 262143739 (4k) blocks long.

Finally Done ! now the Disk have extra space, I can verify the available / free storage by running df -h

It's time to rest and drink some Coffee..

Summary

from this Experience I Learn :

Never ever Shutdown your 'full storage' server, Free up some space before increasing the disk size, and make sure everything is fine before the shutdown.
You don't need to shutdown the server for increasing the storage Disk

I'm so sorry for my bad English, and I hope this post will be guide Me or Someone else when facing the same problem in the future.

if you have suggestion about this post feel free to write it down in the comment section below :)

Deploy ReactJS Production Build with PM2

Taufiq Abdullah — Mon, 10 Aug 2020 04:45:41 +0000

Hello, This is my very first Post in Dev.to and i want to share about how to deploy ReactJS Production Build using VPS with PM2

Server Environment :

OS : Ubuntu 18.04.4 LTS
NodeJS : 10.19.0
NPM : 6.14.2

1. Build it

Make sure you build it (using yarn build / npm run build)

2. Upload build file to VPS

in this step, you can upload to your vps, in my case i put it in /var/www/myReactApp

3. Install PM2

you need pm2 to serve the apps, by using this command in terminal

sudo npm install pm2 -g

4. Run PM2 Command

this is the pm2 command to serve

pm2 serve <path> <port>

now we need to put our project in the command by calling

pm2 serve myReactApp/ 3000 --name "my-react-app" --spa

myReactApp/ : folder of the app
3000 : the port for serve
"my-react-app" : name for PM2 Process, will visible in "pm2 list"
--spa : parameter for Single Page Application, redirect to root URL

5. Apps Running

now your apps Running on the port :3000, we can open browser and access to yourdomain.com:3000 or by using your-ip:3000.

we also can setup the apache sites-enabled to hide the port from url by using ProxyPreserveHost and ProxyPass

6. Monitoring Running Apps with PM2

we can see all of our pm2 process by calling this command

pm2 list

we can start, stop or delete process from list by using

pm2 <start/stop/delete> <process id/process name>

example :

pm2 stop my-react-app

and we can view pm2 dashboard for monitoring all running process by using

pm2 monit

now our apps deployed :D
i hope this article is useful, and i'm so sorry if there is a mistake and my poor english.

Have a Nice Day ;)