ESI Migration Report

May 9th, 2018

It’s now around 36 hours ago that the XML & CREST API have been turned of and send to the virtual graveyard. I can honestly say, the have served us well and we have seen lots of interesting things in the past: Lots of facepalms (Sovereignty Structures Endpoint), lot of hickups (API shutdown during the alliance tournament) or gamedesign changes (removed jumps/kills from wormhole systems). We’ve seen a lot! And we’re still rolling!

But don’t look back the most important thing:

DOTLAN EveMaps has successfully been migrated to the new ESI backend and is now running smooth for around 2 days.

I’ve put a lot of effort into cleaning up the mess EveMaps 🙂 has become over years. With the usual highs and lows it took me around 5 months and a lot of git commits create the new ESI backend, refactoring most data collecting daemons, remove dead stuff (like Dust514 bits and pieces) or fix what was broken (zkillboard/evewho).

This isn’t the first time I had do a major rewrite of the backend to prepare for bigger game design changes or new APIs. Back in 2009 CCP has changed from daily sov changes to the dynamic system some people currently “enjoy” and later the introduction of CREST … anyway.

Lets proceed with the very long list:

What’s new on the backend side:

  • Introduced a new ESI library that takes are of requesting, caching (ETag/Expires headers) and archiving. In the beginning I’ve written the stuff with etag headers in mind (and even fake 304 not modified internally), but thankfully CCP updated their API in the last minute that can safe us tons of unneeded calls.
  • Since we now have to handle multiple thousands requests to get the same result as one of the old XML calls the majority of calls should be in bulk mitigate latency and the serial processing I’ve done in the past.
  • Sure you’ve to first some bulk requests of like 1.000 alliances (and the corp memberships) before abusing the database to commit the changes.
  • Converting/Refactoring for basically all daemon jobs that update the database using the XML or CREST result (or a combination of both).
  • The “Login with Eve Online SSO” Authentication now supports requesting scopes. It’s currently not used yet but might be important if I want to access data that is only available via authenticated endpoints.
  • Stats: On the first day the system has generated:
    • 3.760 single esi calls
    • 4.599 bulk calls (with a total 225.658 esi calls)
    • 217.310 calls resulted in “304 not modified” thanks to ETag/Expires Headers
    • 1.891 updates have been processed

What has has been removed:

  • Basically all user api related tools (My Eve). I offered things like CharacterSheet, Standings Overlay, Asset Manager, etc. I guess I was one of the rare users myself, at least when I was actively playing.
  • All things from Dust514 have been removed. I had a lot of bits and pieces left all over the space with the districts, sub pages and graphs.
  • The ingame browser support
  • Radar tracking (was broken without the old ingame browser)
  • The latest station build page.

What has been fixed:

  • When zkillboard switchover to esi processing of killmails the api Squizz was providing switched from CREST to ESI without notice (at least for myself). It’s now back working again
  • Something broke and the pilots of an alliance or corp (provided by evewho) wouldn’t be displayed. This has been fixed as well.
  • 3rd Party Facebook Authentication (OAuth2 stuff and upgrading GraphAPI version)
  • Somehow Some wars weren’t tracked correctly in the past. With the rewrite, this has been fixed.
  • Wars again: Some alliance had too many wars that were too many to render and resulted in memory limit reached :-). I’ve added pagination, so The Marmite Collective can enjoy their full list of 21.000+ wars they’ve had in the past.
  • The captcha protection on the traditional registration form was broken.
  • The paypal donation button was bugged somehow. It’s working again!

Performance and database issues:

  • Some of you might have expired a lot of hickups and database errors on EveMaps at various times during the day. The root cause were a bad sql query not using the indexes correctly and therefore taken very long (the alliance/corp movement overview) in combination with incoming corp updates (INSERTs) on a table with 230mil rows that were blocking all further corp chart requests and in the end consuming all free mysql connection slots configured for the frontend.
  • Yes this history table (corpID, datetime, memberCount) is a MyISAM table for a reason instead of InnoDB like everything else. The performance and size was way better the last few times I compared it with InnoDB. With newer mysql/mariadb version it might be a different game but the storage amount would still be 3-5 times the size of what is is currently (9gb data + index).
  • This issue got resolved after fixing the query, later adding a similar summary table like I used for kills in the last 3 hours and switching from INSERT to INSERT LOW PRIORITY.
  • Another database lookup occurred during the nightly mysqldump for backups. Dumping a table with 230mil rows (even piped through gzip to lower IO writes) takes it’s time (and locks the table). I’ve updated the mysql backup script to support mysqldumps with where statements dumping only the current/previous month. No need to dump 10 years of history every day and blocking the table for ~15-20 minutes every night. It’s now a matter of seconds.

I’m still looking into optimizing small things left and right to keep EveMaps running and running and running without lot of effort on the maintenance. Basically I should start relaxing now again but Eve is currently flow of constant movements and changes. And there are a few things on the horizon that really requires attention and I waiting for the next grey hair it would generate!

Upcoming / Roadmap / Todos / Ideas:

  • Removal of outposts:
    Yes really! CCP just announced on the Fanfest 2018 that all player build outposts and conquerable stations (basically all non-npc stations in 0.0) will be migrated to faction citadels. This means like all upwell structures they’re no longer public detectable. Sure there will be “public” citadel which can be queried with an authenticated esi endpoint. But that’s something different. Removal of outposts basically means that all stations information in 0.0 will be gone and also the map gets rid of stations icons (at least for player owned nullsec systems).
  • Structures:
    Í’m thinking about integrating upwell structures into the system. I would start with the public structures and thinking about potentially adding private structures as long as people willing to share via Esi where they’re able to dock. But this is future thinking.
  • OS Upgrades:
    EveMaps VM and the KVM Host needs to get some love and needs to be upgraded/reinstalled. At some point I’ll start preparing it and do the migration/switchover.

7 Responses to “ESI Migration Report”

  1. Dant Perst says:

    Was wondering why Jumps and 24hr Jumps are not showing on Dotlan? Any fix in the future.

    BTW, thanks for perhaps the most useful out of game DB for EVE. Your work is appreciated immensely.

  2. Jambe says:

    Thanks for all your work on Dotlan! EVE would be mostly unplayable for me without it.

    An issue came up for me: I follow your RSS feed with Type: Incursion and Actions: AttackStart and AttackEnd, but now that feed’s outputting only State actions (apparently) and AttackEnd and AttackStart no longer show up in the Action selection box. Both AttackStart and AttackEnd still show up in the main feed at though.

  3. Rental Pride says:

    Time to bury conq stations/outposts:
    They still included in some places (e.g. alliance list).
    And history of their changes – may be add final records like “2018-06-05 11:00 Transformed to *one of 5 names* Fortizar”?
    Thanks for great site!

    • Wollari says:

      It‘s on my todo list. I have to remove the outpost stuff, introduce end dates and add structures into the mix. Beginning with public ones

Valid XHTML Valid SVG PHP MySQL NGINX Webserver Pheal API Libary Firefox EVE Onlline Free SSL Secured By StartCom Twitter @wollari Facebook
API J:30 Sep 13:50 K:30 Sep 13:56 C:30 Sep 12:00 A:30 Sep 13:53 O:04 Jun 11:15 F:30 Sep 13:57 S:29 Sep 11:24 W:30 Sep 13:53