Right now, the general bitch is the 5s wait time. People want to be able to click as fast as they fucking please. There-in lies the problem. Geo calcs consume vast amounts of resources and take up a fuckload of time. We're talking about thrashing the fuck out of a 2.6 million record database ON EVERY CLICK.
Fortifying doesn't effect geo-eff, so it's not recaltulated here.
For evacing, average time is 1.1s
For capturing empty land, average time is 1.4s
For attacking another nation, average time is 2s
Running a few tests, a 5K nation attacking another 5K nation (5K as in the number of tiles), we're talking close to 3.6s
Our talks about an "energy system" would involve removing this timeout. While it would undoubtedly cause an untold numbers of problems, it would be one way to make the energy attribute useful for something other than combat calculations. It would also shut those mother fuckers up that continue to bitch and complain about the 5s wait despite numerous explinations of why it's there. It would also become one of those "spend your money here to refresh your energy" money pits of sorts. I don't really want to go that way, but hey, if sheeple love paying for shit, why not?
The inherent problem with this game is the map. The map is intensive. It's massive. Even with indexes on every column, it's still slow as fuck. We don't have a DB server, we have an all-purpose web-server, serving up all-purpose content. We could convert to nginx to get a slight speed improvement, but at the cost of extensions used for functionality. I could hack our server apart and install a different database handler such as Percona MySQL or Maria DB, but we lose all support from our webhost. I could throw more stuff into memcache, but we'd still be waiting on non-async mysql updates...
One solution I threw together was only re-hashing geo-efficiency every 15-30s. This way, we'd be ok for a few clicks, then slam the db, then ok for a few clicks, slam the database, rinse and repeat.
This would involve a timestamp on the nation record, when that timestamp expires, the next time the geo-calc functions is called, run it, restamp.
Some people have suggested crons. Terrible idea. Why cause a bunch of load when not necessary? What if no one's played this hour? Should we rehash the fuck out of everything every 60 seconds? Even at that rate, what would that do to other stuff running on the server, you know, such as KongHack?
I've also been thinking about table partitioning. Even with this, I'm facing issues. For typical data, you could partition on, say, every 100K records, or check a data field and partition on the year. For us, we're looking at an X and a Y column. We could create 1600 partitions for X, but since we're loading from both, it'd probably be slower. Partitioning, along with the image rendering idea, lead me to a different solution that might work, although it would be overly complex.
/Free flow thought, just bear with me...
Separate map tables per 25x25 quadrant. It's insane, sure. We'd wind up with 10,240 tables. We could split tables by X and we'd only have 1600 tables.
Ok, make it a larger quadrant, 50x50. That's 4 map screens total. Hmm, now we're at 512 tables. Sounds much more doable.
How about 100x100. That's 4 screens across and 4 screens down, or 16 screens in total. That's 256 tables.
The war system needs its own database. That way we can keep it's core data separate from the KongHack data. Especially for backups.
10K records is easier to cycle through than 2.56M records. The indexes will play nicer, as unused tables indexes will just pop in and out of memory as needed, while active tables will hang around in memory longer. This should reduce DB lag.
But this will make queries extremely complex. Kinda. We're looking at more programming overhead, but an overall performance gain. How would we calculate geo-eff across quadrants? How would we even display map points across quadrants?
1) We'd need to build unions when looking at the map on a crossroads between quadrants.
2) We'd need to only display fixed quadrant portions instead of focusing on the "current" tile. Add some LRUD buttons around the map so people could pan the map instead of moving and centering on a tile.
How would we calc out geo eff?
-Each nation would have a "I'm in this quadrant, and here's coords for this quadrant" record.
-We'd then pool those coords and run our current geo-eff test. Say you were in 3 quadrants, we'd pull 3 records from the DB and test against an array of coords.
-The inherent problem is storing those coords. We'd have to get all coords you owned in that quadrant any time a change was made to your data in that quadrant, and stash them in a row.
/end
Anyway, anyone out there have any thoughts?