The Oracle of Death, Part II - Page 2 - PlanetSide Universe
PSU Social Facebook Twitter Twitter Google+ YouTube Steam TwitchTV
PlanetSide Universe
PSU: Now available: Tickle-Me-Hamma
Home Forum Chat Wiki Social AGN PS2 Stats
Facebook Connect
Notices
Go Back   PlanetSide Universe > PlanetSide Discussions > PlanetSide 2 Discussion

Reply
 
Thread Tools Search this Thread Display Modes
Old 2013-09-22, 10:28 AM   [Ignore Me] #16
maradine
Contributor
Lieutenant Colonel
 
maradine's Avatar
 
Re: The Oracle of Death, Part II


Originally Posted by Stomps View Post
!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

Last edited by maradine; 2013-09-22 at 11:08 AM.
maradine is offline  
Reply With Quote
Old 2013-09-23, 07:50 AM   [Ignore Me] #17
MGP
Sergeant
 
Re: The Oracle of Death, Part II


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


MGP is offline  
Reply With Quote
Old 2013-09-23, 11:27 AM   [Ignore Me] #18
maradine
Contributor
Lieutenant Colonel
 
maradine's Avatar
 
Re: The Oracle of Death, Part II


You done been taught to fish. Go get some.
maradine is offline  
Reply With Quote
Old 2013-09-24, 10:19 AM   [Ignore Me] #19
MerlO
Private
 
Re: The Oracle of Death, Part II


Hope this works, ESAV Launchers:

https://docs.google.com/spreadsheet/...Wc&usp=sharing
MerlO is offline  
Reply With Quote
Old 2013-09-24, 10:28 AM   [Ignore Me] #20
maradine
Contributor
Lieutenant Colonel
 
maradine's Avatar
 
Re: The Oracle of Death, Part II


Beautiful
maradine is offline  
Reply With Quote
Old 2013-09-24, 10:40 AM   [Ignore Me] #21
MerlO
Private
 
Re: The Oracle of Death, Part II


Originally Posted by maradine View Post
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 is offline  
Reply With Quote
Old 2013-09-24, 10:56 AM   [Ignore Me] #22
MerlO
Private
 
Re: The Oracle of Death, Part II


Harasser ESAV:

https://docs.google.com/spreadsheet/...2c&usp=sharing
MerlO is offline  
Reply With Quote
Old 2013-09-24, 11:55 AM   [Ignore Me] #23
maradine
Contributor
Lieutenant Colonel
 
maradine's Avatar
 
Re: The Oracle of Death, Part II


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.
maradine is offline  
Reply With Quote
Old 2013-09-24, 03:16 PM   [Ignore Me] #24
Plaqueis
Staff Sergeant
 
Plaqueis's Avatar
 
Re: The Oracle of Death, Part II


Originally Posted by MerlO View Post
Lmao.. no surprises there...
Plaqueis is offline  
Reply With Quote
Old 2013-09-24, 07:51 PM   [Ignore Me] #25
MerlO
Private
 
Re: The Oracle of Death, Part II


Originally Posted by maradine View Post
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?
MerlO is offline  
Reply With Quote
Old 2013-09-24, 07:53 PM   [Ignore Me] #26
maradine
Contributor
Lieutenant Colonel
 
maradine's Avatar
 
Re: The Oracle of Death, Part II


Yep! I keep all the timestamps. I'll run it during kill nadir.
maradine is offline  
Reply With Quote
Old 2013-09-25, 02:09 AM   [Ignore Me] #27
MerlO
Private
 
Re: The Oracle of Death, Part II


Harasser ESAI:

https://docs.google.com/spreadsheet/...0E&usp=sharing
MerlO is offline  
Reply With Quote
Old 2013-09-25, 02:18 AM   [Ignore Me] #28
MGP
Sergeant
 
Re: The Oracle of Death, Part II


Originally Posted by MerlO View Post
Can you post AI MAXes plz plz?
__________________


MGP is offline  
Reply With Quote
Old 2013-09-25, 11:18 AM   [Ignore Me] #29
MerlO
Private
 
Re: The Oracle of Death, Part II


Originally Posted by MGP View Post
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.
MerlO is offline  
Reply With Quote
Old 2013-09-25, 12:16 PM   [Ignore Me] #30
maradine
Contributor
Lieutenant Colonel
 
maradine's Avatar
 
Re: The Oracle of Death, Part II


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.
maradine is offline  
Reply With Quote
Reply
  PlanetSide Universe > PlanetSide Discussions > PlanetSide 2 Discussion

Bookmarks

Discord

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 02:11 AM.

Content © 2002-2013, PlanetSide-Universe.com, All rights reserved.
PlanetSide and the SOE logo are registered trademarks of Sony Online Entertainment Inc. © 2004 Sony Online Entertainment Inc. All rights reserved.
All other trademarks or tradenames are properties of their respective owners.
Powered by vBulletin
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.