Swapping analytics table takes too long while running analytics

Hi all
Lately I’ve having an issue while running analytics. Everything seems to be running well until the task “Swapping analytics tables” starts. That task can take up to 2 hours.
Screenshot from 2023-05-19 14-33-25

Could anyone help me on fixing this issue? What I do that works is to shutdown Tomcat, restart Postgres service, and then start Tomcat. After this if I run analytics, it will run smoothly as expected.
My server specs are:
CPU cores = 8
RAM = 32GB
OS = Ubuntu 18.04
PostgreSQL 13.10
Tomcat 9
Java 11, -Xms8184m -Xmx12288m

Best regards

Hi @joao.machiana

I believe this issue has relation to the Postgresql memory configuration.
Therefore please check this article and try to reconfigure your Postgresql.

Regards

2 Likes

Hi @Ulanbek

Thank you for the article. I took in consideration the concenpts referred in the article and tried to do some more tuning of my Postgres configuration, but the issue still remains, and sometimes even after restarting Tomcat and Postgres, when running analytics again it gets stuck on that process, “Swapping analytics table”

Hi @joao.machiana

Let’s take following steps to illuminate the issue sources :

  1. stop your DHIS2 instance: tomcat and postgresql.
  2. clear all tomcat, postgre and dhis2 logs
  3. restart all services
  4. clear analytics table, do maintenance work
  5. run analytics
  6. check your logs for any errors

Other reason for this maybe RAM/HDD/SSD errors. Please check them as well.

Otherwise I cannot see any issues there

Regards,

Ulanbek

Hi! @joao.machiana

did you find solutions for this. The analytics takes long running. we are using DHIS2 version 2.36.

Hi @tshidi.moroma
Can’t remember very well the steps I took to fix it, but I think I simple restart of the entire server fixed it.

Best regards

Hi @joao.machiana
I restarted the entire server and followed some of the suggestions, but to no avail. i will keep on trying out different solutions.

Thanks and regards

I would advise that

  • you don’t trust only the console saying “swapping”
  • you should look at pg_stat_activity view and find what is really happening in postgresl
  • you should check the logs of tomcat/dhis2

if you run

copy (select * from pg_stat_activity) to STDOUT with csv HEADER ;

it will spit out a csv

datid,datname,pid,leader_pid,usesysid,usename,application_name,client_addr,client_hostname,client_port,backend_start,xact_start,query_start,state_change,wait_event_type,wait_event,state,backend_xid,backend_xmin,query_id,query,backend_type
,,290497,,,,"",,,,2024-06-14 19:19:59.771427+02,,,,Activity,AutoVacuumMain,,,,,"",autovacuum launcher
,,290499,,10,postgres,"",,,,2024-06-14 19:19:59.773047+02,,,,Activity,LogicalLauncherMain,,,,,"",logical replication launcher
13807,postgres,290666,,16387,smestach,psql,,,-1,2024-06-14 19:20:07.787416+02,2024-06-14 19:21:12.726151+02,2024-06-14 19:21:12.726151+02,2024-06-14 19:21:12.726153+02,,,active,,91027,,copy (select * from pg_stat_activity) to STDOUT with csv ;,client backend
,,290495,,,,"",,,,2024-06-14 19:19:59.770157+02,,,,Activity,BgWriterMain,,,,,"",background writer
,,290494,,,,"",,,,2024-06-14 19:19:59.76965+02,,,,Activity,CheckpointerMain,,,,,"",checkpointer
,,290496,,,,"",,,,2024-06-14 19:19:59.770776+02,,,,Activity,WalWriterMain,,,,,"",walwriter

that you can analyse or share here.

if it’s really generating analytics that takes time

  • you are running on recommended hardware (ex ssd, enough mem/cpu)
  • check the pg settings : Installation - DHIS2 Documentation
  • check that server settings about parallelism don’t exceed (too much parallelism is actually slowing things down)
  • in aggregate the analytics gets slower the more you have data and category option combos
    • you can limit the analytics to the last 2-3 years ?

Hi there. The most common issue I have seen in the past is that the server simply runs out of disk space. During the analytics generation process, the size of the database tables used for analytics is effectively doubled. So, while you maybe able to drop the analytics tables, and regenerate them, when you run analytics with existing tables, the system needs twice the disk capacity before the tables are swapped. So, if your system has a lot of data (and/or many dimensions on your analytics tables), it is possible that you run out of disk space on the server. I would check this first and be sure its not an issue.

Best regards,
Jason

1 Like