Address slowness resulting from size of MODE data

[mollybsmith-noaa]

The MODE output from MET is quite large, and may cause problems if we run it in realtime and try to store all of it in one database. We need to discuss solutions for what to do about this.

[mollybsmith-noaa]

To document the slowness of mysql queries for MODE data, I plotted our most taxing test case: a month-long reflectivity dieoff. These are the parameters:

This plot requires two queries (to avoid doing a horrendous table join):

select h.fcst_lead as fcst_lead, count(distinct unix_timestamp(h.fcst_valid)) as N_times, min(unix_timestamp(h.fcst_valid)) as min_secs, max(unix_timestamp(h.fcst_valid)) as max_secs, avg(ld2.area) as area, group_concat(distinct ld2.object_id, ';', h.mode_header_id, ';', ld2.area, ';', ld2.intensity_nn, ';', ld2.centroid_lat, ';', ld2.centroid_lon, ';', unix_timestamp(h.fcst_valid), ';', h.fcst_lev order by unix_timestamp(h.fcst_valid), h.fcst_lev) as sub_data2 from mv_gsl_mode_retros.mode_header h, mv_gsl_mode_retros.mode_obj_single ld2 where 1=1 and unix_timestamp(h.fcst_valid) >= '1588483800' and unix_timestamp(h.fcst_valid) <= '1591023600' and h.model = 'HRRRv4' and h.fcst_var = 'REFC' and h.fcst_lev IN('L0','L0=') and h.descr IN('ECONUS') and ld2.simple_flag = 1 and h.mode_header_id = ld2.mode_header_id group by fcst_lead order by fcst_lead;

select h.fcst_lead as fcst_lead, count(distinct unix_timestamp(h.fcst_valid)) as N_times, min(unix_timestamp(h.fcst_valid)) as min_secs, max(unix_timestamp(h.fcst_valid)) as max_secs, avg(ld.interest) as interest, group_concat(distinct ld.interest, ';', ld.object_id, ';', h.mode_header_id, ';', ld.centroid_dist, ';', unix_timestamp(h.fcst_valid), ';', h.fcst_lev order by unix_timestamp(h.fcst_valid), h.fcst_lev) as sub_data from mv_gsl_mode_retros.mode_header h, mv_gsl_mode_retros.mode_obj_pair ld where 1=1 and unix_timestamp(h.fcst_valid) >= '1588483800' and unix_timestamp(h.fcst_valid) <= '1591023600' and h.model = 'HRRRv4' and h.fcst_var = 'REFC' and h.fcst_lev IN('L0','L0=') and h.descr IN('ECONUS') and ld.simple_flag = 1 and h.mode_header_id = ld.mode_header_id group by fcst_lead order by fcst_lead;

For this example on metv-gsd.gsd.esrl.noaa.gov, the combined query execution time was 6:23 (the vast majority of this was spent on the second query), and the time to execute the METexpress statistical code on the results was 2:49.

[bonnystrong]

Thank you so much for documenting this. Just to be clear, I assume the execution time of 6:23 was 6 mins 23 secs?

…

On Tue, Mar 29, 2022 at 12:32 PM Molly Smith ***@***.***> wrote: To document the slowness of mysql queries for MODE data, I plotted our most taxing test case: a month-long reflectivity dieoff. These are the parameters: [image: Screen Shot 2022-03-25 at 12 05 22 PM] <https://user-images.githubusercontent.com/29582181/160679982-7b821b42-2f86-4226-808e-42170fd9b5ae.png> This plot requires two queries (to avoid doing a horrendous table join): select h.fcst_lead as fcst_lead, count(distinct unix_timestamp(h.fcst_valid)) as N_times, min(unix_timestamp(h.fcst_valid)) as min_secs, max(unix_timestamp(h.fcst_valid)) as max_secs, avg(ld2.area) as area, group_concat(distinct ld2.object_id, ';', h.mode_header_id, ';', ld2.area, ';', ld2.intensity_nn, ';', ld2.centroid_lat, ';', ld2.centroid_lon, ';', unix_timestamp(h.fcst_valid), ';', h.fcst_lev order by unix_timestamp(h.fcst_valid), h.fcst_lev) as sub_data2 from mv_gsl_mode_retros.mode_header h, mv_gsl_mode_retros.mode_obj_single ld2 where 1=1 and unix_timestamp(h.fcst_valid) >= '1588483800' and unix_timestamp(h.fcst_valid) <= '1591023600' and h.model = 'HRRRv4' and h.fcst_var = 'REFC' and h.fcst_lev IN('L0','L0=') and h.descr IN('ECONUS') and ld2.simple_flag = 1 and h.mode_header_id = ld2.mode_header_id group by fcst_lead order by fcst_lead; select h.fcst_lead as fcst_lead, count(distinct unix_timestamp(h.fcst_valid)) as N_times, min(unix_timestamp(h.fcst_valid)) as min_secs, max(unix_timestamp(h.fcst_valid)) as max_secs, avg(ld.interest) as interest, group_concat(distinct ld.interest, ';', ld.object_id, ';', h.mode_header_id, ';', ld.centroid_dist, ';', unix_timestamp(h.fcst_valid), ';', h.fcst_lev order by unix_timestamp(h.fcst_valid), h.fcst_lev) as sub_data from mv_gsl_mode_retros.mode_header h, mv_gsl_mode_retros.mode_obj_pair ld where 1=1 and unix_timestamp(h.fcst_valid) >= '1588483800' and unix_timestamp(h.fcst_valid) <= '1591023600' and h.model = 'HRRRv4' and h.fcst_var = 'REFC' and h.fcst_lev IN('L0','L0=') and h.descr IN('ECONUS') and ld.simple_flag = 1 and h.mode_header_id = ld.mode_header_id group by fcst_lead order by fcst_lead; For this example on metv-gsd.gsd.esrl.noaa.gov, the combined query execution time was 6:23 (the vast majority of this was spent on the second query), and the time to execute the METexpress statistical code on the results was 2:49. — Reply to this email directly, view it on GitHub <#67 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AG6HZOXLANBDRTUALI5K7ADVCNEEFANCNFSM5OB7SBWQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Bonny Strong NOAA/GSL and CIRA office: 303 497-3936 or home: 970-669-1188

[mollybsmith-noaa]

Yes! Both of those times are in minutes. On Tue, Mar 29, 2022 at 11:15 PM bonnystrong ***@***.***> wrote:

…

Thank you so much for documenting this. Just to be clear, I assume the execution time of 6:23 was 6 mins 23 secs? On Tue, Mar 29, 2022 at 12:32 PM Molly Smith ***@***.***> wrote: > To document the slowness of mysql queries for MODE data, I plotted our > most taxing test case: a month-long reflectivity dieoff. These are the > parameters: > > [image: Screen Shot 2022-03-25 at 12 05 22 PM] > < https://user-images.githubusercontent.com/29582181/160679982-7b821b42-2f86-4226-808e-42170fd9b5ae.png > > > This plot requires two queries (to avoid doing a horrendous table join): > > select h.fcst_lead as fcst_lead, count(distinct > unix_timestamp(h.fcst_valid)) as N_times, min(unix_timestamp(h.fcst_valid)) > as min_secs, max(unix_timestamp(h.fcst_valid)) as max_secs, avg(ld2.area) > as area, group_concat(distinct ld2.object_id, ';', h.mode_header_id, ';', > ld2.area, ';', ld2.intensity_nn, ';', ld2.centroid_lat, ';', > ld2.centroid_lon, ';', unix_timestamp(h.fcst_valid), ';', h.fcst_lev order > by unix_timestamp(h.fcst_valid), h.fcst_lev) as sub_data2 from > mv_gsl_mode_retros.mode_header h, mv_gsl_mode_retros.mode_obj_single ld2 > where 1=1 and unix_timestamp(h.fcst_valid) >= '1588483800' and > unix_timestamp(h.fcst_valid) <= '1591023600' and h.model = 'HRRRv4' and > h.fcst_var = 'REFC' and h.fcst_lev IN('L0','L0=') and h.descr IN('ECONUS') > and ld2.simple_flag = 1 and h.mode_header_id = ld2.mode_header_id group by > fcst_lead order by fcst_lead; > > select h.fcst_lead as fcst_lead, count(distinct > unix_timestamp(h.fcst_valid)) as N_times, min(unix_timestamp(h.fcst_valid)) > as min_secs, max(unix_timestamp(h.fcst_valid)) as max_secs, > avg(ld.interest) as interest, group_concat(distinct ld.interest, ';', > ld.object_id, ';', h.mode_header_id, ';', ld.centroid_dist, ';', > unix_timestamp(h.fcst_valid), ';', h.fcst_lev order by > unix_timestamp(h.fcst_valid), h.fcst_lev) as sub_data from > mv_gsl_mode_retros.mode_header h, mv_gsl_mode_retros.mode_obj_pair ld where > 1=1 and unix_timestamp(h.fcst_valid) >= '1588483800' and > unix_timestamp(h.fcst_valid) <= '1591023600' and h.model = 'HRRRv4' and > h.fcst_var = 'REFC' and h.fcst_lev IN('L0','L0=') and h.descr IN('ECONUS') > and ld.simple_flag = 1 and h.mode_header_id = ld.mode_header_id group by > fcst_lead order by fcst_lead; > > For this example on metv-gsd.gsd.esrl.noaa.gov, the combined query > execution time was 6:23 (the vast majority of this was spent on the second > query), and the time to execute the METexpress statistical code on the > results was 2:49. > > — > Reply to this email directly, view it on GitHub > < #67 (comment)>, > or unsubscribe > < https://github.com/notifications/unsubscribe-auth/AG6HZOXLANBDRTUALI5K7ADVCNEEFANCNFSM5OB7SBWQ > > . > You are receiving this because you are subscribed to this thread.Message > ID: ***@***.***> > -- Bonny Strong NOAA/GSL and CIRA office: 303 497-3936 or home: 970-669-1188 — Reply to this email directly, view it on GitHub <#67 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHBWGZJO5PCQNORA5W3XUYTVCPPQHANCNFSM5OB7SBWQ> . You are receiving this because you were assigned.Message ID: ***@***.***>

[mollybsmith-noaa]

Today's mysql speed test returned a query time of 7:16, and a statistic calculation time of 2:36.

[mollybsmith-noaa]

One interesting note: while the post-query statistical analysis on localhost takes about two and a half minutes, it seems to take 5-6 times as long on mats-docker-int (I'll try to get a specific number later). Are our containers underpowered? This seems like something we should absolutely fix if we can.

[randytpierce]

Hi Molly, Could you elaborate a little more on this so that I could try to replicate it on my laptop? I might also try it within different containers. I wonder what the limiting factor is. It could be some sort of system resource, like not enough memory or slow disk configuration, or it could be network issues, or it could be the database.. Was it the same database both ways? randy

…

On Wed, Mar 30, 2022 at 10:54 PM Molly Smith ***@***.***> wrote: One interesting note: while the post-query statistical analysis on localhost takes about two and a half minutes, it seems to take 5-6 times as long on mats-docker-int (I'll try to get a specific number later). Are our containers underpowered? This seems like something we should absolutely fix if we can. — Reply to this email directly, view it on GitHub <#67 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGDVQPT75YFART6KGOJIMATVCUVWHANCNFSM5OB7SBWQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Randy Pierce

[mollybsmith-noaa]

Okay, on MATS/METexpress, if you click "Data Lineage" from the graph page, there is a field under "basis" called "data retrieval (query) time". In METexpress, this field represents the TOTAL time for TWO things to occur: 1) for the mysql database itself to return the results of the query, and 2) for python_query_util to parse the returned data into the fields expected by the MATScommon routines and calculate the statistic. Both of these are considered "query time".

I've been trying this week to quantify the time that both 1) and 2) take in MET Objects.

Speed results for 1):
When using the query given previously with the mysql database on metv-gsd, both localhost and the containers deployed on mats-docker-int take around 6-7 minutes to get their data back from the database if it is not cached, and around 1 minute if it is cached. On localhost I can capture this by putting print statements in python_query_util, while on mats-docker-int I have to have a terminal window open to metv-gsd and spam show processlist; to see how long the query takes. However, it seems to be about the same for both.

Speed results for 2):
On localhost, once the data comes back from the database, it takes about 2 and a half minutes to run through all of Jeff Duda's statistical routines and exit python_query_util without any errors. On mats-docker-int, it takes 8-10 minutes, in the absence of caching. This is a substantial difference and leads me to believe that the containers don't have the resources they need. The speed for this component is determined by taking the "data retrieval (query) time" from the Data Lineage and subtracting the time needed for the query itself.

All of these times are for dieoffs, the other plot types are much faster.

[randytpierce]

So the extra time that you are seeing on mats-docker-int is not query time but is the time it takes to do the data processing, and the code is the same in both cases? randy

…

On Thu, Mar 31, 2022 at 11:42 AM Molly Smith ***@***.***> wrote: Okay, on MATS/METexpress, if you click "Data Lineage" from the graph page, there is a field under "basis" called "data retrieval (query) time". In METexpress, this field represents the TOTAL time for TWO things to occur: 1) for the mysql database itself to return the results of the query, and 2) for python_query_util to parse the returned data into the fields expected by the MATScommon routines and calculate the statistic. Both of these are considered "query time". I've been trying this week to quantify the time that both 1) and 2) take in MET Objects. Speed results for 1): When using the query given previously with the mysql database on metv-gsd, both localhost and the containers deployed on mats-docker-int take around 6-7 minutes to get their data back from the database if it is not cached, and around 1 minute if it is cached. On localhost I can capture this by putting print statements in python_query_util, while on mats-docker-int I have to have a terminal window open to metv-gsd and spam show processlist; to see how long the query takes. However, it seems to be about the same for both. Speed results for 2): On localhost, once the data comes back from the database, it takes about 2 and a half minutes to run through all of Jeff Duda's statistical routines and exit python_query_util without any errors. On mats-docker-int, it takes 8-10 minutes, in the absence of caching. This is a substantial difference and leads me to believe that the containers don't have the resources they need. The speed for this component is determined by taking the "data retrieval (query) time" from the Data Lineage and subtracting the time needed for the query itself. All of these times are for dieoffs, the other plot types are much faster. — Reply to this email directly, view it on GitHub <#67 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGDVQPQLFVLHPPU5QR7BIJTVCXPY5ANCNFSM5OB7SBWQ> . You are receiving this because you commented.Message ID: ***@***.***>

-- Randy Pierce

[mollybsmith-noaa]

Yes, but I want to be very specific that it is data processing in the query routines, and as such MATS/METexpress considers it to be "query time". I have not looked at the rest of the data processing, such as diff curves, setting plot options, etc. The query code is the same in both cases but it takes several times as long when run in a container on our MATS servers.