Pages: 1 2 3 [4] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 17 post(s) |
Tachy
|
Posted - 2006.05.18 11:36:00 -
[91]
Aye, they'll start thinking about removing secure cans from highsec. And of removing can art from all over space. --*=*=*-- Megadon CCP wanted a well known artist and celebrity to test the new font so it's approval would be well known. They got Ray |
Weirda
|
Posted - 2006.05.18 15:27:00 -
[92]
Originally by: Valar We can't change the itemID column to bigint because that would increase the size of the items table at least 3 times and all its indexes by at least two and we can't use negtive numbers because of how the inventory system is implemented.
Guids 4tw!
if you using ints though - you should really have multiple key on that table, so that you don't run out of ints... __ Weirda Assault Ship deserve a 4th Bonus and More!
|
Inspiration
|
Posted - 2006.05.18 17:34:00 -
[93]
Edited by: Inspiration on 18/05/2006 17:35:40
Originally by: Valar We can't change the itemID column to bigint because that would increase the size of the items table at least 3 times and all its indexes by at least two.
Not that I want to step on your turf or anything, but this only raises more questions for me :).
Assumping the primary key of the item table is the 'itemID' field and it is also the unique clustered index (common practice) of that table, a move to bigint will 'widen' the table by 4 bytes. The same is true for every non-clustered index that the table has, as the itemID is also stored in those (even if not part of the index fields).
A tripping of the table size, on top of the wider indexes is kind of shocking and to me only plausible under serveral conditions.
1) The table has a LOT of self-references relative to the number of fields (they would all widen)
2) The table records are is so big, that only several 'just' fit per page. Making them wider, will lead to a lot of unused space. For those not familiar with SQL, records are stored in pages, of limited size, and a record cannot be stored over multiple pages.
A combination of the above can occur to ofcourse ;). Man I would love to have more details on the current stuff, but I problably can't :/. Anyway fixing this would free a lot of downtime-time for other stuff, so I think a solution is desired.
Is there any information that can be shared to give your off-island colleagues also a bit more insight into the problem? Without slowing you down that is :).
BRIGHT IDEA:
A 'bright' idea just hit me....... (I hear peeps scream, oh no)......
What if you do use negative ItemIDs in combination with a 64 bit computed column that is always positive? The internal database relations will use a mixture that can be positive or negative (no issue for the DB) and all externaly fired queries work with positive 32 bit numbers.
Several implementations of this idea are possible, the best depending on the inpact on the current codebase. Just an idea, one worth looking into me thinks.
Let me know if it is of any value, never hurts to share ideas imho.
Inspiration
|
Shamis Orzoz
|
Posted - 2006.05.18 21:29:00 -
[94]
My client is spinning. Please make it stop.
|
|
Valar
|
Posted - 2006.05.18 23:04:00 -
[95]
Originally by: Inspiration
Assumping the primary key of the item table is the 'itemID' field and it is also the unique clustered index (common practice) of that table, a move to bigint will 'widen' the table by 4 bytes. The same is true for every non-clustered index that the table has, as the itemID is also stored in those (even if not part of the index fields).
A tripping of the table size, on top of the wider indexes is kind of shocking and to me only plausible under serveral conditions.
This is the thing:
Quote:
1) The table has a LOT of self-references relative to the number of fields (they would all widen)
and to add to that, there are indexes on the self-reference fields ownerID and locationID.
Of course we *could* go to bigint if the need arises but it would require us to invest in more solid state SANs or require that we move tables from the RAMSAN and that would result in a performance hit. So while we can keep the itemID as a signed 32-bit integer with minimum effort, we are going that way. ------ Valar Database admin - Server operations team CCP Games How to write a good bugreport |
|
Nelson Vandermark
|
Posted - 2006.05.19 01:52:00 -
[96]
Originally by: Valar
We are constantly deleting trashed items, old logs and other stuff thats not being used anymore. Without doing that we would make the itemID integer column explode and the database size would increase very fast.
Oh I see well you guys certain know your stuff thatĘs for sure, although is there a possible of widening the range on objects which could be classed as redundant records?
You would only need to look at some of the oldest corporations in game to see this information and it would be objects that reside under corporation tab, I think every thing under that tab records everything, I was think about all the corporations which are not active and where created 3 years ago, thatĘs a lot of records which never get accessed and there wouldn't be any point in keep the data, unless it holds some historical importance.
-
|
|
Valar
|
Posted - 2006.05.19 02:05:00 -
[97]
Originally by: Nelson Vandermark
Originally by: Valar
We are constantly deleting trashed items, old logs and other stuff thats not being used anymore. Without doing that we would make the itemID integer column explode and the database size would increase very fast.
Oh I see well you guys certain know your stuff thatĘs for sure, although is there a possible of widening the range on objects which could be classed as redundant records?
You would only need to look at some of the oldest corporations in game to see this information and it would be objects that reside under corporation tab, I think every thing under that tab records everything, I was think about all the corporations which are not active and where created 3 years ago, thatĘs a lot of records which never get accessed and there wouldn't be any point in keep the data, unless it holds some historical importance.
If a corporation has 0 members, the corporation itself is moved to a location called something like "EVE Corporation Graveyard" and all items owned by the corp are deleted. So a deleted corporation takes up 1 record in the database and that record *has* to be there for f.ex. employment history. Other data that old corporations have f.ex wallet transaction logs, member role auditing and other stuff like that is deleted when we do regular cleanups. Characters that are deleted lose all their items, are moved to a corporation called "Doomheim", moved to a location called "EVE Character Graveyard" and only keep their skills.
I recently checked how many itemIDs we would gain by deleting all items owned by characters that are on accounts that haven't been active for X many months.
By deleting ALL items on ALL characters on accounts that were not active at the time of the check(including accounts that went inactive the day before), we would only get around 30mil free itemIDs. Thats around the same number of items as we delete in two downtimes, so its not worth the grief it would give returning customers.
There is some redundant data, but we try to keep it at a minimum. ------ Valar Database admin - Server operations team CCP Games How to write a good bugreport |
|
Kommander Kool
Caldari
|
Posted - 2006.07.22 16:28:00 -
[98]
Heheh, Im kinda glad you didn't delete it!
(Sitting on my account I opened 1yr, 6mo ago, and didnt use much after. :P )
And yeah. Its sure nice to have Devs that'll give the techies info on whats going on!!!
:)
|
Nyack
JOSTDA Enterprises New Outer Ring Allied Democracy
|
Posted - 2006.07.22 16:39:00 -
[99]
I am fighting in the tournament. i cant sit here waiting for to much longer i need to knwo when u have the deadline when teh delay is to much that the whole day will be rescheduled?
regards
|
Fronnhelm
Freelancer Union
|
Posted - 2006.07.22 16:49:00 -
[100]
I do not understand all the technical talk. But if i understand it correctly the reason the cluster crashed is that it ran out of numbers? Meaning people have to much stuff? Maybe i should start reprocessing all my junk...
|
|
Porven
|
Posted - 2006.07.22 16:59:00 -
[101]
I have to say that I am very impressed with the detailed explanations we are getting from the Dev Team.
Of all the MMOs I've played, EvE has, by FAR, the best Dev interaction with the players.
Thanks for all the detailed explanations. It is very much appreciated.
|
Amantus
Murientor Tribe Ushra'Khan
|
Posted - 2006.07.22 17:07:00 -
[102]
This is in fact an old thread. Whether the thing about numbers and stuff is still true I don't know.
Just pointing it out.
It's still relevant though I guess. -----------
Proud member of Ushra'Khan DEATH TO SLAVERY! |
RichoDemus
Eve University The Big Blue
|
Posted - 2006.07.22 17:08:00 -
[103]
30 minutes before the first reboot I spent 30% of all my ISK on skillbooks, now after reboot I got no skillbooks but I can see the transactions in my wallet :( Join channel: "Eve University" or read here |
Price Watcher
|
Posted - 2006.07.22 18:26:00 -
[104]
Valar, how about getting rid of all the space junk?
Abandoned secure cans and shuttles and noob frigates.
That might free up a gazillion id numbers.
|
Victor Kruger
|
Posted - 2006.07.22 18:49:00 -
[105]
You can even put it into a RPG context. Have Concord fine the people who litter around space stations and stargates then blow up the cans / shuttles. This should be done only after a certain time has passed without the person picking up his trash, i.e. 5 hrs. --------------- Hey, where's my portrait |
|
Eldo Davip
|
Posted - 2006.07.22 19:19:00 -
[106]
Thread necromancy is really baaadddd...
|
|
|
|
|
Pages: 1 2 3 [4] :: one page |
First page | Previous page | Next page | Last page |