Reclaiming Space for Truncated Aurora MySQL Tables

, ,

When working with Aurora MySQL, it’s common to assume that running a TRUNCATE TABLE will completely clear out space and return your database to its pre-load size. Unfortunately, that’s not quite how it works — and it often surprises people during large migrations.

Here’s why truncating doesn’t always free space, and what you should do instead.

TRUNCATE vs. Space Reclamation

In MySQL (and Aurora MySQL), TRUNCATE TABLE is a fast operation that:

  • Deletes all rows from a table.
  • Resets the auto-increment counter.
  • Removes and recreates the underlying table definition internally.

However, with InnoDB tables, the physical storage file is not always shrunk automatically. Instead:

  • Pages inside the tablespace are marked as free.
  • The file size on disk (or Aurora’s volume usage) often stays the same.

That means your database volume won’t shrink even if the table is empty.

The Role of OPTIMIZE TABLE

To actually reclaim space after large deletes or truncations, you need to run:

OPTIMIZE TABLE your_table;

What this does:

  • Creates a new copy of the table with active rows.
  • Rebuilds indexes.
  • Frees up unused pages and defragments data.
  • Releases the unused space back to Aurora’s volume.

In practice, many users see 20–40% of their allocated storage reclaimed after running OPTIMIZE TABLE on heavily churned datasets.

Aurora-Specific Gotchas

  1. Shared Storage Model
    Aurora uses a distributed storage layer, so file-per-table semantics can be a bit confusing. Even with innodb_file_per_table=ON, the space isn’t automatically released at the cluster volume level until the table is rebuilt.
  2. Performance Impact
    • OPTIMIZE TABLE is blocking for writes and can run for minutes to hours on very large tables.
    • Plan downtime or run during low-traffic windows.
  3. Monitoring
    Watch the VolumeBytesUsed CloudWatch metric before and after running OPTIMIZE. If you only truncate, the metric won’t move. After optimize, you’ll see a real decrease.

Best Practices

  • For staging or migrations: Always follow TRUNCATE with OPTIMIZE TABLE to reclaim disk space.
  • For production: Schedule optimizations for off-peak hours, and test on smaller tables first.
  • For ongoing operations: If your workload does lots of churn (bulk loads/deletes), regular optimization may be necessary.

Final Takeaway

In Aurora MySQL, TRUNCATE TABLE clears rows but doesn’t guarantee space reclamation. To actually shrink your database footprint, you need to rebuild the table with OPTIMIZE TABLE. Think of it as the “vacuum” step — without it, your cluster will keep carrying dead weight.

Subscribing to Bookmarks

, ,

This procedure explains how to subscribe to bookmarks.

You could also use the procedure to subscribe to bookmarks, if you have been given access to the bookmark by your system administrator. If the bookmark appears in your list, you have access to it. (See your system administrator if you need access to a bookmark that does not appear in your Bookmarks list.)

To subscribe to bookmarks

  1. From the Portal home page, select Preferences (check marks icon)>Content.
  2. Click on closed books to expand them, if necessary.
  3. Bookmarks you are subscribed to have check marks in front of them.

If the check mark is removed and you want to add it, click on the box.

Unchecked bookmarks will not appear in your navigation pane or content window.

How to fix certain Lawson LBI reports on dashboard prompting for ID and Password

, ,

Follow these steps to learn how to fix certain Lawson LBI reports on dashboard prompting for ID and Password. This typically happens after a migration to a new version of LBI. You’ll need to update these report parameters.

First, edit the report on dashboard:

Next, copy the existing URL:

Run the report in Report Admin with data refresh and grab this part of the URL (see screenshot), append it to the end of the original URL above from the report you’re adding it from:

It should like this:

Now it should load properly without asking for credentials.

Aurora MySQL InnoDB Overhead

, ,

If you’ve ever loaded a few terabytes of raw data into MySQL and then scratched your head when the database footprint grew larger than your flat files, you’re not alone. This question came up recently in a real-world migration: flat files totaled about 2.0 TB, but once ingested into MySQL (with no secondary indexes), the database measured 2.24 TB.

So where did the extra 240 GB come from?

The InnoDB Overhead Story

The main culprit is InnoDB’s design. It’s not just storing your rows — it’s storing them with transactional guarantees, recovery mechanisms, and structures for efficient querying. Here’s what adds up:

  1. Page Structure and Fragmentation
  • InnoDB stores data in 16KB pages.
  • Pages aren’t always fully packed, leaving unused space (internal fragmentation).
  • Multiply that by billions of rows, and you’ve got gigabytes of overhead.
  1. Row Format Metadata

Every row comes with extra fields beyond your raw data:

  • Row headers (7–23 bytes).
  • MVCC metadata like DB_TRX_ID and DB_ROLL_PTR to handle concurrent transactions.

A small per-row overhead scales up dramatically in large datasets.

  1. Redo, Undo, and Temporary Logs

InnoDB maintains:

  • Redo logs (ib_logfile0, ib_logfile1) for crash recovery.
  • Undo logs for rollbacks and consistent reads.
  • Temp tablespace (ibtmp1) for operations like sorts.

Even when idle, these files persist on disk.

  1. Fill Factor / Page Utilization

To keep inserts efficient, InnoDB intentionally leaves free space in pages. This means some storage is held open for growth, rather than packed tightly like in a flat file.

  1. Data Type Inefficiencies
  • VARCHAR, TEXT, BLOB, and DECIMAL fields often take up more space in InnoDB than in a plain CSV.
  • Alignment and padding further contribute.
  1. Deleted but Not Reclaimed Rows

If you’ve done heavy deletes or updates, space may not yet be reclaimed. Running OPTIMIZE TABLE can help, though it’s resource-intensive.

How to Measure the Difference

You can check actual table storage in MySQL:

SELECT

table_schema,

table_name,

ROUND(data_length/1024/1024/1024, 2) AS data_gb,

ROUND(index_length/1024/1024/1024, 2) AS index_gb

FROM information_schema.tables

WHERE table_schema = ‘your_db’

ORDER BY data_gb DESC;

Or examine InnoDB tablespace allocation:

SELECT

table_name,

allocated,

file_size,

free

FROM information_schema.innodb_tablespaces;

Putting It Together

The 12% overhead (240 GB on 2 TB raw) is not unusual at all. In fact, it’s right in line with expectations:

Component Typical Contribution
InnoDB page + row overhead 5–15%
Undo/redo logs, temp files 1–5%
InnoDB system metadata <1%
Fill factor, alignment 2–10%

Flat files are lean but fragile. InnoDB adds the structures that make MySQL durable, transactional, and queryable — and that extra space is the price of those guarantees.

Final Takeaway

If your MySQL footprint is a bit larger than your flat file source, don’t panic. That overhead is just InnoDB doing its job. And in large-scale systems, a 10–15% increase is expected — and often well worth the trade-off for reliability and performance.

PCARD issue- GL165 error file missing

, ,

Problem:

I was able to access the QA site and see the JE, however there is an issue.  I am not seeing the “error” report for GL165 that we would normally see in v9 production. This is the report that tells us which records have an error and what the error is.

How do we see the error report?  Is this a setting somewhere?

 

Below is an example from v 10 QA.  The test JE has an error, and the GL165 report shows that there is an error, but it doesn’t tell me which record has the issue:

 

Resolution:

To resolve this, simple give your user access to the role which gives access to the IFTR.1 files that generate the error files. Then have the user run the job again.

How to reorganize or modify Columns in LBI Maintain Reports

, ,

Follow the steps below to learn how to reorganize or modify your columns in (Lawson Business Intelligence) LBI Maintain Reports.

 

Problem:

You’re an admin for Lawson LBI but you want to reorganize the Maintain Reports columns like the ones shown below:

 

Solution:

There is a simple way to do this. Just follow these steps.

  1. First, log in to LBI and open your Report Administrator:
  2. Next, go to My Reports on the side bar:
  3. Make sure “ALL” is selected under the left column under Report Lists
    1. Click “Edit List” on the top right and reorganize the columns, then click save.

Go back to Maintain Reports and you should see your new columns!

How to correct a name change for Inbasket

, ,

Each Process Automation user has an Inbasket that is accessible through Infor Smart Office. Your Inbasket contains tasks that have been assigned to you, as well as the user’s information.

 

Problem:

Users’ name changed but is not reflected in Inbasket. (See screen shot below for example)

 

Resolution:

In Infor Security Services (ISS), the Preferred Given Name is listed as “Linda”. The value can be updated in this section if the person has changed their name (or if for some reason the name is wrong or misspelled).

Once you edit the “Given Name” field, save your changes. The name displayed should now be the correct one.