Crunching the Crunched Numbers

[twilyth]

Thanks but I wouldn't bother with that if no one else wants to see it. It's a lot of additional work just to show something that you can more or less plot by eye just by looking at a graph.

 

[Aquinus]

Thanks but I wouldn't bother with that if no one else wants to see it. It's a lot of additional work just to show something that you can more or less plot by eye just by looking at a graph.

Lets revisit the idea when I have aggregate stats over time in place and getting calculated. At that point, it should be a relatively light lift and isn't really a whole lot of extra work but, I need to get there first.

Quick little update, you've seen this page but, I've added breadcrumbs to the top of the page and updated the name because I like "GridPowerUp" but, you might also notice that usernames are now links:

The device links aren't wired up yet but, I've started on a profile page. Since yesterday's stats were already handy, I started by using that and calculated a couple totals as a start. At the bottom what you're seeing is some debugging I added to the page. It's really just dumping the request by the time it gets to the page handler just so I could make sure that stuff was working as I was putting it together. You can also see how the breadcrumbs work, where "Home" goes back to the first page I made.

With that said, I'm currently thinking about how I'm going to handle aggregating data while keeping it fast (using a view for this tends to not scale when you get >1m records,) and PostgreSQL's materialized views are a little clunky in the sense that refreshing them recalculates everything in the view which makes incremental updates difficult to do. I can already do things like generate a time series for any time interval I want, that's easy:

wcg=> select generate_series AS "lower-bound", (generate_series + INTERVAL '1 day' - INTERVAL '1 microsecond') AS "upper-bound" from generate_series(NOW()::date - INTERVAL '30 days', NOW()::date, INTERVAL '1 day');
     lower-bound     |        upper-bound         
---------------------+----------------------------
 2017-11-10 00:00:00 | 2017-11-10 23:59:59.999999
 2017-11-11 00:00:00 | 2017-11-11 23:59:59.999999
 2017-11-12 00:00:00 | 2017-11-12 23:59:59.999999
 2017-11-13 00:00:00 | 2017-11-13 23:59:59.999999
 2017-11-14 00:00:00 | 2017-11-14 23:59:59.999999
 2017-11-15 00:00:00 | 2017-11-15 23:59:59.999999
 2017-11-16 00:00:00 | 2017-11-16 23:59:59.999999
 2017-11-17 00:00:00 | 2017-11-17 23:59:59.999999
 2017-11-18 00:00:00 | 2017-11-18 23:59:59.999999
 2017-11-19 00:00:00 | 2017-11-19 23:59:59.999999
 2017-11-20 00:00:00 | 2017-11-20 23:59:59.999999
 2017-11-21 00:00:00 | 2017-11-21 23:59:59.999999
 2017-11-22 00:00:00 | 2017-11-22 23:59:59.999999
 2017-11-23 00:00:00 | 2017-11-23 23:59:59.999999
 2017-11-24 00:00:00 | 2017-11-24 23:59:59.999999
 2017-11-25 00:00:00 | 2017-11-25 23:59:59.999999
 2017-11-26 00:00:00 | 2017-11-26 23:59:59.999999
 2017-11-27 00:00:00 | 2017-11-27 23:59:59.999999
 2017-11-28 00:00:00 | 2017-11-28 23:59:59.999999
 2017-11-29 00:00:00 | 2017-11-29 23:59:59.999999
 2017-11-30 00:00:00 | 2017-11-30 23:59:59.999999
 2017-12-01 00:00:00 | 2017-12-01 23:59:59.999999
 2017-12-02 00:00:00 | 2017-12-02 23:59:59.999999
 2017-12-03 00:00:00 | 2017-12-03 23:59:59.999999
 2017-12-04 00:00:00 | 2017-12-04 23:59:59.999999
 2017-12-05 00:00:00 | 2017-12-05 23:59:59.999999
 2017-12-06 00:00:00 | 2017-12-06 23:59:59.999999
 2017-12-07 00:00:00 | 2017-12-07 23:59:59.999999
 2017-12-08 00:00:00 | 2017-12-08 23:59:59.999999
 2017-12-09 00:00:00 | 2017-12-09 23:59:59.999999
 2017-12-10 00:00:00 | 2017-12-10 23:59:59.999999
(31 rows)

Just to note in case anyone notices it, I'm subtracting 1 microsecond from the upper bound because PostgreSQL's timestamp has a resolution of 1 microsecond and that's just enough to have the next lower-bound to abut the last upper-bound and not leave a gap or to overlap (don't need to count any results more than once or accidentally not count one.)

What becomes costly is taking this generated series and splitting up the results by them as the number of results grow. A potential solution might be to update aggregate statistics when results are created and updated but, that could slow down the rate at which results can be updated. I'm also trying to look forward because I can think of cases where we might want to describe a time frame for something like a challenge so we could have stats for just that so, it requires a little bit of thought. We'll see where it goes.

 

[stinger608]

This is getting great @Aquinus !!!!

Thanks for all your hard work!

 

[Aquinus]

This is getting great @Aquinus !!!!

Thanks for all your hard work!

Slow and steady wins the race.

Edit: I personally like the idea of tracking output during challenges. That could be used for special statistics just for a challenge and could be used for things like determining prize eligibility as well as any metrics we wish to derive from it. Challenge pie could be cool. I'm starting to craft up the schema for storing "time frames" and "time groups". You can think of a time group as something like "hourly stats" which has no time bounds or something like a challenge which does. Something like this might fit the bill (although I might need to add other small things to it,) but if I go this route, I want it to be customizable by an administrator.

wcg=> \d wcg."time-group"
                          Table "wcg.time-group"
       Column       |            Type             |       Modifiers       
--------------------+-----------------------------+------------------------
time-group-id      | integer                     | not null
name               | text                        | not null
description        | text                        |
lower-bound        | timestamp without time zone |
upper-bound        | timestamp without time zone |
gather-statistics? | boolean                     | not null default false
step-size          | interval                    |
Indexes:
    "time-group-pk" PRIMARY KEY, btree ("time-group-id")
Referenced by:
    TABLE ""time-frame"" CONSTRAINT "time-group-fk" FOREIGN KEY ("time-group-id") REFERENCES "time-group"("time-group-id")

wcg=> \d wcg."time-frame"
                 Table "wcg.time-frame"
    Column     |            Type             | Modifiers
---------------+-----------------------------+-----------
time-frame-id | bigint                      | not null
time-group-id | integer                     | not null
lower-bound   | timestamp without time zone | not null
upper-bound   | timestamp without time zone | not null
Indexes:
    "time-frame-pk" PRIMARY KEY, btree ("time-group-id")
Check constraints:
    "enforce-bound-values" CHECK ("lower-bound" < "upper-bound")
Foreign-key constraints:
    "time-group-fk" FOREIGN KEY ("time-group-id") REFERENCES "time-group"("time-group-id")

Edit 2: Also, I didn't make this clear but, my intent is to have something that provides more granular information that gets updated more frequently than Free-DC. This really only can be had by pulling result data which requires user's validation codes.

 

[twilyth]

Lets revisit the idea when I have aggregate stats over time in place and getting calculated. At that point, it should be a relatively light lift and isn't really a whole lot of extra work but, I need to get there first.

That sounds good. Thank you.

I'm not sure I followed what you were saying so this might be gibberish but would it make things easier if you had 2 databases - one that would just be used for viewing information and maybe storing any information the user might be permitted to insert and one for updates. Then you could transfer any user information to the the update db once the daily updates are done and copy that to the viewing db. That should reduce any lag in viewing when the primary db is being updated and could also serve as a backup. Just a thought.

 

[Aquinus]

I'm not sure I followed what you were saying so this might be gibberish but would it make things easier if you had 2 databases - one that would just be used for viewing information and maybe storing any information the user might be permitted to insert and one for updates. Then you could transfer any user information to the the update db once the daily updates are done and copy that to the viewing db. That should reduce any lag in viewing when the primary db is being updated and could also serve as a backup. Just a thought.

You can do it with materialized views but, you're recalculating everything every time you want to refresh it. It's a lot of work and you can't do it often when the data set grows a lot. I wanted a solution that is more responsive than that. Right now it's gathering stats every 10 minutes. It does it in about 30 seconds with the 6 members in there now but, it's also running 100% serially and I could probably get it to run in a fraction of the time very easily, it just hasn't been a priority yet.

Also, don't use two databases unless you're pulling two existing ones together. It tends to be painful, even if they're the both kind of database. I've learned this from experience; it introduces more problems than you ever will anticipate.

Edit: I actually think that the API will let me restrict based on the last modified time on the result. I probably could make the API call more selective as well.

 

[Aquinus]

No pretty interfaces to show today however, I have made some significant progress towards some not so visual things.

First, I just managed to write a trigger to keep track of the "last modified time" field coming back from the queried results. This is important because now when I hit the API for new data, I can tell it "get me everything that has happened since the latest update I have." This is really nice because now the database isn't being hit with constant updates with data I already had, the API only returns what changed, which means getting the actual data is a lot faster. A side effect is that keeping track of this number takes a little bit of time every time a new result is added but, on the other hand, it vastly reduces the number of DML operations which is worth the (very,) small cost of the trigger. So, what used to take ~30 seconds to process now takes about 1 second and even more importantly, doesn't cause the fans on my machine to whirl up every 10 minutes. In all seriousness though, this means a lot when it comes to the question: "Will is scale?"

Second, I've built out some database schema for tracking statistics, mainly revolving around splitting up the data by time. I also managed to make a database function that take in a timestamp and approproiately fetches and/or creates all relevent time frames given all applicable time groups (such as daily intervals, hourly intervals, or something arbitrary like dates for a challenge.) This should put me a step away from calculating statistics on the fly based on any time groups specified by an administrator. Like the above, it isn't something pretty that you can drool over but, it's an important bit of progress.

wcg=> select * from "time-group";
time-group-id |  name  |                          description                          |     lower-bound     | upper-bound | gather-statistics? | step-size
---------------+--------+---------------------------------------------------------------+---------------------+-------------+--------------------+-----------
             1 | Daily  | Describes every day that statistics can be gathered against.  | 2017-11-07 00:00:00 |             | t                  | 1 day
             2 | Hourly | Describes every hour that statistics can be gathered against. | 2017-11-07 00:00:00 |             | t                  | 01:00:00
(2 rows)

wcg=> select * from getstatistictimeframes(NOW()::timestamp);
NOTICE:  Found group: 1
NOTICE:  Has Time Frame?: t
NOTICE:  Found group: 2
NOTICE:  Has Time Frame?: f
time-frame-id | time-group-id |     lower-bound     |        upper-bound    
---------------+---------------+---------------------+----------------------------
             1 |             1 | 2017-12-17 00:00:00 | 2017-12-17 23:59:59.999999
             4 |             2 | 2017-12-17 12:00:00 | 2017-12-17 12:59:59.999999
(2 rows)

Also, we've collected almost 100,000 results since I started this little project:

wcg=> select count(*) from results ;                   
count                   
-------                  
97909                   
(1 row)

In reality, it's really not much data (yet.)

wcg=> SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 1
;
name | owner | size
------+-------+-------
wcg  | wcg   | 36 MB
(1 row)

Edit: I lied, I've now added a "Last Result Update" column in the "Lifetime Gathered Statistics" section on the front page. This actually takes a substantial amount of time to run (page load is >100ms,) so, I might have to reconsidered how this is done because counting everything on the fly probably isn't a great idea as the data set grows.

Edit 2: I wonder if certain projects paired with certain hardware produce more credit than others... Might be a good statistic to track in the future.

Edit 3: You might not realize it, but this is huge:

 time-group-id | time-frame-id | device-id |  granted-credit  | result-count |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  applicable-result-ids                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
---------------+---------------+-----------+------------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
             1 |             1 |   4147721 | 4954.47285948456 |           71 | {1987704461,1987703807,1987456509,1987703080,1987703770,1991568624,1991568662,1991568613,1991568625,1991568661,1991568614,1991568681,1991568660,1989471109,1983508457,1988142374,1987956998,1983519020,1989559342,1983521819,1991805945,1989557147,1991963055,1991963053,1991962899,1991962865,1991962922,1991962872,1991962875,1988355958,1989690329,1988034182,1988385552,1986799147,1988385395,1992057266,1983574236,1989972092,1988420038,1988419651,1988420382,1988106235,1988425691,1989973716,1984043089,1988660640,1988631039,1988755633,1992812859,1988433353,1990027326,1984091717,1987189383,1984118876,1984056908,1992812804,1989007015,1989043873,1988660586,1988773756,1990646114,1984625672,1988551087,1987143111,1988799277,1988950910,1987163545,1992513157,1988799373,1988799255,1992364147}
             1 |            35 |   4147721 | 6647.54506653602 |           83 | {1937295067,1941447435,1941384423,1941407270,1939449825,1941446516,1941446653,1941518102,1941581166,1941654493,1941645089,1943420040,1943450208,1937325722,1943471398,1941784678,1945747467,1945746781,1941744731,1942016500,1945841757,1941853802,1940486667,1941840377,1943770419,1937792230,1945865548,1942016479,1942017220,1941914566,1943904197,1942196352,1942221138,1942228401,1943904264,1941900360,1942196995,1942272841,1942272792,1946357064,1942322564,1942322880,1946357054,1946357069,1941090673,1942681030,1942652887,1944388667,1941081998,1944387270,1938273091,1946403549,1942707889,1946797097,1946797177,1938825070,1942863234,1941132123,1946952190,1944856921,1942915304,1944856096,1943167613,1947199862,1943299810,1945018541,1947273610,1943096361,1947199923,1943299861,1942779953,1942708222,1943166463,1942525131,1942778302,1937820886,1943036678,1946952163,1938927055,1942272803,1942058247,1947199778,1946403355}
             1 |            37 |   4147721 | 6473.92300747193 |           80 | {1940564359,1939997280,1941690661,1941167134,1941941419,1940379864,1944623069,1940659594,1942386731,1940474040,1944622928,1939355065,1940473681,1939862654,1940695147,1940379479,1940060466,1939980959,1942343260,1939768390,1936752827,1940060297,1942385074,1940967064,1940100016,1940137074,1940376831,1939980888,1940621544,1944623190,1941887450,1940968059,1940473644,1940102621,1944622906,1940659931,1942559448,1939623100,1939864045,1944260529,1939869394,1939683126,1938346265,1939297379,1941365882,1939464900,1939623083,1935724530,1939580361,1936752854,1935724535,1939459913,1940806474,1939401680,1941160220,1939768836,1936222050,1942875116,1941322460,1939580478,1939023813,1942875882,1940806489,1941219226,1941158359,1939423818,1943329455,1942988998,1943326457,1941218039,1941260052,1944779567,1944779581,1936221814,1935697064,1940967952,1944779602,1940102407,1944779633,1944779575}
             1 |            42 |   4147721 | 6661.31448208844 |           92 | {1955824136,1962639523,1962976504,1961868506,1960836344,1961868853,1956415372,1956415633,1959195487,1965203365,1965203519,1965203499,1965203161,1965203361,1965203354,1965203171,1965203264,1965260952,1965260951,1965260953,1965260950,1965260944,1962045818,1963216262,1959594512,1957000922,1962109328,1963557588,1963271364,1959326832,1963429760,1959995534,1961462941,1961525749,1954644818,1957587011,1960097647,1960095689,1959479013,1959479084,1963652546,1963652246,1963651004,1963651838,1963651836,1959636254,1955200982,1960347835,1961577845,1963717496,1959718142,1959718253,1963717514,1963717495,1959718006,1959725499,1959724066,1959795150,1959795121,1959794258,1959824104,1959826100,1964030345,1960265507,1960142457,1960283087,1960382164,1960373980,1960324303,1962151658,1960382226,1960142540,1960373906,1960239117,1962219560,1960142389,1960373413,1960141917,1960987110,1960950925,1960732892,1961237328,1964672300,1962712255,1956397277,1960433163,1964758424,1959185562,1960432788,1964758519,1960746872,1959615772}
             1 |            62 |   4147721 | 7269.35582540188 |           85 | {1968623935,1968624067,1968288184,1968289068,1972660978,1963852647,1968479148,1968441899,1968478761,1968818235,1968610743,1968610736,1972955298,1968678107,1968676497,1970235703,1970234919,1968696936,1968870999,1968733752,1967582400,1964426680,1973176410,1973176406,1973176148,1973176079,1968977095,1972955281,1969071327,1969070431,1969071434,1972661027,1969071645,1973267374,1973571282,1973572513,1973571283,1973572254,1973572049,1973571284,1969290769,1969374933,1963534316,1967436151,1972105577,1967969920,1968105612,1967783432,1967531715,1966476871,1966020324,1968021717,1967725095,1972183828,1971992229,1971992257,1971992228,1967725037,1969894333,1966058560,1969681135,1967644838,1967731466,1971992287,1967689253,1963490151,1963440805,1971992318,1966494106,1967436938,1971991995,1971992317,1971992253,1968201666,1967260198,1969980180,1968198186,1963808744,1968249920,1972606467,1972606217,1972606493,1970010163,1971992356,1970175429}
             1 |            64 |   4147721 | 7493.76153094536 |           98 | {1952603574,1957445159,1955386345,1959361718,1958486218,1958544950,1957557379,1957656181,1959679008,1959677800,1957760733,1957761084,1958233604,1957760295,1958231006,1952677806,1952677533,1958014693,1961768639,1959847772,1958581632,1955922698,1953253595,1958669733,1957207221,1957232418,1961017263,1961198918,1957280700,1961198811,1961198932,1957285685,1961198871,1957207265,1959030205,1961198858,1957206994,1961198988,1957280769,1962072331,1958249494,1962072269,1962072076,1962072133,1958436774,1960245244,1958314768,1958954730,1958281999,1962488939,1958282228,1960450370,1953340973,1958873457,1953310630,1959061329,1959478814,1960775103,1962622613,1959177644,1958838971,1963200655,1958655209,1961027682,1953974640,1958707967,1960750828,1953924173,1958839402,1963206072,1963206071,1959061471,1959784646,1962622557,1959478599,1959242900,1962622612,1959176752,1958655588,1963206044,1959060722,1958839323,1957169098,1959479420,1963206028,1963159369,1963271363,1963268343,1963271398,1963266755,1963268039,1963267345,1963266893,1954625670,1957096021,1958544182,1953350353,1953212553}
             1 |           112 |   4147721 | 6820.50369660156 |           75 | {1943368891,1942761911,1939295853,1938528492,1939057836,1939101282,1939473657,1938474286,1938528436,1939102740,1941297766,1936190435,1938605694,1938578890,1934744168,1940250858,1938828149,1935271944,1942918590,1939553244,1939095611,1939095318,1939095697,1939058857,1940604779,1942920990,1939058348,1939159222,1937446557,1937445109,1938052644,1938348837,1938856331,1939109562,1934722287,1938345269,1939519614,1935830420,1938440312,1937503021,1937448700,1934139331,1937444949,1938052344,1941702890,1939184744,1938324951,1937583795,1938439543,1939183789,1937322592,1939725990,1937956478,1937959060,1938023285,1938327256,1937868878,1939784257,1937883695,1942368906,1934057276,1941435846,1941702892,1937798177,1939669671,1941710267,1937324970,1934083520,1934587232,1941720692,1938440538,1937359572,1940210117,1937869355,1937869090}
             1 |           172 |   4147721 |  4730.4405058515 |           60 | {1937148474,1937148362,1935780941,1936465289,1939130786,1937395653,1940865497,1936600185,1938677218,1936465258,1936670005,1940865526,1937391188,1940865494,1940865448,1940769948,1940865517,1940770140,1938272481,1937221410,1936465582,1940865501,1940309936,1940219811,1937389457,1936671641,1936396775,1938677268,1937395477,1940865512,1936791683,1936791700,1936764355,1940865491,1937218577,1936465269,1936612060,1939130667,1940432295,1937047517,1940865506,1936465635,1935710226,1935908823,1940219861,1935908320,1935452335,1935928113,1935373692,1936959758,1940769954,1934189628,1935918986,1936356216,1937440514,1935908720,1940769957,1938734413,1940219864,1935776682}
             1 |           179 |   4147721 |  655.90582765806 |            6 | {1934095127,1934808847,1934035424,1934241815,1933201125,1934096168}
             1 |           201 |   4147721 | 6259.93988646657 |           66 | {1950641339,1947378831,1949791278,1946455458,1950712393,1951875754,1951875889,1949361517,1950376048,1949517480,1950611871,1949550769,1949516827,1950376229,1949447965,1949519489,1944613977,1948508939,1952440717,1952440706,1952440707,1949583577,1952470188,1952470191,1952470192,1952470194,1952470187,1952470193,1952470182,1952470183,1949858522,1949857850,1945150325,1950784532,1949861362,1950787711,1949895228,1949893213,1949892587,1949435563,1950313732,1951207611,1950323965,1950313888,1950278029,1950324265,1950154861,1950470929,1951156197,1950314369,1950471282,1949938181,1950322460,1950221714,1950597759,1950717569,1950717505,1953697787,1950881030,1950880416,1950881910,1951539048,1954353464,1952004436,1951207586,1949032079}
             1 |           209 |   4147721 | 6543.46469184441 |           78 | {1967932386,1965826090,1964541859,1963943308,1963942708,1961835667,1959520337,1964050425,1966181331,1968296757,1968158590,1964670303,1968158566,1962392574,1964080693,1964347685,1959561639,1964411636,1960081881,1964347284,1964412398,1964410219,1964406563,1964409566,1964537044,1964538837,1964537216,1964582107,1964444572,1965105854,1965105675,1966730469,1968819880,1964856164,1964855999,1960583910,1960167725,1964974503,1966892750,1962905743,1968958106,1960607594,1964853998,1965385040,1967103302,1965122279,1967339440,1965350175,1965353635,1965544529,1965544527,1967203857,1967203800,1969361245,1960669567,1965406793,1960679423,1965762506,1963493147,1965762230,1963428291,1963429134,1963426268,1963439417,1963444094,1965558467,1964216343,1963577775,1963578095,1963578414,1963588499,1959164592,1959481063,1963616755,1964285822,1963834889,1963835032,1969361176}
             1 |           212 |   4147721 | 6693.15650825415 |           78 | {1943345684,1943581782,1943368920,1945206897,1943491653,1943547591,1943487156,1943548123,1943487479,1945369914,1943708614,1943709280,1947638136,1947638138,1947638756,1947638261,1947638137,1947679741,1947770579,1947770570,1947770571,1947679969,1947770587,1947770590,1943829093,1939347542,1943883791,1939375650,1944257443,1944149521,1945805827,1942647635,1944000794,1944296569,1944329030,1943248848,1944363850,1944252585,1939918706,1944509709,1944509545,1944551384,1944700816,1944784971,1944785996,1948938553,1944842467,1945064472,1948937793,1948937660,1944843933,1948937510,1944978727,1949132103,1949216474,1949216524,1947638193,1944510092,1944257628,1947770578,1943490419,1945805885,1944061936,1944296768,1949216475,1948839110,1944257117,1944363801,1939948632,1943929197,1944784890,1946333403,1946373321,1947770591,1943345750,1947638135,1944510178,1945914152}
             1 |           234 |   4147721 | 5934.27426605799 |           74 | {1946842514,1945271362,1949216525,1949216527,1949216503,1949216437,1949216446,1949216447,1949216504,1947133356,1945349876,1945540096,1945524418,1947281052,1945623062,1949586564,1949586559,1946259515,1946115259,1949586579,1945613595,1941555309,1946571391,1945834336,1945757334,1945710264,1947677780,1947528042,1946543920,1946531417,1945710465,1946112547,1946112638,1948224163,1946113118,1946013281,1945801707,1946297359,1946515480,1946110354,1946462692,1948244858,1946240625,1949586558,1948216325,1946684468,1946682374,1948285937,1946987705,1946988259,1946989029,1948697615,1947131990,1946844353,1950406989,1950674896,1942521820,1950674875,1946240638,1946987150,1945271352,1949610766,1947240399,1942482475,1945624480,1946109023,1949216526,1949610690,1948697522,1948285574,1945192777,1947280966,1949586560,1946259808}
             1 |           249 |   4147721 | 6585.58040507485 |           81 | {1962976492,1963283151,1961311491,1961313120,1962159521,1962159494,1961410776,1961412464,1957031929,1961444090,1961445712,1961444749,1961483194,1961483089,1961482848,1961518145,1961518258,1961518167,1963708129,1962553463,1961697461,1957446652,1961759454,1966075553,1961911534,1966222629,1966222644,1966222591,1962688966,1957504760,1962688897,1962505943,1962902048,1964631858,1957993471,1966758981,1963205299,1964632505,1966862327,1960235814,1962020181,1962288196,1961924963,1964698345,1962503027,1962290012,1962980476,1963821919,1963207551,1966861941,1967086640,1964208716,1962397940,1958128224,1962021030,1958126694,1966861809,1967086639,1964209308,1967086638,1962503066,1962398061,1958523517,1966222630,1961983680,1967086637,1967186799,1967186771,1963122596,1967349274,1963122552,1967349202,1967349275,1963126467,1967351069,1963121596,1967351080,1967351383,1964173146,1965339644,1957031805}
             1 |           256 |   4147721 | 6825.89710154807 |           91 | {1954761114,1957749709,1960469401,1955471149,1955470591,1955468959,1955160838,1955160555,1955157019,1957330315,1955430883,1957304565,1955333971,1956033263,1957304560,1956033320,1953440989,1950893642,1950893374,1950893161,1955510290,1955510393,1955510360,1955780660,1955766431,1957615977,1951223048,1955911852,1955911735,1956906591,1956659314,1959811784,1956007656,1956338896,1959811707,1951290400,1957862652,1957861872,1954082784,1960469441,1960108877,1951352180,1960108876,1960108557,1956074681,1960108875,1956478510,1956159816,1958347938,1958204608,1956607297,1956570761,1952490381,1956653943,1960469471,1960469470,1956116735,1956159905,1951681240,1956159715,1957943548,1958347842,1956864801,1951728127,1958414670,1956116146,1960108556,1959953554,1960469442,1960492584,1955329897,1960953200,1957797904,1957061423,1956986693,1958649075,1957206986,1957123873,1957056633,1956720435,1954764434,1952072076,1952072260,1956720369,1957797618,1952213524,1958648654,1952183175,1957124047,1956732426,1956742715}
             1 |           265 |   4147721 |  6140.6490472686 |           74 | {1969147117,1969146196,1969145923,1969220901,1969279384,1973690245,1969253508,1969747882,1969749840,1973691212,1969417479,1970329319,1973676347,1969416577,1974065829,1969877896,1969878004,1968155309,1965404017,1969487480,1970414172,1965420944,1970054433,1969739208,1970412966,1970412695,1970060210,1969734091,1965902107,1965902301,1968640238,1974357694,1969752412,1974357608,1974386602,1974461795,1974460872,1974529097,1974529172,1974528931,1974528770,1974528779,1974528769,1974528768,1974911323,1974910596,1974910494,1974946687,1974911395,1974911313,1974911361,1970335300,1974910495,1970232000,1974911386,1970234406,1974910558,1970336316,1974946654,1970235842,1974910597,1970334652,1970234507,1974946631,1969619432,1974946649,1966417343,1974983025,1974983119,1974983285,1974983027,1974983200,1974946634,1970807636}
             1 |           276 |   4147721 | 6752.79860941036 |           84 | {1955695591,1958929346,1954901976,1954901048,1950562475,1954721573,1952720015,1953477542,1955365557,1953547951,1955494492,1954855203,1954855164,1955563193,1955555714,1954855147,1949719988,1957756062,1953573380,1959054179,1953611306,1955663882,1954985228,1952763459,1953611187,1955159962,1954985349,1957854464,1953611323,1957874528,1953964074,1953964738,1955159766,1949771644,1957956007,1957874525,1957875415,1955108461,1954050695,1954215735,1955065593,1950087538,1954142847,1954215504,1954215527,1954214484,1954214945,1954231859,1949395283,1954469455,1949360495,1953153675,1957224079,1957300571,1952953144,1955081253,1952905920,1952945873,1953330378,1953378813,1953330562,1952988338,1955664678,1958378590,1954297342,1954323028,1958381234,1954606856,1954606665,1958500289,1958497691,1958500457,1954743936,1955599146,1954742773,1950492603,1956663066,1950492029,1956662944,1955598877,1956033517,1956760621,1958810777,1953386771}
             1 |           286 |   4147721 | 6713.87105483065 |           88 | {1979160267,1975253027,1975272454,1974279416,1979454799,1979455130,1975701263,1975700562,1975745623,1975746433,1975746105,1979638591,1979638585,1975777494,1972353196,1976080178,1977744337,1979752319,1979752310,1979752313,1979752308,1979752297,1979752295,1979751978,1979751957,1979752045,1979751977,1979751959,1979752296,1976603898,1979770332,1979816168,1980155233,1976523290,1976758305,1977381339,1976512208,1972469248,1980155232,1976510387,1977119252,1978778651,1976577143,1976616309,1976572559,1976462494,1976665907,1976666921,1976758276,1976572613,1976510889,1977339829,1976521939,1976798668,1976572394,1976714953,1972824819,1978591296,1980155227,1977156928,1980155236,1978038890,1980094981,1976616645,1978158724,1975154996,1976666123,1978477549,1980096162,1980712422,1980155226,1975690436,1981196995,1977514016,1973754212,1979023519,1977522335,1979159198,1976356546,1977513820,1981157949,1977514274,1981196873,1977721180,1973810737,1977522419,1981197055,1980580261}
             1 |           289 |   4147721 | 6582.44115671167 |           83 | {1980037073,1979876428,1979768647,1979513500,1979616441,1979616357,1981634967,1983677742,1978071266,1976101537,1980080173,1980091283,1981733096,1980092147,1981785254,1976161605,1980149025,1980134634,1980135809,1980401387,1980515955,1980511583,1980575101,1982099114,1980574830,1982098712,1976246291,1978481160,1980258759,1975525703,1984359385,1984359429,1984359430,1984359409,1984359410,1984359431,1984359408,1984379694,1980663371,1979392929,1980664059,1984917966,1981053916,1981188853,1982622831,1976892931,1980772991,1982762650,1982274716,1981072226,1981050798,1981188859,1981023737,1982763283,1980663823,1980663644,1980663905,1981121824,1981072822,1981072417,1981117248,1981121170,1985481610,1985481313,1981619250,1981660860,1983329868,1977360651,1985326242,1983049328,1981619054,1981576098,1985326262,1979949021,1985481609,1981478701,1982302932,1977360619,1979458094,1981342841,1981576632,1981660875,1977411334}
             1 |           315 |   4147721 | 7135.86229897151 |           88 | {1947624891,1949158321,1949162535,1947535178,1947667622,1947622119,1947718747,1947718797,1947167322,1947236011,1947236080,1947235931,1946910639,1947236016,1947236207,1947006426,1947468384,1947034587,1950758585,1947487348,1942530839,1947250887,1943145310,1947718962,1947978421,1943099682,1947758694,1948863577,1949265635,1949162661,1947461076,1949265675,1948231984,1951297665,1948228088,1948161752,1947871840,1947901906,1949622315,1943543050,1948227703,1948227814,1947842045,1951297876,1948176911,1948161811,1948161482,1948162396,1948197894,1949174546,1943560678,1948423296,1949003861,1952014991,1948593559,1952014938,1944563699,1951530939,1948532827,1951888779,1944546909,1949984417,1948613027,1944107232,1948895039,1948424913,1944147684,1951530954,1949108504,1951530940,1952014971,1949173138,1949004155,1949003982,1948717642,1949172805,1951811016,1951888822,1951531030,1949915228,1952014995,1951531009,1948044434,1949985239,1950220281,1951297408,1950353852,1949109243}
             1 |           349 |   4147721 | 6336.72605783478 |           88 | {1965542412,1967370968,1965512713,1965762536,1965762569,1965615955,1965615977,1961206523,1963493283,1961206140,1963526654,1965812200,1965812904,1965859534,1965859852,1965859575,1967828184,1967883570,1965892007,1965964154,1965964208,1965964101,1966003296,1966109907,1969915765,1966075714,1968177967,1968171885,1966307017,1961654100,1966221652,1966221433,1970463881,1971093541,1966424009,1966791691,1971102006,1966628256,1971092809,1971152753,1966743938,1962273415,1966651844,1961713653,1966746412,1966651620,1966952810,1961753572,1968462267,1966641056,1971092494,1971092601,1968374511,1966641338,1971092524,1970917298,1971092525,1966423776,1970934630,1971093080,1971152745,1970934327,1970934216,1966627850,1966704242,1965013438,1965026965,1971213399,1967126179,1971211399,1967126597,1971212931,1971212883,1967045994,1971224538,1967479757,1971092568,1971212887,1967287226,1962798568,1967173091,1967173093,1962835485,1962880296,1969681136,1971092567,1967916221,1962834060}
             1 |           372 |   4147721 |  7860.5278020166 |           99 | {1970587025,1972997619,1972997459,1972997779,1973641970,1969128929,1972997443,1975131276,1975132909,1971571075,1971527343,1971527238,1975351193,1975351207,1975489882,1975311967,1975756879,1975311036,1971570967,1975309268,1971813424,1975309987,1975756178,1966996623,1975311486,1970510293,1971114311,1971570807,1971568268,1975309265,1971122938,1975756948,1975756168,1975756906,1975756928,1971526941,1971528530,1971305119,1975482917,1975309209,1971604250,1975756233,1975756909,1971663569,1971735383,1972198601,1975870598,1972021151,1975870593,1972020965,1972021188,1972020300,1972020685,1972020696,1972019979,1972020934,1972020655,1972021027,1972021456,1972020541,1972459022,1972082341,1972090546,1972547872,1972019374,1972018942,1972081669,1972082497,1972220374,1972082493,1972082188,1973158967,1976587551,1968537681,1973626023,1974099705,1973625967,1973625894,1969502737,1977267208,1972017580,1972621857,1972846483,1973293596,1972848762,1973291601,1972848295,1972759589,1969058803,1977077702,1977077466,1977077699,1977077703,1977077694,1977077485,1977077548,1977077468,1977077741,1972556277}
             1 |           468 |   4147721 | 6599.51227388243 |           87 | {1951067407,1954862190,1955702899,1951015289,1951116548,1951115445,1951116527,1951116951,1947080885,1952423605,1954840137,1950540502,1951339001,1952450044,1951440629,1951492234,1951491246,1955116045,1953022495,1951525367,1951772291,1951764486,1951789761,1951770531,1951769997,1951768859,1953732022,1953848036,1953524086,1952379059,1953844535,1955693330,1955693249,1952379011,1952063122,1952294925,1955693305,1948929442,1952056475,1955693328,1954275697,1953658948,1955598098,1953790064,1951764222,1955693326,1953480331,1953527062,1952060005,1948061644,1951912036,1955693329,1955693256,1952253160,1951913535,1955693279,1954275804,1955598119,1955693312,1955693280,1955693304,1955693273,1952755145,1954198202,1954393095,1948954272,1954053624,1957224080,1956610525,1956888297,1951778266,1954967514,1952712262,1954279865,1954053591,1948980527,1952584636,1952712785,1949329523,1956888362,1952584582,1954353359,1956723620,1955693251,1951800328,1951770366,1952862252}
             1 |           517 |   4147721 | 255.130226227132 |            3 | {1931232260,1932724490,1932541098}
             1 |           621 |   4147721 | 5878.50515339023 |           80 | {1985802383,1985799916,1986060364,1985719598,1987806048,1985719595,1985802875,1989912749,1985795399,1988075742,1986360901,1986656289,1984708342,1986466960,1989929494,1981996617,1986466239,1986329570,1986855874,1986479894,1989929493,1986855237,1986508470,1989929500,1986279925,1986832260,1988509985,1987977904,1989929503,1986855433,1986516758,1986084027,1986884796,1986685739,1988331600,1986856054,1986414634,1986507633,1986082765,1988134640,1989912742,1989929499,1986780176,1990660659,1990056126,1986552917,1981938978,1984744132,1990230544,1989929498,1987360469,1987410587,1991021691,1987457425,1982452704,1988883978,1988783775,1991021640,1988586443,1990953491,1982452867,1987449550,1987309348,1987310918,1982937593,1991021644,1987290294,1982430370,1987411372,1987089324,1988979467,1987235838,1991021685,1988978863,1987241298,1989929495,1985809395,1986855765,1989929496,1985778384}
             1 |           658 |   4147721 | 7021.82214354383 |          101 | {1973039309,1973039946,1973419189,1973415803,1973414095,1973417269,1973418079,1973420124,1977637516,1973456610,1973456441,1977699808,1977699806,1977699904,1977699923,1977699903,1977699807,1977699811,1977699848,1977699850,1973529360,1973529420,1973530574,1974263702,1973709611,1975390824,1977932217,1973803674,1975774470,1974420446,1973882243,1978111826,1974753388,1970736450,1978270712,1970777019,1974871338,1974134855,1973390583,1974796700,1974327651,1974872826,1970827496,1974479653,1976135024,1976216076,1974637738,1974796643,1970827217,1975051771,1973414036,1973423484,1974757424,1974520910,1976166717,1978270703,1975943720,1978191844,1975954458,1974132528,1978270702,1970799646,1978191718,1976135064,1975180357,1974714924,1971309656,1974796618,1978801464,1978801583,1978801497,1978801585,1978801465,1978801501,1978801498,1978801495,1978801463,1978801499,1976549124,1978801503,1974872501,1974873371,1974873366,1976717683,1975010997,1975059711,1975060385,1973901655,1975234764,1975450113,1979165656,1977060212,1971765326,1975464260,1977106994,1971783248,1975478163,1973920553,1971805619,1977204193,1978801494}
             1 |           665 |   4147721 | 6504.04239423794 |           80 | {1984802709,1984802663,1984801852,1983597481,1983588984,1983739047,1983540601,1982077199,1983738986,1979295838,1985533221,1983799084,1987560997,1987560946,1987560931,1979295676,1983740614,1984289856,1982513943,1984344344,1984343594,1984417908,1988013099,1983049116,1988089830,1983924935,1984343355,1984344510,1979330091,1985994257,1983828986,1985832913,1988013093,1984227818,1984343381,1984530599,1986358971,1984375357,1979933345,1984376944,1984533866,1983068636,1988482298,1988482286,1988482368,1988482299,1985380055,1984795209,1985320729,1984979362,1985381885,1986900076,1980425912,1985056582,1980426098,1985473743,1986633359,1985523321,1985381307,1987203247,1987293331,1983687555,1979827576,1987201146,1983619452,1985522567,1986901707,1985719182,1984797509,1986634051,1985319629,1985523101,1985130687,1985320291,1985402853,1980437042,1985401884,1985064270,1985719540,1984980030}
             1 |           756 |   4147721 | 6690.84818841928 |           83 | {1977704983,1981811714,1978096834,1981897593,1981897582,1979776680,1978407359,1979733346,1974256513,1982011976,1978534404,1978606767,1978711748,1978606928,1978572112,1974709540,1978606988,1978572015,1979018732,1978607008,1978853800,1978572738,1978614541,1978607330,1978572580,1982606936,1982606841,1982606970,1979018513,1982606992,1982606884,1979018284,1982606991,1982770120,1980621052,1982770248,1982770272,1982770171,1982770083,1982770170,1982770197,1982770219,1982770039,1979298041,1982881215,1982881295,1979437487,1979437492,1979435498,1979436988,1979297468,1975632073,1983150173,1979648264,1977477799,1981070765,1977642911,1977646187,1979620923,1978040547,1981598442,1974201366,1981411457,1981605834,1977951679,1977705078,1981598461,1977705204,1977704659,1981598462,1981411225,1977924928,1977704412,1981598424,1977704928,1981598443,1977917948,1978043256,1977501960,1975641900,1979738602,1975642139,1981259922}
             1 |           875 |   4147721 | 5933.39279763428 |           71 | {1982539326,1982603394,1986462447,1982603118,1982601496,1980973813,1982605392,1978362317,1982757630,1982757625,1982655869,1978379662,1982676655,1984746409,1984746822,1982677111,1982689797,1981484268,1982790510,1982790276,1982909153,1978430787,1984844421,1981495434,1983103188,1983103760,1986916622,1984896069,1983149975,1984895416,1983149565,1983149499,1983138299,1984928175,1983226868,1981513916,1981535007,1983219115,1983460445,1982656627,1983461712,1983462910,1983459999,1983137723,1981987208,1981803153,1982123658,1985565090,1983776283,1982154248,1981757481,1982154461,1982005396,1983455762,1981756573,1982154028,1977967319,1982219322,1982219207,1983971026,1982379413,1980924443,1982352189,1982353569,1984173369,1977978580,1982343595,1982383996,1982449350,1982510450,1984300087}
(29 rows)

wcg=> explain analyze select * from "device-result-statistics" WHERE "time-group-id" = 1 AND "device-id" = 4147721;
                                                                           QUERY PLAN                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using "device-result-statistics-pk" on "device-result-statistics"  (cost=0.41..237.42 rows=31 width=166) (actual time=0.021..0.315 rows=29 loops=1)
   Index Cond: (("time-group-id" = 1) AND ("device-id" = 4147721))
Planning time: 0.074 ms
Execution time: 0.340 ms
(4 rows)

Edit 4: Checking out for the weekend but, I do have stats getting gathered now as results are inserted/updated. Stats are broken down by device for whatever time groups exist. I threw together a quick daily total on the front page that sums up all the device stats for each time frame for the daily time group but, I'm going to have to do something about how I display information on these pages. Either way, it's progress.

 

[Aquinus]

It's been a little while since I gave an update and I did say before that I wanted to have something ready for the new year. Well, it's not quite there but, the progress has been good (minus the last week or so due to the holidays.)

I have, unfortunately, found a bug. Apparently time frames are getting duplicated within a single DML operation. So for example, if 20 records (that have points earned and are verified,) are inserted with the same received time, if the time frame doesn't exist, it will create a new time frame for every result, then use some arbitrary one thereafter.

I have identified how this is occuring though. It mainly has to do with the function for handling this not having visibility into the records created in the same transaction. There are a couple ways to handle this but, I've been torn on which I should do. I have an easy solution (insert one result at a time, instead of bulk inserting them,) but, it doesn't solve the bug, it just works around it. Another would be to prepare these records just prior to inserting/updating them but, it puts overhead on the application as opposed to the database to work around a database problem. The third, which requires more work and a newer version of PostgreSQL (>10.0,) which would allow me to do statement level triggers and know which rows happened in the bulk DML operation. This would let me process all of them at the same time instead of one row a time. This would require me to get a PostgreSQL 10 server running.

After thinking about it, I decided that I want to do this the right way and go with the last option. I've already installed PostgreSQL 10 on "Smite", the 3820 machine running in the attic. This would get me another step to making this project publicly visible as well (include me not starting the project every morning that I start my 3930k machine.) Hopefully over the next several weeks I can start to iron out these issues but, I have constantly been starting the service to make sure results are getting stored. We have quite the bit of information to work with already. Just recently we passed the 150k mark for results.

wcg=> select count(*) from results ;
 count  
--------
 154034
(1 row)

As always, if you haven't donated your username and validation code, it's more than welcome. Just send it over in a PM and I'll add you to the mix! Suggestions are always welcome as well. This is for me as much as it is for all of you.

 

[twilyth]

Don't push yourself. We're not going anywhere.

Thank you for the update. Was just thinking about this yesterday. And thanks for all of the work you're doing on it.

 

[Solaris17]

Totally unrelated, I only dabble in DBA as part of my job but I have taken up postgre instead of my normal sqli mysql route and im glad to see it gets so much love by someone who knows far more about this stuff then me. Now I wont doubt teaching myself with it.

 

[Aquinus]

Totally unrelated, I only dabble in DBA as part of my job but I have taken up postgre instead of my normal sqli mysql route and im glad to see it gets so much love by someone who knows far more about this stuff then me. Now I wont doubt teaching myself with it.

PostgreSQL is used all over the place and most people don't even realize it. At my last job everything lived in a PostgreSQL database so, I had to get good at it.

 

[Arjai]

By validation code, do you mean the Account Key? @Aquinus

EDIT: Never mind, got it. PM incoming

 

[Aquinus]

By validation code, do you mean the Account Key? @Aquinus

EDIT: Never mind, got it. PM incoming

You got it, thank you! An additional thank you to @blindfitter and @mstenholm for donating their validation codes as well. It's over 200,000 results at this point.

I'm still trying to hammer out the issue with gathering statistics and duplicating time frames. My initial idea wasn't really a viable solution because the same problem occurs using statement triggers after the fact and I'm having to re-evaluate how I'm doing it. Between not having much time and being held up on this bug, I haven't made a whole lot of progress lately. I have another idea but, it's going to take a little bit of time to implement. The wife has work today for almost the entire afternoon and I don't have a whole lot queued up to do so, I might spend a few hours trying to hammer it out. It seems like I need more than one trigger, one that occurs before the update/insert to ensure that time frames already exist (at the statement level,) and another to actually add the results to the proper time frames after the update/insert occurs instead of doing it all at once. We'll see where it goes.

 

[phill]

By validation code, do you mean the Account Key? @Aquinus

EDIT: Never mind, got it. PM incoming

Whereabouts can I find this?? I read that you found it but you never said where!!

 

[Aquinus]

Whereabouts can I find this?? I read that you found it but you never said where!!

It's hiding earlier in the thread:

It's in the "Profile" section at the WCG website. You can get there directly from here: https://www.worldcommunitygrid.org/ms/viewMyProfile.do

 

[thebluebumblebee]

https://www.techpowerup.com/forums/threads/wcg-badges-are-back.133019/post-2060655
Is the information in that post useful?

 

[Aquinus]

https://www.techpowerup.com/forums/threads/wcg-badges-are-back.133019/post-2060655
Is the information in that post useful?

I'm pulling the data right from WCG itself so, I get rather detailed information on every task that runs. I wouldn't really expect another service to have more data than what WCG actively provides through their APIs. In fact, there are other APIs that don't require the validation code but, you only get aggregated data. You don't get information about the individual tasks that have been run and how they turned out though. I wanted something a little more detailed.

wcg=# select * from wcg.results where "member-id" = 2 limit 20;
 result-id  | member-id | app-id |  claimed-credit  |     cpu-time      |   elapsed-time    | exit-status |  granted-credit  | device-id |  mod-time  | workunit-id |                      name                       | outcome |    received-time    |   report-deadline   |      sent-time      | server-state | validate-state | file-delete-state 
------------+-----------+--------+------------------+-------------------+-------------------+-------------+------------------+-----------+------------+-------------+-------------------------------------------------+---------+---------------------+---------------------+---------------------+--------------+----------------+-------------------
 1948697615 |         2 |      7 | 29.8301011520574 |  1.18309361111111 |  1.18501409805556 |           0 | 29.8301011520574 |   4147721 | 1511827932 |   382239207 | FAH2_001400_avx17285-1_000006_000019_011_0      |       1 | 2017-11-26 19:12:10 | 2017-11-27 13:04:05 | 2017-11-26 13:04:05 |            5 |              1 |                 2
 1947131990 |         2 |      9 |  115.57861353947 |  3.49610833333333 |  3.49666971333333 |           0 | 115.558663956831 |   4147721 | 1511888826 |   381123514 | MCM1_0138372_0709_0                             |       1 | 2017-11-26 22:22:45 | 2017-12-03 13:04:05 | 2017-11-26 13:04:05 |            5 |              1 |                 2
 1946844353 |         2 |      8 | 52.3375829148568 |  1.19127916666667 |  1.19299537805556 |           0 | 52.3375829148568 |   4147721 | 1511834658 |   380893934 | MIP1_00027672_1014_0                            |       1 | 2017-11-26 21:04:12 | 2017-12-06 13:04:05 | 2017-11-26 13:04:05 |            5 |              1 |                 2
 1950406989 |         2 |      5 | 55.6027842906484 | 0.966578888888889 | 0.967971758888889 |           0 | 47.3213098615202 |   4147721 | 1511830499 |   377129813 | OET1_0005185_x4GV3p_rig_57401_1                 |       1 | 2017-11-26 19:54:50 | 2017-11-30 01:04:05 | 2017-11-26 13:04:05 |            5 |              1 |                 2
 1947167322 |         2 |      9 |  115.83156193935 |  3.49771666666667 |  3.49988376861111 |           0 | 114.228093858699 |   4147721 | 1511892700 |   381153760 | MCM1_0138372_7768_0                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 13:22:43 | 2017-11-26 13:22:43 |            5 |              1 |                 2
 1958486218 |         2 |      9 | 113.851831659972 |            3.2492 |  3.25292790861111 |           0 | 133.269188164032 |   4147721 | 1512346758 |   389049231 | MCM1_0138513_0104_0                             |       1 | 2017-12-02 15:17:11 | 2017-12-09 02:46:58 | 2017-12-02 02:46:58 |            5 |              1 |                 2
 1958544950 |         2 |      9 | 116.235898450657 |  3.32084722222222 |  3.32104448861111 |           0 | 115.312236490598 |   4147721 | 1512350976 |   389096963 | MCM1_0138513_9169_0                             |       1 | 2017-12-02 15:17:12 | 2017-12-09 03:25:46 | 2017-12-02 03:25:46 |            5 |              1 |                 2
 1947236011 |         2 |      9 | 115.081032977187 |  3.47521111111111 |  3.47720632138889 |           0 | 113.579855238304 |   4147721 | 1511963167 |   381191261 | MCM1_0138374_1370_1                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1947236080 |         2 |      9 | 116.767996711817 |  3.52739444444444 |  3.52817841305556 |           0 | 115.464862627591 |   4147721 | 1511863588 |   381191280 | MCM1_0138374_1381_0                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1947235931 |         2 |      9 | 116.118943649506 |  3.50474722222222 |  3.50856708916667 |           0 | 114.768276930156 |   4147721 | 1512022691 |   381191238 | MCM1_0138374_1393_0                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1946910639 |         2 |      8 | 73.8430587175639 |  1.68195055555556 |  1.68332775666667 |           0 | 73.8430587175639 |   4147721 | 1511846750 |   380943481 | MIP1_00027712_1132_0                            |       1 | 2017-11-27 00:25:45 | 2017-12-06 15:20:43 | 2017-11-26 15:20:43 |            5 |              1 |                 2
 1947236016 |         2 |      9 | 116.018281738444 |          3.506775 |  3.50833199138889 |           0 | 115.170708471095 |   4147721 | 1511848483 |   381191246 | MCM1_0138374_1415_0                             |       1 | 2017-11-27 00:50:55 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1947236207 |         2 |      9 | 116.240204384259 |           3.51115 |  3.51223101694444 |           0 | 116.938012345142 |   4147721 | 1511846749 |   381191322 | MCM1_0138374_1191_1                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1950674896 |         2 |      5 | 43.5911238614777 | 0.787700555555556 |       0.789095175 |           0 | 39.7292781372023 |   4147721 | 1511839368 |   372199973 | OET1_0005176_x4GV3p_rig_64941_1                 |       1 | 2017-11-26 22:22:45 | 2017-11-30 03:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1942521820 |         2 |      5 | 35.5443068575337 | 0.642360833333333 | 0.643430096666667 |           0 | 35.5443068575337 |   4147721 | 1511839368 |   377822610 | OET1_0005186_x4GV3p_rig_50366_0                 |       1 | 2017-11-26 22:22:45 | 2017-12-06 15:20:43 | 2017-11-26 15:20:43 |            5 |              1 |                 2
 1950674875 |         2 |      5 | 35.1728439035742 | 0.636010833333333 | 0.636705800555556 |           0 | 32.1058763223576 |   4147721 | 1511839367 |   372200247 | OET1_0005176_x4GV3p_rig_64903_1                 |       1 | 2017-11-26 22:22:45 | 2017-11-30 03:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1947006426 |         2 |      8 | 37.3081783988738 | 0.873123611111111 | 0.873691461388889 |           0 | 37.3081783988738 |   4147721 | 1511848261 |   381019165 | MIP1_00027741_0477_0                            |       1 | 2017-11-27 00:50:55 | 2017-12-06 17:02:00 | 2017-11-26 17:02:00 |            5 |              1 |                 2
 1947468384 |         2 |      9 | 116.442905404309 |          3.521625 |  3.52281870472222 |           0 | 115.919721761301 |   4147721 | 1511859061 |   381337732 | MCM1_0138376_4982_0                             |       1 | 2017-11-27 03:50:54 | 2017-12-03 17:02:00 | 2017-11-26 17:02:00 |            5 |              1 |                 2
 1947034587 |         2 |      8 | 50.9230167742068 |  1.17323555555556 |  1.17407489277778 |           0 | 50.9230167742068 |   4147721 | 1511851302 |   381042375 | MIP1_00027745_0135_0                            |       1 | 2017-11-27 01:41:34 | 2017-12-06 17:15:00 | 2017-11-26 17:15:00 |            5 |              1 |                 2
 1950758585 |         2 |     10 | 47.4980647522305 |  1.01460805555556 |  1.01526597194444 |           0 | 47.5720429182576 |   4147721 | 1511851302 |   368162733 | ZIKA_000290493_x4wf8_HCV_NS3pr_Asnprvr_A_0562_1 |       1 | 2017-11-27 01:41:34 | 2017-11-30 05:15:00 | 2017-11-26 17:15:00 |            5 |              1 |                 2

With that said, I realize it has been quite some time since I've provided an update. Real life got rather busy for me so, I haven't spent a whole lot of time on working on this. I did a couple of layout changes but, that's about it. Beyond that, it's just been running on the 3820 box in the attic (which is also crunching.) It stopped running for about 5 days while I was on holiday a few months ago so, there is a small gap in the collected data but, I fixed the bug that caused that and it has been running continuously for a couple months now. The uptime on the machine itself is starting to become rather impressive as well, I might add.

$ uptime
 18:27:43 up 142 days,  9:53,  7 users,  load average: 8.22, 8.22, 8.14

The number of captured results is also getting a bit on the larger side. The front page has become a little sluggish as a result but, that's because I'm crunching lifetime stats when it loads which is something that will have to change.

wcg=# select count(*) from wcg.results ;
 count  
--------
 594429
(1 row)

I'm at a point where I really need to bite the bullet and handle registration and login, then finish a couple more pages to expose some more data before I decide to open up an alpha version to at least some people here at TPU. I just need to find the time to do it.

 

[Aquinus]

Unfortunately due to the European Union General Data Protection Regulation, WCG no longer lets me collect this information despite having asked all of you for your verification codes. I've contacted WCG requesting an access token to continue to work on this project but, that may only allow you to get your own information and I won't know more until I hear back from IBM. It's possible that the GDPR just killed this project.

 

[Norton]

@mstenholm shared a link at the WCG forum discussing this:

https://www.worldcommunitygrid.org/forums/wcg/viewthread_thread,40823_offset,10

Will likely kill off all of the stats programs

 

[Boatvan]

Will likely kill off all of the stats programs

This would make me a sad panda...

 

[phill]

Well that sucks... No more pie!!

 

[HammerON]

Thanks for taking on this task @Aquinus ! I wasn't aware that you were trying to do this. I really hope this doesn't end your project or others such as Free-DC and BOINCstats...

 

[Aquinus]

Well, I heard from IBM. Not only do I have to sign a legally binding document that says I'm complying with all applicable GDPR rules, I have to wait for IBM to finish preparing this document. As it stands right now, I'm unable to continue and once it's ready I'll be forced into a legal agreement should I continue to work on this project, even for members who aren't EU citizens, which personally rubs me the wrong way. I'm going to have to think long and hard before committing to something like this as I feel that the burden isn't necessarily worth the effort being put on to what I consider a hobby project.

Hello Jon,

Thank you for taking the time to contact us. As you've noticed, access to the APIs is now restricted to people who obtain a token. To get the token, you must first sign our Data Processing Agreement to ensure that you're willing to comply with certain requirements about how the API data must be processed. We are still finalizing the text of this Agreement and will email you as soon as it's ready. We have just implemented the release which allows members to access their own API stats: https://www.worldcommunitygrid.org/forums/wcg/viewpostinthread?post=582183

Please note that volunteers now have the option of making their data private, which would exclude it from the API export files and by default, all new volunteers' data will be private. You may want to hold on to the latest export files you have, and once you gain access through the token, you can compare the data to figure out the currency distribution retroactively.

Thanks for your patience as we continue to work aggressively to ensure compliance with the new EU General Data Protection Regulation and the protection of our volunteers' data.

Thank you for your support,

World Community Grid Support Team
---------------------------------------------------------
World Community Grid

 

[HammerON]

Understand whatever you choose to do in this instance.

 

[thebluebumblebee]

Does the EU need a Trump thump?