Performance

Another issue that is affecting performance is that we are running incoming updates through the migration script to ensure that the data we get from the game team is in the correct format.  This seems to be having a pretty major impact on the database.

As part of their weekly maintenance, EQ2 will be updating the data changes that we missed with last week's update and we should be able to turn off this script.  I'm hopeful that we'll get some performance back.

We are also getting more RAM for the database installed tomorrow.  There should be no downtime as we have redundancy.  I'll update this thread about that process just the same.

-dan

 

Fantastic news thanks.

 

FYI...we are updating the RAM on the database servers this morning.  You should see no impact on the service since we have redundancy.  One of the servers is down as I write this.

This is still a temporary step until we expand the resources.  It's not going to be enough, but it should be better.

-dan

 

Thanks for the info.  Once they're both back up with nice fresh expanded RAM, I'll be quite pleased to take em for a spin, kick the tires, etc. 

Great news!

 

Okay...the second server is now down for its upgrade, but all requests are now being handled by a server with 4x the RAM.  Give it a shot.

-dan

 

And the second (redundant) server is back online and syncing up.  I would be very interested in your experiences.  

We still have a couple of big changes that will make a difference coming, but this is the first step.

-dan

 

[url="census.daybreakgames.com/xml/get/eq2/item/?c:count=1">census.daybreakgames.com/xml/get/eq2/item/?c:count=1[/url]

[url="census.daybreakgames.com/xml/get/eq2/spell/?c:count=1">census.daybreakgames.com/xml/get/eq2/spell/?c:count=1[/url]

item only worked 1 time yesterday and took forever, spell worked 2 times but took far too long.

Today spell returned immediate results and item took about 1 second to show on my end.

Huge impact Dan, that's first observance now I can try some of what I was working at yesterday.

 

Awesome.  You know, these 2 particular queries would be better expressed as...

census.daybreakgames.com/xml/count/eq2/item/

census.daybreakgames.com/xml/count/eq2/spell/

Just sayin'.  

-dan

 

Yeah, I actually tried it yesterday and still got the timeout, it was painfully slow both ways. I asked that question in my post if it made a difference, I'll use that method, looks cleaner anyway.

[url="census.daybreakgames.com/xml/get/eq2/guild/?name=[guild name]&c:resolve=members(displayname,type.class,type.level,type.status,type.race,last_update,type.deity,type.gender,guild.rank,guild.status,equipmentslots,secondarytradeskills,tradeskills)">census.daybreakgames.com/xml/get/eq2/gui...ls,tradeskills)[/url]

This also returns the data much faster than it had been.

Good job Dan, sounds like the performance will get even better as well.

 

So far, it's rockin. 

Resolves that were taking over a minute yesterday are taking 6 seconds or less. 

All my searches seem significantly faster.

This is fanfreakingtastic!

Dethdlr

 

The improvement to the response times for the servers is incredible.

I've been doing some searches through items with criteria on multiple fields some I am pretty sure wouldn't be indexed and the response times to return a limit of 50 items is almost instant. The "Bean Counters" need to know that the money they spent on the RAM was well spent.

This is REALLY good stuff Dan.

 

I'm about to turn up the knob of pain on my queries.  I'll let you know how that works out shortly

 

Do your worst.

Another side benefit of our increased performance headroom is that we've been able to turn off our throttle of game data.  Previously, we would toss an update that was less than a certain amount of time so that the game server didn't overwhelm our system.  Since the update, our input queues have been reliably short.  The longest was 169 updates at 1:40pm PST.

To put that into perspective...the queues have been as long as 170,000 (both paperdolls and data updates) at peak times.  I'm going to be holding my breath through prime time (especially now that Oasis is back online), but I think we have a good steady-state for a little while. 

I'm not slowing down on the hardware front - we are now building up the entire cluster of machines serving this data to be much more scaled. 

-dan

 

And here I thought it was what my app was doing that was causing all of the slowness :$. Thanks a bunch for the upgrade Dan, looking forward to more!

 

This is awesome.  I coded our system on the hopes that performance would be like this before we launched.  Fantastic to find out that it's working as I hoped it had.  This is great.  

 

Nice job on the performance update!

 

Found a query that seems able to drag the data feed to its knees

The pvp['last_killer'] field contains a dbid value.  So, I was trying to pull the character that had that dbid:

Code:

census.daybreakgames.com/xml/get/eq2/character/?dbid=1167793

The query took 50 seconds to complete:

Code:

<character_list limit="1" returned="1" seconds="50.281651">

Any suggestion on how to optimize such a query?

 

The dbid is only specific to a particular world, so there is no index on it.  There is an index on the locationdata.worldid+dbid, however.  So, you should get performance from...

census.daybreakgames.com/xml/get/eq2/character/?dbid=1167793&locationdata.worldid=<span style="color: #ff0000;">000 (you need to include the value for the worldid)

The locationdata.worldid value for the last_killer will be the same as the character you are looking at.

-dan

 

I've got a follow up to that.  If the last_killer value IS a dbid, then all the needed data isn't presented, unless I'm missing something.  

My understanding of dbid is that it doesn't have to be globally unique.  It's only unique for a particular server right?  Otherwise, there would have been no reason to have the id field.  It's only when you combine the worldid with the dbid that you get a unique pointer to a character.

So if the only value available is dbid to identify last killer, you don't have enough info. <span style="color: #ff0000;"> If I duel someone on Butcherblock, they kill me, then I transfer to Guk and never duel again, when you look at my last_killer value, you'll be looking at a dbid from Butcherblock but will have no way to know that since the character you're looking at is on Guk.

So if this is actually a dbid value, it doesn't give enough info to accurately identify a character.  Unless I'm missing something.  <img src="/station/images/smilies/3b63d1616c5dfcf29f8a7a031aaa7cad.gif" border="0" />

 

Sure.  My initial response to Lantis was that we should change this to the globally unique ID, but I changed it because I found a solution that would work today.  

I don't know what would be required to provide the id (instead of the dbid).  I'll find out.

-dan