syslog table Index
-
Hi,
I noticed that when I do a search in Syslog, it appears reasonably fast.
But if I select a device, right-click and select Syslog it can take forever to load anything.So I had a look at the database and found the table only has an index on the msg_timestamp (and msg_id) column, but not the source_object_id one.
Of course I could add one manually (and did for now, which sped things up significantly), but that might cause issues with future NetXMS database upgrades down the track?Cheers
-
-
Hi,
I've added to new indexes in 2.1-M3:
CREATE INDEX idx_syslog_source ON syslog(source_object_id);
CREATE INDEX idx_snmp_trap_log_oid ON snmp_trap_log(object_id);Best regards,
Victor -
Yes, I also long been noticed and reported to the developers.
Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument # Time: 161205 12:13:27 # User@Host: netxms[netxms] @ [127.0.0.1] # Query_time: 101.202518 Lock_time: 0.000060 Rows_sent: 30 Rows_examined: 6502043 use netxms; SET timestamp=1480929207; SELECT msg_timestamp,source_object_id,facility,severity,hostname,msg_tag,msg_text FROM syslog WHERE msg_id<=11313990 AND (source_object_id = 38025) ORDER BY msg_timestamp DESC LIMIT 1000; mysql> EXPLAIN SELECT msg_timestamp,source_object_id,facility,severity,hostname,msg_tag,msg_text FROM syslog WHERE msg_id<=11313990 AND (source_object_id = 38025) ORDER BY msg_timestamp DESC LIMIT 1000; +----+-------------+--------+-------+---------------+--------------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+--------------------------+---------+------+------+-------------+ | 1 | SIMPLE | syslog | index | PRIMARY | idx_syslog_msg_timestamp | 4 | NULL | 2000 | Using where | +----+-------------+--------+-------+---------------+--------------------------+---------+------+------+-------------+ 1 row in set (0.00 sec) Uptime: 87 days 15 hours 26 min 22 sec Threads: 14 Questions: 3302913014 Slow queries: 504 Opens: 5677 Flush tables: 1 Open tables: 4130 Queries per second avg: 436.178 select count(*) from syslog; +----------+ | count(*) | +----------+ | 6502281 | +----------+ 1 row in set (2.01 sec) mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> show profile for query 1; +--------------------------------+-----------+ | Status | Duration | +--------------------------------+-----------+ | starting | 0.000085 | | Waiting for query cache lock | 0.000007 | | checking query cache for query | 0.000074 | | checking permissions | 0.000010 | | Opening tables | 0.000029 | | System lock | 0.000012 | | Waiting for query cache lock | 0.000034 | | init | 0.000035 | | optimizing | 0.000019 | | statistics | 0.000103 | | preparing | 0.000047 | | executing | 0.000008 | | Sorting result | 0.000012 | | Sending data | 19.243185 | | end | 0.000030 | | query end | 0.000012 | | closing tables | 0.000011 | | freeing items | 0.000024 | | logging slow query | 0.000006 | | logging slow query | 0.000097 | | cleaning up | 0.000009 | +--------------------------------+-----------+ 21 rows in set (0.00 sec) EXPLAIN SELECT msg_timestamp,source_object_id,facility,severity,hostname,msg_tag,msg_text FROM syslog WHERE msg_id<=9880640 AND (source_object_id = 29124) ORDER BY msg_timestamp DESC LIMIT 1000; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: syslog type: index possible_keys: PRIMARY key: idx_syslog_msg_timestamp key_len: 4 ref: NULL rows: 2000 Extra: Using where 1 row in set (0.00 sec)show create table syslog; +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | syslog | CREATE TABLE `syslog` ( `msg_id` bigint(20) NOT NULL, `msg_timestamp` int(11) NOT NULL, `facility` int(11) NOT NULL, `severity` int(11) NOT NULL, `source_object_id` int(11) NOT NULL, `hostname` varchar(127) DEFAULT NULL, `msg_tag` varchar(32) DEFAULT NULL, `msg_text` text, PRIMARY KEY (`msg_id`), KEY `idx_syslog_msg_timestamp` (`msg_timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> SELECT msg_timestamp,source_object_id,facility,severity,hostname,msg_tag,msg_text FROM syslog WHERE msg_id<=15375265 AND (source_object_id = 11102) ORDER BY msg_timestamp DESC LIMIT 1000; 1000 rows in set (23.48 sec) CREATE INDEX idx_syslog_source ON syslog(source_object_id); Query OK, 0 rows affected (43.65 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT msg_timestamp,source_object_id,facility,severity,hostname,msg_tag,msg_text FROM syslog WHERE msg_id<=15375265 AND (source_object_id = 11102) ORDER BY msg_timestamp DESC LIMIT 1000; 1000 rows in set (0.11 sec) show index from syslog\G 1. row Table: syslog Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: msg_id Collation: A Cardinality: 7444594 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2. row Table: syslog Non_unique: 1 Key_name: idx_syslog_msg_timestamp Seq_in_index: 1 Column_name: msg_timestamp Collation: A Cardinality: 7444594 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 3. row Table: syslog Non_unique: 1 Key_name: idx_syslog_source Seq_in_index: 1 Column_name: source_object_id Collation: A Cardinality: 200 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 3 rows in set (0.00 sec)
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better π
Register Login