Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.11.7
Description
Here is my table :
MariaDB [pmacontrol]> show create table mysql_server\G
|
*************************** 1. row *************************** |
Table: mysql_server
|
Create Table: CREATE TABLE `mysql_server` (
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
`id_client` int(11) NOT NULL, |
`id_environment` int(11) NOT NULL, |
`name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, |
`display_name` varchar(100) NOT NULL, |
`ip` char(15) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, |
`hostname` varchar(200) NOT NULL DEFAULT '', |
`login` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, |
`passwd` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, |
`database` varchar(64) NOT NULL, |
`is_password_crypted` int(11) NOT NULL, |
`port` int(11) NOT NULL, |
`ssh_port` int(11) NOT NULL DEFAULT 22, |
`ssh_login` text CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '\'\'', |
`is_sudo` int(11) NOT NULL DEFAULT 0, |
`is_root` int(11) NOT NULL DEFAULT 1, |
`is_monitored` int(11) NOT NULL DEFAULT 1, |
`is_proxy` int(11) NOT NULL DEFAULT 0, |
`is_available` int(11) NOT NULL DEFAULT 0 COMMENT '-1= not asnwered, 0 = HS, 1=OK', |
`is_acknowledged` int(11) NOT NULL DEFAULT 0, |
`error` text NOT NULL DEFAULT '', |
`warning` text NOT NULL DEFAULT '', |
`date_refresh` datetime NOT NULL DEFAULT current_timestamp(),
|
`ssh_available` int(11) NOT NULL DEFAULT 0 COMMENT '-1= not asnwered, 0 = HS, 1=OK', |
`ssh_date_refresh` datetime NOT NULL DEFAULT current_timestamp(),
|
`ssh_error` text NOT NULL DEFAULT '', |
PRIMARY KEY (`id`),
|
UNIQUE KEY `name` (`name`),
|
UNIQUE KEY `ip` (`ip`,`port`),
|
KEY `id_client` (`id_client`),
|
KEY `id_environment` (`id_environment`),
|
KEY `is_monitored` (`is_monitored`,`id_client`),
|
CONSTRAINT `mysql_server_ibfk_1` FOREIGN KEY (`id_client`) REFERENCES `client` (`id`),
|
CONSTRAINT `mysql_server_ibfk_2` FOREIGN KEY (`id_environment`) REFERENCES `environment` (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci WITH SYSTEM VERSIONING |
1 row in set (0,000 sec) |
the number of line in my table :
MariaDB [pmacontrol]> select table_name, table_rows from information_schema.tables where table_name = 'mysql_server'; |
+--------------+------------+
|
| table_name | table_rows |
|
+--------------+------------+
|
| mysql_server | 16377356 | |
+--------------+------------+
|
1 row in set (0,001 sec) |
Ok about 16 377 356 (with system versioning), but without this my table is about *13 lines *
when i try to the lines :
MariaDB [pmacontrol]> select count(1) from mysql_server; |
+----------+
|
| count(1) | |
+----------+
|
| 13 | |
+----------+
|
1 row in set (25,079 sec) |
OMG ! 25 secondes, if ask for lines it's about (4 min 5,566 sec)
now if ask distinct on table :
MariaDB [pmacontrol]> select distinct id from mysql_server;
|
+----+
|
| id |
|
+----+
|
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 | |
| 11 | |
| 12 | |
| 13 | |
+----+
|
13 rows in set (0,000 sec) |
cool it's pretty fast !
work around i found :
MariaDB [pmacontrol]> select count(1) from (select distinct id from mysql_server) as gg; |
+----------+
|
| count(1) | |
+----------+
|
| 13 | |
+----------+
|
1 row in set (0,001 sec) |
For select * from mysql_server => 4 min 5,566 sec
work around :
with t as (select distinct id from mysql_server) select * from mysql_server a inner join t on a.id=t.id;
|
|
|
13 rows in set (0,001 sec) |
If I can find a workaround in mysql, I am sure you can increase the performance internally !
Attachments
Activity
If I understood it right, the record count in versioned tables will have to ignore all rows that do not belong to the current version. As far as I understand, a full table scan is always needed. (For the record, MyISAM and Aria persistently store the number of rows in the data file, but InnoDB does not; MDEV-18188 has been filed for that.)
The versioning metadata should be available in the primary key index, or in case of heap-organized tables (such as with ENGINE=MyISAM or ENGINE=Aria) in the data heap. According to the EXPLAIN output, the query appears to use a secondary index.
I am guessing here, but I can imagine that by visiting secondary index records, a candidate set of row identifiers is constructed. This could amount to the entire set of all (id,row_start_id) pairs. Then, each of these records would be looked up individually. It would be much more efficient to just scan the entire primary key index sequentially.
Can you try adding FORCE INDEX(PRIMARY) to your query? Would it be a successful work-around of this bug?
I’m only familiar with some low level details of system versioning, so I don’t know how to dump a system-versioned table. I have the impression that there should be a way to do that, to facilitate logical backups.
I think that this needs to be fixed in the optimizer so that COUNT or other full-table traversal will avoid using secondary indexes when filtering by the versioning columns is needed.
Other way i was thinking, add a field not versioned and index it for normal query.
In internal it's could be a solution ?
Aurelien_LEQUOY, as far as I understand, if there is any versioned column in the table, hidden columns for "version start" and "version end" timestamps will be added. Each DELETE will then not actually delete any rows, but only update the "version end" timestamp of the affected from "infinite" to "now". Similarly, each UPDATE that affects versioned columns will be executed as an UPDATE of "version end" of the current row, plus an INSERT of a new row where "version start" is set to "now".
You did not answer my question: Would adding FORCE INDEX(PRIMARY) lead to a faster execution time?
stupid question how dump a table with system versioning with mysqldump ?