[Met_help] [rt.rap.ucar.edu #77533] History for Very slow database access (mv_batch)

Wed Jul 10 16:43:25 MDT 2019

----------------------------------------------------------------
  Initial Request
----------------------------------------------------------------

Hi MET help:

I have been attempting to generate some verification stats that encompass
about 4 months of forecasts (4 cycles per day, indep variable is lead time
and it is hourly).  I've noticed that when running the batch engine to make
these plots that it's going very slowly.  For example, I'm attempting to
make 4 plots at the moment and started the script at 18:56:33 UTC.  It is
was 19:44:32 UTC, nearly an hour later, when I stopped the script since no
plots had been generated.

Anyway, I've run into this issue quite a bit over the last 24 hours.  Is
this associated with trying to pull too many records from the database?
Yesterday I did leave the script overnight and it had completed by morning,
so it did work eventually.

Thanks!
Jacob

----------------------------------------------------------------
  Complete Ticket History
----------------------------------------------------------------

Subject: Very slow database access (mv_batch)
From: Tatiana Burek
Time: Wed Aug 17 11:21:55 2016

Jacob,

Could you please direct me to the XML file you are using for the batch
mode.
It will help me to understand the problem.

Tatiana

On Thu Aug 11 13:51:42 2016, jacob.carley at noaa.gov wrote:
> Hi MET help:
>
> I have been attempting to generate some verification stats that
encompass
> about 4 months of forecasts (4 cycles per day, indep variable is
lead time
> and it is hourly).  I've noticed that when running the batch engine
to make
> these plots that it's going very slowly.  For example, I'm
attempting to
> make 4 plots at the moment and started the script at 18:56:33 UTC.
It is
> was 19:44:32 UTC, nearly an hour later, when I stopped the script
since no
> plots had been generated.
>
> Anyway, I've run into this issue quite a bit over the last 24 hours.
Is
> this associated with trying to pull too many records from the
database?
> Yesterday I did leave the script overnight and it had completed by
morning,
> so it did work eventually.
>
> Thanks!
> Jacob

------------------------------------------------
Subject: Very slow database access (mv_batch)
From: Jacob Carley - NOAA Affiliate
Time: Wed Aug 17 11:29:36 2016

Hi Tatiana,

Sure thing.  Here is the path to one such xml file:

/metviewer/staging/wd20jc/scripts/mv_batch_surface_for_tatiana.xml

This can take 10 mins to an hour to run.  The system's timing seems to
be a
bit fickle.

I can also attach and send the xml if you would prefer that.

Thanks!
Jacob

On Wed, Aug 17, 2016 at 1:21 PM, Tatiana Burek via RT
<met_help at ucar.edu>
wrote:

> Jacob,
>
> Could you please direct me to the XML file you are using for the
batch
> mode.
> It will help me to understand the problem.
>
> Tatiana
>
> On Thu Aug 11 13:51:42 2016, jacob.carley at noaa.gov wrote:
> > Hi MET help:
> >
> > I have been attempting to generate some verification stats that
encompass
> > about 4 months of forecasts (4 cycles per day, indep variable is
lead
> time
> > and it is hourly).  I've noticed that when running the batch
engine to
> make
> > these plots that it's going very slowly.  For example, I'm
attempting to
> > make 4 plots at the moment and started the script at 18:56:33 UTC.
It is
> > was 19:44:32 UTC, nearly an hour later, when I stopped the script
since
> no
> > plots had been generated.
> >
> > Anyway, I've run into this issue quite a bit over the last 24
hours.  Is
> > this associated with trying to pull too many records from the
database?
> > Yesterday I did leave the script overnight and it had completed by
> morning,
> > so it did work eventually.
> >
> > Thanks!
> > Jacob
>
>
>
>

------------------------------------------------
Subject: Very slow database access (mv_batch)
From: Tatiana Burek
Time: Wed Aug 17 13:45:53 2016

Jacob,
I'm pretty sure that the size of emc_g2o database is the main factor
here.
line_data_sl1l2 table has 498 929 053 records in it!

You use fcst_init_beg as a fixed field in your plots.
Is it the most likely field you use? If it is I could partition tables
using this field that would speed up the queries.

Tatiana
On Wed Aug 17 11:29:36 2016, jacob.carley at noaa.gov wrote:
> Hi Tatiana,
>
> Sure thing.  Here is the path to one such xml file:
>
> /metviewer/staging/wd20jc/scripts/mv_batch_surface_for_tatiana.xml
>
> This can take 10 mins to an hour to run.  The system's timing seems
to
> be a
> bit fickle.
>
> I can also attach and send the xml if you would prefer that.
>
> Thanks!
> Jacob
>
>
> On Wed, Aug 17, 2016 at 1:21 PM, Tatiana Burek via RT
> <met_help at ucar.edu>
> wrote:
>
> > Jacob,
> >
> > Could you please direct me to the XML file you are using for the
> > batch
> > mode.
> > It will help me to understand the problem.
> >
> > Tatiana
> >
> > On Thu Aug 11 13:51:42 2016, jacob.carley at noaa.gov wrote:
> > > Hi MET help:
> > >
> > > I have been attempting to generate some verification stats that
> > > encompass
> > > about 4 months of forecasts (4 cycles per day, indep variable is
> > > lead
> > time
> > > and it is hourly).  I've noticed that when running the batch
engine
> > > to
> > make
> > > these plots that it's going very slowly.  For example, I'm
> > > attempting to
> > > make 4 plots at the moment and started the script at 18:56:33
UTC.
> > > It is
> > > was 19:44:32 UTC, nearly an hour later, when I stopped the
script
> > > since
> > no
> > > plots had been generated.
> > >
> > > Anyway, I've run into this issue quite a bit over the last 24
> > > hours.  Is
> > > this associated with trying to pull too many records from the
> > > database?
> > > Yesterday I did leave the script overnight and it had completed
by
> > morning,
> > > so it did work eventually.
> > >
> > > Thanks!
> > > Jacob
> >
> >
> >
> >

------------------------------------------------
Subject: Very slow database access (mv_batch)
From: Jacob Carley - NOAA Affiliate
Time: Wed Aug 17 14:07:57 2016

Hi Tatiana,

Wow - well that's pretty huge!  Unfortunately that database is only
going
to grow with time as more stats are loaded into it each day.

I do use fcst_init_beg quite often to stratify the stats by forecast
cycle
date/time.  However, I am not sure if others tend to prefer the same
approach and do not want to risk slowing down other types of queries.
Alternatively, if you have suggestions for other effective parameters
or
settings to use that may accomplish the same thing, that would be
great too!

We do have an EMC and MET development telecon planned for this Friday
and I
think that is probably the time to discuss this and get everyone's
thoughts.  I'm happy to add you to the Google Calendar invite if you
would
like to attend.

Thanks!
Jacob

On Wed, Aug 17, 2016 at 3:45 PM, Tatiana Burek via RT
<met_help at ucar.edu>
wrote:

> Jacob,
> I'm pretty sure that the size of emc_g2o database is the main factor
here.
> line_data_sl1l2 table has 498 929 053 records in it!
>
> You use fcst_init_beg as a fixed field in your plots.
> Is it the most likely field you use? If it is I could partition
tables
> using this field that would speed up the queries.
>
> Tatiana
> On Wed Aug 17 11:29:36 2016, jacob.carley at noaa.gov wrote:
> > Hi Tatiana,
> >
> > Sure thing.  Here is the path to one such xml file:
> >
> > /metviewer/staging/wd20jc/scripts/mv_batch_surface_for_tatiana.xml
> >
> > This can take 10 mins to an hour to run.  The system's timing
seems to
> > be a
> > bit fickle.
> >
> > I can also attach and send the xml if you would prefer that.
> >
> > Thanks!
> > Jacob
> >
> >
> > On Wed, Aug 17, 2016 at 1:21 PM, Tatiana Burek via RT
> > <met_help at ucar.edu>
> > wrote:
> >
> > > Jacob,
> > >
> > > Could you please direct me to the XML file you are using for the
> > > batch
> > > mode.
> > > It will help me to understand the problem.
> > >
> > > Tatiana
> > >
> > > On Thu Aug 11 13:51:42 2016, jacob.carley at noaa.gov wrote:
> > > > Hi MET help:
> > > >
> > > > I have been attempting to generate some verification stats
that
> > > > encompass
> > > > about 4 months of forecasts (4 cycles per day, indep variable
is
> > > > lead
> > > time
> > > > and it is hourly).  I've noticed that when running the batch
engine
> > > > to
> > > make
> > > > these plots that it's going very slowly.  For example, I'm
> > > > attempting to
> > > > make 4 plots at the moment and started the script at 18:56:33
UTC.
> > > > It is
> > > > was 19:44:32 UTC, nearly an hour later, when I stopped the
script
> > > > since
> > > no
> > > > plots had been generated.
> > > >
> > > > Anyway, I've run into this issue quite a bit over the last 24
> > > > hours.  Is
> > > > this associated with trying to pull too many records from the
> > > > database?
> > > > Yesterday I did leave the script overnight and it had
completed by
> > > morning,
> > > > so it did work eventually.
> > > >
> > > > Thanks!
> > > > Jacob
> > >
> > >
> > >
> > >
>
>
>
>

------------------------------------------------
Subject: Very slow database access (mv_batch)
From: Tara Jensen
Time: Wed Aug 17 14:21:16 2016

Hi Jacob,

Seperate from helpdesk - I'll send you an email list of people to
include
in the discussion. OL?

Thanks, Tara

On Wed, Aug 17, 2016 at 2:07 PM, Jacob Carley - NOAA Affiliate via RT
<
met_help at ucar.edu> wrote:

>
> <URL: https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=77533 >
>
> Hi Tatiana,
>
> Wow - well that's pretty huge!  Unfortunately that database is only
going
> to grow with time as more stats are loaded into it each day.
>
> I do use fcst_init_beg quite often to stratify the stats by forecast
cycle
> date/time.  However, I am not sure if others tend to prefer the same
> approach and do not want to risk slowing down other types of
queries.
> Alternatively, if you have suggestions for other effective
parameters or
> settings to use that may accomplish the same thing, that would be
great
> too!
>
> We do have an EMC and MET development telecon planned for this
Friday and I
> think that is probably the time to discuss this and get everyone's
> thoughts.  I'm happy to add you to the Google Calendar invite if you
would
> like to attend.
>
> Thanks!
> Jacob
>
> On Wed, Aug 17, 2016 at 3:45 PM, Tatiana Burek via RT
<met_help at ucar.edu>
> wrote:
>
> > Jacob,
> > I'm pretty sure that the size of emc_g2o database is the main
factor
> here.
> > line_data_sl1l2 table has 498 929 053 records in it!
> >
> > You use fcst_init_beg as a fixed field in your plots.
> > Is it the most likely field you use? If it is I could partition
tables
> > using this field that would speed up the queries.
> >
> > Tatiana
> > On Wed Aug 17 11:29:36 2016, jacob.carley at noaa.gov wrote:
> > > Hi Tatiana,
> > >
> > > Sure thing.  Here is the path to one such xml file:
> > >
> > >
/metviewer/staging/wd20jc/scripts/mv_batch_surface_for_tatiana.xml
> > >
> > > This can take 10 mins to an hour to run.  The system's timing
seems to
> > > be a
> > > bit fickle.
> > >
> > > I can also attach and send the xml if you would prefer that.
> > >
> > > Thanks!
> > > Jacob
> > >
> > >
> > > On Wed, Aug 17, 2016 at 1:21 PM, Tatiana Burek via RT
> > > <met_help at ucar.edu>
> > > wrote:
> > >
> > > > Jacob,
> > > >
> > > > Could you please direct me to the XML file you are using for
the
> > > > batch
> > > > mode.
> > > > It will help me to understand the problem.
> > > >
> > > > Tatiana
> > > >
> > > > On Thu Aug 11 13:51:42 2016, jacob.carley at noaa.gov wrote:
> > > > > Hi MET help:
> > > > >
> > > > > I have been attempting to generate some verification stats
that
> > > > > encompass
> > > > > about 4 months of forecasts (4 cycles per day, indep
variable is
> > > > > lead
> > > > time
> > > > > and it is hourly).  I've noticed that when running the batch
engine
> > > > > to
> > > > make
> > > > > these plots that it's going very slowly.  For example, I'm
> > > > > attempting to
> > > > > make 4 plots at the moment and started the script at
18:56:33 UTC.
> > > > > It is
> > > > > was 19:44:32 UTC, nearly an hour later, when I stopped the
script
> > > > > since
> > > > no
> > > > > plots had been generated.
> > > > >
> > > > > Anyway, I've run into this issue quite a bit over the last
24
> > > > > hours.  Is
> > > > > this associated with trying to pull too many records from
the
> > > > > database?
> > > > > Yesterday I did leave the script overnight and it had
completed by
> > > > morning,
> > > > > so it did work eventually.
> > > > >
> > > > > Thanks!
> > > > > Jacob
> > > >
> > > >
> > > >
> > > >
> >
> >
> >
> >
>
>

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tara Jensen
Project Manager II
NCAR RAL and DTC
PO Box 3000, Boulder, Colorado 80307 USA
+1 303-497-8479          jensen at ucar.edu

------------------------------------------------