esgf-dashboard-ip only processes entries on startup

[nathanlcarlson]

The esgf-dashboard-ip service is started using

ip.service start

To test the service some cmip5 data has been published to the node. Permissions were modified such that ANY group/role can read and write cmip5 data. That data is then downloaded using the WGET Script generated in the CoG interface by a manually created test user on separate machine. This download is reflected by the following SQL statements in the esgcet database on the node with the data.

select * from esgf_node_manager.access_logging;
select * from esgf_dashboard.dashboard_queue;

The issue is that this information does not appear to be updated in the appropriate /esgf-stats-api/* URL until the esgf-dashboard-ip service is restarted. Each time it is started the follow SQL is reported to be executed:

START PLANA
insert_dmart_project_host DELETE FROM esgf_dashboard.cmip5_dmart_experiment_host_time where host_name='cwt-node.llnl.gov'; INSERT INTO esgf_dashboard.cmip5_dmart_experiment_host_time(total_size, number_of_downloads, number_of_successful_downloads, average_duration, number_of_users, number_of_replica_downloads, month, year, experiment_name, host_name) SELECT SUM(size) AS total_size, COUNT(*) AS number_of_downloads, COUNT(CASE WHEN success THEN 1 END) AS number_of_successful_downloads, ROUND(AVG(CASE WHEN success THEN duration ELSE 0 END)) AS average_duration, COUNT(distinct user_id_hash) AS number_of_users, COUNT(CASE WHEN replica THEN 1 END) AS number_of_replica_downloads, date.month, date.year, exp.experiment_name,fact.host_name FROM esgf_dashboard.cmip5_fact_download AS fact JOIN esgf_dashboard.cmip5_dim_date AS date ON fact.date_key = date.date_key JOIN esgf_dashboard.cmip5_bridge_experiment AS bridgeexp ON fact.experiment_group_key = bridgeexp.experiment_group_key JOIN esgf_dashboard.cmip5_dim_experiment AS exp ON bridgeexp.experiment_key=exp.experiment_key WHERE size <> -1 GROUP BY host_name, date.month, date.year, exp.experiment_name ORDER BY date.year, date.month ASC;
There are no entries to be processed

What is shown in the /esgf-stats-api/* URL appears to be indicated by the processed attribute in the esgf_dashboard.dashboard_queue table.

The question is, should it be "processed" immediately? Why is a restart required? After a certain amount of time will it be "processed"?

[nathanlcarlson]

For completeness here is the full output of ip.service start

# ./ip.service start
Starting the ESGF Information Provider... [OK]
[root@cwt-node bin]# [START] Starting esgf-dashboard-ip
[START] ESGF_HOME attribute not found... setting /esg as default
[START] ESGF_HOME = [/esg/]
[START] //esg//config/esgf.properties
[START] Debug level 2 (1=ERROR, 2=WARNING, 3=DEBUG)
***************************************************
[WARNING][esgf-dashboard-ip.c][589] Please note that 11 non-mandatory properties are missing in the esgf.properties file. Default values have been loaded
NOTICE:  table "cmip5_data_usage_continent_tmp" does not exist, skipping
[WARNING][dbAccess.c][291] Query submission failed [drop table if exists esgf_dashboard.cmip5_data_usage_continent_tmp; create table esgf_dashboard.cmip5_data_usage_continent_tmp as (SELECT EXTRACT (YEAR FROM (TIMESTAMP WITH TIME ZONE 'epoch' + fixed_log.date_fetched * INTERVAL '1 second')) AS year, EXTRACT (MONTH FROM (TIMESTAMP WITH TIME ZONE 'epoch' + fixed_log.date_fetched * INTERVAL '1 second')) AS month, COUNT(*) AS downloads, COUNT(distinct url) AS files, COUNT(distinct user_id_hash) AS users, SUM(fixed_log.size)/1024/1024/1024 AS gb, fixed_log.continent FROM (SELECT cl.continent, file.url, log.user_id_hash, max(log.timestamp) AS date_fetched, max(file.size) AS size FROM esgf_dashboard.dashboard_queue AS log JOIN esgf_dashboard.client_stats_dm AS cl ON (log.remote_addr=cl.ip) JOIN public.file_version AS file ON (UPPER(log.url_path) LIKE '%CMIP5%.NC' AND log.url_path=file.url) WHERE log.success AND log.duration > 1000 GROUP BY file.url, log.user_id_hash, cl.continent) AS fixed_log GROUP BY year, month, continent ORDER BY year, month);]
NOTICE:  table "cordex_data_usage_continent_tmp" does not exist, skipping
[WARNING][dbAccess.c][291] Query submission failed [drop table if exists esgf_dashboard.cordex_data_usage_continent_tmp; create table esgf_dashboard.cordex_data_usage_continent_tmp as (SELECT EXTRACT (YEAR FROM (TIMESTAMP WITH TIME ZONE 'epoch' + fixed_log.date_fetched * INTERVAL '1 second')) AS year, EXTRACT (MONTH FROM (TIMESTAMP WITH TIME ZONE 'epoch' + fixed_log.date_fetched * INTERVAL '1 second')) AS month, COUNT(*) AS downloads, COUNT(distinct url) AS files, COUNT(distinct user_id_hash) AS users, SUM(fixed_log.size)/1024/1024/1024 AS gb, fixed_log.continent FROM (SELECT cl.continent, file.url, log.user_id_hash, max(log.timestamp) AS date_fetched, max(file.size) AS size FROM esgf_dashboard.dashboard_queue AS log JOIN esgf_dashboard.client_stats_dm AS cl ON (log.remote_addr=cl.ip) JOIN public.file_version AS file ON (UPPER(log.url_path) LIKE '%CORDEX%.NC' AND log.url_path=file.url) WHERE log.success AND log.duration > 1000 GROUP BY file.url, log.user_id_hash, cl.continent) AS fixed_log GROUP BY year, month, continent ORDER BY year, month);]
NOTICE:  table "obs4mips_data_usage_continent_tmp" does not exist, skipping
[WARNING][dbAccess.c][291] Query submission failed [drop table if exists esgf_dashboard.obs4mips_data_usage_continent_tmp; create table esgf_dashboard.obs4mips_data_usage_continent_tmp as (SELECT EXTRACT (YEAR FROM (TIMESTAMP WITH TIME ZONE 'epoch' + fixed_log.date_fetched * INTERVAL '1 second')) AS year, EXTRACT (MONTH FROM (TIMESTAMP WITH TIME ZONE 'epoch' + fixed_log.date_fetched * INTERVAL '1 second')) AS month, COUNT(*) AS downloads, COUNT(distinct url) AS files, COUNT(distinct user_id_hash) AS users, SUM(fixed_log.size)/1024/1024/1024 AS gb, fixed_log.continent FROM (SELECT cl.continent, file.url, log.user_id_hash, max(log.timestamp) AS date_fetched, max(file.size) AS size FROM esgf_dashboard.dashboard_queue AS log JOIN esgf_dashboard.client_stats_dm AS cl ON (log.remote_addr=cl.ip) JOIN public.file_version AS file ON (UPPER(log.url_path) LIKE '%OBS4MIPS%.NC' AND log.url_path=file.url) WHERE log.success AND log.duration > 1000 GROUP BY file.url, log.user_id_hash, cl.continent) AS fixed_log GROUP BY year, month, continent ORDER BY year, month);]
NOTICE:  table "all_data_usage_continent_tmp" does not exist, skipping
[WARNING][dbAccess.c][291] Query submission failed [drop table if exists esgf_dashboard.all_data_usage_continent_tmp; create table esgf_dashboard.all_data_usage_continent_tmp as (SELECT EXTRACT (YEAR FROM (TIMESTAMP WITH TIME ZONE 'epoch' + fixed_log.date_fetched * INTERVAL '1 second')) AS year, EXTRACT (MONTH FROM (TIMESTAMP WITH TIME ZONE 'epoch' + fixed_log.date_fetched * INTERVAL '1 second')) AS month, COUNT(*) AS downloads, COUNT(distinct url) AS files, COUNT(distinct user_id_hash) AS users, SUM(fixed_log.size)/1024/1024/1024 AS gb, fixed_log.continent FROM (SELECT cl.continent, file.url, log.user_id_hash, max(log.timestamp) AS date_fetched, max(file.size) AS size FROM esgf_dashboard.dashboard_queue AS log JOIN esgf_dashboard.client_stats_dm AS cl ON (log.remote_addr=cl.ip) JOIN public.file_version AS file ON (UPPER(log.url_path) LIKE '%.NC' AND log.url_path=file.url) WHERE log.success AND log.duration > 1000 GROUP BY file.url, log.user_id_hash, cl.continent) AS fixed_log GROUP BY year, month, continent ORDER BY year, month);]
START PLANA
insert_dmart_project_host DELETE FROM esgf_dashboard.cmip5_dmart_experiment_host_time where host_name='cwt-node.llnl.gov'; INSERT INTO esgf_dashboard.cmip5_dmart_experiment_host_time(total_size, number_of_downloads, number_of_successful_downloads, average_duration, number_of_users, number_of_replica_downloads, month, year, experiment_name, host_name) SELECT SUM(size) AS total_size, COUNT(*) AS number_of_downloads, COUNT(CASE WHEN success THEN 1 END) AS number_of_successful_downloads, ROUND(AVG(CASE WHEN success THEN duration ELSE 0 END)) AS average_duration, COUNT(distinct user_id_hash) AS number_of_users, COUNT(CASE WHEN replica THEN 1 END) AS number_of_replica_downloads, date.month, date.year, exp.experiment_name,fact.host_name FROM esgf_dashboard.cmip5_fact_download AS fact JOIN esgf_dashboard.cmip5_dim_date AS date ON fact.date_key = date.date_key JOIN esgf_dashboard.cmip5_bridge_experiment AS bridgeexp ON fact.experiment_group_key = bridgeexp.experiment_group_key JOIN esgf_dashboard.cmip5_dim_experiment AS exp ON bridgeexp.experiment_key=exp.experiment_key WHERE size <> -1 GROUP BY host_name, date.month, date.year, exp.experiment_name ORDER BY date.year, date.month ASC;
There are no entries to be processed

[nathanlcarlson]

In src/esgf-dashboard-ip.c there are the following lines.

fprintf(stderr, "%s\n", "There are no entries to be processed"); 

This is the last line we see get printed. Then shortly after...

sleep(DATA_METRICS_SPAN*3600); // PRODUCTION_ once a day

Although the details of the code are a little unclear, this seems to indicate that items are "processed" once a day.

I will assume this works and resolve the issue.