Fossil SCM

Add the experimental /artifact_size_stats webpage. There are no links to this page, yet.

drh 2017-12-10 02:30 trunk
Commit 4439f15d6c033fc314c4840bcdeae18bb20e2faa71863196667f2f778d32d206
Parent 9f9ed245f963572…
2 files changed +2 +120 -2
~ src/name.c ~ src/stat.c
M src/name.c
+2
--- src/name.c
+++ src/name.c
@@ -1023,10 +1023,11 @@
10231023
 if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
10241024
 style_header("List Of Artifacts");
10251025
 style_submenu_element("250 Largest", "bigbloblist");
10261026
 if( g.perm.Admin ){
10271027
 style_submenu_element("Artifact Log", "rcvfromlist");
1028
+ style_submenu_element("Artifact Stats", "artifact_stats");
10281029
 }
10291030
 if( !unpubOnly && mx>n && P("s")==0 ){
10301031
 int i;
10311032
 @ <p>Select a range of artifacts to view:</p>
10321033
 @ <ul>
@@ -1096,10 +1097,11 @@
10961097
10971098
 login_check_credentials();
10981099
 if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
10991100
 if( g.perm.Admin ){
11001101
 style_submenu_element("Artifact Log", "rcvfromlist");
1102
+ style_submenu_element("Artifact Stats", "artifact_stats");
11011103
 }
11021104
 style_submenu_element("All Artifacts", "bloblist");
11031105
 style_header("%d Largest Artifacts", n);
11041106
 db_multi_exec(
11051107
 "CREATE TEMP TABLE toshow(rid INTEGER PRIMARY KEY);"
11061108
--- src/name.c
+++ src/name.c
@@ -1023,10 +1023,11 @@
1023 if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
1024 style_header("List Of Artifacts");
1025 style_submenu_element("250 Largest", "bigbloblist");
1026 if( g.perm.Admin ){
1027 style_submenu_element("Artifact Log", "rcvfromlist");
 
1028 }
1029 if( !unpubOnly && mx>n && P("s")==0 ){
1030 int i;
1031 @ <p>Select a range of artifacts to view:</p>
1032 @ <ul>
@@ -1096,10 +1097,11 @@
1096
1097 login_check_credentials();
1098 if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
1099 if( g.perm.Admin ){
1100 style_submenu_element("Artifact Log", "rcvfromlist");
 
1101 }
1102 style_submenu_element("All Artifacts", "bloblist");
1103 style_header("%d Largest Artifacts", n);
1104 db_multi_exec(
1105 "CREATE TEMP TABLE toshow(rid INTEGER PRIMARY KEY);"
1106
--- src/name.c
+++ src/name.c
@@ -1023,10 +1023,11 @@
1023 if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
1024 style_header("List Of Artifacts");
1025 style_submenu_element("250 Largest", "bigbloblist");
1026 if( g.perm.Admin ){
1027 style_submenu_element("Artifact Log", "rcvfromlist");
1028 style_submenu_element("Artifact Stats", "artifact_stats");
1029 }
1030 if( !unpubOnly && mx>n && P("s")==0 ){
1031 int i;
1032 @ <p>Select a range of artifacts to view:</p>
1033 @ <ul>
@@ -1096,10 +1097,11 @@
1097
1098 login_check_credentials();
1099 if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
1100 if( g.perm.Admin ){
1101 style_submenu_element("Artifact Log", "rcvfromlist");
1102 style_submenu_element("Artifact Stats", "artifact_stats");
1103 }
1104 style_submenu_element("All Artifacts", "bloblist");
1105 style_header("%d Largest Artifacts", n);
1106 db_multi_exec(
1107 "CREATE TEMP TABLE toshow(rid INTEGER PRIMARY KEY);"
1108
M src/stat.c
+120 -2
--- src/stat.c
+++ src/stat.c
@@ -576,11 +576,11 @@
576576
 }
577577
578578
 /*
579579
 ** Gather statistics on artifact types, counts, and sizes.
580580
 */
581
-static void gather_artifact_stats(void){
581
+static void gather_artifact_stats(int bWithTypes){
582582
 static const char zSql[] =
583583
 @ CREATE TEMP TABLE artstat(
584584
 @ id INTEGER PRIMARY KEY, -- Corresponds to BLOB.RID
585585
 @ atype TEXT, -- 'data', 'manifest', 'tag', 'wiki', etc.
586586
 @ isDelta BOOLEAN, -- true if stored as a delta
@@ -591,10 +591,12 @@
591591
 @ SELECT blob.rid, NULL,
592592
 @ EXISTS(SELECT 1 FROM delta WHERE delta.rid=blob.rid),
593593
 @ size, length(content)
594594
 @ FROM blob
595595
 @ WHERE content IS NOT NULL;
596
+ ;
597
+ static const char zSql2[] =
596598
 @ UPDATE artstat SET atype='file'
597599
 @ WHERE id IN (SELECT fid FROM mlink)
598600
 @ AND atype IS NULL;
599601
 @ UPDATE artstat SET atype='manifest'
600602
 @ WHERE id IN (SELECT objid FROM event WHERE type='ci') AND atype IS NULL;
@@ -629,10 +631,124 @@
629631
 @ WHERE atype IS NULL
630632
 @ AND id IN (SELECT objid FROM event WHERE type='g');
631633
 @ UPDATE artstat SET atype='unknown' WHERE atype IS NULL;
632634
 ;
633635
 db_multi_exec("%s", zSql/*safe-for-%s*/);
636
+ if( bWithTypes ){
637
+ db_multi_exec("%s", zSql2/*safe-for-%s*/);
638
+ }
639
+}
640
+
641
+/*
642
+** WEBPAGE: artifact_size_stats
643
+**
644
+** Show information about the sizes of artifacts.
645
+*/
646
+void artifact_size_stats_page(void){
647
+ Stmt q;
648
+ int nTotal = 0;
649
+ int nDelta = 0;
650
+ int nFull = 0;
651
+ int med;
652
+ double r;
653
+ login_check_credentials();
654
+ if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
655
+ style_header("Artifact Sizes");
656
+ gather_artifact_stats(0);
657
+ @ <table class="label-value">
658
+ db_prepare(&q,
659
+ "SELECT count(*), sum(isDelta), avg(szCmpr), avg(szExp), max(szCmpr),"
660
+ " max(szExp)"
661
+ " FROM artstat"
662
+ );
663
+ if( db_step(&q)==SQLITE_ROW ){
664
+ nTotal = db_column_int(&q,0);
665
+ nDelta = db_column_int(&q,1);
666
+ nFull = nTotal - nDelta;
667
+ @ <tr><th>Number of artifacts:</th><td>%d(nTotal)</td></tr>
668
+ if( nTotal>0 ){
669
+ @ <tr><th>Number of deltas:</th>\
670
+ @ <td>%d(nDelta) (%d(nDelta*100/nTotal)%%)</td></tr>
671
+
672
+ @ <tr><th>Number of full-text:</th><td>%d(nFull) \
673
+ @ (%d(nFull*100/nTotal)%%)</td></tr>
674
+ }
675
+ @ <tr><th>Largest compressed artifact size:</th>\
676
+ @ <td>%d(db_column_int(&q,4))</td></tr>
677
+
678
+ @ <tr><th>Average compressed artifact size:</th> \
679
+ @ <td>%.2f(db_column_double(&q,2))</td></tr>
680
+
681
+ db_multi_exec("CREATE INDEX artstatx1 ON artstat(szCmpr, isDelta);");
682
+ med = db_int(0, "SELECT szCmpr FROM artstat ORDER BY szCmpr"
683
+ " LIMIT 1 OFFSET %d", nTotal/2);
684
+ @ <tr><th>Median compressed artifact size:</th><td>%d(med)</td></tr>
685
+
686
+ @ <tr><th>Largest uncompressed artifact size:</td>\
687
+ @ <td>%d(db_column_int(&q,5))</td></tr>
688
+
689
+ @ <tr><th>Average uncompressed artifact size:</th> \
690
+ @ <td>%.2f(db_column_double(&q,3))</td></tr>
691
+
692
+ med = db_int(0, "SELECT szExp FROM artstat ORDER BY szExp"
693
+ " LIMIT 1 OFFSET %d", nTotal/2);
694
+ @ <tr><th>Median uncompressed artifact size:</th><td>%d(med)</td></tr>
695
+
696
+ }
697
+ db_finalize(&q);
698
+ db_prepare(&q,
699
+ "SELECT avg(szCmpr), max(szCmpr) FROM artstat WHERE isDelta"
700
+ );
701
+ if( db_step(&q)==SQLITE_ROW ){
702
+ @ <tr><th>Largest delta:</td>\
703
+ @ <td>%d(db_column_int(&q,1))</td></tr>
704
+
705
+ @ <tr><th>Average delta:</th> \
706
+ @ <td>%.2f(db_column_double(&q,0))</td></tr>
707
+
708
+ med = db_int(0, "SELECT szCmpr FROM artstat WHERE isDelta ORDER BY szCmpr"
709
+ " LIMIT 1 OFFSET %d", nDelta/2);
710
+ @ <tr><th>Median delta:</th><td>%d(med)</td></tr>
711
+ }
712
+ db_finalize(&q);
713
+
714
+ r = db_double(0.0, "SELECT avg(szCmpr) FROM artstat WHERE NOT isDelta;");
715
+ @ <tr><th>Average full-text artifact:</th><td>%.2f(r)</td></tr>
716
+
717
+ med = db_int(0, "SELECT szCmpr FROM artstat WHERE NOT isDelta ORDER BY szCmpr"
718
+ " LIMIT 1 OFFSET %d", nFull/2);
719
+ @ <tr><th>Median full-text artifact:</th><td>%d(med)</td></tr>
720
+ @ </table>
721
+ if( nTotal>0 ){
722
+ sqlite3_int64 szTotal;
723
+ sqlite3_int64 szPart;
724
+ @ <h2>Artifact size distribution facts:</h2>
725
+ @ <ol>
726
+ szTotal = db_int64(0, "SELECT sum(szCmpr) FROM artstat");
727
+ szPart = db_int64(0,
728
+ "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
729
+ " LIMIT %d)", (nTotal+99)/100);
730
+ @ <li><p>The largest 1%% of artifacts (the largest %d((nTotal+99)/100) \
731
+ @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
732
+ szPart = db_int64(0,
733
+ "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
734
+ " LIMIT %d)", (nTotal+9)/10);
735
+ @ <li><p>The largest 10%% of artifacts (the largest %d((nTotal+9)/10) \
736
+ @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
737
+ szPart = db_int64(0,
738
+ "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
739
+ " LIMIT %d)", nTotal/4);
740
+ @ <li><p>The largest 25%% of artifacts (the largest %d(nTotal/4) \
741
+ @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
742
+ szPart = db_int64(0,
743
+ "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
744
+ " LIMIT %d)", nTotal/2);
745
+ @ <li><p>The largest 50%% of artifacts (the largest %d(nTotal/2) \
746
+ @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
747
+ @ </ol>
748
+ }
749
+ style_footer();
634750
 }
635751
636752
 /*
637753
 ** WEBPAGE: artifact_stats
638754
 **
@@ -641,11 +757,13 @@
641757
 void artifact_stats_page(void){
642758
 Stmt q;
643759
 login_check_credentials();
644760
 if( !g.perm.Admin ){ login_needed(g.anon.Admin); return; }
645761
 style_header("Artifact Statistics");
646
- gather_artifact_stats();
762
+ style_submenu_element("Repository Stats", "stat");
763
+ style_submenu_element("Artifact List", "bloblist");
764
+ gather_artifact_stats(1);
647765
 db_prepare(&q,
648766
 "SELECT atype, count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
649767
 " FROM artstat GROUP BY 1"
650768
 " UNION ALL "
651769
 "SELECT 'ALL', count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
652770
--- src/stat.c
+++ src/stat.c
@@ -576,11 +576,11 @@
576 }
577
578 /*
579 ** Gather statistics on artifact types, counts, and sizes.
580 */
581 static void gather_artifact_stats(void){
582 static const char zSql[] =
583 @ CREATE TEMP TABLE artstat(
584 @ id INTEGER PRIMARY KEY, -- Corresponds to BLOB.RID
585 @ atype TEXT, -- 'data', 'manifest', 'tag', 'wiki', etc.
586 @ isDelta BOOLEAN, -- true if stored as a delta
@@ -591,10 +591,12 @@
591 @ SELECT blob.rid, NULL,
592 @ EXISTS(SELECT 1 FROM delta WHERE delta.rid=blob.rid),
593 @ size, length(content)
594 @ FROM blob
595 @ WHERE content IS NOT NULL;
 
 
596 @ UPDATE artstat SET atype='file'
597 @ WHERE id IN (SELECT fid FROM mlink)
598 @ AND atype IS NULL;
599 @ UPDATE artstat SET atype='manifest'
600 @ WHERE id IN (SELECT objid FROM event WHERE type='ci') AND atype IS NULL;
@@ -629,10 +631,124 @@
629 @ WHERE atype IS NULL
630 @ AND id IN (SELECT objid FROM event WHERE type='g');
631 @ UPDATE artstat SET atype='unknown' WHERE atype IS NULL;
632 ;
633 db_multi_exec("%s", zSql/*safe-for-%s*/);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
634 }
635
636 /*
637 ** WEBPAGE: artifact_stats
638 **
@@ -641,11 +757,13 @@
641 void artifact_stats_page(void){
642 Stmt q;
643 login_check_credentials();
644 if( !g.perm.Admin ){ login_needed(g.anon.Admin); return; }
645 style_header("Artifact Statistics");
646 gather_artifact_stats();
 
 
647 db_prepare(&q,
648 "SELECT atype, count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
649 " FROM artstat GROUP BY 1"
650 " UNION ALL "
651 "SELECT 'ALL', count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
652
--- src/stat.c
+++ src/stat.c
@@ -576,11 +576,11 @@
576 }
577
578 /*
579 ** Gather statistics on artifact types, counts, and sizes.
580 */
581 static void gather_artifact_stats(int bWithTypes){
582 static const char zSql[] =
583 @ CREATE TEMP TABLE artstat(
584 @ id INTEGER PRIMARY KEY, -- Corresponds to BLOB.RID
585 @ atype TEXT, -- 'data', 'manifest', 'tag', 'wiki', etc.
586 @ isDelta BOOLEAN, -- true if stored as a delta
@@ -591,10 +591,12 @@
591 @ SELECT blob.rid, NULL,
592 @ EXISTS(SELECT 1 FROM delta WHERE delta.rid=blob.rid),
593 @ size, length(content)
594 @ FROM blob
595 @ WHERE content IS NOT NULL;
596 ;
597 static const char zSql2[] =
598 @ UPDATE artstat SET atype='file'
599 @ WHERE id IN (SELECT fid FROM mlink)
600 @ AND atype IS NULL;
601 @ UPDATE artstat SET atype='manifest'
602 @ WHERE id IN (SELECT objid FROM event WHERE type='ci') AND atype IS NULL;
@@ -629,10 +631,124 @@
631 @ WHERE atype IS NULL
632 @ AND id IN (SELECT objid FROM event WHERE type='g');
633 @ UPDATE artstat SET atype='unknown' WHERE atype IS NULL;
634 ;
635 db_multi_exec("%s", zSql/*safe-for-%s*/);
636 if( bWithTypes ){
637 db_multi_exec("%s", zSql2/*safe-for-%s*/);
638 }
639 }
640
641 /*
642 ** WEBPAGE: artifact_size_stats
643 **
644 ** Show information about the sizes of artifacts.
645 */
646 void artifact_size_stats_page(void){
647 Stmt q;
648 int nTotal = 0;
649 int nDelta = 0;
650 int nFull = 0;
651 int med;
652 double r;
653 login_check_credentials();
654 if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
655 style_header("Artifact Sizes");
656 gather_artifact_stats(0);
657 @ <table class="label-value">
658 db_prepare(&q,
659 "SELECT count(*), sum(isDelta), avg(szCmpr), avg(szExp), max(szCmpr),"
660 " max(szExp)"
661 " FROM artstat"
662 );
663 if( db_step(&q)==SQLITE_ROW ){
664 nTotal = db_column_int(&q,0);
665 nDelta = db_column_int(&q,1);
666 nFull = nTotal - nDelta;
667 @ <tr><th>Number of artifacts:</th><td>%d(nTotal)</td></tr>
668 if( nTotal>0 ){
669 @ <tr><th>Number of deltas:</th>\
670 @ <td>%d(nDelta) (%d(nDelta*100/nTotal)%%)</td></tr>
671
672 @ <tr><th>Number of full-text:</th><td>%d(nFull) \
673 @ (%d(nFull*100/nTotal)%%)</td></tr>
674 }
675 @ <tr><th>Largest compressed artifact size:</th>\
676 @ <td>%d(db_column_int(&q,4))</td></tr>
677
678 @ <tr><th>Average compressed artifact size:</th> \
679 @ <td>%.2f(db_column_double(&q,2))</td></tr>
680
681 db_multi_exec("CREATE INDEX artstatx1 ON artstat(szCmpr, isDelta);");
682 med = db_int(0, "SELECT szCmpr FROM artstat ORDER BY szCmpr"
683 " LIMIT 1 OFFSET %d", nTotal/2);
684 @ <tr><th>Median compressed artifact size:</th><td>%d(med)</td></tr>
685
686 @ <tr><th>Largest uncompressed artifact size:</td>\
687 @ <td>%d(db_column_int(&q,5))</td></tr>
688
689 @ <tr><th>Average uncompressed artifact size:</th> \
690 @ <td>%.2f(db_column_double(&q,3))</td></tr>
691
692 med = db_int(0, "SELECT szExp FROM artstat ORDER BY szExp"
693 " LIMIT 1 OFFSET %d", nTotal/2);
694 @ <tr><th>Median uncompressed artifact size:</th><td>%d(med)</td></tr>
695
696 }
697 db_finalize(&q);
698 db_prepare(&q,
699 "SELECT avg(szCmpr), max(szCmpr) FROM artstat WHERE isDelta"
700 );
701 if( db_step(&q)==SQLITE_ROW ){
702 @ <tr><th>Largest delta:</td>\
703 @ <td>%d(db_column_int(&q,1))</td></tr>
704
705 @ <tr><th>Average delta:</th> \
706 @ <td>%.2f(db_column_double(&q,0))</td></tr>
707
708 med = db_int(0, "SELECT szCmpr FROM artstat WHERE isDelta ORDER BY szCmpr"
709 " LIMIT 1 OFFSET %d", nDelta/2);
710 @ <tr><th>Median delta:</th><td>%d(med)</td></tr>
711 }
712 db_finalize(&q);
713
714 r = db_double(0.0, "SELECT avg(szCmpr) FROM artstat WHERE NOT isDelta;");
715 @ <tr><th>Average full-text artifact:</th><td>%.2f(r)</td></tr>
716
717 med = db_int(0, "SELECT szCmpr FROM artstat WHERE NOT isDelta ORDER BY szCmpr"
718 " LIMIT 1 OFFSET %d", nFull/2);
719 @ <tr><th>Median full-text artifact:</th><td>%d(med)</td></tr>
720 @ </table>
721 if( nTotal>0 ){
722 sqlite3_int64 szTotal;
723 sqlite3_int64 szPart;
724 @ <h2>Artifact size distribution facts:</h2>
725 @ <ol>
726 szTotal = db_int64(0, "SELECT sum(szCmpr) FROM artstat");
727 szPart = db_int64(0,
728 "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
729 " LIMIT %d)", (nTotal+99)/100);
730 @ <li><p>The largest 1%% of artifacts (the largest %d((nTotal+99)/100) \
731 @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
732 szPart = db_int64(0,
733 "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
734 " LIMIT %d)", (nTotal+9)/10);
735 @ <li><p>The largest 10%% of artifacts (the largest %d((nTotal+9)/10) \
736 @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
737 szPart = db_int64(0,
738 "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
739 " LIMIT %d)", nTotal/4);
740 @ <li><p>The largest 25%% of artifacts (the largest %d(nTotal/4) \
741 @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
742 szPart = db_int64(0,
743 "SELECT sum(x) FROM (SELECT szCmpr AS x FROM artstat ORDER BY 1 DESC"
744 " LIMIT %d)", nTotal/2);
745 @ <li><p>The largest 50%% of artifacts (the largest %d(nTotal/2) \
746 @ artifacts) use %lld(szPart*100/szTotal)%% of the total artifact space.
747 @ </ol>
748 }
749 style_footer();
750 }
751
752 /*
753 ** WEBPAGE: artifact_stats
754 **
@@ -641,11 +757,13 @@
757 void artifact_stats_page(void){
758 Stmt q;
759 login_check_credentials();
760 if( !g.perm.Admin ){ login_needed(g.anon.Admin); return; }
761 style_header("Artifact Statistics");
762 style_submenu_element("Repository Stats", "stat");
763 style_submenu_element("Artifact List", "bloblist");
764 gather_artifact_stats(1);
765 db_prepare(&q,
766 "SELECT atype, count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
767 " FROM artstat GROUP BY 1"
768 " UNION ALL "
769 "SELECT 'ALL', count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
770

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button