Getting capacity metrics for these two dabatases is suprisingly easily. We will get the size of database(s), current users connected and current queries made (and also the size for each tablespace for PostgreSQL).

MySQL

Size of database :

SELECT LOCALTIMESTAMP, table_schema AS 'Name', ROUND( SUM( data_length + index_length ) /1024 /1024, 3 ) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'name_of_our_database'
GROUP BY table_schema

Size of all the tables :

SELECT LOCALTIMESTAMP, table_name, table_rows,
ROUND(data_length/1024/1024,2) + ROUND(index_length/1024/1024,2) FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')

Current users and queries

SELECT LOCALTIMESTAMP, COUNT(*) AS cnt, user, host, info
FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY user, info ORDER BY cnt DESC;

If you have the slow query (doc here) activated, it’s really worth getting the data from it.

PostgreSQL

Size of all the databases from the instance (we don’t use pg_size_pretty because we want all our reports to use the same unit and we exclude the basic databases)

SELECT localtimestamp, pg_database.datname, (pg_database_size(pg_database.datname)::float/1024/1024)::numeric(8,3)
FROM pg_database WHERE datname NOT IN ('template0','template1','postgres')

Size of the tablespaces (same thing as before)

SELECT localtimestamp, spcname, (pg_tablespace_size(oid)::float/1024/1024)::numeric(8,3)
FROM pg_tablespace WHERE spcname != 'pg_global';

Size of the tables (only the one above 10MB)

SELECT * FROM (
    SELECT localtimestamp, nspname || '.' || relname AS "table", reltuples,
    (pg_total_relation_size(C.oid)::float/1024/1024)::numeric(8,3) AS "size"
    FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND C.relkind <> 'i') T
WHERE size > 10

Current users and queries

SELECT localtimestamp, COUNT(*) AS cnt, usename, current_query
FROM pg_stat_activity GROUP BY usename,current_query ORDER BY cnt DESC;