Grr. Thought I'd posted this earlier...
On Tuesday, 3 April 2012 16:32:55 UTC-4, Abstrct wrote:
Have a ship (3204) sitting directly on a planet (252) and although
there are no ships (enemy or friendly) showing up on ships_in_range
(have max range too), it simply won't capture, the event log shows
that it's successfully mining every turn as well.
I can't exactly figure out what is up right now regarding this but I am looking into it. IN_RANGE_PLANET seems fine. Your ships are showing up as within range of the other ships around that planet, but for some reason they are not showing up as in range to you. I will keep debugging for a bit and hopefully figure it out shortly.
It looks as though something happened recently; events and successful fleet action seem to be ticking along now, which is a fine thing!
I got to looking through the code in perform_mining() that handles the
change of conqueror_id and it appears that it's calculating the new
conqueror based on how many ships mined the planet instead of how much
fuel was mined as I originally thought. Plus, it's hammering the
already overworked events table.
Thanks to all the great code and ideas lately, perform_mining() is one of the last really slow parts of the game. Even the particular event calls it make seem to happen pretty fast, it is a combination of all the other sql calls it needs to repeatedly make. There is definitely room for improvement in this function, we just need to figure out exactly how/where.
It seems to me that mining should be taken out of the "main loop", and turned into something that gets requested and processed automagically. The mine() function winds up running logic on an individual tuple basis each time it's called, which can't be as quick as doing it on a mass basis.
It seems to me that there are two things to be done, both somewhat separate:
a) I'd like to see ship_control split into two tables, one for movement, and the other for actions.
The clever use of views thing would involve making 2 views:
a) my_ships_movement, where an ON UPDATE rule allows doing what we use move() for now.
b) my_ships_actions, where an ON INSERT rule allows one to indicate MINE/REPAIR/ATTACK, and where this simply inserts a tuple into the "ship_action" table. ON DELETE allows removing that, in case you screw up.
I imagine it would be good for ship_action to be persistent. That is, once I have told ship 2 to MINE planet 72, or ship 17 to REPAIR ship 58, or ship 3240 to ATTACK ship 3241, those actions will be repeated until I stop things. That's particularly good for mining, as an enormous amount of the present scheduling work relates to saying "yep, mine that planet again!" Make ship_action persist, and that work of scheduling the action disappears.
The tic process then should handle performing mining in a somewhat different way, and I think the use of temporary tables would be hugely good for this. It eliminates a *pile* of locking for the interim work.
1. Establish a temp table with the list of all ships that have MINE as their action, the planet id, and player. That'll be scanning ship_action. Make sure it has an index on (planet, player). That index will be useful later.
I'm not sure of ideal orderings of the next few bits; they might be mixed into one grand query, or be better as several UPDATEs or as subsequent INSERT INTO another temp table.
a) Need to know which ships are still alive, and prospecting capabilities; draw that from ship.
b) Need to know if the ship is near the planet. Draw from ship.range, ship.location, and planet.location. That probably means we fold a) and b) together into one query.
Eliminate ships that are dead; they don't matter.
c) Ships that are too far from the planet should log MINE_FAIL. We can probably do the logging immediately; no need for intermediaries.
d) Do a per-planet loop, and handle mining on each planet.
d.1) If planet is undisputed, that is, there are no miners for other than the planet's owner trying to mine it, then there's guaranteed success.
Select ships on that planet in random order (from temp table!) and update to indicate success and set random amount of mining, for the first 30 ships (or whatever is the planetary limit). Update all other ships on that planet (in temp table) to indicate failure.
d.2) Planet is disputed.
Go through logic to figger out who wins the planet, and which ships get successful mining. Do this to "the temp table." The successful ships get random amounts based on the prospecting data already in the temp table. Remember it's 0 if the planet has been emptied.
Notice that no balances have yet been touched by this.
d.3) Work out how much fuel they *actually* got.
All we have, at this point, is a temporary table that knows which ships successfully mined, and which didn't, and how much they mined. Now, for accounting...
Another temp table... Aggregate stuff computed in step d on a per-planet basis, to give us something like...
create temp table t_mining_per_planet (planet_id integer, player_id integer, total integer);
insert into t_mining_per_planet (planet_id, player_id, total)
select planet_id, player_id, sum(mined_fuel) from t_mining_results_by_ship
where mined_fuel > 0 group by planet_id, player_id;
But... Some planets run out
for c_planet_id in select planet_id from t_mining_per_planet where total > (select whatsleft from planet where id = planet_id) loop
-- Curtail mining; give 'em 1/2
update t_mining_results_by_ship set mined_fuel = mined_fuel / 2 where mined_fuel > 0 and planet_id = c_planet_id;
update t_mining_per_planet set total = (select sum(mined_fuel) from t_mining_results_by_ship where planet_id = c_planet_id and mined_fuel > 0);
end loop;
e) Update planets to remove mined material, setting to 0 if < 0.
f) t_mining_results_by_ship contains all the data needed to populate events. Insert the whack of data there.
g) Aggregate the per-planet data, and adjust player balances on that basis.
update player set fuel_reserve = fuel_reserve + (select sum(total) from t_mining_per_planet where player_id = id);
That wants an index on t_mining_per_planet(player_id).
The stuff that locks heavily will take place on the temp table(s) that are never visible outside the tic process.
I haven't looked at all at how movement takes place; I suspect similar might be true.
I won't profess to know enough about this to think I have a solution,
so maybe cbrowne can offer some insight, but would it be more
efficient to have the planet/player/totals tossed into a temporary
table, or a table that gets truncated every tic at the same time the
Mine_success/Fail events are added, and then total up that table? I'm
sure by now you're looking at a couple million event entries, this
might help to get the total number of entries into something
manageable, plus you could base it on the total extracted from the
planet instead of just how many ships showed up to the party.
This is definitely something I have thought about too. Event is a beast so I am sure it would help speed things up. This just needs to be done in addition optimizing all the other calls it needs to make repeated.
The HUGE value of using a temp table for the work process is that it gets rid of most of the locking. It is not infrequently the case that test runs of my fleet script struggle with the tic process when both of them are trying to mess with my player's balance. That slows both, to everyone's detriment.
If most of the work is taking place in a temp table, and you only update the "main" tables with finalized results, there's a lot of locking that doesn't need to take place.
Also, for mining, it'll be a HUGE win to not have 8000 iterations of "update player set fuel_reserve = fuel_reserve + 27 where id = 89", but rather do that once with the 2.5M total.