Splunk DB Connect 2.3: Why is rising column value is not used in search after indexing?

[dlisicre]

I am using an input in Splunk 6.4.3 and Splunk DB Connect 2.3, that makes use of Rising Column in Advanced mode for MySQL.
Indexing occurs correctly, and all goes well until it has finished parsing data and no new data is available.
This occurs on all my inputs (9) and makes my MySQL go up to 90% CPU usage without motive.

When indexing, if I do a show full processlist, it shows search being performed with the rising column value.
But after it has finished the initial index, every time it has to run (specified by interval value set to 60), I notice CPU goes up and my show full processlist indicates that my rising column value is empty: ''.

My Input is:

[mi_input://xxxx_occupancy]
connection = xxxx
index = xxxx_idx
interval = 60
mode = advanced
query = SELECT alert.id AS alert_id,\
         alert.timestamp AS alert_timestamp,\
         occupancy.occupancy AS occupancy_occupancy,\
         barrier.id AS barrier_id,\
         barrier.name AS barrier_name,\
         barrier.process_id AS barrier_process_id\
FROM \
    (SELECT *\
    FROM alert\
    WHERE alert.id > ?) AS alert\
JOIN occupancy\
    ON alert.id=occupancy.alert_id\
JOIN barrier\
    ON occupancy.barrier_id=barrier.id\
JOIN process\
    ON alert.process_id=process.id\
WHERE process.license_id>1099\
        AND process.license_id<1200\
        AND process.active=1\
        AND barrier.process_id is NOT NULL
source = //xxxx/xxxx_occupancy
sourcetype = xxxx_occupancy
tail_follow_only = 1
tail_rising_column_checkpoint_value = 12081444
tail_rising_column_name = alert_id
ui_query_mode = advanced
disabled = 0
enable_query_wrapping = 1
max_rows = 5000000
output_timestamp_format = epoch
ui_query_schema = NULL
input_timestamp_column_fullname = (002) alert.alert_timestamp.TIMESTAMP
input_timestamp_column_name = alert_timestamp
tail_rising_column_fullname = (001) alert.alert_id.BIGINT
fetch_size = 1000

And show process list query is:

SELECT alert.id AS alert_id,
         alert.timestamp AS alert_timestamp,
         occupancy.occupancy AS occupancy_occupancy,
         barrier.id AS barrier_id,
         barrier.name AS barrier_name,
         barrier.process_id AS barrier_process_id
FROM 
    (SELECT *
    FROM alert
    WHERE alert.id > '') AS alert
JOIN occupancy
    ON alert.id=occupancy.alert_id
JOIN barrier
    ON occupancy.barrier_id=barrier.id
JOIN process
    ON alert.process_id=process.id
WHERE process.license_id>1099
        AND process.license_id<1200
        AND process.active=1
        AND barrier.process_id is NOT NULL

As there are many results, searching without taking into account rising column for:

 (SELECT *
    FROM alert
    WHERE alert.id > '') AS alert

results in unnecessary CPU cycles that should take less than 300ms and take 30 seconds or more.

Any help would be very much appreciated.

NOTE: Didn't have this behaviour in Splunk DB Connect 1.1.6.

Kind regards,

David

[gcusello]

Are you sure that your rising column is always growing?
Because if it restarts for any reason, you cannot take the new records.
in this way, you have to insert in your query a new field with the record date (in epoch time or yyyy-mm-dd hh:mm:ss) and your rising column and use it as rising column.
In this way you'll be sure that the rising column is always growing.
Bye.
Giuseppe

[dlisicre]

Good morning Giusseppe,

I know Raising column is working correclt as it shows in current raising column value.

Problem is that after all indexing is done, and there is no new data in time interval frame, the query done to mysql is without value.

However, as indicated in my input value config, it is correctly stated. And for testing purposes, if i decrement by one the raising column value, it show the last value correctly.

However, in attached image, I do see that if last column value is reached, it shows raising column as NULL. Is that normal?

The fact that 9 dbinputs do a full scan make my PC have a high CPU usage.

Kind regards, and again, any help would be very much appreciated!!

If you need any logs, configs, or information, please feel free to ask them from me.

David

[dlisicre]

Could it be that the health logger is actually not taking into account the rising column value?

2016-09-19 11:18:16 INFO  HealthLogger:193 - DB_JDBC_URL=jdbc:mysql://127.0.0.1:3306/ganetec FREE_MEMORY=610374208 FUNCTION=dbinputAdvanceIterator LABEL=JP MAX_MEMORY=2112618496 PROTOCOL=HTTP SQL="SELECT alert.id AS alert_id,
         alert.timestamp AS alert_timestamp,
         occupancy.occupancy AS occupancy_occupancy,
         barrier.id AS barrier_id,
         barrier.name AS barrier_name,
         barrier.process_id AS barrier_process_id
FROM 
    (SELECT *
    FROM alert
    WHERE alert.id > ?) AS alert
JOIN occupancy
    ON alert.id=occupancy.alert_id
JOIN barrier
    ON occupancy.barrier_id=barrier.id
JOIN process
    ON alert.process_id=process.id
WHERE process.license_id>1099
        AND process.license_id<1200
        AND process.active=1
        AND barrier.process_id is NOT NULL" STATE=completed TOTAL_MEMORY=1838809088 TRANS_OBJECT_ID=2f7c0541-7a0d-48ee-9b4e-fa928e024b95 UPTIME=3493782 task_id=da9bd483-156b-41fc-9ed2-29e0accc828a

Is there a way I can disable it to see if that is the case? Or does it just reflect what my dbinput is quering?