I know you guys hate Trade. But come on!!!

"

vio wrote:

i worked with that db but no complex stuff. i'm just interrested on how do you build a "proper indexer" around it for the several thousands of item types, hundreds of different affixes and affix value ranges items can have?

That's actually not a problem at all. It doesn't matter how many item types there are, they are nothing more then a 'tag' basically. Same going for the specific affixes. It only becomes a bother when checking for specific value-ranges, but even those can be simplified vastly in comparison to the existing system.

First of all the base itself isn't overly important when searching for a finished item, you want the respective values as an end-result, this allows for some simplification of the indexer.

First there are specific 'categories' presented in which the indexer pre-sorts items. Based on Phys DPS, Elemental DPS, Chaos DPS, Total DPS and Attacks per second.
This for instance is the following:
Category 1 = 50 DPS
Category 2 = 100 DPS
and so on.

Now if someone searches only the respective category which updates every 5 minutes has the be copied over, the actual query only accesses a very limited amount of the database, hence lowering the strain on the system.

Then we can go further, GGG SHOULD (but probably doesn't) have a weighting on which mods are searched most of all. Having the system automatically handle the categories respective to weight is an important thing there.
Hence if during the last... let's say 5 hours the search for T1 %phys + T1 flat phys was done a lot more then the other mods... then those get a separate category for the next 5 hours. Further lowering the burden on the query.

Also we can simply force the same system on magic items, here it's even easier. A much-searched mod on it? Into a specific category. Not? Well, it can stay in the 'normal' as those produce low ongoing loads anyway.

Now we got white items... those need only ilvl sorting, really easy as each of them is the same, simply sort by price and done.

Also if you put double T1 suffix/prefix items and tripple T1 suffix/prefix items into a separate category the burden on the system is lowered even further.

So it looks like this at the end:
5 minutes are over, the system checks the total amount of queries for each individual mod. All heavy loads get separated for the future until the load sinks underneath a specific value.
Then the system goes once through the whole database (which it needs to do anyways regularly at the moment) and sets all of said items into their specific categories.
Afterwards an indexer is made which is accessed as a priority before even going further into the system.

So, as soon as someone makes a search the database first looks 'Is it one of the most-used combinations?' which is a smaller list then the individual items by far. If yes it only searches through that specific category... or another indexer to slim down the process further along the way. Each 'inception' of this method creates less and less burden along the way. Of it's a no for any of the specific indexers it goes over to do a 'broad search', simply taking the full list and checking the respective search-terms.
This means if 'T1 non-ailment chaos' on items is searched extremely often it will take all viable bases with this mod and T1 available and pre-store the query. Mid-load would pre-store all lower rolls regarding this query as well then. Suddenly the indexer only has to search through 15k options rather then 500 million, already sorted out beforehand. The heavier the load the smaller the respective 'chunk' of information to access as little database as possible.

Hence a search would look roughly the following:
-The game gets a query. It first looks through the league, each league has a specific table attached with all items for sale.
-Then it decides with 'category' the search is based on. Equipment, consumable, unique? All of them provide different important parameters.
-Since equipment has the most variance there is another step to decide if it's a 'heavy load' 'mid load' or 'low load' query, respective to how many roughly same ones where searched for before.
-Heavy load has more tables underneath to even decide which data-set to access, the heavier the load-category the more precisely it's pre-chosen.
-Having only specific data-sets available to even look through, slimmed down as much as possible the respective list can easily be accessed. So the system only has to search through a very limited amount of lines instead of going through a large chunk (or worst-case all) of the lines.
-Then, and only then it shows the actual search-results.

This requires a massive storage for static data as the data needs to be kept accessible for at least 30 minutes before it can be deleted, hence 'old' listings need to be shown still if someone decides to go up to page 135 for some odd reason.
On the other hand fluid data is held at a minimum, the indexers are re-created every 5 minutes... staggering them so they are re-made in 'waves' can lower the load massively with this system.

But well... that's just beginner-stuff for optimization of loads, far more intricate options available out there as well, mine is actually sub-par... still better then what most databases do though.

"

vio wrote:

then there are several leagues with individual markets.

They are handled completely separately, hence they shouldn't even look at items from another league in the first place. Top-tier indexing would only access the league an item is in first... then move down the layers one after another. No issue performance-wise as the amount of leagues is very small, usually less then 1000 with private-leagues added even.

"

vio wrote:

additionally, players can search for prices in all the currencies the game curently has.

That's a fairly high priority query, hence searching for a specific currency-type would throw out a hefty load of listings already.

"

vio wrote:

that's alot of index tables you will have there and it's a permanently updated db with incoming data from 10+ gameservers and the forum entries from the realm server constantly need to be merged.

Which is the reason why I'm saying that GGG needs to detach the trading-system from the premium-tabs, create trade-slots (and provide a specific amount for every premium-tab bought), give every tab the base-functionality of a premium one for re-naming and sorting and add the option to buy 'trade slots' on the market. Also they need to provide a base-amount to F2P people, 10 should be more then enough.
This removes the dependency on Forum and the API and allows an in-game version of the indexers... a single venue to get data and thus lowering the load. Also disrupting the whole API-abusing bot-community.

"

vio wrote:

it's sad that the times ggg would take the efforts to explain their implementations are gone.

Yes, very sad... but understandable. The quality of their implementations has gone down a lot, knowledgeable people would realize just par any future-thought the code is by now. Hence they simply don't communicate it anymore.

"

Kulze wrote:

[...]

Do you have any actual experience with working on a db of such a scale with such constraints ?


Because you make it sound like it's simple ... by throwing pure theory here it seems.

And we definitely don't know all the intricacies of GGG's system/db either so ...

"

Fruz wrote:

"

Kulze wrote:

[...]

Do you have any actual experience with working on a db of such a scale with such constraints ?


Because you make it sound like it's simple ... by throwing pure theory here it seems.

And we definitely don't know all the intricacies of GGG's system/db either so ...

You don't need to if you understand the base-mechanics of how load-optimization works.

Less lines accessed = less load.
Hence every method to reduce accessed lines per query reduces obviously load.

Less intervals for updates = more load
Hence quick turnover items without variability (consumables) need to be listed immediately while low turnover (gear) can have a fairly long staggered effect where new lists are added after a specific time-frame.

Since we don't have any sort of communication regarding the system the community can only throw out bareback-models, not detailed stuff.
What we can say though: Current system shit, needs adjustment. Large-scale websites with massive databases (Let's take Deviantart, massive data huge amount of queries each second) manage, GGG doesn't. Hence there is a problem somewhere which can be solved.


You don't need to be a master-chef to realize that a hotter flame will cause stuff to char outside but keep it raw inside... same line of thought there.

well, i'm just interested in such tech stuff. currently reading about apache solr.

looks like a cool system which works independent of the database used when i got it right. you can just feed it the json data the api would send to external sources and get indexed queries back in real time.


edit: according to the incident report it takes them 40 minutes to roll back the item database and account databases.

they must be huge.

"

ffogell wrote:

"

Novynn wrote:

This is because the search term is too broad and our site only sorts on the first 5000 matches. Try using some search filters (setting a maximum price for example) to get more accurate results.

We should probably raise this limit slightly but it will always be a factor to be aware of.

omg that is fucking horrible.

I always thought that sorting on the result would re-order and gives you an accurate search result o_O

I feel like I have been ripped off now the whole time I have been using your official site without knowing this lmao

EDIT: People need to be informed about that ASAP.

So this why i have been getting different results with official trade and poe.trade

I also though the same as you. So the cheapest results by official site might not actually be the cheapest?
Does anyone know how many results poe.trade and poeapp.com sort?

"

Kulze wrote:

You don't need to if you understand the base-mechanics of how load-optimization works.

Stop.

This is already wrong, very wrong.

You are missing elements brought by experience, things that you pretty much cannot know or understand well.
And if you really think that pure theory gives you all the knowledge and comprehension to know in details how projects of such a scale work, then you likely have a lot to learn imho.

"

Kulze wrote:

Large-scale websites with massive databases (Let's take Deviantart, massive data huge amount of queries each second) manage, GGG doesn't. Hence there is a problem somewhere which can be solved

.....
Deviant Art's database and PoE's database are likely extremely different things, and you likely lack quite a lot of knowledge about both of those, so ...

Yes, there are problems with their databases as this thread showed, but that does not mean that you know ... you just know better, because whatever you think : you don't.

"

Kulze wrote:

Yes, one which GGG should've solved leagues ago even, by now they're simply ripping off customers with their lack of functionality. Everyone who bought a premium-tab has bought the ability to list items. Not showing all of those is just fucking them over, plain and simple.

yes, this is exactly what i was saying.

i imagine most people buying the premiums tabs are buying them with the expectation that their listing will not be randomly omitted from search results.

it is particularly disturbing in that Chris has mentioned making it more difficult for people to search for things in some of his posts, as well as stating that he does not want the value of items trivialised by easy trade - and coincidentally, it seems the lowest priced items are frequently not showing in results sets as has been noted by the OP.

GGG's response seems to me more of their same dismissive attitude toward those who trade - the title of the opening post says it all "I know you guys hate trade, but come on". i think this takes the dismissive attitude towards trade too far - into the realm of misrepresentation, and i'm surprised GGG don't see the ethical issues with it, as i actually believed they were better than that.

and while people are debating the technical issues and how difficult or not it is, i'd just like to highlight one very small and obvious detail.

the reason we are aware of this is because poe.trade seems to be doing a better job of it than the official website.

Hmm didn't think this thread would still be alive. I guess I should log-in more often.

Believe it or not I am a DB expert. I work primarily as a database converter. I convert data from one DB system to another. I often have to fix indexes and keys all the time to make queries faster. Usually from the source database. As destination databases just care that the data is there. And the indexes are optimized for how the software works. Instead of conversion speed.

First thing not all Databases are created equal. Some are just bad. Others are overly complex. And some just break because of how you write your select command.

Anyways. This can usually be fixed by proper indexing. Any good database will have a way for you to index each column or group of columns properly. And to be honest the fact they allow more than 1k results is already a problem. And increasing that limit is just a band-aid that won't heal or fix the real problem.

If the database they are using won't allow this to be fixed by indexing. Or something equivalent to that. Than the problem is the DB they are using. Otherwise it is there implementation of it. It could even be something related to table structure. I cant really know without looking at the DB, query, and related Code itself. Hell it could be as simple as them needing an order by on the select statement.

Either way this is just plain stupid. It shows exactly why they are reluctant to make an AH. If they have this big of a problem with creating indexes. An AH is just out of the question.

There is a very big difference in understanding how to create a DB. Verses someone who understand how to create an properly optimized DB. Proper Table structure is like having a good foundation. Or you could just create the tables you think you need. Only later to realize you just built a house quick sand.

"

TheLockedGuy wrote:

Hmm didn't think this thread would still be alive. I guess I should log-in more often.

Believe it or not I am a DB expert. I work primarily as a database converter. I convert data from one DB system to another. I often have to fix indexes and keys all the time to make queries faster. Usually from the source database. As destination databases just care that the data is there. And the indexes are optimized for how the software works. Instead of conversion speed.

First thing not all Databases are created equal. Some are just bad. Others are overly complex. And some just break because of how you write your select command.

Anyways. This can usually be fixed by proper indexing. Any good database will have a way for you to index each column or group of columns properly. And to be honest the fact they allow more than 1k results is already a problem. And increasing that limit is just a band-aid that won't heal or fix the real problem.

If the database they are using won't allow this to be fixed by indexing. Or something equivalent to that. Than the problem is the DB they are using. Otherwise it is there implementation of it. It could even be something related to table structure. I cant really know without looking at the DB, query, and related Code itself. Hell it could be as simple as them needing an order by on the select statement.

Either way this is just plain stupid. It shows exactly why they are reluctant to make an AH. If they have this big of a problem with creating indexes. An AH is just out of the question.

There is a very big difference in understanding how to create a DB. Verses someone who understand how to create an properly optimized DB. Proper Table structure is like having a good foundation. Or you could just create the tables you think you need. Only later to realize you just built a house quick sand.

Maybe would be better to allow less results then instead of more?