Analyse database usage

0
We received several warnings about our database running low on diskspace.  In the v4 cloud dashboard I can see the total database size, split in table and index size in the metrics section, but I want to analyse this further and see which tables or indexes are taking up most of the space. I could download a backup and restore it locally and use local tooling like pgAdmin to analyse it. But I was wondering if it is possible to connect directly to the cloud database or if I forgot some options to analyse this better in the cloud dashboard.
asked
3 answers
3

Hi Stephan,

I believe i is not really possible to connect to the remote DB. Even if it was, I do not see an advantage as compared to doing it locally. In fact if you do it locally you can use any DB analysis tool you like, and you also don't have to worry about locking the DB by running some slow to execute analysis queries.
-Andrej
PS: one final note, the cloud v4 does not automacially vacuum AFAIK, so you might clear up some dead tuples by simply doing a backup&restore.

answered
3

Hi Stefan, 

If you contact Mendix support they can run queries for you on the database. What I've done in the past is run a query like this: 

 

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

in order to gain more insight into the usage of my database. You can experiment with this query in a local version of your database to adjust for the result you desire. 

 

kind regards. 

answered
2

Mendix 7 has a java api to execute sql statements on the app database: https://apidocs.mendix.com/7/runtime/com/mendix/datastorage/DataStorage.html

You can run the query mentioned by stefan using this api.

answered