Changes between Version 6 and Version 7 of Raw_Images_Files_in_Nebulous

Timestamp:

Feb 29, 2012, 12:50:24 PM (14 years ago)

Author:

Serge CHASTEL

Comment:

--

Raw_Images_Files_in_Nebulous

@@ -12 +12 @@
 Note: 60 nodes are hosting the data (namely: ipp006, ipp007, ipp008, ipp009, ipp010, ipp011, ipp012, ipp013, ipp014, ipp015, ipp016, ipp017, ipp018, ipp019, ipp020, ipp021, ipp023, ipp024, ipp025, ipp026, ipp027, ipp028, ipp029, ipp030, ipp031, ipp032, ipp033, ipp034, ipp035, ipp036, ipp037, ipp038, ipp039, ipp040, ipp041, ipp042, ipp043, ipp044, ipp045, ipp046, ipp047, ipp048, ipp049, ipp050, ipp051, ipp052, ipp053, ipp054, ipp055, ipp056, ipp057, ipp058, ipp059, ipp060, ipp061, ipp062, ipp063, ipp064, ipp065, ipp066)
 
-On the ippc11 mysql server, the FilesStatistics database was created:
+On the ippc11 mysql server, the {{{FilesStatistics}}} database was created:
 {{{
 CREATE DATABASE FilesStatistics; 
@@ -33 +33 @@
    {{{SELECT host, SUM(how_many)-580145 AS adiff, (SUM(how_many)-580145)/580145*100 AS rdiff FROM RawImages GROUP BY host;}}}
  * View without the directory information: {{{CREATE VIEW RawImagesSimple AS (SELECT host, ota, SUM(how_many) AS ota_count FROM RawImages GROUP BY host, ota);}}}
+
+=== Maxima in terms of ota counts ===
  * To get the maximum in terms of ota counts for each host (we also display the argmax), run the following statement:
 {{{
@@ -38 +40 @@
 }}}
    * Two otas are not displayed: ota23 and ota35. This means that no node has a majority of ota23 and ota35. However there are 301127 ota23 on ipp015 (while the maximum on this host is reached for ota43 with 305879) and 308061 ota35 on ipp013 (Max on this host for ota20: 310086). The absolute difference between the maximum ota count and the ota count for ota23 and ota35 is however acceptable (~4,000 and ~2,000) if it is compared to the total number of otas (~300,000).
-   * Some nodes have a relative small number of exposures: ipp026 (63678), ipp045(13804), ipp064(8736), ipp066(95218)
+   * Some nodes have a relative small number of exposures: ipp026 (63678), ipp045(13804), ipp064(8736), ipp066(95218). We know those nodes had problems in the past and can exclude them from our study. We will refer to them as "outlier nodes".
    * Two otas are shown twice: ota17 (ipp060 and ipp026) and ota34 (ipp023 and ipp045) but for each of them one of the nodes has a small number of exposures. Nothing to worry about here too.
-   * The table containing the maxima by ota is {{{RawImagesMaximaByOta}}}
+   * Without the outlier nodes (ipp026, ipp045, ipp064, and ipp066):
+     * Mean max count: 274682
+     * Stdev max count: 41327
+     * Max of max count: 376716
+     * Min of max count: 201702
+     * Hosts with abs(max count - mean > 3*stdev): 0
+     * Hosts with abs(max count - mean > 2*stdev): 1 => ipp033 (+102034)
+     * Hosts with abs(max count - mean > 1*stdev): 12 (i.e. 5 nodes with max count greater than mean+sigma; 7 with max count less than mean-sigma)
+     * Queries derived from {{{SELECT RI1.host, RI1.ota, max_ota_count-274682 AS delta FROM RawImagesSimple RI1 JOIN (SELECT MAX(RI2.ota_count) AS max_ota_count, host FROM RawImagesSimple RI2 WHERE host != 'ipp026' AND host != 'ipp045' AND host != 'ipp064' AND host != 'ipp066' GROUP BY host) RI3 ON RI3.max_ota_count = RI1.ota_count AND RI3.host=RI1.host ORDER BY delta DESC;}}}
+
+   * A table containing the maxima by ota was created: {{{RawImagesMaximaByOta}}}
+{{{
+CREATE TABLE RawImagesMaximaByOta (id INT PRIMARY KEY AUTO_INCREMENT, host CHAR(7), INDEX(host), ota CHAR(6), INDEX(ota), max_ota_count INT, INDEX(max_ota_count));
+INSERT INTO RawImagesMaximaByOta SELECT NULL, RI1.host, RI1.ota, max_ota_count FROM RawImagesSimple RI1 JOIN (SELECT MAX(RI2.ota_count) AS max_ota_count, host FROM RawImagesSimple RI2 WHERE host != 'ipp026' AND host != 'ipp045' AND host != 'ipp064' AND host != 'ipp066' GROUP BY host) RI3 ON RI3.max_ota_count = RI1.ota_count AND RI3.host=RI1.host;
+}}}
+
+=== Are OTAs which are not supposed to be targeted on one host evenly spread? ===
+ * Table based on RawImagesSimple without the maximum ota counts is named: {{{RawImagesWithoutMaximumOta}}}
+{{{
+CREATE TABLE RawImagesWithoutMaximumOta (host CHAR(7), INDEX(host), ota CHAR(6), INDEX(ota), ota_count INT, INDEX(ota_count));
+INSERT INTO RawImagesWithoutMaximumOta SELECT host, ota, ota_count FROM RawImagesSimple WHERE (SELECT COUNT(*) FROM RawImagesMaximaByOta WHERE RawImagesMaximaByOta.host = RawImagesSimple.host AND RawImagesMaximaByOta.ota = RawImagesSimple.ota) = 0 AND host != 'ipp026' AND host != 'ipp045' AND host != 'ipp064' AND host != 'ipp066';
+}}}
+
+ * Remove the outliers from the {{{RawImagesWithoutMaximumOta}}} table:
+{{{
+CREATE TABLE RawImagesWithoutMaximumOtaNoOutliers (host CHAR(7), INDEX(host), ota CHAR(6), INDEX(ota), ota_count INT, INDEX(ota_count));
+INSERT INTO RawImagesWithoutMaximumOtaNoOutliers SELECT * FROM RawImagesWithoutMaximumOta;
+DELETE FROM RawImagesWithoutMaximumOtaNoOutliers WHERE ota_count>300000;  -- (Those are the "missing but there" ota23 and ota35)
+}}}
+
+ * Now to get the histogram of the values in RawImagesWithoutMaximumOta: 
+{{{
+SELECT ROUND(ota_count, -4) AS bucket, COUNT(*), RPAD('', LN(COUNT(*)), '*') AS bar FROM RawImagesWithoutMaximumOtaNoOutliers GROUP  BY bucket;
+
+!!!!!! NOTE THE USE OF THE LOGARITHMIC SCALE FOR THE '*' !!!!!!!
+
++--------+----------+----------+
+| bucket | COUNT(*) | bar      |
++--------+----------+----------+               i.e.:
+|      0 |     2349 | ******** |      2349 different otas have a count 0 to 10000 on one of the nodes
+|  10000 |      819 | *******  |           
+|  20000 |       10 | **       | 
+|  30000 |       63 | ****     |        
+|  40000 |       12 | **       | 
+|  50000 |       10 | **       | 
+|  60000 |        6 | **       | 
+|  70000 |        7 | **       | 
+|  80000 |        8 | **       | 
+|  90000 |        1 |          | 
+| 100000 |        6 | **       | 
+| 110000 |        5 | **       | 
+| 130000 |        1 |          | 
+| 140000 |        3 | *        | 
+| 180000 |        1 |          | 
+| 190000 |        1 |          | 
++--------+----------+----------+
+}}}
+
+Detail of the first part of the histogram: 
+{{{
+SELECT ROUND(ota_count, -2) AS bucket, COUNT(*), RPAD('', (COUNT(*)/10), '*') AS bar FROM RawImagesWithoutMaximumOtaNoOutliers GROUP  BY bucket LIMIT 100;
+
+!!! NO LOG SCALE FOR THIS ONE BUT A SCALING BY 1/10 !!!
+
++--------+----------+-----------------+
+| bucket | COUNT(*) | bar             |
++--------+----------+-----------------+
+|    100 |        1 |                 | 
+|    200 |       16 | **              | 
+|    300 |       12 | *               | 
+|    400 |       13 | *               | 
+|    500 |       18 | **              | 
+|    600 |       39 | ****            | 
+|    700 |       50 | *****           | 
+|    800 |       32 | ***             | 
+|    900 |       42 | ****            | 
+|   1000 |       32 | ***             | 
+|   1100 |       48 | *****           | 
+|   1200 |       56 | ******          | 
+|   1300 |       48 | *****           | 
+|   1400 |       34 | ***             | 
+|   1500 |       38 | ****            | 
+|   1600 |       45 | *****           | 
+|   1700 |       42 | ****            | 
+|   1800 |       72 | *******         | 
+|   1900 |      107 | ***********     | 
+|   2000 |      130 | *************   | 
+|   2100 |      152 | *************** | 
+|   2200 |      134 | *************   | 
+|   2300 |      131 | *************   | 
+|   2400 |       87 | *********       | 
+|   2500 |       62 | ******          | 
+|   2600 |       68 | *******         | 
+|   2700 |       58 | ******          | 
+|   2800 |       48 | *****           | 
+|   2900 |       58 | ******          | 
+|   3000 |       56 | ******          | 
+|   3100 |       40 | ****            | 
+|   3200 |       59 | ******          | 
+|   3300 |       41 | ****            | 
+|   3400 |       43 | ****            | 
+|   3500 |       46 | *****           | 
+|   3600 |       34 | ***             | 
+|   3700 |       36 | ****            | 
+|   3800 |       32 | ***             | 
+|   3900 |       30 | ***             | 
+|   4000 |       29 | ***             | 
+|   4100 |       27 | ***             | 
+|   4200 |       23 | **              | 
+|   4300 |       37 | ****            | 
+|   4400 |       21 | **              | 
+|   4500 |       25 | ***             | 
+|   4600 |       17 | **              | 
+|   4700 |       26 | ***             | 
+|   4800 |       27 | ***             | 
+|   4900 |       19 | **              | 
+|   5000 |       18 | **              | 
+|   5100 |       22 | **              | 
+|   5200 |       21 | **              | 
+|   5300 |       31 | ***             | 
+|   5400 |       18 | **              | 
+|   5500 |       17 | **              | 
+|   5600 |       29 | ***             | 
+|   5700 |       21 | **              | 
+|   5800 |       28 | ***             | 
+|   5900 |       33 | ***             | 
+|   6000 |       32 | ***             | 
+|   6100 |       24 | **              | 
+|   6200 |       20 | **              | 
+|   6300 |       27 | ***             | 
+|   6400 |       24 | **              | 
+|   6500 |       16 | **              | 
+|   6600 |       14 | *               | 
+|   6700 |       21 | **              | 
+|   6800 |       18 | **              | 
+|   6900 |       25 | ***             | 
+|   7000 |       12 | *               | 
+|   7100 |       12 | *               | 
+|   7200 |       20 | **              | 
+|   7300 |       11 | *               | 
+|   7400 |       19 | **              | 
+|   7500 |       14 | *               | 
+|   7600 |       22 | **              | 
+|   7700 |       21 | **              | 
+|   7800 |       12 | *               | 
+|   7900 |       14 | *               | 
+|   8000 |       16 | **              | 
+|   8100 |        7 | *               | 
+|   8200 |        5 | *               | 
+|   8300 |       12 | *               | 
+|   8400 |       11 | *               | 
+|   8500 |       10 | *               | 
+|   8600 |        3 |                 | 
+|   8700 |       15 | **              | 
+|   8800 |        8 | *               | 
+|   8900 |        7 | *               | 
+|   9000 |        8 | *               | 
+|   9100 |       10 | *               | 
+|   9200 |        5 | *               | 
+|   9300 |        7 | *               | 
+|   9400 |        5 | *               | 
+|   9500 |        4 |                 | 
+|   9600 |        5 | *               | 
+|   9700 |        4 |                 | 
+|   9800 |        5 | *               | 
+|  10000 |        6 | *               | 
+|  10100 |        5 | *               | 
++--------+----------+-----------------+
+}}}