The Oracle of Death, Part II - Page 2

maradine · 2013-09-22, 11:28 AM

Originally Posted by Stomps

!oracle ask weapon 39001 (to retrieve data for the uppercut, note no period used)
ORACLE MyIrcName: Was that a legit weapon id and period? I didn't get anything back. (error here)

15:24:17 @maradine | !oracle ask weapon 39001
15:24:18 ORACLE | maradine: TRS-12 Uppercut - kills: 4621 uniques: 355 kpu: 13.0 avgbr: 68.7 q1kpu: 6.2 q2kpu: 7.6 q3kpu: 12.4 q4kpu: 25.5

I can see the failed queries up in scrollback, so something's definitely up. It's a bug hunt!
edit: I think I see whats happening here - all of the failed examples are happening during nightly maintenance. That shouldn't cause them to fail (not in that failure mode, anyway), but now I know when to watch.

edit2: tricky tricky tricky. I call the assembly's attention to the nightly aggregation procedure:

Code:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`fkpk`@`%` PROCEDURE `kills_nightly`(out deleted_rows int, out time_elapsed int)
BEGIN
	DECLARE start_stamp int;
	DECLARE end_stamp int;
	DECLARE period_id int;

	SELECT UNIX_TIMESTAMP(NOW()) into start_stamp;

	-- get the timestamps
	CALL fkpk.get_time_brackets(@front, @back, @week);
	
	-- create a new time period

	INSERT INTO fkpk.v2_time_periods (start_time, end_time, is_daily) VALUES (@front, @back, 1);
	SELECT last_insert_id() into period_id;


	-- run aggregates over timestamps
	CALL fkpk.aggregate_weapons(period_id, @front, @back);

	-- delete before weekly
	DELETE FROM fkpk.v2_kills
	WHERE timestamp < @week;
	SELECT row_count() INTO deleted_rows;

	SELECT UNIX_TIMESTAMP(NOW()) into end_stamp;
	SET time_elapsed = end_stamp - start_stamp;

END

Parts, in essence:

1. Figure out what time it is.
2. Build a new time period.
3. Aggregate every weapon for that time period.
4. Clean out any raw kill data older than a week.

Parts one and two are essentially instant. Parts 3 and 4 each take about an hour each for an RDS instance of this class. Now to the request:

Code:

public static HashMap<String, String> getKillAggregate(Properties props, int id) throws SQLException {
	//figure out most revent daily and then run more granular call;

	Connection conn = null;
	ResultSet rs = null;
	PreparedStatement stmt = null;

	try {
		conn = getConnection(props);
		String sqlString = "SELECT id "+
				"FROM fkpk.v2_time_periods "+
					"WHERE is_daily = 1 "+
					"ORDER BY end_time DESC "+
					"LIMIT 1;";
		stmt = conn.prepareStatement(sqlString);
		rs = stmt.executeQuery();
		rs.next();
		int period = rs.getInt(1);
		return getKillAggregate(props, id, period);
	} catch (SQLException ex) { blah blah blah

Typos aside, a period-less call queries the database for the most recent period and then runs the more specific call with that period.

You are asking the Oracle for an aggregate immediately after the time period has been built, but before the aggregator has fought it's way to the weapon id in question. Fun!

A few possible simple fixes for this logic error. I'll probably just not write out the period until after the aggregator's done; I just need to make sure I'm not relying on that row being written already elsewhere.

Good catch, guys!

edit3: Your reward is a better period query:

15:58:54 @maradine | !oracle list periods
15:58:56 ORACLE | Listing Periods:
15:58:56 ORACLE | id: 8 start: 2013-09-21T06:31:40.000Z end: 2013-09-22T06:31:40.000Z daily? true
15:58:57 ORACLE | id: 7 start: 2013-09-20T06:31:38.000Z end: 2013-09-21T06:31:38.000Z daily? true
15:58:57 ORACLE | id: 6 start: 2013-09-19T06:31:36.000Z end: 2013-09-20T06:31:36.000Z daily? true
15:58:58 ORACLE | id: 5 start: 2013-09-18T06:31:34.000Z end: 2013-09-19T06:31:34.000Z daily? true
15:58:58 ORACLE | id: 4 start: 2013-09-17T06:31:32.000Z end: 2013-09-18T06:31:32.000Z daily? true
15:58:59 ORACLE | id: 3 start: 2013-09-16T06:35:30.000Z end: 2013-09-17T06:35:30.000Z daily? true
15:59:00 ORACLE | id: 2 start: 2013-09-15T06:31:28.000Z end: 2013-09-16T06:31:28.000Z daily? true
15:59:02 ORACLE | id: 1 start: 2013-09-14T06:31:26.000Z end: 2013-09-15T06:31:26.000Z daily? true

MGP · 2013-09-23, 08:50 AM

It's been a week.
Let's see fresh ESAV statistics, shall we?

maradine · 2013-09-23, 12:27 PM

You done been taught to fish. Go get some.

MerlO · 2013-09-24, 11:19 AM

Hope this works, ESAV Launchers:

https://docs.google.com/spreadsheet/...Wc&usp=sharing

maradine · 2013-09-24, 11:28 AM

Beautiful

MerlO · 2013-09-24, 11:40 AM

Originally Posted by maradine

Beautiful

Yeah

I took the liberty of adding in the before GU 8/12/13 Launcher sheet for KPU comparison, from the old Oracle thread, thanks for that.

MerlO · 2013-09-24, 11:56 AM

Harasser ESAV:

https://docs.google.com/spreadsheet/...2c&usp=sharing

maradine · 2013-09-24, 12:55 PM

Can't do a KPU comparison - KPU tends to increase over time frame. This is one of the reasons the Oracle is now standardized on 24 hour time blocks. The prior set was a 125 hour single pull, and you ran the average of a bunch of 24 hour pulls. Apple to oranges, in this case. What I can do is run a modern 125 hour set if you really want to see the changes.

Plaqueis · 2013-09-24, 04:16 PM

Originally Posted by MerlO

Hope this works, ESAV Launchers:

https://docs.google.com/spreadsheet/...Wc&usp=sharing

Harasser ESAV:

https://docs.google.com/spreadsheet/...2c&usp=sharing

Lmao.. no surprises there...

MerlO · 2013-09-24, 08:51 PM

Originally Posted by maradine

Can't do a KPU comparison - KPU tends to increase over time frame. This is one of the reasons the Oracle is now standardized on 24 hour time blocks. The prior set was a 125 hour single pull, and you ran the average of a bunch of 24 hour pulls. Apple to oranges, in this case. What I can do is run a modern 125 hour set if you really want to see the changes.

Thanks, it would be great if you could do this for the ESAV launchers to really see what effect the Striker changes had. Are you able to match the modern 125 hour pull to be the same time as the prior data, given the weekend KPU shifts?

maradine · 2013-09-24, 08:53 PM

Yep! I keep all the timestamps. I'll run it during kill nadir.

MerlO · 2013-09-25, 03:09 AM

Harasser ESAI:

https://docs.google.com/spreadsheet/...0E&usp=sharing

MGP · 2013-09-25, 03:18 AM

Originally Posted by MerlO

Harasser ESAI:

https://docs.google.com/spreadsheet/...0E&usp=sharing

Can you post AI MAXes plz plz?

MerlO · 2013-09-25, 12:18 PM

Originally Posted by MGP

Can you post AI MAXes plz plz?

Started on it, but the #planetside2 channel spam doing queries is pretty crazy, need maradine to arrange a separate channel before going any further.

maradine · 2013-09-25, 01:16 PM

I'm redoing the interface so you can ask both publicly (for illustrative calls) and privately (for walls'o'text). I'd also like to get the avg queries done today, but that's wishful thinking. Today is a cluster.