0

I have recently started working with the Path of Exile API (https://api.pathofexile.com/public-stash-tabs) to track Items for sale by users The API json is 4 layers deep before I get to the items which I actually care about.

{"stashes":[{"items":[{"name":"item1"},{"name":"item2"}]},{"items":[{"name":"item3"},{"name":"item4"}]}]}

My current attack method is to

1: insert the full json into a poll table from a seperately written program running on another server. (also stores insert time and the poll ID/Next Poll ID.)

insert into polls(
        called_id,
        returned_id, 
        data
    )values ($1,$2,$3)

2: On insert to polls there is a trigger that parses the stashes and updates/inserts each one into the stashes table with the relavent data extracted.

BEGIN
    UPDATE
        stashes
    SET
        last_character_name = date2 ->> 'lastCharacterName',
        stash_name = date2 ->> 'stash',
        items = date2 -> 'items',
        is_public = (date2 ->> 'public')::bool,
        fetched_on_id = NEW.called_id,
        next_fetch_id = NEW.returned_id,
        fetch_index = NEW.id,
        league = date2 ->> 'league'
    FROM
        jsonb_array_elements(NEW.data -> 'stashes') date2
WHERE
    date2 ->> 'id' = stashes.stash_id;

    /**/
    INSERT INTO stashes (account_name, last_character_name, stash_id, stash_name, stash_type, items, is_public, fetched_on_id, next_fetch_id, fetch_index, league)
SELECT
    date2 ->> 'accountName',
    date2 ->> 'lastCharacterName',
    date2 ->> 'id',
    date2 ->> 'stash',
    date2 ->> 'stashType',
    date2 -> 'items',
    (date2 ->> 'public')::bool,
    NEW.called_id,
    NEW.returned_id,
    NEW.id,
    date2 ->> 'league'
FROM
    jsonb_array_elements(NEW.data -> 'stashes') AS date2 ON CONFLICT DO nothing;
    RETURN NEW;
END;

3: on insert to stashes there is a trigger that parses and deletes/updates/inserts into the items table.

BEGIN
    DELETE FROM items
    WHERE --
        item_id NOT IN (
            SELECT
                d ->> 'id'
            FROM
                jsonb_array_elements(NEW.items) d)
        AND stash_id = NEW.stash_id;

    /**/
    UPDATE
        items
    SET
        abyss_jewel = (date2 ->> 'abyssJewel')::bool,
        additional_properties = date2 -> 'additionalProperties',
        art_filename = date2 ->> 'artFilename',
        category = date2 -> 'extended',
        corrupted = (date2 ->> 'corrupted')::bool,
        cosmetic_mods = date2 -> 'cosmeticMods',
        crafted_mods = date2 -> 'craftedMods',
        descr_text = date2 ->> 'descrText',
        duplicated = (date2 ->> 'duplicated')::bool,
        elder = (date2 ->> 'elder')::bool,
        enchant_mods = date2 -> 'enchantMods',
        explicit_mods = date2 -> 'explicitMods',
        flavor_text = date2 -> 'flavorText',
        frame_type = (date2 ->> 'frameType')::integer,
        icon = date2 ->> 'icon',
        item_id = date2 ->> 'id',
        identified = (date2 ->> 'identieied')::bool,
        ilvl = (date2 ->> 'ilvl')::integer,
        implicit_mods = date2 -> 'implicitMods',
        is_relic = (date2 ->> 'isRelic')::bool,
        league = new.league,
        locked_to_character = (date2 ->> 'lockedToCharacter')::bool,
        max_stack_size = (date2 ->> 'maxStackSize')::integer,
        item_name = date2 ->> 'itemName',
        next_level_requirements = date2 -> 'nextLevelRequirements',
        note = date2 ->> 'note',
        properties = date2 -> 'properties',
        prophecy_diff_text = date2 ->> 'prophecyDiffText',
        prophecy_text = date2 ->> 'prophecyText',
        requirements = date2 -> 'requirements',
        sec_descr_text = date2 ->> 'secDescrText',
        shaper = (date2 ->> 'shaper')::bool,
        socketed_items = date2 -> 'socketedItems',
        sockets = date2 -> 'sockets',
        stack_size = (date2 ->> 'stackSize')::integer,
        support = (date2 ->> 'support')::bool,
        talisman_tier = (date2 ->> 'talismanTier')::integer,
        type_line = date2 ->> 'typeLine',
        utility_mods = date2 -> 'utilityMods',
        verified = (date2 ->> 'verified')::bool,
        height = (date2 ->> 'h')::integer,
        width = (date2 ->> 'w')::integer,
        x = (date2 ->> 'x')::integer,
        y = (date2 ->> 'y')::integer,
        fetch_id = new.fetched_on_id,
        is_public = new.is_public,
        last_character_name = NEW.last_character_name,
        stash_name = new.stash_name,
        stash_type = NEW.stash_type
    FROM
        jsonb_array_elements(NEW.items) date2
WHERE
    date2 ->> 'id' = items.stash_id;

    /**/
    INSERT INTO items ( --
        abyss_jewel, --
        additional_properties, --
        art_filename, --
        category, --
        corrupted, --
        cosmetic_mods, --
        crafted_mods, --
        descr_text, --
        duplicated, --
        elder, --
        enchant_mods, --
        explicit_mods, --
        flavor_text, --
        frame_type, --
        icon, --
        item_id, --
        identified, --
        ilvl, --
        implicit_mods, --
        is_relic, --
        league, --
        locked_to_character, --
        max_stack_size, --
        item_name, --
        next_level_requirements, --
        note, --
        properties, --
        prophecy_diff_text, --
        prophecy_text, --
        requirements, --
        sec_descr_text, --
        shaper, --
        socketed_items, --
        sockets, --
        stack_size, --
        support, --
        talisman_tier, --
        type_line, --
        utility_mods, --
        verified, --
        height, --
        width, --
        x, --
        y, --
        fetch_id, --
        stash_id, --
        is_public, --
        account_name, --
        last_character_name, --
        stash_name, --
        stash_type)
SELECT
    (date2 ->> 'abyssJewel')::bool,
    date2 -> 'additionalProperties',
    date2 ->> 'artFilename',
    date2 -> 'extended',
    (date2 ->> 'corrupted')::bool,
    date2 -> 'cosmeticMods',
    date2 -> 'craftedMods',
    date2 ->> 'descrText',
    (date2 ->> 'duplicated')::bool,
    (date2 ->> 'elder')::bool,
    date2 -> 'enchantMods',
    date2 -> 'explicitMods',
    date2 -> 'flavorText',
    (date2 ->> 'frameType')::integer,
    date2 ->> 'icon',
    date2 ->> 'id',
    (date2 ->> 'identieied')::bool,
    (date2 ->> 'ilvl')::integer,
    date2 -> 'implicitMods',
    (date2 ->> 'isRelic')::bool,
    new.league,
    (date2 ->> 'lockedToCharacter')::bool,
    (date2 ->> 'maxStackSize')::integer,
    date2 ->> 'itemName',
    date2 -> 'nextLevelRequirements',
    date2 ->> 'note',
    date2 -> 'properties',
    date2 ->> 'prophecyDiffText',
    date2 ->> 'prophecyText',
    date2 -> 'requirements',
    date2 ->> 'secDescrText',
    (date2 ->> 'shaper')::bool,
    date2 -> 'socketedItems',
    date2 -> 'sockets',
    (date2 ->> 'stackSize')::integer,
    (date2 ->> 'support')::bool,
    (date2 ->> 'talismanTier')::integer,
    date2 ->> 'typeLine',
    date2 -> 'utilityMods',
    (date2 ->> 'verified')::bool,
    (date2 ->> 'h')::integer,
    (date2 ->> 'w')::integer,
    (date2 ->> 'x')::integer,
    (date2 ->> 'y')::integer,
    new.fetched_on_id, --fetch_id
    new.stash_id, --stash_id
    new.is_public, --is_public
    NEW.account_name, --account_name
    NEW.last_character_name, --last_character_name
    new.stash_name, --stash_name
    new.stash_type --stash_type
FROM
    jsonb_array_elements(NEW.items) AS date2 --
    ON CONFLICT DO nothing;

    /**/
    RETURN NEW;
END;

Given that I only need to track the one instance of a item and one instance of a stash, I update if I find a matching uuid. I also need to make sure that I remove any items that a user no longer has in their stash tabs. (IE it sold.)

My issue is that I am VERY close to not following the datastream as far as I can tell. My best guess, based on the usage stats that I am seeing, is that I am CPU bound (due to context switches from the triggers?) not IO bound. I am at about 80% usage on only one core, and 10%? max IO throughput.

Is my usage of triggers causing context switching based cpu bottelknecking? (or if nothing else determine if it is context switching based)

Is there a way to improve my processing/insertion speed more than marginally?

Is there a way to use more than one core in this kind of process?

  • Code may not be exact. Will edit with current code when I have access to the server after Maintenance. – deef0000dragon1 Oct 15 at 0:51
  • Where are your transactional boundaries? – jjanes Oct 15 at 12:57
  • What is your OS and version? What usage stats are you seeing? – jjanes Oct 15 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.