Challenges of Archiving Industry Index Values

Posted: 2014-08-20 | Author: | Filed under: eveonline | Tags: , , , , | 6 Comments

If you have any interest in 3rd party development or databases, this post will be a entertaining as I share my current lackluster architecture for saving some of the new Eve API data.

I have a Raspberry Pi running MySQL that I use as a basic storage location for various databases. One of them is a new database that contains index values from the CREST API endpoint with a timestamp so I can start to develop a archive of the values.

The current solution I have for importing the new Index data isn’t very elegant. I’m using Lockefox’s Crius Toolset sheet to grab a CSV every day, adding two columns for a primary key and a timestamp, and importing into my table to get a table that looks like this:

“transactionID” “transactionDateTime” “solarSystemName” “solarSystemID” “manufacturing”
“103724” “2014-08-19 13:28:00” “Jouvulen” “30011392” “0.0339317910000”
“103725” “2014-08-19 13:28:00” “Urhinichi” “30040141” “0.0236668590000”
“103726” “2014-08-19 13:28:00” “Akiainavas” “30011407” “0.0285709850000”
“103727” “2014-08-19 13:28:00” “Seitam” “30041672” “0.0162879230000”
“103728” “2014-08-19 13:28:00” “BK4-YC” “30003757” “0.0143238350000”

It’s growing and starting to show how under-powered the Raspberry Pi is for data processing. Most of my issue stems from a lack of salable design on my part. I have no table indexes and am joining with the bulky mapDenormalize table.

I have a love-hate relationship with the mapDenormalize table. If you have ever worked with this table, you know that it is a beast: 502,558 rows, 15 columns with five of them being DOUBLE values coming in at 214MB. Normally not a problem for server with a lot of CPU cycles and RAM, but the 700MHz ARM processor on the Raspberry Pi has a hard time with multiple JOIN operations and GROUP BYs.

Here’s a query I was running against my dataset that ran for 15.5 minutes (!).

SELECT systemCosts.solarSystemName, systemCosts.transactionDateTime ,systemCosts.manufacturing, mapRegions.regionName, mapDenormalize.security
FROM systemCosts
JOIN mapDenormalize
ON (systemCosts.solarSystemID = mapDenormalize.solarSystemID)
JOIN mapRegions
ON (mapDenormalize.regionID = mapRegions.regionID)
GROUP BY transactionDateTime,solarSystemName

So two full table JOIN and two GROUP operations and a table with no indexes, uf. I sent a screenshot off to James, my development partner.

2014-08-04_terrible_query

My first solution was to remove extraneous data from the mapDenomalize table. After removing the groupID, constellationID, orbitID, x, y, z, radius, celestialIndex, and orbitIndex I trimmed the table down even further by deleting all entries that were not star systems. What was left was 7,929 rows coming in at around 1MB.

I’m glad to report that my terrible query is now running in 20 seconds. This was a small step to getting my growing index dataset to a usable state while I write something more permanent.

Data Warehousing using EVE Market Data Relay (EMDR)

Posted: 2013-02-20 | Author: | Filed under: eveonline, industry, market | Tags: , , , , , | 3 Comments

Overview

Knowing the price and volume of items moving in the Eve market at a specific point in time is a very powerful piece of information that can help further your space empire.

Historically eve-central was the repository of market data but with new advances in cache scraping, data transport methods, and large archival storage methods have brought us exciting new capabilities.

tl;dr EMDR

What the heck is EMDR and why should I care?

Original implementations of market aggregation sites had a user click the Market Export button in the Eve client to produce text files in their local Documents\EVE\logs\Marketlogs folder and then use an application to transfer the exported files to a database. This method was tedious, lacked region and item coverage, and was prone to people editing the data before it was sent off.

A renascence was generated when developers began to explore the cache in the Eve client. The client cache is a location that serves as temporary storage for information that you are working with in the client; it is volatile and changes all the time rather than holding static art, formulas, or universe data as seen in the client .stuff files.

2013-02-20_stuff_files

EMDR is a service that takes market orders from the cache of your local client and sends that immediately upon viewing it in the client to a relay service that people can subscribe to. If you click on an item in the market window, that piece of data is immediately sent off to many people that can receive it. The transfer is quick, the data is not tampered with, and it can easily be relayed to many interested parties. This is pure data, a statisticians dream!

Working with Eve-Central

What follows are notes from my partner, Raath, who has been withing with the EMDR method to improve our industrial operation.

When I first began adding price dependencies to the DRK Industry Tracker, it was back in the day before we had the EMDR. Eve-central was the bespoke out of game price lookup service and with the aid of their API I pulled prices from there.

At the time I don’t think it had the option to do a full pull of prices on every item in game so I devised a system where the tracker would cache prices locally and update them when they were older than an hour on a need to know mechanic so only the relevant data was requested.

I did this so that I didn’t swamp eve-central with hundreds of requests every hour for information that 95% of which would never be used. It was a system that worked well except for the occasional lag caused by the prices updating. As I said before, it only updated when prices were older than an hour as the users requested them. But when I started to think of releasing the tracker publicly, I needed something a little more reliable.

Transition to EMDR

At this point in time the EMDR was a fully developed solution so I started to look into how I could begin using this as my price basis and integrating data into our industrial workflow. Not really knowing the volume of information that would soon be assaulting our little virtual machine, I make a few mistakes.

Flood of Data

The term for the EMDR feed that people have adopted is the “fire hose” as it is literally a flood of overwhelming data being constantly sent at you with no regard for your ability to process it. As clients all across the world are clicking around the market window, updates are being sent to you in near real-time. The function name of “engage_fire_hose” only seemed natural.

2013-02-20_engage_fire_hose

Statistics

Our Industry site currently consumes 8 gig of data, serves 6-700 MB and calls around 150,000 API requests per day with the vast majority of our incoming data coming from the EMDR updates. In January we received 179 GB and sent 17 GB for an average of 5.8 GB/day of incoming data.

2013-02-20_january_bandwidth_utilization

Processing Challenges

With the large amount of incoming price data, we needed to be able to efficiently process and store it while keeping the server responsive.

The first method I devised was a system where I stored transactionIDs in a hash table. This design soon showed its weakness as we hit a memory limit when the hash table started to fill up with millions of transactionIDs.

Additionally my attempts to keep load on MySQL server down were also in vain as the information was coming in so quickly that my consumer was having trouble keeping up. We soon had problems with locked rows and inserts failing so I had to completely revise my entire approach.

What resulted was a consumer process that guzzles around 8 GB of data per day and spits all the information to CSV files. There is no logical processing done in the consumer now, it just munches and spits data to file. Another cron job that runs every minute scans the temp file directory for CSVs, parses them all into one single large CSV, and then performs a LOAD DATA IN FILE into MySQL, a method which I’ve found is not only lightening fast but also keeps the load down.

Not all of the data is useful as the majority of it is duplicates. When the server has free time, we run a cleanup process in the form of flag checking remove expired records so that the data we display is as up to date.

2013-02-20_market_database

Future Plans

All this work for clean price data. We have plans to further expand some of the market data functionality in projects to start using the cached market history to show the historical price or items and build costs compared to live data.

Trading 202: Challenges of Asset Tracking

Posted: 2012-12-16 | Author: | Filed under: eveonline, industry | Tags: , | 4 Comments

Overview

You buy something and then sell it somewhere else at a higher price. This process is easy enough to do, but for accurate profit records it turns out to be rather troublesome due to how asset identification is implemented in Eve at the database level.

If you want to preserve a chain of custody for bookkeeping reasons, you need some additional tools for asset tracking.

Stacks and itemIDs

A single item or a stack of items is stored in the database as a single row with a unique itemID. Below is an exmaple of a Medium Shield Drone that I have in a Dodixie station.

2012-12-16_asset_example-1

Every item in the game as a unique itemID that is created and destroyed when it is stacked or split.  Here is an example of what happens when you take a stack of ore, split it, and then combine it again.

assets_combined

This is a very simple example, but imagine a larger trading operation. You are buying 200 implants and 3 are for your personal characters, 100 are going to your primary trade hub, and the other 97 to your secondary trade hub. As you split up the stacks, your itemIDs change. The chain of ownership gets lost along the way.

While your current orders are selling, you are going to buy more of the same implants so you can replenish stock in the trade hub. This new purchase is at a slightly higher cost, but well within good profit margins.

So how do you calculate profit? You have items at a certain price currently selling and then a new price to work with for the next batch. Do you use the last price that you bought it at? A global price from a source like Eve-Central? An average?

The ideal solution involves working with a record of quantity and price at a point in time. Once you have this information, you can keep better track of profits as you continuously sell and buy items.

Solutions

1. First In, First Out

This system can be explained by James, my coding right hand man. Here is a quote when we introduced a ‘inventory’ table to our Wallet Manager.

“As far as getting profit and costing, that’s pretty simple. We get costs in a FIFO basis (first in, first out). As purchases come in through the API, they go to the ‘inventory’ table. As things are sold, the quantities are deducted from the items in that table and the profit calculated on the original purchase cost. This allows for a pretty accurate profit calculation.

The caveat is that method is pretty prone to drift. You might buy 1,000 heavy missiles you don’t plan on selling, but they’ll end up in the table. However, we have a way of combating that.

One is that the tool works best when the characters being tracked do nothing but trade or produce. Because the Eve API doesn’t allow you to track specific instances of items well enough, that’s really the first line of defense. You can always spin an alt to buy your toys and keep them off the tool’s books.”

2. Assign Assets to a Project

Raath, my production partner that heads up development at industry.darkshadowindustries.com, uses Projects.

A asset or resource that enters the system can be assigned to a project. That item and the price can then be tracked and rolled into the final profit calculation of the job when it is completed.

Wallet Manager Project Code

Posted: 2012-10-22 | Author: | Filed under: industry, market | Tags: , , | 65 Comments

If you haven’t been following this blog, I’ve periodically posted about a “Wallet Manager” project that myself and my trading partners, Raath and James, have been working on for the past two years to help us manage our Eve activities using the Eve API.

The project started small, with the first page being driven only by the wallet API feed. As we expanded our industrial scope in the game so too did the Wallet Manager’s requirements. Soon I needed to keep track of blueprints, assets, capital production, PI, and tech 2 invention; each new requirement expanded the original scope.

What grew was a project that contained many methods by coders of different proficiencies. James took my undergraduate C/C++ knowledge and showed me how to work with more objects, models, JavaScript, and even Ajax. Over the course of two years we spent many hours developing methods to handle assets (when items get stacked their unique ID changes), minerals pricing, API caching, and ultimately usability.

The code backend is not something we are extremely proud of, but the presentation and workflow has suited our operations well. If you have the fortitude to tolerate our design, then feel free to give our project a test run.

Disclaimer *IMPORTANT*

  1. Code is provided as is.
  2. If you want to try to setup your own instance, I would advice that you have an understanding of PHP, MySQL, and general code architecture. Getting this to run is not a beginner project.
  3. I cannot provide any commitment for bugs corrections, adding features, or providing support.
  4. There are many, many non-sanitized input points that are major security issues as noted in this post. I would not use this as a public-facing site.
  5. There are unfinished pages, hacked together solutions, code in the view, display code in the controllers, etc. — don’t expect clean, production quality code.
  6. Be prepared to edit the database table to enter your users and API keys. The ‘admin’ section is only partially completed.

Prerequisites

  1. MySQL, PHP5+
  2. Yii Framework
  3. phpMyAdmin to efficiently work with the database

Installation

  1. Download the ZIP here. [April 2014 Update] I’ve moved the project to GitHub here.
  2. Extract the code to your var/www folder
  3. Edit protected/config/main.php.changeme
    1. Line 29 contains IP limitations for working with Gii. Enter your WAN IP address here if you want to work with this module.
    2. Line 57/58 contain your database connection information.
    3. Rename to main.php
  4. Edit protected/config/console.php.changeme
    1. Line 22 contains the database name. My project was named after my holding corporation PROHD so the database name is ‘prohd’.
    2. Line 24/25 contain the user/password to login to the application.
    3. Rename to console.php.
  5. Modify Yii path in index.php
    1. Change the path to your Yii framework location $yii=’/usr/local/lib/yii/yii.php’;
  6. Replace /usr/local/lib/yii/caching/CDbCache.php with the one in the zip.
  7. Create database structure with prohd.sql
  8. Create a login in the ‘accounts’ table. The default userLevel is ‘1’.
  9. Enable the API feed by adding a 1,1 row to the apiStatus table.
  10. Define a group in trackingGroups table.
  11. Add your character information in the ‘characters’  table.
  12. Import the Eve static data tables from https://www.fuzzwork.co.uk/dump/
  13. Import the following table into typeBuildReqs for Tech 2 items. You can generate this table using my query or import from the previous table export.

Recommendations

If you are intimated by the scope of the setup procedure, I would recommend that you use the DRK Industry Tracker for your construction projects as my partner Raath is actively developing this project.

Update

https://k162space.com/2013/08/13/wallet-manager-code-update/

Blake’s Papercuts

Posted: 2012-09-27 | Author: | Filed under: eveonline, industry, market, nullsec | Tags: , , , , , , | 2 Comments

Inspired by Jester’s post about accepting Eve as it stands, I started to record my own personal headaches while performing Industry and Trade related tasks.

Manufacturing/Invention
1. Menu items on Blueprints: I want to be able to Right-click, build or Right-click, invent from the hangar.
3. Batch processing: multi-select right-click, build/invent.
3. Two stage jobs, like capitals that have components that are build from minerals, can be built with one click from the capital Blueprint.
4. Easy display of Blueprints ME/PE from hanger view.
5. Stack Blueprints of equal ME/PE levels.
6. Multi cycle runs. Make 10 items 10 times as long as you have the required items in stock.
7. Pause jobs if they run out of materials and allow resume when sufficient items are placed in the hangar.

Booster Trade
1. Expand profession. Currently I know of a few major manufacturers of drugs and distribute to niche markets. I want to be able to have more players in the market, many more markets, and a feeling of sneaking contraband around the galaxy.
2. Given the limitations on production to POS’es, the barrier of entry for manufacturers is high. I remember a long time ago hearing about how Mittani proposed a drug manufacturing ship; imagine a stealthy ship that arrives out of nowhere, makes you drugs, and retreats into the abyss.

API
1. Expose Blueprint ME/PE/Run/Remaining.
2. Expose tower CPU/Power utilization and any timers such as anchoring, on-lining, or reinforced. (Metagame++;)
3. Expose saved ship fits for read/write to allow developers to create alliance/corp/mobile 3rd party applications for working with fits using XML.
4. Do not allow the buying, selling, modification of orders (I’ve wrote about the potential botting implications here).

POS Rework
1. Grant the ability to move POS’es between characters, corporations, and alliances.
2. Scrap the worthless Refining Arrays. They refine at too high a loss, are cumbersome to load and start refine jobs. In the new system you should be able to create paths between modules like we have in PI to transfer goods for constant production.
3. Add a mechanic for hacking/taking over offlined and abondoned towers.

Unified Inventory
1. Dragging something to the station Item or Ship hangar should automatically place it in the proper hangar. I hate having to drag ships to ships and item to items. The goods are going to my local hangar, just put them there.

General
1. Automatically remove me from my ship, stop skills from training, and initiate the clone jump.
2. Prompt/warn me that my clone is out of date before undocking.

Timers
1. I want to be able to see the time remaining for various things (GCC, etc) in the same fashion as the undock timer.

Kill Reports
1. Add Logistic repair amount.
2. Add coordinate and time data down to the millisecond to allow people to develop applications to replay and analyse battles.

← Older Entries