WarcraftRealms.com

To Rollie:

Greeting,

I have started following WarCraftRealms because I started using the information posted to monitor the new Dark Iron server I rerolled on when it went live.

I have watched with dismay at the data processing backlogs due to increased traffic. (please note, traffic has increased due to the contest, but reality is the contest just simulates success, ergo, changes need to be made to avoid inevitable success/future backlogs)

I would have to assume the database is in good shape, but there might be possible tweaks to the data scheme that might yield significant improvements. There might also be possible tweaks to the parser/import engine.

I have a bit of experience in Web Driven Database systems being an IT Director as well as owning a couple of domains. I am sure there are others that have similar or more experience then I.

Question: Would you be willing to get some peer review to your data scheme and/or processing systems?

Hero-

I have some tweaks and optimizations planned for when the contest ends. I could push those tweaks live now, but rebuilding the database takes a solid 24 hours as it is and would only push the backlog out further with no guarantee that it would make things that much better.

That being said, I am open to some help and will be taking a couple of folks up on their offer after I have made my initial modifications.

The first step would be to post up the schema of your DB's ... This will speak volumes as to where things are at, good or bad.

Everything else builds on that. I have seen DB Schemas that needed to be totally, and I mean totally redone .... and I have seen schemas that are spot on with only preference changes, and everything in between.

I imagine the schema is fairly well put together, so there will probably not be much oppotunity there.

The next step is to take some random samples of what is going through your mysql db. If sql logging is not turned on, turn it on ... then tail the file and post up the last 50 queries.

Probably going to need to run EXPLAIN on a few of the choicers ones ... but can't identify those till we have had a looksie at the schema and at the queries

Anyways ... post them up and let's see how things look ... or if you are not comfortable posting on open boards ... throw up a private board and invite a few folks.

I'm a bit leery of posting up my database schema. I don't mind sharing it privately with select individuals however.

I will share this much however. The main character table is an innodb table with over 14million rows. This is the only part of the site that is slow at all. Doing updates and inserts on this table takes a bit of time. Again, I do have some ideas planned, mostly with indexes at this moment, and if possible, breaking this table apart with a few different options.

In general, the site performance is just fine, it has only become an issue with updates and inserts to this table, especially when someone submits data that contains over 20k character entries.