Gnuplot супротив 2MASS

    image
    Данная статья повествует о пользе низкоуровневых вычислений на примере атласа звездных объектов 2MASS.
    2MASS — это ~471 млн. объектов, для которых приведены координаты, а также сопутствующая информация, всего 60 атрибутов.
    Физически — это 50Гб исходных гзипнутых текстовых файлов.
    Можно ли работать с такой базой, не прибегая к «тяжелой артиллерии»?
    Давайте попробуем.

    Прелюдия


    Данная работа проводилась в 2009 году и была частью усилий компании DataEast по созданию собственной ГеоСУБД.
    Ставилась цель отладить, отпрофилировать и сделать benchmark процессора запросов.
    В качестве языка запросов был выбран диалект Turorial D, точнее его часть, касающаяся реляционной алгебры. Диалект был адаптирован под требования реальной жизни, в частности, добавлена возможность работы с курсорами.

    Впрочем, это просто контекст и цель данного поста не рассказ о былых победах, что «растаяли, как слёзы под дождем», а демонстрация техники максимального переноса вычислений непосредственно в курсор СУБД.
    Конечно, применить данную технику можно и средствами PL/SQL при помощи хранимых процедур и/или используя агрегаты.

    Введение


    Нет смысла подробно останавливаться на процессе подготовки данных, скажем просто, что заняло это 8ч 26мин на заливку данных и 1ч 17мин на пространственную индексацию.
    В комплекте с исходными данными идет проверочный запрос
    select count(*) as rows,
                sum(pts_key::bigint) as sum_pts_key,
                sum(pxcntr::bigint) as sum_pxcntr,
                sum(scan_key::bigint) as sum_scan_key,
                sum(scan::bigint) as sum_scan,
                sum(ext_key::bigint) as sum_ext_key,
                sum(coadd_key::bigint) as sum_coadd_key,
                sum(coadd::bigint) as sum_coadd,
                sum(mp_flg) as sum_mp_flg,
                sum(gal_contam) as sum_gal_contam,
                sum(use_src) as sum_use_src,
                sum(dup_src) as sum_dup_src,
                sum(nopt_mchs) as sum_nopt_mchs,
                sum(phi_opt::bigint) as sum_phi_opt,
                sum(dist_edge_ew::bigint) as sum_dist_edge_ew,
                sum(dist_edge_ns::bigint) as sum_dist_edge_ns,
                sum(err_ang::bigint) as sum_err_ang
                FROM twomass_psc;
    
    В нашем случае этот запрос будет выглядеть как
    select "PSC"
      chew {
    	"rows" INTEGER, 
    	"sum_pts_key" INTEGER,
    	"sum_pxcntr" INTEGER, 
    	"sum_scan_key" INTEGER,
    	"sum_scan" INTEGER,
    	"sum_ext_key" INTEGER, 
    	"sum_coadd_key" INTEGER, 
    	"sum_coadd" INTEGER, 
    	"sum_mp_flg" INTEGER, 
    	"sum_gal_contam" INTEGER, 
    	"sum_use_src" INTEGER, 
    	"sum_dup_src "INTEGER, 
    	"sum_nopt_mchs" INTEGER, 
    	"sum_phi_opt" INTEGER,
    	"sum_dist_edge_ew" INTEGER, 
    	"sum_dist_edge_ns" INTEGER,
    	"sum_err_ang" INTEGER
      } 
      hook "init" {
       "rows" := 0;
       "sum_pts_key" := 0;
    ...
       "sum_err_ang" := 0;
      }
      hook "row" {
       "rows" := "rows" + 1;
       if (not isnull("pts_key/cntr")) then
         "sum_pts_key" := "sum_pts_key" + "pts_key/cntr";
       end if;
    ...
       if (not isnull("err_ang")) then
         "sum_err_ang" := "sum_err_ang" + "err_ang";
       end if;
      }
      hook "finit" {
       call __interrupt ();
      };
    
    Результат выполнения таков:
    INFO: start [04/Jun/2009:09:37:45:822]
    -----------------------------------------------------------------
    rows			sum_pts_key		sum_pxcntr	
    sum_scan_key		sum_scan		sum_ext_key	
    sum_coadd_key		sum_coadd		sum_mp_flg	
    sum_gal_contam		sum_use_src		sum_dup_src	
    sum_nopt_mchs		sum_phi_opt		sum_dist_edge_ew
    sum_dist_edge_ns	sum_err_ang	
    -----------------------------------------------------------------
    470992970		306810325437475788	306815556538478936	
    16902776758555		32666066948		2048692118201	
    388758631396659		64617139213		16048	
    729878			464456155		79798372	
    369187043		64916239773		69388217174	
    2670725813652		29279563815	
    -----------------------------------------------------------------
    INFO: stop [04/Jun/2009:12:35:59:571]
    
    Здесь оператор chew использован для преобразования исходного потока данных (вся таблица) в синтетический с определенными полями, которые становятся локальными переменными. Оператор hook использован (аналогично агрегатам SQL) для подписки на события — старт/стоп потока и получение строки. При вызове __interrupt текущие значения локальных переменных попадают в resultset.

    Результаты совпадают с проверочными, значит данные подготовлены к работе.
    Время работы — 3 часа, около 10 мин на колонку при сплошном просмотре. Такая скорость (на Intel Core2 P4-2.4 GHz, 2Gb, disk ST3400620AS) характерна для всех дальнейших запросов. С другой стороны, если бы мы распаковывали исходные текстовые файлы и обрабатывали их через GAWK, например, у нас бы ушло 50Гб по 50 мб/сек = 1000 секунд на чтение данных и столько же на распаковку, 35 минут только лишь на организацию потока. На распаковку и прогон всех данных через wc уходило полтора часа.

    Для желающих увидеть всё своими глазами, приводится кусочек неба с дефектом пластины:
    image
    Кое-кто принимает его за боевые порядки имперского флота.

    Data mining


    Простая статистика. Max, Min, Avg — поиграемся с колонкой «j-m» (Default J-band magnitude).
    select "PSC"
      chew {
    	"nrows" INTEGER, 
    	"nulls_j_m" INTEGER, 
    	"num_j_m" INTEGER, 
    	"sum_j_m" RATIONAL,
    	"avg_j_m" RATIONAL,
    	"max_j_m" RATIONAL,
    	"min_j_m" RATIONAL
      } 
      hook "init" {
       "nrows" := 0;
       "nulls_j_m" := 0; 
       "num_j_m" := 0;
       "sum_j_m" := 0;
       "avg_j_m" := 0;
       "max_j_m" := -1e66;
       "min_j_m" := 1e66;
      }
      hook "row" {
       nrows := nrows + 1;
       if (isnull("j_m")) then
         "nulls_j_m" := "nulls_j_m" + 1;
       else
         "num_j_m" := "num_j_m" + 1;
         "sum_j_m" := "sum_j_m" + "j_m";
         if ("max_j_m"<"j_m") then
           "max_j_m" := "j_m";
         end if;
         if ("min_j_m">"j_m") then
           "min_j_m" := "j_m";
         end if;
       end if;
      }
      hook "finit" {
       "avg_j_m" := "sum_j_m" / "num_j_m";
       call __interrupt ();
      };
    

    Результат:
    INFO: start [04/Jun/2009:15:00:54:821] 
    --------------------------------------------------------------------
    nrows		nulls_j_m		num_j_m	
    sum_j_m		avg_j_m		max_j_m		min_j_m	
    --------------------------------------------------------------------
    470992970	19			470992951	
    7.15875e+009	15.1993		25.86		-2.989	
    --------------------------------------------------------------------
    INFO: stop [04/Jun/2009:15:18:47:743]
    

    Дисперсия:
    select "PSC"
      chew {
    	"nrows" INTEGER, 
    	"nulls_j_m" INTEGER, 
    	"num_j_m" INTEGER, 
    	"disp_j_m" RATIONAL
      } 
      hook "init" {
       "nrows" := 0;
       "nulls_j_m" := 0; 
       "num_j_m" := 0;
       "disp_j_m" := 0;
       var tmp rational;
       var tavg rational;
       tavg := 15.1993; -- previously calculated
      }
      hook "row" {
       nrows := nrows + 1;
    
       if (isnull("j_m")) then
         "nulls_j_m" := "nulls_j_m" + 1;
       else
         "num_j_m" := "num_j_m" + 1;
         tmp := "j_m" - tavg;
         tmp := tmp * tmp;
         "disp_j_m" := "disp_j_m" + tmp;
       end if;
      }
      hook "finit" {
       "disp_j_m" := "disp_j_m" / "num_j_m";
       call __interrupt ();
      };
    

    Результат:
    INFO: start [04/Jun/2009:15:48:27:274] 
    --------------------------------------------------------------------
    nrows	nulls_j_m	num_j_m	disp_j_m	
    --------------------------------------------------------------------
    470992970	19	470992951	1.98223	
    --------------------------------------------------------------------
    INFO: stop [04/Jun/2009:16:05:01:946
    

    Гистограмма:
    select "PSC"
      chew {
    	"j_m_cell" rational, 
    	"num" INTEGER
      } 
      hook "init" {
       var tmp integer;
       var tmp2 integer;
    
       var tmax rational;
       tmax := 26.;  -- previously calculated
       var tmin rational;
       tmin := -5.; -- previously calculated
       var ncells integer;
       ncells := 500;
       var tcell rational;
       tcell := (tmax - tmin)/ncells;
    
       var htptr integer;
       htptr := hti_alloc (0);
       call hti_set (htptr, 0, 0);
      }
      hook "row" {
       if (not isnull("j_m")) then
         tmp := ("j_m" - tmin)/tcell;
         tmp2 := hti_get (htptr, tmp);
         tmp2 := tmp2 + 1;
         call hti_set (htptr, tmp, tmp2);
       end if;
      }
      hook "finit" {
       tmp := 0;
       while tmp < ncells;
         begin;
         "j_m_cell" := tmin + tcell * tmp + tcell * 0.5;
         "num" := hti_get (htptr, tmp);
         tmp := tmp + 1;
         call __interrupt ();
         end;
       end while;
       call hti_free (htptr);
      };
    
    Накопление в ячейках гистограммы происходит с помощью хэш таблицы за отсутствием массивов в языке. В данном случае результатом будет распечатка ячеек гистограммы, которую удобно смотреть через gnuplot.В нижеследующей картинке добавлены также распечатки по колонкам «k-m»(Default Ks-band magnitude) и «h-m»(Default H-band magnitude).

    image
    3D
    Хорошо, «k-m» и «h-m» на гистограмме выглядят очень похоже, а как насчет совместного распределения?
    select "PSC"
      chew {
    	"cell" rational,
    	"num" INTEGER
      } 
      hook "init" {
       var tmp integer;
       var tmp2 integer;
       var tmp4 integer;
    
       var tmax rational;
       tmax := 25;
       var tmin rational;
       tmin := -5.;
       var ncells integer;
       ncells := 512;
       var tcell rational;
       tcell := (tmax - tmin)/ncells;
       var "hnd" integer;
       hnd:= img_create (
    	ncells, 
    	ncells,
    	tmin, tmin, tmax, tmax, 1);
       var cl  integer;
       cl := img_alloc_color (hnd, 255,0,0);
      }
      hook "row" {
       if ((not isnull("h_m")) and (not isnull("k_m"))) then
         tmp := img_get_point (hnd, "h_m", "k_m");
         tmp := tmp + 1;
         call img_draw_point (hnd, "h_m", "k_m", tmp);
       end if;
      }
      hook "finit" {
       tmp := 0;
       while tmp < ncells;
         begin;
         tmp2 := 0;
         while tmp2 < ncells;
           begin;
           tmp4 := img_get_point (hnd, tmin + tcell * tmp, 
                tmin + tcell * tmp2);
           call print ((tmin + tmp * tcell)+' '+
             (tmin + tmp2* tcell)+' '+tmp4+'\n');
           if tmp4 > 0 then
             tmp4 := cl;
           else
             tmp4 := 0;
           end if;
           call img_draw_point (hnd, tmin + tcell * tmp, 
             tmin + tcell * tmp2, cl);
           tmp2 := tmp2 + 1;
           end;
         end while;
         tmp := tmp + 1;
         end;
       end while;
       call img_saveas (hnd, 'stat4.gif');
       call img_destroy (hnd);
      };
    
    В данном примере для хранения двумерной гистограммы использовался образ картинки, полученный посредством плагина к GD, просмотр логарифмов попаданий в клетки осуществляется опять же средствами gnuplot в режиме pm3d.
    image
    Аналогичная картинка для распределения “j_m“-“k_m“ против “h_m“-“k_m“:
    image
    Построение этих гистограмм занимает ~35мин — по 12 мин на колонку.
    А что, если нам надо построить такое распределение по конкретной пространственной области? Можем ли мы сделать это за секунды? Конечно:
    Выборка с пространственными ограничениями.
    select "PSC"
    where 
      	sp_overlap ("Point", 
      	  [rational:xmin], 
       	  [rational:ymin], 
       	  [rational:xmax], 
       	  [rational:ymax]) 
      chew {
    	"m_cell" rational, 
    	"num" INTEGER
      }    
    … -- below is the same text
    

    Добавление sp_overlap изменило исходный поток идентификаторов от таблицы к пространственному индексу.
    image
    В нижних левых углах клеток указаны экстенты выборок.

    А как насчет атрибутивных ограничений?
    Это так же легко, как и ограничить по пространству.
    select "PSC"
    where “j_snr” < 10. 
    -- or you may combine your restrictions adding 
    -- and sp_overlap ("Point", … ) 
      chew {
    	"m_cell" rational, 
    	"num" INTEGER
      }   
    … -- below is the same text
    
    А вот и результат.
    image

    Итого


    Что дает нам перенос вычислений в курсор?
    • скорость, обрабатывая прочитанные данные на месте, мы свободны от того, чтобы их сериализовывать, отправлять по сети, принимать, десериализовывать...
    • сохраненные ресурсы, например, полмиллиарда строк, будучи отправленными по сети, надолго займут изрядную долю ее пропускной способности

    А чем нам грозит такая техника?
    • Придется всё делать самим, а это потенциальный источник ошибок.
    • Алгоритмы, в которых действительно много вычислений не очень удобно выполнять на общем сервере.
    • Не все вычисления можно делать в потоке.

    Всё так, но автор не утверждает, что так надо делать всегда, это лишь один из многих инструментов для работы с данными, пусть и удобный, особенно для исследовательской части работы, например, когда надо быстро проверять и модифицировать гипотезы.
    Метки:
    • +11
    • 7,5k
    • 7
    Поделиться публикацией
    Похожие публикации
    Комментарии 7
    • –4
      Извините, но где здесь BigData?
      Покупаем сервак с десятками ядер и сотнями гигабайт RAM, создаем виртуальный диск в оперативной памяти, распараллеливаем все запросы. Вот было бы 100 ТБ данных — тогда да
      • +2
        Попробуйте создать пространственный индекс на полмиллиарда объектов.
        • +1
          Без сервака с десятками ядер и сотнями Гб RAM
          • –3
            Мыло уместно в бане. Используется обычный комп, если не нравится скорость работы — покупается сервак. А вот если на серваке нельзя выполнить расчеты или это занимает слишком много времени — тогда это BigData и нужен кластер.
            • +3
              А у меня другой подход, мне интересно выжать из железа максимум.
              Если я могу решить такую задачу на завалящем десктопе, то и на серваке раздвигаются границы допустимого.
              • 0
                Подход хороший, просто BigData — это другое
                • +1
                  Ну, в 2009 году эти data были достаточно big.

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.