Crunching the Crunched Numbers

[Aquinus]

Back over in the WCG 13th Birthday Challenge (11/16-11/22/2017)- Calling all crunchers!!! thread, @Norton had said:

Have a project for one of you web gurus....

SETI.Germany is offering code to setup a personal stats webpage that will read the database from WCG and allow a cruncher to view their stats in greater detail.

*Note that this is similar to what FreeDC does with their stats pages.

I have no clue how to do this but am hoping that a Team member has experience with PHP and MYSQL and is willing to have a look.

See below for more details:


https://www.seti-germany.de/wcg/39_en_Personal WCG-Stats.html

I had poked at the APIs that IBM exposes and while they're not exactly well documented or consistent, I was able to pull apart the member API. First of all, you can't get older historical data for what work you've done in the past. IBM only exposes the last several days worth of results that have been processed. There comes a point where results become stale and no longer show up in the API. This requires a service that's capable of constantly checking and storing both new results and the difference on exists ones since the state of a result gets updated over time (time it was received, if it verified, it's status, etc.)

Once I figured out what was going on, I whipped out my handy go-to dev tools and went to work. I've made a basic schema in PostgreSQL and a small service that is capable of fetching the remote data, parsing it, and storing it in PostgreSQL. For me, the next step would be to turn it into a stream-based service based on the members stored in the database (which contains the username and verification code, both of which are required to fetch a member's stats.) That will be enough for a service that can run long-term and start building a historical database of the information coming out of WCG (IBM.) However, that will take time to gather enough data to be useful if any way. So, I would like to ask TPU's crunchers, what would you like to see from the data that gets gathered from all the crunching that you do?

Stats can be broken down obviously by date and time but, the "results" table in my database mimics the API in the sense that I capture everything that gets sent over the wire:

wcg=> \d wcg.results
                     Table "wcg.results"
      Column       |            Type             | Modifiers
-------------------+-----------------------------+-----------
 result-id         | bigint                      | not null
 member-id         | integer                     | not null
 app-id            | smallint                    | not null
 claimed-credit    | double precision            | not null
 cpu-time          | double precision            | not null
 elapsed-time      | double precision            | not null
 exit-status       | smallint                    | not null
 granted-credit    | double precision            | not null
 device-id         | integer                     | not null
 mod-time          | bigint                      | not null
 workunit-id       | bigint                      | not null
 name              | text                        | not null
 outcome           | smallint                    | not null
 received-time     | timestamp without time zone |
 report-deadline   | timestamp without time zone | not null
 sent-time         | timestamp without time zone | not null
 server-state      | smallint                    | not null
 validate-state    | smallint                    | not null
 file-delete-state | smallint                    | not null
Indexes:
    "results_pkey" PRIMARY KEY, btree ("result-id")
Foreign-key constraints:
    "app-fk" FOREIGN KEY ("app-id") REFERENCES apps("app-id")
    "device-fk" FOREIGN KEY ("device-id") REFERENCES devices("device-id")
    "member-fk" FOREIGN KEY ("member-id") REFERENCES members("member-id")

wcg=> select * from wcg.results 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
------------+-----------+--------+------------------+-------------------+-------------------+-------------+------------------+-----------+------------+-------------+-----------------------------------------------------+---------+---------------------+---------------------+---------------------+--------------+----------------+-------------------
 1944779575 |         2 |     10 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511446829 |   375352955 | ZIKA_000291302_x4mvn_Saur_SplApr_Inhib_chA_A_0398_1 |       0 |                     | 2017-12-03 09:20:29 | 2017-11-23 09:20:29 |            4 |              0 |                 0
 1944622928 |         2 |      5 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511442807 |   373622333 | OET1_0005179_x4GV3p_rig_28905_1                     |       0 |                     | 2017-12-03 08:13:27 | 2017-11-23 08:13:27 |            4 |              0 |                 0
 1944623069 |         2 |      5 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511442807 |   373621728 | OET1_0005179_x4GV3p_rig_23807_1                     |       0 |                     | 2017-12-03 08:13:27 | 2017-11-23 08:13:27 |            4 |              0 |                 0
 1940100016 |         2 |      9 | 113.678794959877 |  3.42911111111111 |  3.43268434694444 |           0 |                0 |   4147721 | 1511446829 |   376093930 | MCM1_0138293_1973_1                                 |       1 | 2017-11-23 09:20:29 | 2017-11-30 00:49:38 | 2017-11-23 00:49:38 |            5 |              0 |                 0
 1941690661 |         2 |      7 | 168.486486174654 |          6.002775 |  6.00404577805556 |           0 | 168.486486174654 |   4147721 | 1511450842 |   377230844 | FAH2_001911_avx17587-3_000003_000019_005_0          |       1 | 2017-11-23 10:27:16 | 2017-11-23 22:31:56 | 2017-11-22 22:31:56 |            5 |              1 |                 0
 1940659594 |         2 |      9 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511442128 |   376490337 | MCM1_0138300_5560_1                                 |       0 |                     | 2017-11-30 08:02:08 | 2017-11-23 08:02:08 |            4 |              0 |                 0
 1940474040 |         2 |      9 | 112.753212327416 |  3.39741944444444 |  3.39905427027778 |           0 |                0 |   4147721 | 1511458069 |   376358739 | MCM1_0138297_2767_0                                 |       1 | 2017-11-23 12:27:49 | 2017-11-30 04:42:42 | 2017-11-23 04:42:42 |            5 |              0 |                 0
 1944779602 |         2 |     10 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511446829 |   375353038 | ZIKA_000291303_x4mvn_Saur_SplApr_Inhib_chA_A_0196_1 |       0 |                     | 2017-12-03 09:20:29 | 2017-11-23 09:20:29 |            4 |              0 |                 0
 1940102407 |         2 |      9 | 112.552704046652 |  3.39271944444444 |        3.39701266 |           0 |                0 |   4147721 | 1511442807 |   376096095 | MCM1_0138293_1581_0                                 |       1 | 2017-11-23 08:13:27 | 2017-11-30 00:49:38 | 2017-11-23 00:49:38 |            5 |              0 |                 0
 1944779633 |         2 |     10 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511446829 |   375353024 | ZIKA_000291303_x4mvn_Saur_SplApr_Inhib_chA_A_0124_1 |       0 |                     | 2017-12-03 09:20:29 | 2017-11-23 09:20:29 |            4 |              0 |                 0
 1939997280 |         2 |      9 | 114.485224757391 |  3.45490833333333 |  3.45533908916667 |           0 | 114.388920818186 |   4147721 | 1511455369 |   376022608 | MCM1_0138291_7325_1                                 |       1 | 2017-11-23 08:13:27 | 2017-11-29 22:43:14 | 2017-11-22 22:43:14 |            5 |              1 |                 0
 1941887450 |         2 |      7 | 31.5086387184113 |  1.19480111111111 |  1.19673545694444 |           0 | 31.5086387184113 |   4147721 | 1511442813 |   377364209 | FAH2_001534_avx38743-1_000009_000085_007_0          |       1 | 2017-11-23 08:13:27 | 2017-11-24 01:13:54 | 2017-11-23 01:13:54 |            5 |              1 |                 0
 1940376831 |         2 |      9 | 112.894154366055 |  3.40276111111111 |  3.40330310388889 |           0 |                0 |   4147721 | 1511458069 |   376276524 | MCM1_0138296_1826_1                                 |       1 | 2017-11-23 12:27:49 | 2017-11-30 03:42:09 | 2017-11-23 03:42:09 |            5 |              0 |                 0
 1939862654 |         2 |      9 | 115.446118926124 |          3.478325 |  3.48295114305556 |           0 |                0 |   4147721 | 1511433982 |   375910450 | MCM1_0138290_2082_0                                 |       1 | 2017-11-23 05:46:22 | 2017-11-29 21:24:03 | 2017-11-22 21:24:03 |            5 |              0 |                 0
 1940379864 |         2 |      9 | 111.656539888297 |  3.36397222222222 |      3.3659940225 |           0 |                0 |   4147721 | 1511458069 |   376279210 | MCM1_0138296_0333_1                                 |       1 | 2017-11-23 12:27:49 | 2017-11-30 03:42:09 | 2017-11-23 03:42:09 |            5 |              0 |                 0
 1940695147 |         2 |      8 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511446829 |   376511213 | MIP1_00026328_0590_0                                |       0 |                     | 2017-12-03 09:20:29 | 2017-11-23 09:20:29 |            4 |              0 |                 0
 1936221814 |         2 |      5 | 49.0047204465838 | 0.882578055555556 | 0.883728396944444 |           0 |                0 |   4147721 | 1511426529 |   373321970 | OET1_0005178_x4GV3p_rig_36946_0                     |       1 | 2017-11-23 03:42:09 | 2017-12-02 22:31:56 | 2017-11-22 22:31:56 |            5 |              0 |                 0
 1939980888 |         2 |      9 | 114.041287003792 |  3.43873611111111 |      3.4415333825 |           0 |                0 |   4147721 | 1511442128 |   376009182 | MCM1_0138291_0595_0                                 |       1 | 2017-11-23 08:02:08 | 2017-11-29 22:31:56 | 2017-11-22 22:31:56 |            5 |              0 |                 0
 1940060297 |         2 |      9 | 113.045950913802 |  3.41115833333333 |  3.41189960444444 |           0 |                0 |   4147721 | 1511442807 |   376070391 | MCM1_0138292_6023_1                                 |       1 | 2017-11-23 08:13:27 | 2017-11-29 23:45:08 | 2017-11-22 23:45:08 |            5 |              0 |                 0
 1940137074 |         2 |      8 | 34.1818833143194 | 0.806429444444445 | 0.807077143888889 |           0 | 34.1818833143194 |   4147721 | 1511446838 |   376118938 | MIP1_00026200_0646_0                                |       1 | 2017-11-23 09:20:29 | 2017-12-03 02:26:13 | 2017-11-23 02:26:13 |            5 |              1 |                 0
(20 rows)

How would you like to see this data broken down and represented? What would like to get out of this information? Once I get the polling of data setup, I can get this running on the 3820 crunching in the attic and expose whatever I'm doing to all of you who are interested. Also, if you would like to donate your crunching statistics to the cause of science, I could use your username and verification code to make the API calls to watch your stat history as well.

Questions, comments, suggestions?

 

[stinger608]

One thing that I'd like to see is the separate systems each member has and see what kind of PPD he or she is getting. Don't know if it's possible, but to see each system overclocks, if any, as well as what operating system is being used.

 

[Aquinus]

One thing that I'd like to see is the separate systems each member has and see what kind of PPD he or she is getting. Don't know if it's possible, but to see each system overclocks, if any, as well as what operating system is being used.

I think that devices might have their own API but it's not documented and things like clock speeds will vary over time and aren't likely consistent even for a single result. Things like cores and memory should though but, that can still change for a single result so, I'm not sure. As far as the member stats API is concerned, all I get is an integer device id and a textual device name, likely the machine's hostname. These numbers can definitely be broken down by device though. What I thought was interesting is that they provide a validation status and how many points "claimed" by the client and how many actually get granted by WCG. In addition to breaking it down by device, it could show validated and invalidated results in case a device is unstable (no one wants their results thrown away!) So, I think there is a lot that can be derived from this information.

With that said though, there isn't a reason why this information couldn't be manually added by a user but, that would be down the road. Extracting information from APIs tends to be easier than designing and implementing interfaces for people to interact with.

Apparently most of the statistics pages have at least a XML version available just by adding an "xml=true" query parameter. Interesting information but, probably less useful than gathering result data itself since most of the statistic pages don't offer as granular information. This is probably handy if someone wanted to gather stats based on the team as a whole though.

<?xml version="1.0" encoding="UTF-8"?>
<TeamStats>
    <LastUpdated>2017-11-22T23:59:59</LastUpdated>
    <Team>
        <Name>TechPowerUp!</Name>
        <TeamId>S8TLJ6TFV1</TeamId>
        <Captain>Norton01</Captain>
        <URL>http://www.techpowerup.com</URL>
        <DateCreated>2009-02-20</DateCreated>
        <Description>Techpowerup.com</Description>
        <Type>Unclassified</Type>
        <BoincId>22175</BoincId>
    </Team>
    <StatisticsTotals>
        <CurrentMembers>672</CurrentMembers>
        <CurrentMembersRank>59</CurrentMembersRank>
        <RetiredMembers>73</RetiredMembers>
        <AllTimeMembers>745</AllTimeMembers>
        <AllTimeMembersRank>60</AllTimeMembersRank>
        <AllTimeDevices>5983</AllTimeDevices>
        <RunTime>289575552310</RunTime>
        <RunTimeRank>16</RunTimeRank>
        <Points>18004440186</Points>
        <PointsRank>11</PointsRank>
        <Results>42941430</Results>
        <ResultsRank>9</ResultsRank>
    </StatisticsTotals>
    <StatisticsAverages>
        <RunTimePerDay>90520648</RunTimePerDay>
        <RunTimePerResult>6743</RunTimePerResult>
        <PointsPerHourRunTime>223.830997308</PointsPerHourRunTime>
        <PointsPerDay>5628146.353860582</PointsPerDay>
        <PointsPerResult>419.27900831434005</PointsPerResult>
        <ResultsPerDay>13423.391684901531</ResultsPerDay>
    </StatisticsAverages>
    <TeamStatsByProjects>
        <Project>
            <ProjectName>Microbiome Immunity Project</ProjectName>
            <RunTime>1081367218</RunTime>
            <Points>56595813</Points>
            <Results>163659</Results>
        </Project>
        <Project>
            <ProjectName>Smash Childhood Cancer</ProjectName>
            <RunTime>4357771448</RunTime>
            <Points>249177603</Points>
            <Results>882952</Results>
        </Project>
        <Project>
            <ProjectName>OpenZika</ProjectName>
            <RunTime>9255776409</RunTime>
            <Points>449451719</Points>
            <Results>1234727</Results>
        </Project>
        <Project>
            <ProjectName>Help Stop TB</ProjectName>
            <RunTime>575731486</RunTime>
            <Points>30229247</Points>
            <Results>15631</Results>
        </Project>
        <Project>
            <ProjectName>FightAIDS@Home - Phase 2</ProjectName>
            <RunTime>11371397147</RunTime>
            <Points>525576329</Points>
            <Results>224606</Results>
        </Project>
        <Project>
            <ProjectName>Outsmart Ebola Together</ProjectName>
            <RunTime>49089508966</RunTime>
            <Points>2873147376</Points>
            <Results>11222806</Results>
        </Project>
        <Project>
            <ProjectName>Mapping Cancer Markers</ProjectName>
            <RunTime>86308592513</RunTime>
            <Points>4176264126</Points>
            <Results>5330461</Results>
        </Project>
        <Project>
            <ProjectName>FightAIDS@Home</ProjectName>
            <RunTime>40288962539</RunTime>
            <Points>1990703957</Points>
            <Results>8132631</Results>
        </Project>
        <Project>
            <ProjectName>Beta Testing</ProjectName>
            <RunTime>535223438</RunTime>
            <Points>25968932</Points>
            <Results>37473</Results>
        </Project>
        <Project>
            <ProjectName>Uncovering Genome Mysteries</ProjectName>
            <RunTime>9000944202</RunTime>
            <Points>428146212</Points>
            <Results>660738</Results>
        </Project>
        <Project>
            <ProjectName>Computing for Sustainable Water</ProjectName>
            <RunTime>897079173</RunTime>
            <Points>51646123</Points>
            <Results>196518</Results>
        </Project>
        <Project>
            <ProjectName>Say No to Schistosoma</ProjectName>
            <RunTime>4679698255</RunTime>
            <Points>231953234</Points>
            <Results>442701</Results>
        </Project>
        <Project>
            <ProjectName>GO Fight Against Malaria</ProjectName>
            <RunTime>5283018553</RunTime>
            <Points>255159157</Points>
            <Results>288355</Results>
        </Project>
        <Project>
            <ProjectName>Drug Search for Leishmaniasis</ProjectName>
            <RunTime>6431279603</RunTime>
            <Points>306928389</Points>
            <Results>345911</Results>
        </Project>
        <Project>
            <ProjectName>Computing for Clean Water</ProjectName>
            <RunTime>6415024793</RunTime>
            <Points>269607804</Points>
            <Results>593519</Results>
        </Project>
        <Project>
            <ProjectName>The Clean Energy Project - Phase 2</ProjectName>
            <RunTime>7207613574</RunTime>
            <Points>350003583</Points>
            <Results>303733</Results>
        </Project>
        <Project>
            <ProjectName>Discovering Dengue Drugs - Together - Phase 2</ProjectName>
            <RunTime>147240594</RunTime>
            <Points>6406141</Points>
            <Results>20552</Results>
        </Project>
        <Project>
            <ProjectName>Help Cure Muscular Dystrophy - Phase 2</ProjectName>
            <RunTime>10663696838</RunTime>
            <Points>465955813</Points>
            <Results>757353</Results>
        </Project>
        <Project>
            <ProjectName>Influenza Antiviral Drug Search</ProjectName>
            <RunTime>370159759</RunTime>
            <Points>12411160</Points>
            <Results>19778</Results>
        </Project>
        <Project>
            <ProjectName>Help Fight Childhood Cancer</ProjectName>
            <RunTime>9126781421</RunTime>
            <Points>364816261</Points>
            <Results>462818</Results>
        </Project>
        <Project>
            <ProjectName>The Clean Energy Project</ProjectName>
            <RunTime>85365447</RunTime>
            <Points>2810766</Points>
            <Results>3197</Results>
        </Project>
        <Project>
            <ProjectName>Nutritious Rice for the World</ProjectName>
            <RunTime>1998167499</RunTime>
            <Points>72071149</Points>
            <Results>79093</Results>
        </Project>
        <Project>
            <ProjectName>Help Conquer Cancer</ProjectName>
            <RunTime>15406653416</RunTime>
            <Points>4464368853</Points>
            <Results>11095413</Results>
        </Project>
        <Project>
            <ProjectName>Discovering Dengue Drugs - Together</ProjectName>
            <RunTime>222980332</RunTime>
            <Points>7810567</Points>
            <Results>12378</Results>
        </Project>
        <Project>
            <ProjectName>Human Proteome Folding - Phase 2</ProjectName>
            <RunTime>8775517687</RunTime>
            <Points>337229872</Points>
            <Results>414427</Results>
        </Project>
    </TeamStatsByProjects>
    <ResourceList>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/challenge/viewTeamChallengeHistory.do?teamId=S8TLJ6TFV1&amp;listType=1&amp;xml=true</Url>
            <Description>Upcoming Challenges</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/challenge/viewTeamChallengeHistory.do?teamId=S8TLJ6TFV1&amp;listType=2&amp;xml=true</Url>
            <Description>Current Challenges</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/challenge/viewTeamChallengeHistory.do?teamId=S8TLJ6TFV1&amp;listType=3&amp;xml=true</Url>
            <Description>Past Challenges</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=cpu&amp;xml=true</Url>
            <Description>Team Members by Run Time</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=points&amp;xml=true</Url>
            <Description>Team Members by Points</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=results&amp;xml=true</Url>
            <Description>Team Members by Results</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=name&amp;xml=true</Url>
            <Description>Team Members by Name</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=status&amp;xml=true</Url>
            <Description>Team Members by Date Joined</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamStatHistory.do?teamId=S8TLJ6TFV1&amp;xml=true</Url>
            <Description>Team Statistics History</Description>
        </Resource>
    </ResourceList>
</TeamStats>

https://www.worldcommunitygrid.org/team/viewTeamInfo.do?teamId=S8TLJ6TFV1&xml=true

I'm actively accepting usernames and verification codes to help me dev against. I can work with just my own but, more devices and members to dev against gives me a better picture when I go to start making an interface to regurgitate this information. In case you're worried about giving it away, you shouldn't. This kind of thing is what it was intended for:

What is the Verification Code and what is it used for?

The Verification Code is a private, personal code that you can use to verify your team membership and team stats. There are teams that provide incentives and/or rewards to members for being a part of their team. World Community Grid is providing the Verification Code as a way for these teams to verify their members' statistics and team membership without requiring members to give the team their password.

Note that your Verification Code will change if you change your member name and/or password. If your team is relying on your verification code to verify your membership and statistics, be sure they have your current Verification Code.

To verify the team membership and statistics for a member, use the API:
http://www.worldcommunitygrid.org/verifyMember.do?name=MEMBERNAME&code=VERIFICATIONCODE

You will get a response in XML containing the membership information for that member. If there is an error, it will be reported back in the XML response.

https://www.worldcommunitygrid.org/help/viewSearch.do?searchString=Verification+Code

Thanks to @Norton's donation of his verification code, I learned very quickly that I needed to be able to handle large amounts of data at once such as paging the API requests and inserting new records a chunk at a time. What's nifty is that there is enough data where I can do things like query how many points were earned in a day by member and even further, by device:

wcg=> select "member-id", SUM("granted-credit") from wcg.results where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY "member-id";
 member-id |       sum     
-----------+------------------
         2 | 5245.97557681395
         3 | 66964.4821919866
(2 rows)

wcg=> select "member-id", "device-id", SUM("granted-credit") from wcg.results where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY "member-id", "device-id";
 member-id | device-id |       sum     
-----------+-----------+------------------
         2 |   4147721 | 5245.97557681395
         3 |   2656516 | 6027.92572390353
         3 |   2853709 |  6473.3430370545
         3 |   3193884 |  21368.575360062
         3 |   3591341 | 4908.10795091952
         3 |   3984182 | 12043.5505308277
         3 |   4052433 | 16142.9795892193
(7 rows)

Last night (while semi-intoxicated,) I got the service running with an HTTP server and got a template using Bootstrap in place. It currently just spits out usernames and result counts but, that's it. I haven't added any routing into the mix so, it's really just a place where I can quickly prototype. My hope is to have something people can poke around at by the end of the year however, the data gathering part right now is almost stable (I need to do error handling,) so, I'm planning on running the interfaceless service to continue gathering data because a lot of things like plotting data over time requires data to see. Also as further incentive to donate your verification code, your data will already be partially populated when I open it up to the world to poke around at so you won't have to wait to start seeing data and possibly some pretty graphs.

For what it's worth, I'll probably publish the service on GitHub as open source software when it's closer to having a v1 completed.

 

[Norton]

Looks like you put quite a lot of work into this!

Would be great if a few more team members contributed their info to help @Aquinus build this out- send him a PM if you want to contribute your info to this project

 

[stinger608]

Would be great if a few more team members contributed their info to help @Aquinus build this out- send him a PM if you want to contribute your info to this project

I'd be more than happy to send whatever he needs. Just not sure where you get the verification code at?

 

[Aquinus]

I'd be more than happy to send whatever he needs. Just not sure where you get the verification code at?

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

 

[stinger608]

Okay, I see something on my profile page that says verification code. Is that what you need?

 

[twilyth]

@Aquinus you've got mail

 

[Hugis]

Sent my code & user name @Aquinus

Could be interesting as I haven't crunched in a while, lost a laptop to a storm and a tablet went belly up..

 

[4x4n]

pm sent

 

[Aquinus]

Thank you everyone for your contribution. Even between only a handful of members, it's really quite a large set of data for just a few days.

For example, this is just for completed work yesterday:

wcg=> select username, "device-name", SUM("granted-credit"), COUNT("result-id") from results join members using ("member-id") join devices using ("device-id") where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY username, "device-name";
  username  |     device-name     |       sum        | count
------------+---------------------+------------------+-------
 4x4n_tpu   | DH-PC               | 7736.15819838233 |   138
 4x4n_tpu   | mpower              | 19224.8523669279 |   438
 4x4n_tpu   | taichi-desktop      | 24275.7667378746 |   553
 aquinus    | smite               | 5541.81245691666 |    78
 Norton01   | DESKTOP-M6MN4N2     | 12347.4803879874 |   294
 Norton01   | ELLA-PC             | 4027.82146718246 |    55
 Norton01   | julia               | 6438.83071894064 |    84
 Norton01   | norton2011-desktop  | 18181.9472218535 |   408
 Norton01   | norton4p-Altus-1804 | 24299.5195763465 |   466
 Norton01   | tricia              |  6065.2347922258 |    70
 stinger608 | cruncher            | 3587.52525522426 |    46
 stinger608 | DeanMachine         |   3955.367090909 |    51
 stinger608 | DESKTOP-900270A     | 2402.88825371186 |    46
 stinger608 | WIN-UFMJ0MCV9J2     | 11320.7182622099 |   223
 twilyth    | 14CS                | 24972.6093114645 |   356
 twilyth    | 2600k2              |  8641.1379639476 |   114
 twilyth    | DESKTOP-57KS3G1     | 2016.65340216146 |    29
 twilyth    | i7-2600k2           | 3519.78272345441 |    47
(18 rows)

Sent my code & user name @Aquinus

Could be interesting as I haven't crunched in a while, lost a laptop to a storm and a tablet went belly up..

I got your code and username and validated that they work however, since you haven't crunched in a while, no results are showing up. IBM only provides the most recent result data and at some point, they eventually become "stale" and stop showing up in the API. As a result, I can't get historical data, I have to constantly keep my own history up to date by constantly checking the API. It's not entirely ideal but, it's better than nothing.

Edit: Since it's checking every 10 minutes, my 3930k warms up and makes the fans speeds up every 10 minutes when it goes to update the database with more data and I get audio hiccups on resampled streams when it occurs. I might have to move it to the 3820 sooner rather than later.

 

[XZero450]

What kind of service are you running to interrogate the API?

 

[Aquinus]

What kind of service are you running to interrogate the API?

I'm using Clojure. I just wrote something to regularly hit the API with the members stored in the PostgreSQL database. It massages the data a little bit and puts it into PostgreSQL.

As far as extracting the data from the API itself, it's really quite simple. I used Aleph for HTTP since I'm using it for the regular job to pull the data and for the HTTP server to barf out information and Cheshire for handling JSON. Getting the data is really this simple, at least in Clojure:

(defn make-url [username code limit offset]                                                                                                                                                 
  (str                                                                                                                                                                                       
    "https://www.worldcommunitygrid.org/api/members/"                                                                                                                                       
    username "/results?code=" code "&limit=" limit "&offset=" offset))                                                                                                                       
                                                                                                                                                                                             
(defn query-member-api [username code limit offset]                                                                                                                                         
  (json/parse-stream                                                                                                                                                                         
    (clojure.java.io/reader                                                                                                                                                                 
      (:body @(http/get (make-url username code limit offset)))) true))                                                                                                                     
                                                                                                                                                                                             
(defn get-data                                                                                                                                                                               
  [username code]                                                                                                                                                                           
  (loop [offset 0                                                                                                                                                                           
         return (list)]                                                                                                                                                                     
    (let [data (query-member-api username code 250 offset)                                                                                                                                   
          {results-available :ResultsAvailable                                                                                                                                               
           results-returned :ResultsReturned                                                                                                                                                 
           results :Results} (:ResultsStatus data)                                                                                                                                           
          updated-return (into return results)                                                                                                                                               
          new-offset (+ (Integer. results-returned) offset)]                                                                                                                                 
      (if (<= (Integer. results-available) new-offset)                                                                                                                                       
        updated-return (recur new-offset updated-return)))))

In short, it's a service that I'm building, not one I found.

 

[twilyth]

I remember that DAWS(?) used to do something like this but it choked once you started getting into thousands of results. I guess the real issue is going to be how well the system scales.

 

[Aquinus]

I remember that DAWS(?) used to do something like this but it choked once you started getting into thousands of results. I guess the real issue is going to be how well the system scales.

I've spent a lot of time in my last job optimizing a PostgreSQL database where some historical tables had several million records with a couple over 10m. If you know how you're going to query it and in what ways the data set is going to grow, you can plan around it. PostgreSQL also has tools to help me figure out how it planned on executing the query and how it actually panned out, which is nice from a tuning perspective.

So, I'm going to walk you through how I would optimize a poorly written query (by me,) that doesn't scale well in PostgreSQL. It's a great learning opportunity.

So lets say we have this many results:

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

wcg=> explain analyze select count(*) from wcg.results ;
                                                   QUERY PLAN                                                  
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=939.23..939.24 rows=1 width=8) (actual time=10.151..10.151 rows=1 loops=1)
   ->  Seq Scan on results  (cost=0.00..872.98 rows=26498 width=0) (actual time=0.014..5.755 rows=26498 loops=1)
 Planning time: 0.082 ms
 Execution time: 10.203 ms
(4 rows)

It gets even more interesting when you try to do say, a query that aggregates data:

wcg=> select username, "device-name", SUM("granted-credit"), COUNT("result-id") from results join members using ("member-id") join devices using ("device-id") where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY username, "device-name";
  username  |     device-name     |       sum        | count
------------+---------------------+------------------+-------
 4x4n_tpu   | DH-PC               | 8952.24814170299 |   130
 4x4n_tpu   | mpower              | 19214.1775239653 |   432
 4x4n_tpu   | taichi-desktop      | 23946.2451875794 |   539
 aquinus    | smite               | 4438.34912934682 |    74
 Norton01   | DESKTOP-M6MN4N2     | 12752.7377335826 |   290
 Norton01   | ELLA-PC             |  4299.5168611983 |    62
 Norton01   | julia               | 5971.11323818165 |    68
 Norton01   | norton2011-desktop  | 17821.9336815164 |   400
 Norton01   | norton4p-Altus-1804 |  21458.223920495 |   350
 Norton01   | tricia              | 6043.73143995742 |    84
 stinger608 | cruncher            | 4098.88799049337 |    68
 stinger608 | DeanMachine         | 4623.43757457595 |    74
 stinger608 | DESKTOP-900270A     | 3664.34179903938 |    44
 stinger608 | WIN-UFMJ0MCV9J2     | 11059.2137892713 |   180
 twilyth    | 14CS                | 22655.2403945448 |   315
 twilyth    | 2600k2              |  8663.4214587507 |   103
 twilyth    | DESKTOP-57KS3G1     | 1921.78183320034 |    25
 twilyth    | i7-2600k2           | 1339.58374298233 |   193
 XZero450   | Enyo                | 15148.7135864938 |   191
 XZero450   | Kryos               | 13206.0733791159 |   190
 XZero450   | Phobos              | 9123.14956541549 |   126
(21 rows)

wcg=> explain analyze select username, "device-name", SUM("granted-credit"), COUNT("result-id") from results join members using ("member-id") join devices using ("device-id") where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY username, "device-name";
                                                             QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1214.17..1215.83 rows=1 width=30) (actual time=1361.792..1362.690 rows=21 loops=1)
   Group Key: members.username, devices."device-name"
   ->  Sort  (cost=1214.17..1214.50 rows=132 width=30) (actual time=1361.757..1361.950 rows=3938 loops=1)
         Sort Key: members.username, devices."device-name"
         Sort Method: quicksort  Memory: 404kB
         ->  Nested Loop  (cost=0.00..1209.53 rows=132 width=30) (actual time=9.610..1360.271 rows=3938 loops=1)
               Join Filter: (results."device-id" = devices."device-id")
               Rows Removed by Join Filter: 94512
               ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=10) (actual time=0.014..0.017 rows=25 loops=1)
               ->  Nested Loop  (cost=0.00..1206.87 rows=132 width=28) (actual time=4.079..54.194 rows=3938 loops=25)
                     Join Filter: (results."member-id" = members."member-id")
                     Rows Removed by Join Filter: 19690
                     ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.002 rows=6 loops=25)
                     ->  Seq Scan on results  (cost=0.00..1204.20 rows=132 width=24) (actual time=0.094..8.763 rows=3938 loops=150)
                           Filter: (("received-time")::date = ((now())::date - '1 day'::interval))
                           Rows Removed by Filter: 22560
 Planning time: 0.527 ms
 Execution time: 1362.792 ms
(18 rows)

This is a great case where my query ran slow on a relatively small (<100,000 records,) set of data. This is absolutely terrible because we can't be taking over a second to make a query to display data to a user. People are impatient, and so am I but, what you'll see here is that there are two nested loops and that always spells exponential time. This particular query can be re-written to be a little more efficient, even though this was the easy way to write it. In order to speed this up, we need to give up on querying directly on the results because that's what's killing performance. The two smaller tables are getting joined up to large set of data where most of the data is getting thrown away. That's a lot of wasted effort on the computer's part because the reality is that querying on devices and members isn't very costly:

wcg=> explain analyze SELECT username, "device-name" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                               QUERY PLAN                                               
---------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2.03 rows=1 width=14) (actual time=0.026..0.135 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=10) (actual time=0.013..0.014 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.002 rows=6 loops=25)
 Planning time: 0.197 ms
 Execution time: 0.172 ms
(7 rows)

Since wcg.results has a foreign key on both "wcg."devices"."device-id" and "wcg"."members"."member-id", we can look up those records fairly quickly. The hardest part is checking the time range because there isn't an index on it. We can isolate the aggregation to each unique device by doing a sub-query instead of a JOIN.

wcg=> SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
  username  |     device-name     | total-granted-credit | total-results 
------------+---------------------+----------------------+---------------
 aquinus    | smite               |     4438.34912934682 |            74
 Norton01   | norton2011-desktop  |     17821.9336815164 |           400
 Norton01   | DESKTOP-M6MN4N2     |     12800.8226101572 |           290
 Norton01   | tricia              |     6043.73143995742 |            84
 Norton01   | julia               |     5971.11323818165 |            68
 Norton01   | ELLA-PC             |      4299.5168611983 |            62
 Norton01   | norton4p-Altus-1804 |     21499.8998872169 |           350
 stinger608 | cruncher            |     4098.88799049337 |            68
 stinger608 | cruncher            |                      |             0
 stinger608 | DeanMachine         |     4623.43757457595 |            74
 stinger608 | WIN-UFMJ0MCV9J2     |     11059.2137892713 |           180
 stinger608 | DESKTOP-900270A     |     3664.34179903938 |            44
 stinger608 | DESKTOP-2RG7LMF     |                      |             0
 stinger608 | DESKTOP-7GGICF0     |                      |             0
 twilyth    | i7-2600k2           |     361.985833127019 |             7
 twilyth    | DESKTOP-57KS3G1     |     1921.78183320034 |            25
 twilyth    | 14CS                |     22655.2403945448 |           315
 twilyth    | 2600k2              |     8663.42145875069 |           103
 4x4n_tpu   | mpower              |     19214.1775239653 |           432
 4x4n_tpu   | DH-PC               |     8952.24814170299 |           130
 4x4n_tpu   | taichi-desktop      |     23946.2451875794 |           539
 twilyth    | i7-2600k2           |     977.597909855306 |           186
 XZero450   | Phobos              |      9123.1495654155 |           126
 XZero450   | Enyo                |     15148.7135864938 |           191
 XZero450   | Kryos               |     13206.0733791159 |           190
(25 rows)

wcg=> explain analyze SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2563.58 rows=1 width=30) (actual time=12.068..147.161 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=14) (actual time=0.012..0.014 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.001 rows=6 loops=25)
   SubPlan 1
     ->  Aggregate  (cost=1280.76..1280.77 rows=1 width=8) (actual time=3.068..3.068 rows=1 loops=25)
           ->  Seq Scan on results  (cost=0.00..1280.75 rows=5 width=8) (actual time=1.082..3.056 rows=158 loops=25)
                 Filter: (("device-id" = devices."device-id") AND (("received-time")::date = ((now())::date - '1 day'::interval)))
                 Rows Removed by Filter: 26405
   SubPlan 2
     ->  Aggregate  (cost=1280.76..1280.77 rows=1 width=8) (actual time=2.811..2.811 rows=1 loops=25)
           ->  Seq Scan on results results_1  (cost=0.00..1280.75 rows=5 width=0) (actual time=1.032..2.800 rows=158 loops=25)
                 Filter: (("device-id" = devices."device-id") AND (("received-time")::date = ((now())::date - '1 day'::interval)))
                 Rows Removed by Filter: 26405
 Planning time: 0.421 ms
 Execution time: 147.246 ms
(17 rows)

Look at that! Not only does the new query take only 147ms instead of the initial 1362ms, but we're now seeing all the devices that don't actually have earned credit! However what you might notice is that both of the sub-queries aren't using an index and while the sub-queries themselves don't take much time (~2ms,) they're occurring 25 times for each sub-query so, the bulk of the time is still being spent on aggregating but, it's a lot less. The main reason is because sequential scans are costly. Whenever possible, we want to use an index because it will prevent us from having to filter unnecessary records by doing a scan of the index instead of the table.

In this case, a single generic index on wcg.results."received-time" could get us some improvement, then we're only filtering on the actual times. We could index on "received-time", which could further speed up the query but, if we're tuning to make this specific query fast, the best index would either be on "device-id" then on "received-time" or the opposite with the date coming first (less likely.) The first lets us still quickly query all results with a particular device id, the second helps query all results given a time range. So, depending on what other queries you're going to be doing. In this case alone, the ideal index would probably be on device, then the time range because no filtering should have to occur:

wcg=> create index "daily-device-results-idx" ON wcg.results ("device-id", "received-time");
CREATE INDEX
wcg=> explain analyze SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                                                       QUERY PLAN                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1383.47 rows=1 width=30) (actual time=3.617..36.977 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=14) (actual time=0.009..0.011 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.002 rows=6 loops=25)
   SubPlan 1
     ->  Aggregate  (cost=690.71..690.72 rows=1 width=8) (actual time=0.928..0.929 rows=1 loops=25)
           ->  Bitmap Heap Scan on results  (cost=28.28..690.70 rows=5 width=8) (actual time=0.288..0.910 rows=158 loops=25)
                 Recheck Cond: ("device-id" = devices."device-id")
                 Filter: (("received-time")::date = ((now())::date - '1 day'::interval))
                 Rows Removed by Filter: 908
                 Heap Blocks: exact=2517
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..28.28 rows=1066 width=0) (actual time=0.121..0.121 rows=1066 loops=25)
                       Index Cond: ("device-id" = devices."device-id")
   SubPlan 2
     ->  Aggregate  (cost=690.71..690.72 rows=1 width=8) (actual time=0.543..0.543 rows=1 loops=25)
           ->  Bitmap Heap Scan on results results_1  (cost=28.28..690.70 rows=5 width=0) (actual time=0.192..0.530 rows=158 loops=25)
                 Recheck Cond: ("device-id" = devices."device-id")
                 Filter: (("received-time")::date = ((now())::date - '1 day'::interval))
                 Rows Removed by Filter: 908
                 Heap Blocks: exact=2517
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..28.28 rows=1066 width=0) (actual time=0.075..0.075 rows=1066 loops=25)
                       Index Cond: ("device-id" = devices."device-id")
 Planning time: 0.909 ms
 Execution time: 37.086 ms
(25 rows)

Notice how both sequential scans now have become a bitmap index scan with a condition on... the device id? This is because we're casting the time field to a date to make everything easier for for the person writing it (me.) We can probably fix this by doing a query against a range instead of just casting the target field to a date:

wcg=> explain analyze SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time" BETWEEN NOW()::date - INTERVAL '1 day' AND NOW()::date) AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time" BETWEEN NOW()::date - INTERVAL '1 day' AND NOW()::date) AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                                                                 QUERY PLAN                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..748.53 rows=1 width=30) (actual time=0.275..9.108 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=14) (actual time=0.011..0.023 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.002..0.003 rows=6 loops=25)
   SubPlan 1
     ->  Aggregate  (cost=373.24..373.25 rows=1 width=8) (actual time=0.198..0.198 rows=1 loops=25)
           ->  Bitmap Heap Scan on results  (cost=6.31..372.84 rows=158 width=8) (actual time=0.059..0.143 rows=158 loops=25)
                 Recheck Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
                 Heap Blocks: exact=619
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..6.28 rows=158 width=0) (actual time=0.048..0.048 rows=158 loops=25)
                       Index Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
   SubPlan 2
     ->  Aggregate  (cost=373.24..373.25 rows=1 width=8) (actual time=0.153..0.153 rows=1 loops=25)
           ->  Bitmap Heap Scan on results results_1  (cost=6.31..372.84 rows=158 width=0) (actual time=0.050..0.121 rows=158 loops=25)
                 Recheck Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
                 Heap Blocks: exact=619
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..6.28 rows=158 width=0) (actual time=0.041..0.041 rows=158 loops=25)
                       Index Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
 Planning time: 0.644 ms
 Execution time: 9.237 ms
(21 rows)

So, by changing the query and adding a single index, I was able to make a query that was taking well over a second to take under 10ms. A lot of times, I would see 37ms and say, "that's good enough," but, I thought that going that extra step further would show you can really optimize databases and queries to be very performant even with massive amounts of data.

This is also with a 100% stock PostgreSQL installation. I could tune the server itself to use a lot more system memory (it's typically very conservative on memory,) to make it go even faster.

The thing I would point out though is that the PostgreSQL query planner got the query cost dead wrong which means stats are out of date and the cost for I/O needs to be adjusted to account for the fact that it's running on a RAID-0 SSD array and not a traditional rotational media drive so, that could further help PostgreSQL optimize how it's going to make the query.

With that said, whenever I'm going to be dealing with large amounts of data that need to be queried upon and changed quickly and often, I will always opt for PostgreSQL because it's open source, very fast, and has a boatload of features and this only skims the surface.

 

[twilyth]

I didn't follow most of that but thanks for putting in the time you are on this. It's great you're optimizing everything for large datasets now while you're building the system.

 

[Aquinus]

I didn't follow most of that but thanks for putting in the time you are on this. It's great you're optimizing everything for large datasets now while you're building the system.

I want to go even one step further now. On the earlier post there were 26498 results total, now there are:

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

Which is a nice healthy increase of just over 50%. If I run that same query:

wcg=> explain analyze SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time" BETWEEN NOW()::date - INTERVAL '1 day' AND NOW()::date) AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time" BETWEEN NOW()::date - INTERVAL '1 day' AND NOW()::date) AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                                                                 QUERY PLAN                                                                                
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..422.52 rows=1 width=30) (actual time=0.179..4.806 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=14) (actual time=0.008..0.010 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.002 rows=6 loops=25)
   SubPlan 1
     ->  Aggregate  (cost=408.49..408.50 rows=1 width=8) (actual time=0.136..0.137 rows=1 loops=25)
           ->  Bitmap Heap Scan on results  (cost=10.18..408.13 rows=147 width=8) (actual time=0.037..0.106 rows=146 loops=25)
                 Recheck Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
                 Heap Blocks: exact=1065
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..10.14 rows=147 width=0) (actual time=0.028..0.028 rows=146 loops=25)
                       Index Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
   SubPlan 2
     ->  Aggregate  (cost=11.98..11.99 rows=1 width=8) (actual time=0.049..0.049 rows=1 loops=25)
           ->  Index Only Scan using "daily-device-results-idx" on results results_1  (cost=0.30..11.61 rows=147 width=0) (actual time=0.006..0.032 rows=146 loops=25)
                 Index Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
                 Heap Fetches: 0
 Planning time: 0.557 ms
 Execution time: 4.881 ms
(19 rows)

This is what a query that will scale looks like.

I haven't had time to work on anything during the week since I have my day job but, I have been letting the service run so I can get as much data as I can for when I have time to work on it. I might have some time to work on it a little bit over the weekend.

 

[Aquinus]

...and the struggle continues! I've taken the above "yesterday's stats" query and threw it into a view, so I don't have to replicate the query using HoneySQL. I find that a lot of times, when a query starts to become complex, sometimes the best option is to throw it into a view, particularly if it's something like this where it's relatively static and the only variable is what the day is, so it works out well. So, there's a new "wcg"."view-yesterday-stats" and I'm throwing it on the single page that the HTTP server is currently barfing out (locally.) With only the lifetime gathered stats it was rather empty but, having yesterday's stats in there makes it feel like it's actually doing something.

I need to figure out what routing library I'm going to use to handle multiple pages though. I've built my own several years ago but, there are things about it that I would like to change. I'm not sure if I want to use it as it is, change it first, or use something else. I suspect that I'll add a navigation bar to the top of the page that lets you get to various pages with generalized stats as I produce the queries to make them. Bootstrap doesn't really have a very good sidebar nav and my CSS skills are very limited so, I'm probably going to avoid that.

As I said before, if there is something you would like to see out of this data, I'm sure I can make something to show it if the data is available. This isn't a tool for me as much as it would be a tool for all of you. I'm just using this as a project to keep certain skills of mine sharp while I'm doing other things in my day job (different languages, databases, etc.) Plus, I like doing things for the community when I can. I would write open source software all day long if I didn't have to pay the bills with it.

I'm also accepting any creative names for the service as well. WCG Stats is just my boring "I need a name" name.

 

[Norton]

As I said before, if there is something you would like to see out of this data, I'm sure I can make something to show it if the data is available. This isn't a tool for me as much as it would be a tool for all of you. I'm just using this as a project to keep certain skills of mine sharp while I'm doing other things in my day job (different languages, databases, etc.) Plus, I like doing things for the community when I can. I would write open source software all day long if I didn't have to pay the bills with it.
.

That output looks great so far! Really appreciate the time you're putting into this!


Would be great to start off with a daily totals by member, possibly a conversion from WCG points to FreeDC/BOINC points (7 WCG points= 1 FreeDC point). Not sure what other members want to see- maybe we can start a poll?

EDIT- the stats you posted may already be in BOINC points but will need to be checked?

I'm also accepting any creative names for the service as well. WCG Stats is just my boring "I need a name" name.

Going to post this question on the team thread to see if we can get some replies

 

[T.R.]

I'm also accepting any creative names for the service as well. WCG Stats is just my boring "I need a name" name.

GridPowerUp

 

[Aquinus]

GridPowerUp

I actually kind of like that one. I wouldn't want to intrude on @W1zzard as the (something)PowerUp naming scheme is kind of his thing. Either way, I saw that the GridPowerUp.com domain was available for very little money so, I grabbed it, just in case.

 

[phill]

I could have sworn I posted in this thread a few days ago... Still, definitely something PowerUp.com is something I'd like to see

 

[W1zzard]

I don't mind you using that name, if this turns out into something more mature we could probably host it for you or make it an official part of TPU, if you are interested

 

[twilyth]

As I said before, if there is something you would like to see out of this data, I'm sure I can make something to show it if the data is available.

I'd like to see a least squares regression for the past 30 days for the points generated by each host as well as for total points.

.

.

.

I'm kidding of course . . . . unless . . .

 

[Aquinus]

I'd like to see a least squares regression for the past 30 days for the points generated by each host as well as for total points.

.

.

.

I'm kidding of course . . . . unless . . .

It's not unrealistic and might actually not be too difficult as PostgreSQL happens to already have some built-in aggregation functions for statistics and might have enough for me to do a simple least squares linear regression directly using just SQL.
https://www.postgresql.org/docs/9.5/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE

In order to spit out things like daily, weekly, or hourly statistics for hosts or users, I already would need to be grouping by time so, I can include things like this when I go to start dumping out aggregate data against regular time intervals. This is something I already wanted to do already and adding this to it shouldn't be a big deal. I also have experience producing this kind of output from data in PostgreSQL. It would probably take me more time to wire up C3.js to display it.

With that said, I've loaded a routing library in and I've started hashing out some routes for the different pages that could be useful. Maybe tonight or tomorrow I'll craft up a query or something that produces this kind out output.