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.
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.
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.
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?