leader tracking and unbalanced in moses

[eugeneckim]

Two requests:

• Add a column which indicates if a tablet is a leader, in the tablet table.
• Add the equivalent of the unbalanced_tables_tablet_count_per_size which is found in the standard trp.

Did a quick prototype in house and this appears to work:

SELECT
    TABLE_NAME,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 < 2048 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 <2048 THEN 1 ELSE 0 END) END AS LT2GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 2048 AND 3072 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 2048 AND 3072 THEN 1 ELSE 0 END) END AS s2GB_3GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 3072 AND 4096 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 3072 AND 4096 THEN 1 ELSE 0 END) END AS s3GB_4GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 4096 AND 6144 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 4096 AND 6144 THEN 1 ELSE 0 END) END AS s4GB_6GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 6144 AND 8192 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 6144 AND 8192 THEN 1 ELSE 0 END) END AS s6GB_8GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 8192 AND 10240 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 8192 AND 10240 THEN 1 ELSE 0 END) END AS s8GB_10GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 10240 AND 12288 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 10240 AND 12288 THEN 1 ELSE 0 END) END AS s10GB_12GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 12288 AND 14336 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 12288 AND 14336 THEN 1 ELSE 0 END) END AS s12GB_14GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 14336 AND 16384 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 14336 AND 16384 THEN 1 ELSE 0 END) END AS s14GB_16GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 16384 AND 20480 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 16384 AND 20480 THEN 1 ELSE 0 END) END AS s16GB_20GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 20480 AND 24576 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 20480 AND 24576 THEN 1 ELSE 0 END) END AS s20GB_24GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 24576 AND 28672 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 24576 AND 28672 THEN 1 ELSE 0 END) END AS s24GB_28GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 28672 AND 32768 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 28672 AND 32768 THEN 1 ELSE 0 END) END AS s28GB_32GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 32768 AND 36864 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 32768 AND 36864 THEN 1 ELSE 0 END) END AS s32GB_36GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 36864 AND 40960 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 BETWEEN 36864 AND 40960 THEN 1 ELSE 0 END) END AS s36GB_40GB,
    CASE WHEN SUM(CASE WHEN TOTAL/1024/1024 > 40960 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN TOTAL/1024/1024 > 40960 THEN 1 ELSE 0 END) END AS GT40GB
FROM
    tablet,node
WHERE
    nodeUuid = node_uuid AND private_ip = leader
GROUP BY
    TABLE_NAME
ORDER BY
    2 DESC;

[eugeneckim]

The above will technically do this for all tables vs. just the "unbalanced" ones.
I would say that all tables is sufficient as a superset.

As a bonus third request: make a separate unbalanced view

[pgyogesh]

Looks like we are fetching this data from /tablets endpoint which doesn't tell about lease status (unlike tablet report parser).

We can have separate tablet view like below:

DROP VIEW IF EXISTS tablet_view;
CREATE VIEW tablet_view AS
SELECT
    t.node_uuid,
    t.TABLET_UUID,
    t.NAMESPACE,
    t.TABLE_NAME,
    t.TABLE_UUID,
    t.STATE,
    t.HIDDEN,
    t.LEADER,
    t.FOLLOWERS,
    t.NUM_SST_FILES,
    t.PARTITION,
    t.LAST_STATUS,
    t.SST_FILES,
    t.SST_FILES_UNCOMPRESSED,
    t.TOTAL,
    t.WAL_FILES,
    CASE 
        WHEN t.LEADER = n.private_ip THEN 1
        ELSE 0
    END AS is_leader
FROM
    tablet t,
    node n
WHERE
    t.node_uuid = n.nodeUuid AND
    t.state = 'RUNNING';

SELECT count(*), is_leader FROM tablet_view GROUP BY is_leader;
count(*)  is_leader
--------  ---------
5332      0        
2666      1     

Tested on sqlite : sqlite3 -header -column /cases/10858/2024-08-26T23_19_35/2024-08-26.OF060G71Y9MD6TK.tabletInfo.inventory-prod-eastus2.sqlite

[pgyogesh]

Created PR #167