Database Compaction - Reclaiming Table Free Space

Database Compaction – Reclaiming Table Free Space

The primary database type MariaDB used by ServiceNow will automatically increase the size of tables to support additional data. This space is not automatically reclaimed when large amounts of data are removed from a table by the database engine. Included in the Utah release a new feature called Database Compaction was introduced that checks tables for this situation nightly and will automatically rebuild tables as needed. Table free space information can be seen per table from the "Telemetry - Table Growth" dashboard by clicking into a table or from per table from /sys_physical_table_stats_list.do, field 'Reclaim Estimate in GB'.

• Requirements for table rebuilds

  • The instance is on the Utah or new release

  • The table has a Table Cleaner rule (see 'Rebuilding tables that do not have a Table Cleaner rule but meet all other requirements' below for more details)

  • Has more than 10gb for 'Reclaim Estimate in GB'

  • To be eligible for compaction, the table must have over 50% free space, as indicated by the 'Reclaim Estimate in GB' value in the related sys_physical_table_stats record. For example, if the 'Table Size in GB' is 100GB, then the 'Reclaim Estimate in GB' must exceed 50GB (i.e., more than 50% of 100GB) for the table to be considered eligible for compaction

    • This requirement can be adjusted from 50% to 30%.

      • Add the property: glide.db.compaction.criteria.reclaim_percentage

      • Type: integer

      • Value: 30

  • The table has less than 50 million rows.

  • Rebuilding tables that do not have a Table Cleaner rule but meet all other requirements

    • The requirement for a table having a Table Cleaner rule can be bypassed.

    • This requires that the attribute "Do optimize" (do_optimize=true) be added to the table's collection record on the sys_dictionary table. Adding an attribute

    • All other requirements stated above are required (see 'Requirements for table rebuilds' above for more details) minus the Table Cleaner rule.

    • This should be tested in sub-production on a table with a matching similar dataset before forcing a table rebuild in production.

• Excluded Tables

  • Tables on Database Rotation/Extension cannot be rebuilt

  • Shadow/Rollback Tables

• Related Job

  • Job: DB Compaction > Executes daily, this job triggers the rebuild of tables that meet the requirements for compaction. This job does not rely on the data in sys_physical_table_stats and pulls the data table independently.

  • Table: Review /sys_schema_change_list.do for the table record with 'alter_type' field as 'compact_table'

  • Field: reclaim_size_estimate > Estimated reclaim size before compaction

  • Field: table_size_before > Table size before compaction

Hi there,

Keeping your instance database footprint tidy? Reducing instance database footprint? An important subject, though actually a subject that barely gets attention. Barely if you search for any content, hardly any replies when reaching out on ServiceNow Community/LinkedIn/Slack/etcetera, and to be honest: also if you look at customers in general... so much focus on Development, so little (or almost no) focus on Platform or System Administration. Also there is not a single ServiceNow course that goes in depth on this subject.

So why is this such an important subject? ServiceNow runs in the cloud, right? Sure (unless you are on-prem of course), though that doesn't mean ServiceNow does everything for you! Keeping your instance database footprint tidy and reducing the database footprint, will have several positive effects: Performance gain, slimmer and optimized tables, less future manageability or technical depth, shorter system clone time (or time to place backups back in case of emergencies!), avoiding licensing implications if your database footprint gets over 4 TB, etcetera.

In the upcoming weeks, I will share several blogs regarding reducing your instance database footprint and keeping your instance database footprint tidy. All based on experiences gained in the field, at several customers, after collaborating with ServiceNow Support, investigating myself, etcetera.

If you have any thoughts yourself on this subject, don't hesitate to share!

Topics in this series of blogs (I will update this section when publishing new blogs):
- Maintain Audit Delete and Audit Relation
- Maintain Attachments and Attachment Documents
- Activating/adding Table cleaners
- Reduce (over)auditing
- Lower duration Table Rotation
- Drop syslog_trans_prejakarta* tables
- Cleanup Shadow tables 

- Maintain Emails

- Reclaiming Table Free Space

Reclaiming Table Free Space
Within ServiceNow the database will automatically increase the size of tables to support additional data. This space is not automatically reclaimed when large amounts of data are removed from a table by the database engine. This is something most developers and system administrators are not aware of and this behavior has several downsides: like a decrease in performance when the table concerned gets only bigger and bigger, and an increase of the Database Footprint resulting in possible overlicensing.

So can we do something about this, or can ServiceNow Support help do something about this, or is ever increasing size of tables a simple fact that we cannot do anything about? Let's have a closer look.

Note: This article has been written with knowledge of the current ServiceNow database in mind, MariaDB.

Increasing table size

The database within ServiceNow growing and growing isn't a strange thing. This will happen over time, most companies are also using more and more of ServiceNow, and every release ServiceNow is expanding functionality, etcetera. There are tables or situations where the number of records in tables can fluctuate, for example staging tables, tables with Table Cleaners or other cleaning methods (either structural or one-off), or defects/bugs that caused an unnecessary increase of records in tables. Which tables or situations this exactly concerns, or how much such growth of the database is will differ for every customer. An increase in size of tables of course influences the Database Footprint size and possible licensing. For a lot of customers this is also the trigger for when to work on such tables. Less attention is for the manageability that gets harder and harder and decreasing performance! In general, performance on tables will get less and less on ever-growing tables.   

Deleting data from a table

When deleting data from tables, ServiceNow has several options you can utilize. Some of these are:

- Scripted deletions, using the GlideRecord API and functions "deleteRecord()" or "deleteMultiple()"

- One-off deletions using no code "Data Management Delete Jobs"

- Maintaining data using no code Table Cleaners [sys_auto_flush]

There are definitely more methods available for deleting data from tables. The above three are three of the more commonly used features within ServiceNow.

Optimizing/rebuilding tables

As mentioned in the introduction of this blog and in several other articles/blogs that I shared: Table space is not automatically reclaimed when large amounts of data are removed from a table. While you can trigger table optimization yourself, I wouldn't advocate doing so. You can trigger table optimization for example by adding an index to a table or adding a field to a table. Up to the Tokyo release you could also use the undocumented "force_optimize" field on Table Cleaners. While this worked on every valid Table Cleaner, the downside was the possible locking of tables. You might still see the "force_optimize" field on your Instance (ServiceNow does not delete this field), though the functionality behind this has been deprecated. The safest to optimize tables is getting ServiceNow Support involved through a ServiceNow Support Case. In most cases ServiceNow Support will opt to go for a table rebuild on the backend instead of table optimization.

If you have been working on optimizing tables and raising cases for this with ServiceNow Support, you probably have seen a switch in how the cases are handled since about a year ago. ServiceNow Support changed their way of working and started to decline to pick up individual table optimization requests, they are just overloaded with work. Also ServiceNow Support is more and more referencing customers to use "Database Compaction" that was introduced with the Utah release (or this will actually be the solution proposed to ServiceNow Support Cases).

Database Compaction

With the Utah release, ServiceNow added Database Compaction. A feature that automatically rebuilds eligible tables to free up space (and with that increase performance) once a day. The official documentation on this is limited to a ServiceNow Support knowledge article (KB1518213) that you can only view when logged in to the ServiceNow Support website.

Requirements

So what are "eligible" tables for Database Compaction? Requirements for table rebuilds:

- The instance is on the Utah release or newer

- The table has a Table Cleaner record

- The table has more than 10 GB for "Reclaim Estimate in GB"

- To be eligible for compaction, the table must have over 50% free space (this can be adjusted to 30%, by adding System Property "glide.db.compaction.criteria.reclaim_percentage" with value "30")

- The table has less than 50 million rows

If a table has no Table Cleaner record, the requirement for a table having a Table Cleaner rule can be bypassed:

- This requires that the attribute "Do optimize" (do_optimize=true) is added to the tables collection record on the sys_dictionary table

Components involved

Database Compaction is triggered by a daily Schedule record [sys_trigger] "DB Compaction". This Schedule scripted executes:

new GlideTableCompactor().compact();

If there are any tables that meet the requirements, the table optimization will be executed and a record will be generated in the "Compaction Job Execution Tables" table [sys_compaction_run].

As mentioned by default the reclaim percentage is 50%. This can be lowered to 30%, by adding System Property "glide.db.compaction.criteria.reclaim_percentage" with value "30". Any other value will not be respected, I tried  .

Thoughts on Database Compaction

Database Compaction looks like a solid functionality and is safer to use than the other methods I mentioned. Also it is not limited to only tables with a Table Cleaner, like the "force_optimize" functionality was.

Personally I do feel that Database Compaction is not yet workable enough if you want to stay on top of your Database Footprint. The thresholds are just not okay. Imagine you want to apply Database Compaction on for example the Audit Delete table, it is not uncommon that such a table has hundreds of millions of records and is way over 1 TerraByte. Looking at the requirements... this table would be overqualified for the requirements. Same for for example the Audit table. Or for customers with Audit or Email on Table extension, the requirements are quickly too high. For all the tables where it is not possible or not realistic to add a Table Cleaner, you would be forced to add an Attribute while this could easily concern hundreds of tables. Also good to know and not mentioned in the ServiceNow Support knowledge article: Not all tables are eligible for Database Compaction, for example Shadow tables which will be occupying hundreds of gigabytes on customers instances and these tables are part of the billable dataset.

I hope ServiceNow makes the Database Compaction requirements more configurable so it does become a useful feature. And it could lower the number of ServiceNow Support Cases being raised .

ServiceNow Support Case

If you want to stay on top of your Database Footprint (and yes you do want to do that: For the Database Footprint size and possible overlicensing or perhaps more importantly... performance!), in my opinion raising a ServiceNow Support Case is still the way to go.

As mentioned earlier in this blog, ServiceNow Support does not handle individual requests for Table optimization anymore. So you would need to collect multiple tables to be optimized with a single ServiceNow Support Case. For example add a monthly or bi-monthly story to your backlog and list the default tables to be optimized and additional tables to be optimized because work has been on cleaning those tables. Then just create a ServiceNow Support Case monthly or bi-monthly with these tables listed. ServiceNow Support does handle those cases.

---

That's it. Hope you like it. If any questions or remarks, let me know!

C

If this helped you in any way, I would appreciate it if you hit bookmark or mark it as helpful.

Interested in more Articles, Blogs, Videos, Podcasts, Share projects which I shared/participated in?
- 350+ Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field

Kind regards,

Mark Roethof

Independent ServiceNow Consultant
9x ServiceNow MVP

---