Friday, June 13, 2014

The import of Friday the 13th

The import process is still running, as it appears I royally underestimated the time needed for the database server to get through the last batch. About 4.5GB of raw data has been pre-processed into a 700MB SQL that is still being imported as I write this. Many small mistakes escalated into the current situation, here is what happened:

Mistake one: I decided to create one large batch instead of many smaller ones. The last few imports were going too fine, and I decided to make a big step. Bad move.

Mistake two: I've put the whole batch into one transaction. While importing in a transaction is generally faster then doing it without, at this scale it becomes a long-running transaction that imposes many pitfalls. One being the possibility of user generated writes (comments and screenshots) clashing with a record modified in transaction, which leads to the write request being stalled until the transaction concludes.

Mistake three: I've misidentified the hanging connections issue as being low on database connections, so I happily increased the limit. How dumb of me. This only led to more hanging write connections waiting for the transaction to be finished, but more connections eat more memory, so practically I served all the available memory at the database server on a platter to be hanged in Limbo. So the database server is now at its knees (hindering performance even more), importing the batch with its last breath. Go database server!

So right now it seems it needs a few more hours (somewhere between 1 and 4, but it's a wild guess). I've glued myself to the monitor, and will launch the site at the very moment the import process is over. Really sorry for the mess.

3 comments:

  1. Thanks for the update and all you do, I'm sure you'll get it up and running when you can. It's a great site, so I'm just happy you're still working at it to get the update through rather than calling it quits. :)

    P.S. not everyone has a wordpress.com account or wants to use their google account. If you could add some more options, that'd be great!

    ReplyDelete
  2. Not quite the right place, but it I have 2 features that I think would REALLY put your site ahead of the others :)

    1. - show crafting boards/plots - I would love to be able to look at a crafting schematic and see the socket colors and efficiencies, or even have some form of calculator in place (much more difficult).

    2. - items with "random" specials, somehow show a list of all specials they could possibly random.

    ReplyDelete