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

Document ID : KB000016443
Last Modified Date : 14/02/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 and 10.5.2
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 "sqlTools5.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 sqlTools5.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 customer, 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://support.ca.com/us/knowledge-base-articles.TEC1182967.html

- How can we tune Oracle database with 64GB Memory?
https://support.ca.com/us/knowledge-base-articles.TEC1697454.html

- How can we tune Postgres database for Linux box with 16GB Memory?
https://support.ca.com/us/knowledge-base-articles.TEC1230537.html

- How to purge or reduce the size of a Postgresql APM database and optimize CEM data retention.
https://support.ca.com/us/knowledge-base-articles.TEC597519.html

- APM database is growing fast - too many appmap_states tables. Is there a property to prune those tables.
https://support.ca.com/us/knowledge-base-articles.TEC1846866.html