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)
1
0 Votes1 Posts20k Views
