PDA

View Full Version : My terribly archaic Database


KingMort
05-15-2009, 03:04 PM
Well as some of you know most of my Database is from 2003ish....

I strongly believe that it's the main cause of many of my performance issues on KMRA...

I rent a Fully dedicated and very nice box that should be enough to handle 200+ people just like PEQ and VZ...

However at around 50+ people start to go LD while zoning alot.. Generalized Tell Lag and lag in encounters...

So what I was hoping was for someone who is good with Database stuff to help me out... And I will be willing to Donate to this person generously...

Please PM me here in the Forums... I would prefer to have some one that's well known in the community since they will be having access to the OLDEST Database with all of those years of work put into it...

Trev maybe ? Or KLS Or Cavedude would be my top choices.. If you guys could find the time to help me I will make it worth your while..

Jordan "King" Mortenson
Owner - www.raidaddicts.org

trevius
05-15-2009, 03:30 PM
If you are seeing lag like that, it sounds like it is most likely the connection speed, in particular your upload bandwidth, that is causing it. Does your hosting provide a guaranteed speed for upload bandwidth? I have 2MB up and my server can probably handle about 150-180 before performance started to take a serious hit. Most professional hosting offers 100MB up/down, so that shouldn't be a problem, but it sounds like the same symptoms.

As long as your server is running fairly new source code from the SVN, your database should be fairly up-to-date as far as that goes. Anything else causing performance hits might be due to either certain settings for your server, or possibly from quests or something that is eating a lot of your resources. One thing that I did that seems to help quite a bit for raid zones was to change the rule for zonewide spawn updates from 1 time per minute to 1 time every 10 minutes. Having it set longer like that can cause minor issues with spawn position ghosting if people are running through a large zone with lots of roaming npcs that have long path ranges, but it isn't anything major.

What is your CPU running at, and how much memory does the server have?

BTW, I don't want any payment lol. I give my help for free to anyone that I can :P

KingMort
05-15-2009, 04:04 PM
Thanks Trev

Server is hosted here http://www.spry.com/dedicated-servers/

We have 2 gigs of Ram.. on Fedora Core 6 Linux...

100 MB UP / DOWN

How do I do that with the mobs updating is that a Rule or a Variable.. Or an actual binary mod..

Really appreciate your help

King

John Adams
05-15-2009, 07:03 PM
KM, if you have direct access to your MySQL instance, you might want to run some checks/optimizations to make sure your tables aren't fragmented or corrupt. On my EQ2Emu server, one table is HUGE and I notice that with recent power glitches, that table gets a little buggered up - but is immediately fixed with OPTIMIZE TABLES.

Another thing, many people poo-poo, is running the command-line too "mysqltuner.pl". Just to get a glimpse if something is f'd up.

...not that I was on your preferred list :p ;)

KingMort
05-16-2009, 05:11 PM
I run optimize usually once a month yeah...

KLS
05-16-2009, 06:12 PM
The configuration of the mysql tables actually makes a pretty startling impact on performance. If you're using myisam on a table that is both written to and read from a lot like character_ it can really bog things down with all the locks going off left and right.

Tell / Combat lag could happen if it's bogged down from the database but it would be intermittent: not every tell would lag for example just the ones that happen during a slow query. Oh and I guess you could turn on http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html and see if there are any queries really causing problems.

KingMort
05-16-2009, 07:54 PM
You guys rock....

I have had a hard time asking for help since I have been around so long it was hard for me to ask for help but....

The time has come to swallow my pride...

King

cavedude
05-16-2009, 08:57 PM
alter table spawn2 engine=innodb;

and

alter table character_ engine=innodb;

Made HUGE differences on TGC. Inventory would also benefit from InnoDB. Though, keep in mind it does require a lot more HDD space (character_ grew 4x after the change here.)

KingMort
05-17-2009, 12:14 AM
Would it be wise to convert everything to InnoDB ??

cavedude
05-17-2009, 11:32 AM
Smaller tables that aren't used much generally benefit more from MyISAM over InnoDB. However, it wouldn't hurt to convert everything over. The only table I would highly recommend you leave MyISAM is character_backup. I'm not sure about you, but TGC's table is huge. Converting to InnoDB would waste so much space it's silly, and for that table you wouldn't really see much peformance boost at all, if any.

Kobaz
05-17-2009, 04:16 PM
Tables that aren't UPDATED often and aren't huge tend to be faster with myisam. innodb has row-level locks, whilst myisam only has table-level locks. None of the tables in eqemu are really huge, so it's the locking that's going to make the biggest difference on tables that have frequent updates. Innodb also clusters data about the primary key, so SELECTs are quicker when the WHERE clause uses that key.

So I would hesitate to convert everything to innodb unless you have extremely fast drives, although any table that might have concurrent writes to different rows could be quicker with innodb.

I've noticed a marked slowdown on build 488 vs the 450's, with higher loads on mysql. I suspect there was a query introduced that would benefit from better indexing.

trevius
05-17-2009, 04:41 PM
Smaller tables that aren't used much generally benefit more from MyISAM over InnoDB. However, it wouldn't hurt to convert everything over. The only table I would highly recommend you leave MyISAM is character_backup. I'm not sure about you, but TGC's table is huge. Converting to InnoDB would waste so much space it's silly, and for that table you wouldn't really see much peformance boost at all, if any.

Cavedude, I have been thinking about starting a discussion about the player profile blobs. This thread may not be the place for it, but since table size and performance is being mentioned, it makes me think about it. Currently, our profile blob is built from an old player profile packet that is no longer used. Essentially, this means that the structure of it doesn't matter to us at all anymore, we just use it to pull data from. The issue is that over half of the player profile blob is from unknowns in the PP struct. This means that half of the character_ and character_backup table space is being wasted for no reason. If we would remove the unknowns from the profile struct and only store stuff that is known, I think it would cut database backup size nearly in half of what it currently is. It may also help performance as well.

The actual change itself should be as simple as editing the common/eq_packet_structs.h player profile struct. The only problem is that will make any profile created before the change unusable unless it was converted. That is the worst part about making a change like this, but it might be worth considering if we had a way to convert all of the old profiles all at once. It may not be worth the risk, but if we had a good safe way to do the conversions (maybe in game command?), this could potentially have a pretty big impact on performance and backup file sizes.

Something to maybe consider or discuss further. If the idea is worth considering, we can start a discussion on it in the development forum to get input on the best way to handle it.

cavedude
05-17-2009, 05:26 PM
Trev, that's a really good thought but the problem is what if down the road we identify those unknowns? Then, we'd have to go through the conversion process all over again. I think the fewer times we convert, the safer and better off all of us will be.

trevius
05-17-2009, 07:19 PM
I already explained a bit more about it here:
http://www.eqemulator.net/forums/showpost.php?p=169596&postcount=3

But ultimately, it probably isn't worth the extra effort I guess. I am probably going to have to make changes to that structure anyway soon for SoF to reach full functionality. Luckily, none of the changes I would be making should cause any impact at all on previously saved profiles. I won't be adjusting the size of the structure or adjusting where fields currently are. I would just be adding more stuff in where there are some unknowns currently. If I do have to change anything, I will make sure to run it by you and KLS first, since the risk of issues is high when dealing with the PP. As long as size of the total struct doesn't change and no current field positions change, it shouldn't cause any issues.

Sorry for getting off-topic, but the reason for mentioning it was to possibly help optimize everyone's database a bit.

KingMort
05-18-2009, 01:13 PM
Should I be using the launcher???

What Priority should the zones be set at, and world..

King

John Adams
05-21-2009, 11:46 PM
I used to debate the pros/cons of both MyISAM and Blobs. Hate them both, because I am a constraint freak (ask LE, I may have convinced our team to use InnoDB for FK constraints ;)). Saves a lot of processing time cascading updates/deletes within MySQL than having an executable run 15 update/delete queries, but again, just my opinion based on my own analysis and research.

I hate the blobs simply because they are... blobs. Unreadable, unless you convert them... and, well, blobby. It shouldn't matter how the data is stored in the DB, as long as the server/client talk the same blobby language.

And you're right, probably not the place to debate it. But I am still thinking of KM's issue, not really sure why he'd be seeing such poor performance unless the hardware is just utter crap.

Shendare
05-21-2009, 11:56 PM
Adding indexes to table columns that are often sorted or filtered by can yield a huge performance boost in tables that are read from often but added to rarely (adding a record takes longer with a large table containing many indexes to update).