<jupyter_start><jupyter_text>Optimistic Provide - Status Quo Analysis  Setup  Import Dependencies<jupyter_code>import sqlalchemy as sa
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

sns.set_theme()

plt.rcParams['figure.figsize'] = [12, 7]<jupyter_output><empty_output><jupyter_text>Establish Database Connection<jupyter_code>conn = sa.create_engine("postgresql://optprov:password@localhost:5432/optprov")<jupyter_output><empty_output><jupyter_text>Analysis  Define Helper functions<jupyter_code>def cdf(series: pd.Series) -> pd.DataFrame:
    """ calculates the cumulative distribution function of the given series"""
    return pd.DataFrame.from_dict({
        series.name: np.append(series.sort_values(), series.max()),
        "cdf": np.linspace(0, 1, len(series) + 1)
    })<jupyter_output><empty_output><jupyter_text>Numbers<jupyter_code>query = """
SELECT * FROM provides WHERE measurement_id = 5;
"""
df = pd.read_sql_query(query, con=conn)
len(df)
query = """
SELECT * FROM measurements WHERE id = 5;
"""
pd.read_sql_query(query, con=conn)<jupyter_output><empty_output><jupyter_text>Durations Overall Provide Operation<jupyter_code>column_name = "Duration in s"

query = f"""
SELECT
    EXTRACT('epoch' FROM ended_at - started_at) "{column_name}"
FROM provides WHERE measurement_id = 5;
"""
df = pd.read_sql_query(query, con=conn)


cdf_df = cdf(df[column_name])

print(df[column_name].quantile(0.5))

fig, ax = plt.subplots(figsize=(12, 7))
ax.step(cdf_df[column_name], cdf_df["cdf"])
ax.set_xlabel(column_name)
ax.set_ylabel("CDF")
ax.set_title(f"Number of Provide Operations {len(df)}, p50 {df[column_name].quantile(0.5):.2f}s, p90 {df[column_name].quantile(0.9):.2f}s, p99 {df[column_name].quantile(0.99):.2f}s")
ax.set_xlim(-10,90)<jupyter_output>5.1578465<jupyter_text>Connections per Provide<jupyter_code>column_name = "Number of Connections"

query = f"""
SELECT count(pxc.connection_id) "{column_name}"
FROM provides p
         INNER JOIN provides_x_connections pxc on p.id = pxc.provide_id
WHERE p.measurement_id = 5
GROUP BY p.id
"""
df = pd.read_sql_query(query, con=conn)
len(df)
cdf_df = cdf(df[column_name])

fig, ax = plt.subplots()
ax.step(cdf_df[column_name], cdf_df["cdf"])
ax.set_xlabel(column_name + " per Provide")
ax.set_ylabel("CDF")
ax.set_title(f"Number of Provide Operations {len(df)}")<jupyter_output><empty_output><jupyter_text>Dials per Provide<jupyter_code>query = """
SELECT
    sum(1) dials_count,
    sum(1) FILTER ( WHERE d.error IS NULL OR d.error = 'context canceled' ) successful_dials
FROM provides p
         INNER JOIN provides_x_dials pxd on p.id = pxd.provide_id
         INNER JOIN dials d on pxd.dial_id = d.id
WHERE p.measurement_id = 5
GROUP BY p.id
"""
df = pd.read_sql_query(query, con=conn)
fig, ax = plt.subplots()

cdf_df = cdf(df["dials_count"])
ax.step(cdf_df["dials_count"], cdf_df["cdf"], label=f"All Dials")

cdf_df = cdf(df["successful_dials"])
ax.step(cdf_df["successful_dials"], cdf_df["cdf"], label=f"Successful Dials")
ax.set_xlabel("Number of Dials per Provide")
ax.set_ylabel("CDF")
ax.set_title(f"Number of Provide Operations {len(df)}")
ax.legend()<jupyter_output><empty_output><jupyter_text>`FIND_NODES` RPCs per Provide<jupyter_code>query = """
SELECT
    sum(1) find_nodes_rpcs_count,
    sum(1) FILTER ( WHERE apr.error IS NULL ) successful_find_nodes_rpcs_count
FROM provides p
         INNER JOIN provides_x_find_nodes_rpcs pxapr on p.id = pxapr.provide_id
         INNER JOIN find_nodes_rpcs apr on pxapr.find_nodes_rpc_id = apr.id
WHERE p.measurement_id = 5
GROUP BY p.id
"""
df = pd.read_sql_query(query, con=conn)
fig, ax = plt.subplots()

cdf_df = cdf(df["find_nodes_rpcs_count"])
ax.step(cdf_df["find_nodes_rpcs_count"], cdf_df["cdf"], label=f"All FIND_NODES RPCs")

cdf_df = cdf(df["successful_find_nodes_rpcs_count"])
ax.step(cdf_df["successful_find_nodes_rpcs_count"], cdf_df["cdf"], label=f"Successful FIND_NODES RPCs")
ax.set_xlabel("Number of FIND_NODES RPCs per Provide")
ax.set_ylabel("CDF")
ax.set_title(f"Number of Provide Operations {len(df)}")<jupyter_output><empty_output><jupyter_text>`ADD_PROVIDER` RPCs per Provide<jupyter_code>query = """
SELECT
    sum(1) add_provider_rpcs_count,
    sum(1) FILTER ( WHERE apr.error IS NULL ) successful_add_provider_rpcs_count
FROM provides p
         INNER JOIN provides_x_add_provider_rpcs pxapr on p.id = pxapr.provide_id
         INNER JOIN add_provider_rpcs apr on pxapr.add_provider_rpc_id = apr.id
WHERE p.measurement_id = 5
GROUP BY p.id
"""
df = pd.read_sql_query(query, con=conn)
fig, ax = plt.subplots()

cdf_df = cdf(df["add_provider_rpcs_count"])
ax.step(cdf_df["add_provider_rpcs_count"], cdf_df["cdf"], label=f"All ADD_PROVIDER RPCs")

cdf_df = cdf(df["successful_add_provider_rpcs_count"])
ax.step(cdf_df["successful_add_provider_rpcs_count"], cdf_df["cdf"], label=f"Successful ADD_PROVIDER RPCs")
ax.set_xlabel("Number of ADD_PROVIDER RPCs per Provide")
ax.set_ylabel("CDF")
ax.set_title(f"Number of Provide Operations {len(df)}")
ax.set_xticks(np.arange(0, 21, step=1))
plt.legend()
plt.plot()<jupyter_output><empty_output><jupyter_text>Distances Selected Provider Record Storing Peers<jupyter_code>query = """
SELECT encode(apr.distance, 'hex') distance FROM provides p
         INNER JOIN provides_x_add_provider_rpcs pxapr on p.id = pxapr.provide_id
         INNER JOIN add_provider_rpcs apr on pxapr.add_provider_rpc_id = apr.id
WHERE p.measurement_id = 5
"""
df = pd.read_sql_query(query, con=conn)
df["distance_normed"] = df.apply(lambda row: int(row["distance"], base=16) / (2 ** 256 - 1), axis=1)
df["distance_normed_pct"] = df.apply(lambda row: 100 * row["distance_normed"], axis=1)

fig, ax = plt.subplots()

cdf_df = cdf(df["distance_normed_pct"])
ax.step(cdf_df["distance_normed_pct"], cdf_df["cdf"], label=f"All Distances")

ax.set_xlabel("Normed XOR Distance in %")
ax.set_ylabel("CDF")
ax.set_title(f"Number of ADD_PROVIDER RPCs {len(df)}")
ax.legend()
plt.hist(df["distance_normed_pct"], bins=30)
plt.plot()<jupyter_output><empty_output><jupyter_text>Distribution of Average Provider Record storing peer distance per Provide<jupyter_code>query = """
SELECT array_agg(encode(apr.distance, 'hex')) distances
FROM provides p
         INNER JOIN provides_x_add_provider_rpcs pxapr on p.id = pxapr.provide_id
         INNER JOIN add_provider_rpcs apr on pxapr.add_provider_rpc_id = apr.id
WHERE p.measurement_id = 5
GROUP BY p.id
"""
df = pd.read_sql_query(query, con=conn)
df["distance_normed_pct_avg"] = df.apply(
    lambda row: np.average(list(map(lambda distance: 100 * int(distance, base=16) / (2 ** 256 - 1), row["distances"]))),
    axis=1)
df["distance_normed_pct_median"] = df.apply(
    lambda row: np.median(list(map(lambda distance: 100 * int(distance, base=16) / (2 ** 256 - 1), row["distances"]))),
    axis=1)

fig, ax = plt.subplots()

cdf_df = cdf(df["distance_normed_pct_avg"])
ax.step(cdf_df["distance_normed_pct_avg"], cdf_df["cdf"], label=f"Average Distance per Provide")

# cdf_df = cdf(df["distance_normed_pct_median"])
# ax.step(cdf_df["distance_normed_pct_median"], cdf_df["cdf"], label=f"Median Distance per Provide")

ax.set_xlabel("Normed XOR Distance in %")
ax.set_ylabel("CDF")
ax.set_title(f"Number of Provide Operations {len(df)}")
ax.legend()
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=[20, 7])

ax1.hist(df["distance_normed_pct_avg"], bins=30, label=f"Average Distance per Provide")
ax1.set_xlabel("Normed XOR Distance in %")
ax1.set_ylabel("Count")
ax1.set_title(f"Number of Provide Operations {len(df)}")
ax1.legend()

ax2.hist(df["distance_normed_pct_median"], bins=30, label=f"Median Distance per Provide")
ax2.set_xlabel("Normed XOR Distance in %")
ax2.set_ylabel("Count")
ax2.set_title(f"Number of Provide Operations {len(df)}")
ax2.legend()<jupyter_output><empty_output><jupyter_text>Follow-Up Analysis<jupyter_code>query = """
SELECT p.id,
       EXTRACT('epoch' FROM max(fnr.ended_at) FILTER ( WHERE fnr.query_id != '00000000-0000-0000-0000-000000000000' ) -
                            min(fnr.started_at)
                            FILTER ( WHERE fnr.query_id != '00000000-0000-0000-0000-000000000000' )) "dht_walk",
       coalesce(EXTRACT('epoch' FROM
                        max(fnr.ended_at) FILTER ( WHERE fnr.query_id = '00000000-0000-0000-0000-000000000000' ) -
                        min(fnr.started_at) FILTER ( WHERE fnr.query_id = '00000000-0000-0000-0000-000000000000' )),
                0)                                                                                   "follow_up"
FROM provides p
         INNER JOIN provides_x_find_nodes_rpcs pxfnr on p.id = pxfnr.provide_id
         INNER JOIN find_nodes_rpcs fnr on fnr.id = pxfnr.find_nodes_rpc_id
WHERE p.measurement_id = 5
GROUP BY p.id
"""
df = pd.read_sql_query(query, con=conn)
fig, ax = plt.subplots()

cdf_df = cdf(df["dht_walk"])
ax.step(cdf_df["dht_walk"], cdf_df["cdf"], label=f"DHT Walk")

cdf_df = cdf(df["follow_up"])
ax.step(cdf_df["follow_up"], cdf_df["cdf"], label=f"Follow Up")

ax.set_xlabel("Duration in s")
ax.set_ylabel("CDF")
ax.set_title(f"Number of Provide Operations {len(df)}")
ax.legend()
df['ratio'] = df.apply(lambda row: row["follow_up"] / row["dht_walk"], axis=1)
fig, ax = plt.subplots()

cdf_df = cdf(df["ratio"])
ax.step(cdf_df["ratio"], cdf_df["cdf"], label=f"Follow-Up to DHT Walk Ratio")

ax.set_xlabel("Ratio Follow-Up Duration/DHT Walk Duration")
ax.set_ylabel("CDF")
ax.set_title(f"Number of Provide Operations {len(df)}")
ax.legend()<jupyter_output><empty_output><jupyter_text>Routing Table Analysis<jupyter_code>query = """
SELECT EXTRACT('epoch' FROM p.ended_at - p.started_at) "provide_duration",
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT('epoch' FROM rts.created_at - rte.added_at)) "median_age_in_s"
FROM provides p
         INNER JOIN routing_table_snapshots rts on rts.id = p.initial_routing_table_id
         INNER JOIN routing_table_entries rte on rts.id = rte.routing_table_snapshot_id
WHERE p.measurement_id = 5
GROUP BY p.id
"""
df = pd.read_sql_query(query, con=conn)
fig, ax = plt.subplots()

ax.scatter(df["median_age_in_s"], df["provide_duration"])

m, c = np.polyfit(df["median_age_in_s"], df["provide_duration"], 1)
ax.plot(df["median_age_in_s"], m * df["median_age_in_s"] + c, color="orange", label="linear fit")

ax.set_xlabel("Median Routing Table Age in s")
ax.set_ylabel("Provide Duration in s")

ax.set_title(f"Number of Provide Operations {len(df)}")
ax.legend()<jupyter_output><empty_output><jupyter_text>Discovery Analysis<jupyter_code>query = """
WITH providers AS (
    SELECT p.id provide_id, p.started_at, apr.remote_id
    FROM provides p
             INNER JOIN provides_x_add_provider_rpcs pxapr on p.id = pxapr.provide_id
             INNER JOIN add_provider_rpcs apr on pxapr.add_provider_rpc_id = apr.id
    WHERE p.measurement_id = 5
),
     referrers AS (
         SELECT p.*, ps.referrer_id
         FROM providers p
                  INNER JOIN provides_x_peer_states pxps on p.provide_id = pxps.provide_id
                  INNER JOIN peer_states ps on pxps.peer_state_id = ps.id
         WHERE ps.peer_id = p.remote_id
           AND pxps.provide_id = p.provide_id
     ),
     find_nodes AS (
         SELECT r.*,
                coalesce(EXTRACT('epoch' FROM (SELECT min(fnr.ended_at)
                                               FROM find_nodes_rpcs fnr
                                                        INNER JOIN provides_x_find_nodes_rpcs pxfnr on fnr.id = pxfnr.find_nodes_rpc_id
                                               WHERE pxfnr.provide_id = r.provide_id
                                                 AND fnr.remote_id = r.referrer_id) - r.started_at), 0) delay_in_s
         FROM referrers r
     )
SELECT fn.provide_id, fn.remote_id, fn.referrer_id, fn.delay_in_s
FROM find_nodes fn
"""
df = pd.read_sql_query(query, con=conn)
fig, ax = plt.subplots()

cdf_df = cdf(df["delay_in_s"])
ax.step(cdf_df["delay_in_s"], cdf_df["cdf"])

ax.set_xlabel("Time to Discover eventually selected Peers in s")
ax.set_ylabel("CDF")
ax.set_title(f"Number of Provide Operations {len(df)}")<jupyter_output><empty_output><jupyter_text>Final Routing Table Overlap<jupyter_code>query = """
WITH cte AS (
    SELECT p.id provide_id, ps.peer_id, count(DISTINCT ps.query_id) query_count
    FROM provides p
             INNER JOIN provides_x_peer_states pxps on p.id = pxps.provide_id
             INNER JOIN peer_states ps on ps.id = pxps.peer_state_id
    WHERE p.measurement_id = 5
    GROUP BY p.id, ps.peer_id
)
SELECT cte.provide_id, count(cte.query_count)
FROM cte
WHERE cte.query_count > 1
GROUP BY cte.provide_id
"""
df = pd.read_sql_query(query, con=conn)
fig, ax = plt.subplots()

cdf_df = cdf(df["count"])
ax.step(cdf_df["count"], cdf_df["cdf"])

ax.set_ylabel("CDF")
ax.set_title(f"Query Overlap after reaching threshold of 20")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1313201703445606, 'fraction_numerical': 0.02013162988772745, 'mean_word_length': 4.581676750216076, 'pattern_counts': {'":': 1, '<': 60, '<?xml version=': 0, '>': 62, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 8, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '146339', 'license': 'Apache-2.0', 'n_tokens_mistral': 5160, 'n_tokens_neox': 4898, 'n_words': 961, 'path': 'jupyter/optprov_mq.ipynb', 'repo_name': 'dennis-tra/optimistic-provide', 'size': '12915'}