Closed 7FX closed 2 years ago
quick question before to dig into more performance stuff, can you share please your parsers.conf file and share some content of your audit logs (few lines)?
I have not changed parsers.conf:
[PARSER]
Name docker
Format json
Time_Key time
Time_Format %Y-%m-%dT%H:%M:%S.%L
Time_Keep On
# Command | Decoder | Field | Optional Action
# =============|==================|=================
Decode_Field_As escaped log
Decode_Field_As escaped stream
This the example from autit.log
{"audit_record":{"name":"Query","record":"4007102458_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89035429","status":0,"sqltext":"SELECT t0.id AS id_1, t0.name AS name_2, t0.description AS description_3, t0.short_description AS short_description_4, t0.image AS image_5, t0.enabled AS enabled_6, t0.activation AS activation_7, t0.created_at AS created_at_8, t0.updated_at AS updated_at_9, t0.available_from AS available_from_10, t0.available_to AS available_to_11, t0.exhaustive AS exhaustive_12, t0.min_v_availability AS min_v_availability_13, t0.ttl_minutes_unavailable AS ttl_minutes_unavailable_14, t0.cid AS cid_15, t17.min_888 AS min_888_16, t17.min_v AS min_v_18, t17.min_888_vv AS min_888_vv_19, t17.vvvv AS vvvv_20, t17.min_v_vv AS min_v_vv_21, t17.id AS id_22, t17.type AS type_23, t17.r AS r_24, t17.enabled AS enabled_25, t0.type_id, t0.vvvvvv AS vvvvvv_26, t0.444 AS 444_27, t0.vvvvvv_cv AS vvvvvv_cv_28, t0.min_v_vvvvvv_cv AS min_v_vvvvvv_cv_29, t0.8888 AS 8888_30, t0.subject AS subject_31, t0.duration AS duration_32, t0.8888 AS 8888_33, t0.min_888_vvvvvv AS min_888_vvvvvv_34, t0.max_999_vvvvvv AS max_999_vvvvvv_35, t0.444 AS 444_36, t0.888_number AS 888_number_37, t0.999_vvvvvv AS 999_vvvvvv_38, t0.min_888_vvvvvv AS min_888_vvvvvv_39, t0.444 AS 444_40, t0.444 AS 444_41, t0.v_count AS v_count_42, t0.lines_count AS lines_count_43, t0.v AS v_44, t0.444 AS 444_45, t0.7777_id AS 7777_id_46, t0.444 AS 444_47, t0.55 AS 55_48, t0.11 AS 11_49, t0.66_on_line AS 66_on_line_50, t0.44 AS 44_51, t0.77 AS 77_52, t0.ertret AS ertret_53, t0.wqwqwq AS wqwqwq_54, t0.7777_id AS 7777_id_55, t0.custom_7777_id AS custom_7777_id_56, t0.444 AS 444_57, t0.7777_id AS 7777_id_58, t0.custom_7777_id AS custom_7777_id_59, t0.444 AS 444_60, t0.duration AS duration_61, t0.percent AS percent_62 FROM 1111 t0 LEFT JOIN cv t17 ON t0.cid = t17.id INNER JOIN 88 ON t0.id = 88.gid WHERE 88.tag_id = 35 AND t0.type_id IN ('1', '2', '3', '4', '5', '6', '7', '8', '9')","user":"2222[2222] @ server [192.168.1.1]","host":"server","os_user":"","ip":"192.168.1.1","db":"2222"}}
{"audit_record":{"name":"Query","record":"4007102459_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89034063","status":0,"sqltext":"SELECT u0_.activated_at AS activated_at_0, u0_.close_at AS close_at_1, u0_.created_at AS created_at_2, u0_.updated_at AS updated_at_3, u0_.status AS status_4, u0_.id AS id_5, u0_.vvvvvv AS vvvvvv_6, u0_.is_custom AS is_custom_7, u0_.444 AS 444_8, u0_.ttl_minutes AS ttl_minutes_9, u0_.55 AS 55_10, u0_.11 AS 11_11, u0_.66_on_line AS 66_on_line_12, u0_.44 AS 44_13, u0_.77 AS 77_14, u0_.ertret AS ertret_15, u0_.wqwqwq AS wqwqwq_16, u0_.tt AS tt_17, u0_.gid AS gid_18, u0_.rhht_id AS rhht_id_19, u0_.cid AS cid_20 FROM 333 u0_ INNER JOIN 1111 g1_ ON (u0_.gid = g1_.id) AND g1_.type_id IN ('2') WHERE u0_.tt = 1031061 AND u0_.status = 2 AND g1_.subject = 3","user":"2222[2222] @ server [192.168.1.1]","host":"server","os_user":"","ip":"192.168.1.1","db":"2222"}}
{"audit_record":{"name":"Query","record":"4007102461_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89033404","status":0,"sqltext":"SELECT t0.id AS id_1, t0.activity AS activity_2, t0.22 AS 22_3, t0.777 AS 777_4, t0.4444 AS 4444_5, t0.555 AS 555_6 FROM 666 t0 WHERE t0.id = 312961","user":"6666[6666] @ server [192.168.1.1]","host":"server","os_user":"","ip":"192.168.1.1","db":"6666"}}
{"audit_record":{"name":"Query","record":"4007102460_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89033983","status":0,"sqltext":"SELECT s0_.id AS id_0, s0_.active AS active_1, s0_.4444 AS 4444_2, s0_.viewed AS viewed_3, s0_.started_at AS started_at_4, s0_.completed_at AS completed_at_5, s1_.id AS id_6, s1_.photo AS photo_7, s1_.video_mp4 AS video_mp4_8, s1_.video_ogv AS video_ogv_9, s1_.5555_number AS 5555_number_10, s1_.threshold AS threshold_11, s0_.5555_id AS 5555_id_12, s0_.tt AS tt_13, s1_.vid AS vid_14 FROM 33 s0_ LEFT JOIN 99 s1_ ON s0_.5555_id = s1_.id WHERE s0_.tt = 350530","user":"2222[2222] @ server [192.168.1.1]","host":"server","os_user":"","ip":"192.168.1.1","db":"2222"}}
{"audit_record":{"name":"Query","record":"4007102462_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89038072","status":0,"sqltext":"SELECT t0.id AS id_1, t0.name AS name_2 FROM wetwtwr t0 INNER JOIN 88 ON t0.id = 88.tag_id WHERE 88.gid = 3329","user":"2222[2222] @ host1 [192.168.1.4]","host":"host1","os_user":"","ip":"192.168.1.4","db":"2222"}}
{"audit_record":{"name":"Query","record":"4007102463_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89034063","status":0,"sqltext":"SELECT t0.id AS id_1, t0.name AS name_2, t0.description AS description_3, t0.min_v AS min_v_4, t0.dgdfg_v_r AS dgdfg_v_r_5, t0.rerhht_r AS rerhht_r_6, t0.image AS image_7, t0.image_custom AS image_custom_8 FROM user_statuses t0 WHERE t0.id = 3","user":"2222[2222] @ server [192.168.1.1]","host":"server","os_user":"","ip":"192.168.1.1","db":"2222"}}
{"audit_record":{"name":"Query","record":"4007102464_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89038063","status":0,"sqltext":"SELECT t0.id AS id_1, t0.name AS name_2 FROM wetwtwr t0 INNER JOIN 88 ON t0.id = 88.tag_id WHERE 88.gid = 3442","user":"2222[2222] @ host1 [192.168.1.4]","host":"host1","os_user":"","ip":"192.168.1.4","db":"2222"}}
{"audit_record":{"name":"Query","record":"4007102465_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89036850","status":0,"sqltext":"SELECT u0_.activated_at AS activated_at_0, u0_.close_at AS close_at_1, u0_.created_at AS created_at_2, u0_.updated_at AS updated_at_3, u0_.status AS status_4, u0_.id AS id_5, u0_.vvvvvv AS vvvvvv_6, u0_.is_custom AS is_custom_7, u0_.444 AS 444_8, u0_.ttl_minutes AS ttl_minutes_9, u0_.55 AS 55_10, u0_.11 AS 11_11, u0_.66_on_line AS 66_on_line_12, u0_.44 AS 44_13, u0_.77 AS 77_14, u0_.ertret AS ertret_15, u0_.wqwqwq AS wqwqwq_16, u0_.tt AS tt_17, u0_.gid AS gid_18, u0_.rhht_id AS rhht_id_19, u0_.cid AS cid_20 FROM 333 u0_ INNER JOIN 1111 g1_ ON (u0_.gid = g1_.id) AND g1_.type_id IN ('9') WHERE u0_.tt = 350530 AND u0_.status = 2","user":"2222[2222] @ host2 [10.0.0.11]","host":"host2","os_user":"","ip":"10.0.0.11","db":"2222"}}
{"audit_record":{"name":"Query","record":"4007102466_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89035429","status":0,"sqltext":"SELECT t0.id AS id_1, t0.name AS name_2 FROM wetwtwr t0 INNER JOIN 88 ON t0.id = 88.tag_id WHERE 88.gid = 3315","user":"2222[2222] @ server [192.168.1.1]","host":"server","os_user":"","ip":"192.168.1.1","db":"2222"}}
{"audit_record":{"name":"Query","record":"4007102467_2019-01-10T04:44:06","timestamp":"2019-01-19T07:38:08 UTC","command_class":"select","connection_id":"89038124","status":0,"sqltext":"SELECT t0.id AS id_1, t0.v AS v_2, t0.required_same AS required_same_3, t0.required_999_same AS required_999_same_4, t0.999_v AS 999_v_5, t0.999_same AS 999_same_6, t0.eps_999_v AS eps_999_v_7, t0.total_in AS total_in_8, t0.total_out AS total_out_9, t0.version AS version_10, t0.v_updated_at AS v_updated_at_11, t0.cid AS cid_12, t0.tt AS tt_13 FROM wallets t0 WHERE t0.tt = 141108 AND t0.cid = 2 LIMIT 1","user":"2222[2222] @ host3 [192.168.1.2]","host":"host3","os_user":"","ip":"192.168.1.2","db":"2222"}}
Also I found optimal parameters in my case:
[INPUT]
Name tail
Parser docker
Path /mnt/sdb/mysql_audit_log/audit.log*
# ~1.2g mem usage for td-agent-bit
# Buffer_Max_Size 3M
# Buffer_Chunk_Size 3M
# Mem_Buf_Limit 400m
# ~3g mem usage for td-agent-bit
Buffer_Max_Size 5M
Buffer_Chunk_Size 5M
Mem_Buf_Limit 500m
DB /mnt/sdb/logs.db
Also I tried set only one log file, for example
[INPUT]
Name tail
Parser docker
Path /mnt/sdb/mysql_audit_log/audit.log
# ~1.2g mem usage for td-agent-bit
# Buffer_Max_Size 3M
# Buffer_Chunk_Size 3M
# Mem_Buf_Limit 400m
# ~3g mem usage for td-agent-bit
Buffer_Max_Size 5M
Buffer_Chunk_Size 5M
Mem_Buf_Limit 500m
DB /mnt/sdb/logs.db
The count of documents sent to the elasticksearch decreased by 2-3 times. For example max count documents 138870147 from 2019-01-20 03:00 to 2019-01-20 06:00 And max count after switch to single file 58757151 from 2019-01-20 18:00 to 2019-01-20 21:00. Also I haven't seen lock on the files. For example into the database:
_With Path /mnt/sdb/mysql_auditlog/audit.log*
1685 /mnt/sdb/mysql_audit_log/audit.log.320 1073774262 262065663 1547972905 0
1686 /mnt/sdb/mysql_audit_log/audit.log.301 1074021587 124665204 1547973044 0
1687 /mnt/sdb/mysql_audit_log/audit.log.179 1073742926 127 1547973099 0
1688 /mnt/sdb/mysql_audit_log/audit.log.191 1074021322 262065408 1547973150 0
1689 /mnt/sdb/mysql_audit_log/audit.log.184 1073936865 126 1547973267 0
1690 /mnt/sdb/mysql_audit_log/audit.log.225 1073793728 262065478 1547973365 0
1691 /mnt/sdb/mysql_audit_log/audit.log.200 1074156580 112 1547973414 0
1692 /mnt/sdb/mysql_audit_log/audit.log.312 1074160645 124665186 1547973514 0
1693 /mnt/sdb/mysql_audit_log/audit.log.315 1073965485 262128996 1547973600 0
1694 /mnt/sdb/mysql_audit_log/audit.log.347 1073905922 262065453 1547973685 0
1695 /mnt/sdb/mysql_audit_log/audit.log.248 1074159709 100 1547973755 0
1696 /mnt/sdb/mysql_audit_log/audit.log.295 704876486 124665211 1547973837 0
_With Path /mnt/sdb/mysql_auditlog/audit.log
363 /mnt/sdb/mysql_audit_log/audit.log.001 1073780412 262065409 1548075186 1
364 /mnt/sdb/mysql_audit_log/audit.log.001 1073763722 262065411 1548075422 1
365 /mnt/sdb/mysql_audit_log/audit.log.001 1074164412 66973828 1548075655 1
366 /mnt/sdb/mysql_audit_log/audit.log.001 1073753466 66973829 1548075873 1
367 /mnt/sdb/mysql_audit_log/audit.log.001 1074078537 262065414 1548076081 1
368 /mnt/sdb/mysql_audit_log/audit.log.001 1074125701 66973832 1548076313 1
369 /mnt/sdb/mysql_audit_log/audit.log.001 1074211244 262065419 1548076533 1
370 /mnt/sdb/mysql_audit_log/audit.log.001 1074204250 66973833 1548076764 1
371 /mnt/sdb/mysql_audit_log/audit.log.001 1073953327 66973834 1548076984 1
372 /mnt/sdb/mysql_audit_log/audit.log.001 1073877791 66973835 1548077213 1
373 /mnt/sdb/mysql_audit_log/audit.log 102215655 262065424 1548077433 0
And process ram usage decrease from 3g to 40-70 m
Why does fluent-bit create a greater load when specifying a directory and less when specifying a single file in the long run (several days)?
I would like to start td-agent without setup Path in in_tail section. And I need a some option (with time interval) for force re-read sqlite. Because I would like management Path variable dynamically via sqlite database.
This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 5 days. Maintainers can add the exempt-stale
label.
This issue was closed because it has been stalled for 5 days with no activity.
I use audit plugin from percona. I have ~ 400 log files, 1Gb per file. Audit plugin constantly creates new logs ~ 400-1000MB per minute. Also audit plugin rotate them.
For example:
I see the process td-agent-bit use only 80-97% cpu. Does the td-agent-bit process use only single core?
Also I see process locked many files after some time work, for example:
I see td-agent-bit read ~ 500-600 MB per minute from ssd drive. But audit plugin write ~ 300-1000 MB per minute (sdc device used only audit plugin and td-agent-bit)
ps output per minute
How I can optimize configuration for this scenario usage?
SOS Report