Fossil

Check-in [4439f15d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the experimental /artifact_size_stats webpage. There are no links to this page, yet.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:4439f15d6c033fc314c4840bcdeae18bb20e2faa71863196667f2f778d32d206
User & Date: drh 2017-12-10 02:30:51
Context
2017-12-11
14:52
Merge the /artifact_size_stats page into the /artifact_stats page. Make that page accessible to anybody with check-in privilege. check-in: cefadbd5 user: drh tags: trunk
2017-12-10
02:30
Add the experimental /artifact_size_stats webpage. There are no links to this page, yet. check-in: 4439f15d user: drh tags: trunk
00:56
On the Unknown Artifacts section of the /artifact_stats page, provide links to the RCVFROM table entry for each unknown artifact. check-in: 9f9ed245 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/name.c.

1021
1022
1023
1024
1025
1026
1027

1028
1029
1030
1031
1032
1033
1034
....
1094
1095
1096
1097
1098
1099
1100

1101
1102
1103
1104
1105
1106
1107

  login_check_credentials();
  if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
  style_header("List Of Artifacts");
  style_submenu_element("250 Largest", "bigbloblist");
  if( g.perm.Admin ){
    style_submenu_element("Artifact Log", "rcvfromlist");

  }
  if( !unpubOnly && mx>n && P("s")==0 ){
    int i;
    @ <p>Select a range of artifacts to view:</p>
    @ <ul>
    for(i=1; i<=mx; i+=n){
      @ <li> %z(href("%R/bloblist?s=%d&n=%d",i,n))
................................................................................
  Stmt q;
  int n = atoi(PD("n","250"));

  login_check_credentials();
  if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
  if( g.perm.Admin ){
    style_submenu_element("Artifact Log", "rcvfromlist");

  }
  style_submenu_element("All Artifacts", "bloblist");
  style_header("%d Largest Artifacts", n);
  db_multi_exec(
    "CREATE TEMP TABLE toshow(rid INTEGER PRIMARY KEY);"
    "INSERT INTO toshow(rid)"
    "  SELECT rid FROM blob"







>







 







>







1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
....
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109

  login_check_credentials();
  if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
  style_header("List Of Artifacts");
  style_submenu_element("250 Largest", "bigbloblist");
  if( g.perm.Admin ){
    style_submenu_element("Artifact Log", "rcvfromlist");
    style_submenu_element("Artifact Stats", "artifact_stats");
  }
  if( !unpubOnly && mx>n && P("s")==0 ){
    int i;
    @ <p>Select a range of artifacts to view:</p>
    @ <ul>
    for(i=1; i<=mx; i+=n){
      @ <li> %z(href("%R/bloblist?s=%d&n=%d",i,n))
................................................................................
  Stmt q;
  int n = atoi(PD("n","250"));

  login_check_credentials();
  if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
  if( g.perm.Admin ){
    style_submenu_element("Artifact Log", "rcvfromlist");
    style_submenu_element("Artifact Stats", "artifact_stats");
  }
  style_submenu_element("All Artifacts", "bloblist");
  style_header("%d Largest Artifacts", n);
  db_multi_exec(
    "CREATE TEMP TABLE toshow(rid INTEGER PRIMARY KEY);"
    "INSERT INTO toshow(rid)"
    "  SELECT rid FROM blob"

Changes to src/stat.c.

574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
...
589
590
591
592
593
594
595


596
597
598
599
600
601
602
...
627
628
629
630
631
632
633


634
635

636















































































































637
638
639
640
641
642
643
644
645


646
647
648
649
650
651
652
653
  }
  style_footer();
}

/*
** Gather statistics on artifact types, counts, and sizes.
*/
static void gather_artifact_stats(void){
  static const char zSql[] = 
    @ CREATE TEMP TABLE artstat(
    @   id INTEGER PRIMARY KEY,   -- Corresponds to BLOB.RID
    @   atype TEXT,               -- 'data', 'manifest', 'tag', 'wiki', etc.
    @   isDelta BOOLEAN,          -- true if stored as a delta
    @   szExp,                    -- expanded, uncompressed size
    @   szCmpr                    -- size as stored on disk
................................................................................
    @ );
    @ INSERT INTO artstat(id,atype,isDelta,szExp,szCmpr)
    @    SELECT blob.rid, NULL,
    @           EXISTS(SELECT 1 FROM delta WHERE delta.rid=blob.rid),
    @           size, length(content)
    @      FROM blob
    @     WHERE content IS NOT NULL;


    @ UPDATE artstat SET atype='file'
    @  WHERE id IN (SELECT fid FROM mlink)
    @    AND atype IS NULL;
    @ UPDATE artstat SET atype='manifest'
    @  WHERE id IN (SELECT objid FROM event WHERE type='ci') AND atype IS NULL;
    @ UPDATE artstat SET atype='cluster'
    @  WHERE atype IS NULL 
................................................................................
    @    AND id IN (SELECT srcid FROM tagxref);
    @ UPDATE artstat SET atype='tag'
    @  WHERE atype IS NULL 
    @    AND id IN (SELECT objid FROM event WHERE type='g');
    @ UPDATE artstat SET atype='unknown' WHERE atype IS NULL;
  ;
  db_multi_exec("%s", zSql/*safe-for-%s*/);


}


/*















































































































** WEBPAGE: artifact_stats
**
** Show information about the sizes of artifacts in this repository
*/
void artifact_stats_page(void){
  Stmt q;
  login_check_credentials();
  if( !g.perm.Admin ){ login_needed(g.anon.Admin); return; }
  style_header("Artifact Statistics");


  gather_artifact_stats();
  db_prepare(&q,
    "SELECT atype, count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
    "  FROM artstat GROUP BY 1"
    " UNION ALL "
    "SELECT 'ALL', count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
    "  FROM artstat"
    " ORDER BY 4;"







|







 







>
>







 







>
>
|
|
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>









>
>
|







574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
...
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
...
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
  }
  style_footer();
}

/*
** Gather statistics on artifact types, counts, and sizes.
*/
static void gather_artifact_stats(int bWithTypes){
  static const char zSql[] = 
    @ CREATE TEMP TABLE artstat(
    @   id INTEGER PRIMARY KEY,   -- Corresponds to BLOB.RID
    @   atype TEXT,               -- 'data', 'manifest', 'tag', 'wiki', etc.
    @   isDelta BOOLEAN,          -- true if stored as a delta
    @   szExp,                    -- expanded, uncompressed size
    @   szCmpr                    -- size as stored on disk
................................................................................
    @ );
    @ INSERT INTO artstat(id,atype,isDelta,szExp,szCmpr)
    @    SELECT blob.rid, NULL,
    @           EXISTS(SELECT 1 FROM delta WHERE delta.rid=blob.rid),
    @           size, length(content)
    @      FROM blob
    @     WHERE content IS NOT NULL;
  ;
  static const char zSql2[] = 
    @ UPDATE artstat SET atype='file'
    @  WHERE id IN (SELECT fid FROM mlink)
    @    AND atype IS NULL;
    @ UPDATE artstat SET atype='manifest'
    @  WHERE id IN (SELECT objid FROM event WHERE type='ci') AND atype IS NULL;
    @ UPDATE artstat SET atype='cluster'
    @  WHERE atype IS NULL 
................................................................................
    @    AND id IN (SELECT srcid FROM tagxref);
    @ UPDATE artstat SET atype='tag'
    @  WHERE atype IS NULL 
    @    AND id IN (SELECT objid FROM event WHERE type='g');
    @ UPDATE artstat SET atype='unknown' WHERE atype IS NULL;
  ;
  db_multi_exec("%s", zSql/*safe-for-%s*/);
  if( bWithTypes ){
    db_multi_exec("%s", zSql2/*safe-for-%s*/);
  }
}

/*
** WEBPAGE: artifact_size_stats
**
** Show information about the sizes of artifacts.
*/
void artifact_size_stats_page(void){
  Stmt q;
  int nTotal = 0;
  int nDelta = 0;
  int nFull = 0;
  int med;
  double r;
  login_check_credentials();
  if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
  style_header("Artifact Sizes");
  gather_artifact_stats(0);
  @ <table class="label-value">
  db_prepare(&q,
    "SELECT count(*), sum(isDelta), avg(szCmpr), avg(szExp), max(szCmpr),"
    "       max(szExp)"
    "  FROM artstat"
  );
  if( db_step(&q)==SQLITE_ROW ){
    nTotal = db_column_int(&q,0);
    nDelta = db_column_int(&q,1);
    nFull = nTotal - nDelta;
    @ <tr><th>Number of artifacts:</th><td>%d(nTotal)</td></tr>
    if( nTotal>0 ){
      @ <tr><th>Number of deltas:</th>\
      @ <td>%d(nDelta) (%d(nDelta*100/nTotal)%%)</td></tr>

      @ <tr><th>Number of full-text:</th><td>%d(nFull) \
      @ (%d(nFull*100/nTotal)%%)</td></tr>
    }
    @ <tr><th>Largest compressed artifact size:</th>\
    @ <td>%d(db_column_int(&q,4))</td></tr>

    @ <tr><th>Average compressed artifact size:</th> \
    @ <td>%.2f(db_column_double(&q,2))</td></tr>

    db_multi_exec("CREATE INDEX artstatx1 ON artstat(szCmpr, isDelta);");
    med = db_int(0, "SELECT szCmpr FROM artstat ORDER BY szCmpr"
                    " LIMIT 1 OFFSET %d", nTotal/2);
    @ <tr><th>Median compressed artifact size:</th><td>%d(med)</td></tr>

    @ <tr><th>Largest uncompressed artifact size:</td>\
    @ <td>%d(db_column_int(&q,5))</td></tr>

    @ <tr><th>Average uncompressed artifact size:</th> \
    @ <td>%.2f(db_column_double(&q,3))</td></tr>

    med = db_int(0, "SELECT szExp FROM artstat ORDER BY szExp"
                    " LIMIT 1 OFFSET %d", nTotal/2);
    @ <tr><th>Median uncompressed artifact size:</th><td>%d(med)</td></tr>

  }
  db_finalize(&q);
  db_prepare(&q,
    "SELECT avg(szCmpr), max(szCmpr) FROM artstat WHERE isDelta"
  );
  if( db_step(&q)==SQLITE_ROW ){
    @ <tr><th>Largest delta:</td>\
    @ <td>%d(db_column_int(&q,1))</td></tr>

    @ <tr><th>Average delta:</th> \
    @ <td>%.2f(db_column_double(&q,0))</td></tr>

    med = db_int(0, "SELECT szCmpr FROM artstat WHERE isDelta ORDER BY szCmpr"
                    " LIMIT 1 OFFSET %d", nDelta/2);
    @ <tr><th>Median delta:</th><td>%d(med)</td></tr>
  }
  db_finalize(&q);

  r = db_double(0.0, "SELECT avg(szCmpr) FROM artstat WHERE NOT isDelta;");
  @ <tr><th>Average full-text artifact:</th><td>%.2f(r)</td></tr>

  med = db_int(0, "SELECT szCmpr FROM artstat WHERE NOT isDelta ORDER BY szCmpr"
                  " LIMIT 1 OFFSET %d", nFull/2);
  @ <tr><th>Median full-text artifact:</th><td>%d(med)</td></tr>
  @ </table>
  if( nTotal>0 ){
    sqlite3_int64 szTotal;
    sqlite3_int64 szPart;
    @ <h2>Artifact size distribution facts:</h2>
    @ <ol>
    szTotal = db_int64(0, "SELECT sum(szCmpr) FROM artstat");
    szPart = db_int64(0,
       "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
                          " LIMIT %d)", (nTotal+99)/100);
    @ <li><p>The largest 1%% of artifacts (the largest %d((nTotal+99)/100) \
    @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
    szPart = db_int64(0,
       "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
                          " LIMIT %d)", (nTotal+9)/10);
    @ <li><p>The largest 10%% of artifacts (the largest %d((nTotal+9)/10) \
    @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
    szPart = db_int64(0,
       "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
                          " LIMIT %d)", nTotal/4);
    @ <li><p>The largest 25%% of artifacts (the largest %d(nTotal/4) \
    @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
    szPart = db_int64(0,
       "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
                          " LIMIT %d)", nTotal/2);
    @ <li><p>The largest 50%% of artifacts (the largest %d(nTotal/2) \
    @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
    @ </ol>
  }
  style_footer();
}

/*
** WEBPAGE: artifact_stats
**
** Show information about the sizes of artifacts in this repository
*/
void artifact_stats_page(void){
  Stmt q;
  login_check_credentials();
  if( !g.perm.Admin ){ login_needed(g.anon.Admin); return; }
  style_header("Artifact Statistics");
  style_submenu_element("Repository Stats", "stat");
  style_submenu_element("Artifact List", "bloblist");
  gather_artifact_stats(1);
  db_prepare(&q,
    "SELECT atype, count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
    "  FROM artstat GROUP BY 1"
    " UNION ALL "
    "SELECT 'ALL', count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
    "  FROM artstat"
    " ORDER BY 4;"