Investigate frag issue on TU

[SWvheerden]

Remote nodes on TU have high fragmentaion 7%-10%
The seed nodes have a close to 0 % frag rate, so we need to see why remote nodes which uses the seed nodes have such a high frag rate.

[martinserts]

Summary

Metabase reports 7.39% Network Fragmentation. That is not a single issue. It is composed of two distinct groups of failing miners:

1. ~4.7% "Hard Stuck" (Critical): Windows users whose Tor is being silently killed (likely by Antivirus/Defender), causing an infinite restart loop.

2. ~2.7% "Soft Stuck" (Minor): Users who lost P2P peers (internet drop/sleep mode) and failed to reconnect. The app continues "mining" on old blocks without realizing it is isolated.
   Maybe we could implement a peer watchdog (if connected_peers == 0 for > 5 minutes), restart.

Step-by-Step Analysis

1. Fragmentation

Goal: Determine if miners are just "slow" (internet lag) or "broken" (stuck far behind).

WITH max_block_height AS (
    SELECT 
        quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 1 MINUTE and version>='0.9.913'
     
      
),
active_miners AS (
    SELECT 
        app_id,
        max(block_height) AS height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 3 MINUTE and version>='0.9.913'   
    GROUP BY app_id
)
SELECT 
    1 - (1.0 * SUM(CASE WHEN abs(height -max_height)<=4 THEN 1 ELSE 0 END) / COUNT(*)) AS network_fragmentation_kpi,
    1.0 * SUM(CASE WHEN height < max_height - 1000 THEN 1 ELSE 0 END) / COUNT(*) AS percent_more_than_1000_blocks_behind,
    1.0 * SUM(CASE WHEN height >= max_height - 100 AND height < max_height - 4 THEN 1 ELSE 0 END) / COUNT(*) AS percent_between_4_and_100_blocks_behind,
    COUNT(*) AS miners_reporting
FROM active_miners, max_block_height;

network_fragmentation_kpi	percent_more_than_1000_blocks_behind	percent_between_4_and_100_blocks_behind	miners_reporting
0.073	0.047	0.0067	891

Results:

• >1000 Blocks Behind: 4.7% (The majority of the issue)
• 4 - 100 Blocks Behind: 0.67%
• Total Miners: 891
  Insight: The issue is primarily miners getting permanently stuck, not just network latency.

2. Who is stuck?

Goal: Identify if this is a version-specific or OS-specific bug.

WITH max_block_height AS (
    SELECT 
        quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 1 MINUTE 
      AND version >= '0.9.913'
),
active_miners AS (
    SELECT 
        app_id,
        max(block_height) AS height,
        any(current_os) AS os,
        any(version) AS app_version
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 3 MINUTE 
      AND version >= '0.9.913'   
    GROUP BY app_id
)
SELECT 
    CASE 
        WHEN height < max_height - 1000 THEN '1_Stuck (>1000 blocks behind)'
        WHEN abs(height - max_height) <= 4 THEN '3_Healthy (In sync)'
        ELSE '2_In Between'
    END as status,
    os,
    app_version,
    COUNT(*) as miner_count
FROM active_miners, max_block_height
GROUP BY status, os, app_version
ORDER BY status ASC, miner_count DESC;

status	os	app_version	miner_count
1_Stuck (>1000 blocks behind)	windows	1.6.10	36
1_Stuck (>1000 blocks behind)	windows	1.6.9	3
1_Stuck (>1000 blocks behind)	windows	1.6.8	1
1_Stuck (>1000 blocks behind)	macos	1.6.10	1
1_Stuck (>1000 blocks behind)	windows	1.6.4	1
2_In Between	windows	1.6.10	21
2_In Between	macos	1.6.10	5
3_Healthy (In sync)	windows	1.6.10	720
3_Healthy (In sync)	macos	1.6.10	94
3_Healthy (In sync)	linux	1.6.7	1
3_Healthy (In sync)	windows	1.6.8	1
3_Healthy (In sync)	macos	1.6.6	1
3_Healthy (In sync)	linux	1.6.9	1
3_Healthy (In sync)	windows	1.6.9	1

Results:

• 36 out of 41 "Stuck" miners are on Windows.
• Versions match healthy miners (v1.6.10), ruling out a bad update.
  Insight: The issue is specific to the Windows environment.

3. What is breaking?

Goal: Analyze failure rates of internal components (Node, Wallet, Tor, P2Pool).

WITH max_block_height AS (
    SELECT 
        quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 1 MINUTE 
      AND version >= '0.9.913'
),
active_miners AS (
    SELECT 
        app_id,
        max(block_height) AS height,
        any(node_num_failure) AS node_fails,
        any(tor_num_failure) AS tor_fails,
        any(p2pool_num_failure) AS p2pool_fails,
        any(wallet_num_failure) AS wallet_fails
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 3 MINUTE 
      AND version >= '0.9.913'   
    GROUP BY app_id
)
SELECT 
    CASE 
        WHEN height < max_height - 1000 THEN '1_Stuck (>1000 blocks behind)'
        WHEN abs(height - max_height) <= 4 THEN '3_Healthy (In sync)'
        ELSE '2_In Between'
    END as status,
    round(avg(node_fails), 2) as avg_node_fails,
    round(avg(tor_fails), 2) as avg_tor_fails,
    round(avg(p2pool_fails), 2) as avg_p2pool_fails,
    round(avg(wallet_fails), 2) as avg_wallet_fails,
    COUNT(*) as miner_count
FROM active_miners, max_block_height
GROUP BY status
ORDER BY status ASC;

status	avg_node_fails	avg_tor_fails	avg_p2pool_fails	avg_wallet_fails	miner_count
1_Stuck (>1000 blocks behind)	60.41	362.61		28.49	41
2_In Between	12.83	0.83		52.08	24
3_Healthy (In sync)	4.92	14.55		72.94	819

Results:

• Healthy Miners: Avg Tor Fails = 14
• Stuck Miners: Avg Tor Fails = 362 (!!!)
  Insight: The Tor component is crashing repeatedly on stuck machines.

4. Tor Diagnostics: Why is it crashing?

Goal: Check error logs and uptime for the crashing Tor instances.

WITH max_block_height AS (
    SELECT 
        quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 1 MINUTE 
      AND version >= '0.9.913'
),
stuck_miners AS (
    SELECT 
        app_id,
        any(tor_num_restarts) AS tor_restarts,
        any(tor_uptime_seconds) AS tor_uptime,
        any(free_memory) AS free_ram,
        any(error) AS error_msg
    FROM airdrop.miner_logs_decoded, max_block_height
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 3 MINUTE 
      AND version >= '0.9.913'
      AND block_height < max_height - 1000
    GROUP BY app_id
)
SELECT 
    error_msg,
    round(avg(tor_restarts), 2) as avg_tor_restarts,
    round(avg(tor_uptime), 0) as avg_tor_uptime_sec,
    round(avg(free_ram)/1024/1024/1024, 2) as avg_free_ram_gb,
    COUNT(*) as miner_count
FROM stuck_miners
GROUP BY error_msg
ORDER BY miner_count DESC;

error_msg	avg_tor_restarts	avg_tor_uptime_sec	avg_free_ram_gb	miner_count
	179.95	84,267		41

Results:

• Avg Restarts: 180 times.
• Avg Uptime: 23+ hours.
• Error Msg: None (Silent failure).
  Insight: Tor is in an infinite boot-loop. It starts, gets killed immediately, and the app restarts it.

5. Network Isolation Check

Goal: Are they completely blocked from the internet?

WITH max_block_height AS (
    SELECT 
        quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 1 MINUTE 
      AND version >= '0.9.913'
),
stuck_miners AS (
    SELECT 
        app_id,
        max(block_height) AS height,
        any(p2pool_connected_peers) AS peers
    FROM airdrop.miner_logs_decoded, max_block_height
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 3 MINUTE 
      AND version >= '0.9.913'
      AND block_height < max_height - 1000
    GROUP BY app_id
)
SELECT 
    CASE 
        WHEN height <= 1 THEN '1_Never Synced (Height 0 or 1)'
        ELSE '2_Synced partially, then died'
    END as sync_status,
    round(avg(peers), 1) as avg_connected_peers,
    COUNT(*) as miner_count
FROM stuck_miners
GROUP BY sync_status
ORDER BY sync_status ASC;

sync_status	avg_connected_peers	miner_count
1_Never Synced (Height 0 or 1)		9
2_Synced partially, then died		34

WITH max_block_height AS (
    SELECT 
        quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 1 MINUTE 
      AND version >= '0.9.913'
),
stuck_miners AS (
    SELECT 
        app_id,
        max(block_height) AS height
    FROM airdrop.miner_logs_decoded, max_block_height
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 3 MINUTE 
      AND version >= '0.9.913'
      AND block_height < max_height - 1000
    GROUP BY app_id
)
SELECT 
    height AS stuck_at_block_height,
    COUNT(*) as miner_count
FROM stuck_miners
GROUP BY height
ORDER BY miner_count DESC
LIMIT 15;

stuck_at_block_height	miner_count
0	8
204,743	4
203,883	1
204,094	1
202,056	1
205,007	1
202,196	1
193,756	1
199,618	1
203,994	1
203,640	1
205,189	1
204,738	1
200,433	1
203,422	1

Results:

• Block Heights: Stuck at random heights (193k, 202k, 204k) or Block 0/1.
• Peers: 0 Connected Peers.
  Insight:
  • Block 0/1 users likely blocked by Firewall immediately upon install.
  • Random height users likely had Antivirus quarantine the Tor executable during a background scan.
  • This is not a blockchain consensus bug (no bad block).

6. The "Lagging" Group (The remaining ~2.7%)

Goal: Investigate miners stuck between 4 and 1000 blocks behind.

WITH max_block_height AS (
    SELECT 
        quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 1 MINUTE 
      AND version >= '0.9.913'
),
lagging_miners AS (
    SELECT 
        app_id,
        any(node_num_failure) AS node_fails,
        any(wallet_num_failure) AS wallet_fails,
        any(cpu_usage) AS system_cpu_usage,
        any(free_memory) AS free_ram,
        any(p2pool_connected_peers) AS peers
    FROM airdrop.miner_logs_decoded, max_block_height
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 3 MINUTE 
      AND version >= '0.9.913'
      AND block_height >= max_height - 1000
      AND block_height < max_height - 4
    GROUP BY app_id
)
SELECT 
    round(avg(node_fails), 1) as avg_node_fails,
    round(avg(wallet_fails), 1) as avg_wallet_fails,
    round(avg(system_cpu_usage), 1) as avg_cpu_usage_percent,
    round(avg(free_ram)/1024/1024/1024, 1) as avg_free_ram_gb,
    round(avg(peers), 1) as avg_connected_peers,
    COUNT(*) as miner_count
FROM lagging_miners;

avg_node_fails	avg_wallet_fails	avg_cpu_usage_percent	avg_free_ram_gb	avg_connected_peers	miner_count
4.9	16.8	46.7			22

WITH network_max AS (
    SELECT quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 1 MINUTE 
      AND version >= '0.9.913'
),
miner_stats AS (
    SELECT 
        app_id,
        max(block_height) as current_height,
        min(block_height) as past_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 30 MINUTE 
      AND version >= '0.9.913'
    GROUP BY app_id
)
SELECT 
    CASE 
        WHEN current_height > past_height THEN '1_Actively Syncing (Moving forward)'
        ELSE '2_Completely Frozen (No movement)'
    END AS sync_behavior,
    COUNT(*) AS miner_count
FROM miner_stats, network_max
WHERE current_height >= max_height - 1000 
  AND current_height < max_height - 4
GROUP BY sync_behavior
ORDER BY sync_behavior ASC;

sync_behavior	miner_count
1_Actively Syncing (Moving forward)	9
2_Completely Frozen (No movement)	30

Results:

• Failures: Node/Wallet/Tor failure rates are low/normal.
• Movement: ~30% are actively syncing (catching up). ~70% are frozen near the tip.
• Peers: Low to Zero.
  Insight: These users are not crashing. They simply lost connection to peers (e.g., PC went to sleep, Wi-Fi dropped) and the app did not auto-recover the P2P connection.

7. The "Frozen" Diagnostics

Goal: Distinguish between miners that are simply catching up vs. those that are frozen near the tip.

WITH network_max AS (
    SELECT quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 1 MINUTE 
      AND version >= '0.9.913'
),
miner_stats AS (
    SELECT 
        app_id,
        max(block_height) as current_height,
        min(block_height) as past_height,
        max(node_num_failure) as max_node_fails,
        max(wallet_num_failure) as max_wallet_fails,
        max(tor_num_failure) as max_tor_fails
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND COALESCE(cpu_hash_rate, gpu_hash_rate) > 0
      AND created_at > NOW() - INTERVAL 30 MINUTE 
      AND version >= '0.9.913'
    GROUP BY app_id
)
SELECT 
    current_height AS stuck_at_block,
    (max_height - current_height) AS blocks_behind_tip,
    round(avg(max_node_fails), 1) AS avg_node_fails,
    round(avg(max_wallet_fails), 1) AS avg_wallet_fails,
    round(avg(max_tor_fails), 1) AS avg_tor_fails,
    COUNT(*) AS miner_count
FROM miner_stats, network_max
WHERE current_height >= max_height - 1000 
  AND current_height < max_height - 4
  AND current_height = past_height
GROUP BY current_height, blocks_behind_tip
ORDER BY miner_count DESC
LIMIT 15;

stuck_at_block	blocks_behind_tip	avg_node_fails	avg_wallet_fails	avg_tor_fails	miner_count
206,305	7	0	0.5	0	2
206,117	195	1	9	0	1
206,272	40	0	1	0	1
205,857	455	2	0	0	1
205,984	328	1	0	0	1
205,393	919	1	7	0	1
205,992	320	1	0	0	1
206,102	210	0	1	0	1
205,378	934	1	1	0	1
206,271	41	1	1	0	1
206,260	52	15	11	0	1
205,635	677	1	147	0	1
206,299	13	1	5	0	1
206,028	284	0	1	0	1
206,188	124	0	1	0	1

Results:

• Status: ~30 miners are frozen (height hasn't changed in 30 mins).
• Block Distribution: Stuck at random heights (e.g., 206,305; 206,117; 205,857).
• Vitals: Node/Wallet/Tor failure counts are near zero.
  Insight:
  • Since they are stuck at different heights, this is not a consensus bug (bad block).
  • Since vitals are healthy but block height isn't moving, these nodes have likely lost their peer connections (0 peers) and are failing to auto-reconnect.

[SWvheerden]

If the frag numbers are local nodes and not remote nodes, then we should do two things here:
On the base node: Make sure that when we have 0 connection peers, we go to network silence mode, if its tor thats the issue; the base node cannot do anything about it. But if its not, we can change and make the network dialing more agressive.
On Universe, ensure we are looking for the network silence flag, and we can check tor etc, or restart the node if it goes too long.

[SWvheerden]

We still have a big issue with "remote only node" they dont have a base node that can go wrong. Yet they have a 6% fragmentation rate. We use the http calls for the node from TU side

[martinserts]

Let's check "Remote" nodes only

Remote nodes use simple HTTP calls to Seed Nodes, so they shouldn't get stuck.

The Reality

The HTTP client is hanging indefinitely when routing through the Tor proxy. It creates "Zombies" that never crash, and never recover.

---

Let's analyze only "remote" nodes, which have sent logs in last 20 minutes.

Are stuck ones using TOR or not?

WITH max_block_height AS (
    SELECT quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet' AND created_at > NOW() - INTERVAL 1 MINUTE
),
remote_miners AS (
    SELECT 
        app_id,
        any(config_tor_enabled) as uses_tor, -- Check if Tor is ON or OFF
        max(block_height) as height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND node_type = 'Remote' -- ONLY Remote Nodes
      AND created_at > NOW() - INTERVAL 20 MINUTE
      AND version >= '0.9.913'
    GROUP BY app_id
)
SELECT 
    CASE WHEN uses_tor = 1 THEN 'Using Tor' ELSE 'Not Using Tor' END as transport_layer,
    COUNT(*) as total_miners,
    SUM(CASE WHEN height < max_height - 1000 THEN 1 ELSE 0 END) as stuck_count,
    ROUND(SUM(CASE WHEN height < max_height - 1000 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as failure_rate_percent
FROM remote_miners, max_block_height
GROUP BY transport_layer;

transport_layer	total_miners	stuck_count	failure_rate_percent
Not Using Tor	17	0	0
Using Tor	733	9	1.23

• Not Using Tor: 17 miners -> 0 stuck (0% fail rate).
• Using Tor: 717 miners -> All stuck miners are in this group.
• Conclusion: The HTTP code works fine natively. But when Tor drops a packet or stalls, the HTTP client hangs forever instead of timing out.

---

Why fragmentation slowly rises over days (3% -> 8%)

Let's check the Uptime

WITH max_block_height AS (
    SELECT quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet' AND created_at > NOW() - INTERVAL 1 MINUTE
),
remote_node_health AS (
    SELECT 
        app_id,
        max(block_height) as height,
        max(uptime) as uptime_seconds
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND node_type = 'Remote'
      AND created_at > NOW() - INTERVAL 20 MINUTE
    GROUP BY app_id
)
SELECT 
    CASE 
        WHEN height < max_height - 1000 THEN '1_Stuck (Zombie)'
        ELSE '2_Healthy (Syncing)'
    END as status,
    COUNT(*) as miner_count,
    ROUND(AVG(uptime_seconds) / 3600, 1) as avg_uptime_hours,
    ROUND(MAX(uptime_seconds) / 3600, 1) as max_uptime_hours
FROM remote_node_health, max_block_height
GROUP BY status
ORDER BY status;

status	miner_count	avg_uptime_hours	max_uptime_hours
1_Stuck (Zombie)	10	184.3	999.4
2_Healthy (Syncing)	741	39.1	2,733.6

If nodes were crashing, they would restart and recover. But they aren't crashing.

• Healthy Nodes Avg Healthy Nodes Avg Uptime: ~39 hours.
• Stuck Nodes Avg Uptime: ~252 hours (10+ days).
• Conclusion: When we deploy a new update, the app restarts (fragmentation drops to ~3%). Over the next few days, users experience minor network/Tor drops. Their HTTP client hangs. They become "Zombies" and stay stuck for days. They accumulate, causing the slow rise to 8%.

[martinserts]

Let's check stuck/zombie by versions:

WITH max_block_height AS (
    SELECT quantile(.85)(block_height) AS max_height
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet' AND created_at > NOW() - INTERVAL 1 MINUTE
),
remote_miners AS (
    SELECT 
        app_id,
        any(version) as app_version,
        max(block_height) as height,
        max(uptime) as uptime_seconds
    FROM airdrop.miner_logs_decoded
    WHERE network='mainnet'
      AND node_type = 'Remote'
      AND created_at > NOW() - INTERVAL 20 MINUTE
    GROUP BY app_id
)
SELECT 
    CASE 
        WHEN height < max_height - 1000 THEN '1_Stuck (Zombie)'
        ELSE '2_Healthy (Syncing)'
    END as status,
    app_version,
    COUNT(*) as miner_count,
    ROUND(AVG(uptime_seconds) / 3600, 1) as avg_uptime_hours
FROM remote_miners, max_block_height
GROUP BY status, app_version
ORDER BY status, miner_count DESC;

status	app_version	miner_count	avg_uptime_hours
1_Stuck (Zombie)	1.6.11	6	18.5
1_Stuck (Zombie)	1.6.10	5	148.3
1_Stuck (Zombie)	1.6.9	1	999.7
2_Healthy (Syncing)	1.6.11	723	32
2_Healthy (Syncing)	1.6.10	13	98.6
2_Healthy (Syncing)	1.6.9	2	471.6
2_Healthy (Syncing)	1.6.5	1	2,733.9
2_Healthy (Syncing)	1.6.8	1	916.9