EVE Metrics and popularity

Well, it’s been a while. I’ve been busy with things in this nasty place called reality which has been kicking my ass as a result with all sorts of fun ailments. Recently though I was forced to pay a bit more attention to EVE and specifically EVE Metrics.

A couple of months back we worked with E-ON to put an advert in the magazine as well as running a login screen advert, and this has now run. So for a few days anyone logging into EVE will have been greeted with this fine advert created by Zapatero at MMM Publishing.

Of course, this suddenly meant more people going to and using the site. Fortunately one of the primary concerns when myself and Makurid put EM2 and later EM3 together was scalability. Whereas EM1 would’ve fallen over and died, EM3 has soldiered on like a champ. The only intervention we’ve had to perform was to fix the API processor, which was hanging regularly and causing problems as a result. That’s fixed, and we’re now stable and responsive. So, of course, we now have some numbers! These are the statistics for the most recent 30 days as of this post.

  • ~85,000 visits
  • ~500,000 page views
  • ~5 pages per visit
  • ~29,000 unique visitors
  • ~1,500 additional account registrations as a result of login screen advert (Above ~20 account registrations per day baseline)

In terms of the site’s dataset, we’re getting fairly huge.

  • 6,000 EVE API keys, of which 4,000 are full keys
  • 75,000 EVE API methods enabled
  • 32,000 EVE API calls per hour (about 10 every second)
  • 75,000 characters (includes characters noted in market and other data)
  • 15,000,000 wallet journal entries
  • 6,300,000 wallet transactions
  • 200,000 EVE Mails
  • 1,600,000 active market orders
  • 6,400,000 EVE API loaded trades
  • 52,000,000 Inferred trades
  • 11,000,000 processed uploads
  • 146,000,000,000 total skillpoints of loaded characters
  • 4,100,000,000,000 total ISK of loaded characters

Which is… scary. But! We’ve grown hugely (a threefold increase in requests per second) and we’re still performing well. The site is not throwing errors, users are generally happy with their experience with the site, and we’re stable – no crashes, no fires breaking out, no climbing resource usage. Our caching and design philosophies have worked well and the extra effort invested earlier in development has really paid off. We’ve just handled a huge amount of growth with no effort at all. I’d estimate we’re good to around 12,000-15,000 users on our current hardware.

Of course, we’re now wondering what to do. I stopped playing EVE long ago aside from the odd spot of tinkering, but I’ve even let accounts lapse and stopped updating my skill queue as of a month or two ago. Makurid’s just started playing again recently. We’re not really heavily invested in EM in terms of motivation, other than making a cool webapp.

Capsuleer, as I’m sure many of you know, is an EVE iPhone app that recently shut down because it couldn’t be monetized and the time and money invested in it by the developers was simply unreasonable. A couple of weeks back I was looking at the realities of what EM costs to run, and what it costs in terms of time to maintain. And the logistics of, if needed, shutting the site down. This is still somewhat in my mind but the site will be sticking around for a little while yet. I’m very much hoping CCP will come to their senses in terms of how they choose to support third party developers (a free account subscription appears to be the greatest gift given by CCP, but that’s a far cry from the ~£130 per month it costs to run EVE Metrics – and that’s just our costs _now_, not including new hardware costs or upgrade costs. If we grow much more we’re going to be looking at ~£200/mo to run EM, possibly more). Advertising isn’t an option, and donations have failed every time we’ve tried to support ourselves with them. Timecode sale affiliations barely made a mark on my accounting sheets. We do enjoy writing sites and producing something big that people find useful, but there are certain realities to be faced – both myself and Makurid are students, with no full-time jobs. I’ve recently picked up some part time work which means I can now afford to buy bacon on a weekly basis again.

In the meantime we will continue to support the EVE community by maintaining EVE Metrics. We’re working on the codebase right now to upgrade it to the latest and greatest Rails release and Makurid’s beavering away at the API processor to split it up into a puller and a processor, enabling us to achieve much higher throughput on API calls to CCP’s (slow) servers. Now that we’ve breached 30,000 requests per hour, that 10 request per second figure is actually beyond our capacity right now because of our single-threaded processor/puller mechanism. This is actually a CCP limit- we just need to work around it by hitting them with more requests simultaneously, thus spreading our load over multiple servers on their end. I’ll also be making performance and usability improvements wherever I can and incorporating as many bug reports as possible into what we release. This will likely be a slowish process, but in the long run, it should be worth it. The question really is, will CCP make it worthwhile for us to be investing our time and energy into longer term planning?

PostgreSQL recovery tips

Having run into some disk issues last night we’d not expected, I had some scary moments trying to find any resources for PostgreSQL recovery scenarios relating to disk failure. I chalk this up to most PostgreSQL users being sensible and using RAID1 or similar. We’re doing things on the mother of all shoestring budgets, though, so when disks start spewing things like:

[11180714.763689] ata2.00: exception Emask 0x0 SAct 0x1f SErr 0x0
action 0x0
[11180714.763726] ata2.00: irq_stat 0x40000008
[11180714.763760] ata2.00: cmd 60/08:20:17:6c:a4/00:00:31:00:00/40 tag
4 ncq 4096 in
[11180714.763761]          res 41/40:00:19:6c:a4/ff:00:31:00:00/40
Emask 0x409 (media error) <F>
[11180714.763864] ata2.00: status: { DRDY ERR }
[11180714.763893] ata2.00: error: { UNC }
[11180714.765974] ata2.00: configured for UDMA/133
[11180714.765989] sd 1:0:0:0: [sdb] Unhandled sense code
[11180714.765991] sd 1:0:0:0: [sdb] Result: hostbyte=DID_OK
[11180714.765995] sd 1:0:0:0: [sdb] Sense Key : Medium Error [current]
[11180714.765999] Descriptor sense data with sense descriptors (in hex):
[11180714.766001]         72 03 11 04 00 00 00 0c 00 0a 80 00 00 00 00 00
[11180714.766010]         31 a4 6c 19
[11180714.766014] sd 1:0:0:0: [sdb] Add. Sense: Unrecovered read error
- auto reallocate failed
[11180714.766019] end_request: I/O error, dev sdb, sector 832859161
[11180714.766062] ata2: EH complete

… you really, really panic.

sdb in this case is one of our large 500GB disks we use for archival, so I knew damage would be limited to some of the archive tables at worst. With some help from the #postgresql channel on Freenode I got to work.

First things first: Shut down PostgreSQL. service postgresql-8.4 stop in our case.

Next, we copy all the data we can off that disk. Turned out it was just throwing errors on one file, so I duplicated the tablespace except that file onto our other 500GB disk. That one file was pg_data/16394/461543 in our tablespace – 16394 being the evemetrics_production database OID, but I didn’t know what this file was.

Once I’d moved all the data across to another disk I umounted the old one and got to work on bringing the server back up without the file.

At this point it’s worth noting one thing I did before all this cropped up: I’d taken a full backup with pg_dump, which had completed without errors. This lead me to think that the file we were looking at was an index or some system catalog.

Next, I sorted out the tablespace symlink for our sdb tablespace:

# cd /var/lib/postgresql/8.4/main/pg_tblspc/
# ls -lar
lrwxrwxrwx  1 postgres postgres   14 2010-05-14 20:32 461544 -> /disk2/pg_data
lrwxrwxrwx  1 postgres postgres   20 2010-05-14 20:44 461543 -> /disk1/pg_data
# rm 461543
# ln -s /disk2/disk1/pg_data 461543

With our tablespace now pointing at the backup copy, I brought the server back online with service postgresql-8.4 start.

Before you do anything else, you now have to update your tablespace entry on the server or Bad Things happen.

UPDATE pg_tablespace WHERE spclocation = '/disk1/pg_data' SET spclocation = '/disk2/disk1/pg_data'

Now we could find out what that file was:

postgres@pandora:/disk2$ /usr/lib/postgresql/8.4/bin/oid2name -d evemetrics_production -f 461577
From database "evemetrics_production":
  Filenode               Table Name
    461577  index_on_api_request_id
And there you have it- an index! We got off seriously lucky here, and RAID1/10/5 would’ve saved us if we had the money for it. All we had to do was issue a REINDEX command on that table and we were good to bring everything back up. Moral of the story? Backup often, backup early, and use RAID. Also, reliable disks are _so_ worth the money.

EVE Metrics 3

Okay, as someone who feels an obligation to post at least once a month I’m ashamed. 8th of May? Time to sort that out.

So here’s a post about EVE Metrics 3.

We’re getting close to having everything polished and ready for release. The main issue at hand thus far has been the homepage; I say this like it’s a small thing but it’s required me to learn some new and interesting things about CSS, Makurid’s done some excellent work to produce some feed scrapers and elements for the lower portion of the page… there’s a lot to it.

I thought that it’d be good to list a few of the changes we’ve made for version 3.

  • Complete redesign of the site thanks to Rettic
  • Market detail pages have been entirely renovated
  • Various pages which haven’t been improved in some time have now been tidied up
  • API key management has been improved
  • API key permissions management has been improved
  • Backend processors for API functions and upload processing have been improved and made more reliable
  • My Metrics has been entirely renovated, now with sparklines for wallets and a new layout
  • Orders and transactions have been moved from My Metrics into their own detail pages, with a summary on My Metrics
  • Journal information has been added and given it’s own detail page
  • Player Owned Structure integration has been added, though still in it’s infancy
  • Sensitive portions of the site now make use of SSL transport encryption (HTTPS) automatically
  • Wormhole pages have been updated
  • Improvements to the corporation pages through refactoring to share view code between character and corporation pages
  • Graph improvements
  • Complete test coverage of every line of code (Nah, just kidding, we’re still pretty thin on those test things for large chunks of UI code)
  • 0.2% more cowbell
  • 5% other features I’ve not listed above, plus 100% more polish overall

Excited? We are! There’s a lot of work in the lines above and I think you’ll like the results. We’re not sticking to any firm release schedule because we’re terrible at sticking to them; we’re students, not full-time developers (incidentally, if anyone’s got any jobs available for temporary/contract work, SE UK preferred (or work-at-home), 6 weeks max, let me know!). That said, we hope to have a release before the end of July.

We’ve also been rewriting our uploader! That’s right- Linux, Mac and Windows support all in one neat package. The GUI’s not anything special but it works, and we’ll be polishing it and getting it release-ready before long. Huge thanks should be directed to TTimo, who has been the driving force behind this with some welcome Python experience, and Makurid for assisting him in developing the new client.

Once we’ve gotten that polished, packaged and rolled out, we’ll be running a 5 billion ISK contest to promote it- the three winners (each receiving a portion of the 5 billion pool) will be selected from the most active uploaders for the week or two after the competition is announced. We’ll finalize all the details and have it posted up when we’re ready to go ahead with that, of course. If you’d like to make that 5 billion figure larger, you can contribute ISK to the character MMMetrics Agent ingame! So far, thanks go out to Rilcon, Chribba and Entity for contributing to the current pool. The new uploader will be released after the new site – we’d like to change one thing at a time so we can iron out all the kinks. Once we’ve gotten it out and tested it thoroughly we’ll roll out the upgrade- your existing uploader will prompt you to update when you start it.

One last thing – I’ve personally submitted a proposal to the CSM regarding development efforts from CCP surrounding the API and EVE Gate. If you’ve not done so already, I strongly urge you to read my proposal and support it if you feel, like I do, that CCP have made some serious mistakes lately in this regard. The thread can be found here. It has been picked up and supported by at least 3 CSM members so far, but the community support will help considerably to drive CCP to consider it seriously.