How can we optimize our APM database that is growing larger very quickly?

Document ID : KB000016443
Last Modified Date : 13/08/2018
Show Technical Document Details
Introduction:

Our APM DB size is growing quickly and is affecting the performance of the environment so we are looking for a way to optimize the APM database.

Question:

How can we optimize the database when the database size is growing very quickly?

Environment:
10.5.1, 10.5.2 and 10.7
Answer:

There were some issues in the GA version of APM that was causing a large amount of topological changes to be stored.  As a result, the database size was growing very quickly and was making complexity of query evaluation hard.

To remedy this, one should take a look on size of tables to decide if cleanup procedure will be needed. These problems are fixed in hotfixes, so please install latest available.

Cleanup decision:

  1. Execute SQL
    1. Postgres:

      select external_id, history from (
      select vertex_id, count(1) as history from appmap_vertices
      where start_time > (CURRENT_DATE - INTERVAL '5 day')
      group by vertex_id
      order by 2 desc
      limit 20) t
      join appmap_id_mappings m on t.vertex_id=m.vertex_id;

    2. Oracle:

      select external_id, history from (
      select vertex_id, count(1) as history from appmap_vertices
      where start_time > (sysdate - 5)
      group by vertex_id
      order by 2 desc) t
      join appmap_id_mappings m on t.vertex_id=m.vertex_id
      where rownum < 20;

  2. If this command gives many history values greater than 100 results, then you are experiencing many changes in topology
  3. Another way is to list number of records in appmap_vertices (1M rows is warning) or appmap_attribs (10M rows is warning):
    • select count(*) from appmap_vertices;
    • select count(*) from appmap_attribs;
  4. Another way is to get size of appmap_attribs table. If the size is greater than 4GB then it should be cleaned as well. To get the table size in MB:
    • select relname, (relpages * 8) / 1024 as size_mb from pg_class where relname = 'appmap_attribs'; (Postgres)
    • select bytes/1024/1024 MB from user_segments where segment_name='APPMAP_ATTRIBS'; (Oracle)
  5. Apply the most recent Hotfix as soon as possible
  6. Follow cleanup procedure: 
    1. Open a case and ask Support for cleanup utility "sqlTools6.jar"
    2. Put utility in EM_HOME folder
    3. Execute cleanup that will delete topological history before some date (14 days before current time). For example "2017-09-15 00:00:00":
      jre/bin/java -jar sqlTools6.jar cleanup "2017-09-15 00:00:00
    1. Optimize tables in Postgres SQL:
      vacuum full analyze appmap_edges;
      vacuum full analyze appmap_vertices;
      vacuum full analyze appmap_attribs;
    2. Optimize tables in Oracle:
      alter table appmap_edges enable row movement;
      alter table appmap_vertices enable row movement;
      alter table appmap_attribs enable row movement;

      alter table appmap_edges shrink space compact;
      alter table appmap_vertices shrink space compact;
      alter table appmap_attribs shrink space compact;

There is no need to stop the Enterprise Manager, it will just slow down a little bit the database.

Additional Information:

- How can we tune Postgres database for Windows box with 16GB Memory?
https://comm.support.ca.com/kb/how-can-we-tune-postgres-database-for-windows-box-with-16gb-memory/kb000016515

- How can we tune Oracle database with 64GB Memory?
https://comm.support.ca.com/kb/how-can-we-tune-oracle-database-with-64gb-memory/kb000016409

- How can we tune Postgres database for Linux box with 16GB Memory?
https://comm.support.ca.com/kb/how-can-we-tune-postgres-database-for-linux-box-with-16gb-memory/kb000016381

- How to purge or reduce the size of a Postgresql APM database and optimize CEM data retention.
https://comm.support.ca.com/kb/how-to-purge-or-reduce-the-size-of-a-postgresql-apm-database-and-optimize-cem-data-retention/kb000009486

- APM database is growing fast - too many appmap_states tables. Is there a property to prune those tables.
https://comm.support.ca.com/kb/apm-database-is-growing-fast-too-many-appmapstates-tables-is-there-a-property-to-prune-those-tables/kb000004232

- Oracle 11gR2 APM database is growing very large
https://comm.support.ca.com/kb/_Oracle-11gR2-APM-database-is-growing-very-large/KB000020013