Raw Images Files in Nebulous

Shuffling Analysis

(Unless noted all files are referenced from /export/ippc11.1/schastel/FilesStatistics/slocate on host ippc11)

On week 5 (i.e. around 1/30/2012-2/3/2012), the slocate utility was ran on the different cluster nodes hosting data. Those databases can be found in slocate_entries.

From those databases, the number of raw images files for gpc1 were extracted for each host, for each ota, and for each nebulous first-level directory (that is, one of the 256 directories from /export/<hostname>.0/nebulous/00, /export/<hostname>.0/nebulous/01, ..., /export/<hostname>.0/nebulous/ff). The script slocate_statistics_multiple.py has been used to create the files in slocate_data (the script can be executed: slocate_statistics_multiple.py <hostname_1> ... <hostname_n> and creates the files slocate_data/<hostname_1>...)

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:

CREATE DATABASE FilesStatistics; 
USE FilesStatistics;
CREATE TABLE RawImages (id INT PRIMARY KEY AUTO_INCREMENT, 
                        host CHAR(7), INDEX(host), 
                        directory CHAR(3), INDEX(directory), 
                        ota CHAR(6), INDEX(ota), 
                        how_many INT, INDEX(how_many));

It has been populated with the various files from slocate_data.

cat slocate_data/* | sed 's/^/INSERT INTO RawImages(host, directory, ota, how_many) VALUES /g' | sed 's/$/;/g' > populate.sql
cat populate_db.sql | mysql -u ippuser -pipp FilesStatistics

• Average number of ota files by host: SELECT SUM(how_many)/60 FROM RawImages;: 580145
• Difference (absolute and relative) between that average number and the current number on one host: 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:

  SELECT RI1.host, RI1.ota, max_ota_count FROM RawImagesSimple RI1 JOIN (SELECT MAX(RI2.ota_count) AS max_ota_count, host FROM RawImagesSimple RI2 GROUP BY host) RI3 ON RI3.max_ota_count = RI1.ota_count AND RI3.host=RI1.host ORDER BY RI1.ota;
  

  • 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). 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.
  • 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 an idea on how the ota are spread on the different hosts, let's display the histogram of the count_ota values in RawImagesWithoutMaximumOta.

First with a (huge) bucket size of 100,000 (Note the log scale for the y axis):

SELECT ROUND(ota_count, -4) AS bucket, COUNT(*) FROM RawImagesWithoutMaximumOtaNoOutliers GROUP BY bucket;

We are interested in the detail of the first part of the histogram. With a bucket size of 100:

SELECT ROUND(ota_count, -2) AS bucket, COUNT(*) FROM RawImagesWithoutMaximumOtaNoOutliers GROUP BY bucket LIMIT 150;

Conclusion: Are we good or bad?

We had something like 420,000 exposures (from pzDownloadImfile for gpc1, with fault = 0). Ideally, at mhpcc, we should have only one ota on one node, therefore the expected ota count on each node should be precisely 420,000 (the actual mean being 274682, the maximum 376716, the minimum 201702). On average, we have a difference of (420,000 - 274,682) roughly 150,000 otas / node. If they were uniformly spread on the nodes, we would have something like 2500 exposures/node (2500 is roughly 150,000 otas spread on 59 nodes). The last histogram shows a peak around 2500 which is precisely what we expect.

Note that the study was lead without the use of nebulous. It will be interesting to see if nebulous can give the same results (if the result are the same, it would indirectly validate that nebulous keeps track of the files; if not, it would mean that there is a problem...).

To see if shuffling works, we have to repeat the same experiment in the future and see if that number of 2500 gets smaller. It would mean that the files are moving towards where they should be that is, all the files related to one ota on the same node.

The answer to the question is: We are not that bad.

Shuffling Analysis (Continued)

Raw OTAs Count per Node

The data generation code is in /export/ippc11.1/schastel/FilesStatistics/otas/ota_by_host. Run:

1. populate_table_OtaByHost.sh from this directory on ippc11
2. SELECT * FROM OtaByHost INTO OUTFILE '/tmp/otaByHost.data'; in a mysql client on the FilesStatistics database
3. generate_gnuplot_script.py and copy/paste its output into a gnuplot session (the 'otaByHost.data' has to be in the directory where the gnuplot session is executed)

The product is the following image:

Storage Comparison with the Available Space

Gene's questions (from e-mail sent on Thu, 1 Mar 2012 06:41:22).

• how does this storage compare with the available space (eg, note the min & max numbers of OTAs per host, 200k & 376k, correspond to a total storage usage of 5.0T & 9.4T, so it matters somewhat which host is close to well-balanced and which is not)
• what is our distribution of raw vs short-term vs long-term ('non-ephemeral') storage as a function of node?

Using Chris' data generated and the "Raw/Permanent/Ephemeral" definitions given in the Cluster Storage Nodes page, I created the following plots: