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
| Field | Original Value | New Value |
|---|---|---|
| Epic Colour | ghx-label-3 | |
| Epic Name | MariaDB with SYSTEM VERSIONING | |
| Epic Status | To Do [ 10100 ] | |
| Issue Type | Epic [ 5 ] | Bug [ 1 ] |
| Labels | versioned-table |
| Assignee | Marko Mäkelä [ marko ] |
| Affects Version/s | 10.11.7 [ 29519 ] |
| Fix Version/s | 10.11 [ 27614 ] |
Can you please post the output of your SELECT statement prepended with ANALYZE FORMAT=JSON? Also, what does the query plan look like?
ANALYZE FORMAT=JSON select count(1) from mysql_server; |
EXPLAIN select count(1) from mysql_server; |
It could also make sense to enable https://mariadb.com/kb/en/optimizer-trace-overview/ to understand what is going on.
Please check if this report could be a duplicate of MDEV-31227. If the table initially was not cached in the InnoDB buffer pool, executing the first SELECT statement could force it to be loaded, and subsequent statements would be faster, given that the data is resident in the buffer pool.
I think that further analysis of this would belong to the optimizer team.
| Assignee | Marko Mäkelä [ marko ] | Sergei Petrunia [ psergey ] |
| Status | Open [ 1 ] | Needs Feedback [ 10501 ] |
made on an other server : (10.6.15)
MariaDB [pmacontrol]> ANALYZE FORMAT=JSON select count(1) from mysql_server\G |
*************************** 1. row *************************** |
ANALYZE: {
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 1509.019, |
"table": { |
"table_name": "mysql_server", |
"access_type": "index", |
"key": "id_client", |
"key_length": "4", |
"used_key_parts": ["id_client"], |
"r_loops": 1, |
"rows": 2599817, |
"r_rows": 3090782, |
"r_table_time_ms": 478.095, |
"r_other_time_ms": 1030.919, |
"r_engine_stats": { |
"pages_accessed": 5061, |
"old_rows_read": 1 |
},
|
"filtered": 100, |
"r_filtered": 3.235427e-4, |
"attached_condition": "mysql_server.row_end = TIMESTAMP'2038-01-19 04:14:07.999999'", |
"using_index": true |
}
|
}
|
}
|
1 row in set (1,509 sec) |
MariaDB [pmacontrol]> EXPLAIN select count(1) from mysql_server; |
+------+-------------+--------------+-------+---------------+-----------+---------+------+---------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------+-------+---------------+-----------+---------+------+---------+--------------------------+
|
| 1 | SIMPLE | mysql_server | index | NULL | id_client | 4 | NULL | 2599820 | Using where; Using index | |
+------+-------------+--------------+-------+---------------+-----------+---------+------+---------+--------------------------+
|
1 row in set (0,000 sec) |
|
MariaDB [pmacontrol]> analyze select count(1) from mysql_server; |
+------+-------------+--------------+-------+---------------+-----------+---------+------+---------+------------+----------+------------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+--------------+-------+---------------+-----------+---------+------+---------+------------+----------+------------+--------------------------+
|
| 1 | SIMPLE | mysql_server | index | NULL | id_client | 4 | NULL | 2599823 | 3090788.00 | 100.00 | 0.00 | Using where; Using index | |
+------+-------------+--------------+-------+---------------+-----------+---------+------+---------+------------+----------+------------+--------------------------+
|
1 row in set (1,292 sec) |
MariaDB [pmacontrol]> select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE \G
|
*************************** 1. row *************************** |
QUERY: analyze select count(1) from mysql_server |
TRACE: {
|
"steps": [ |
{
|
"join_preparation": { |
"select_id": 1, |
"steps": [ |
{
|
"expanded_query": "select count(1) AS `count(1)` from mysql_server" |
}
|
]
|
}
|
},
|
{
|
"join_optimization": { |
"select_id": 1, |
"steps": [ |
{
|
"condition_processing": { |
"condition": "WHERE", |
"original_condition": "mysql_server.row_end = TIMESTAMP'2038-01-19 04:14:07.999999'", |
"steps": [ |
{
|
"transformation": "equality_propagation", |
"resulting_condition": "multiple equal(TIMESTAMP'2038-01-19 04:14:07.999999', mysql_server.row_end)" |
},
|
{
|
"transformation": "constant_propagation", |
"resulting_condition": "multiple equal(TIMESTAMP'2038-01-19 04:14:07.999999', mysql_server.row_end)" |
},
|
{
|
"transformation": "trivial_condition_removal", |
"resulting_condition": "multiple equal(TIMESTAMP'2038-01-19 04:14:07.999999', mysql_server.row_end)" |
}
|
]
|
}
|
},
|
{
|
"table_dependencies": [ |
{
|
"table": "mysql_server", |
"row_may_be_null": false, |
"map_bit": 0, |
"depends_on_map_bits": [] |
}
|
]
|
},
|
{
|
"ref_optimizer_key_uses": [] |
},
|
{
|
"rows_estimation": [ |
{
|
"selectivity_for_indexes": [], |
"selectivity_for_columns": [], |
"cond_selectivity": 1 |
},
|
{
|
"table": "mysql_server", |
"table_scan": { |
"rows": 2599825, |
"cost": 55101 |
}
|
}
|
]
|
},
|
{
|
"considered_execution_plans": [ |
{
|
"plan_prefix": [], |
"table": "mysql_server", |
"best_access_path": { |
"considered_access_paths": [ |
{
|
"access_type": "scan", |
"resulting_rows": 2599825, |
"cost": 55101, |
"chosen": true |
}
|
],
|
"chosen_access_method": { |
"type": "scan", |
"records": 2599825, |
"cost": 55101, |
"uses_join_buffering": false |
}
|
},
|
"rows_for_plan": 2599825, |
"cost_for_plan": 575066 |
}
|
]
|
},
|
{
|
"best_join_order": ["mysql_server"] |
},
|
{
|
"substitute_best_equal": { |
"condition": "WHERE", |
"resulting_condition": "mysql_server.row_end = TIMESTAMP'2038-01-19 04:14:07.999999'" |
}
|
},
|
{
|
"attaching_conditions_to_tables": { |
"attached_conditions_computation": [], |
"attached_conditions_summary": [ |
{
|
"table": "mysql_server", |
"attached": "mysql_server.row_end = TIMESTAMP'2038-01-19 04:14:07.999999'" |
}
|
]
|
}
|
}
|
]
|
}
|
},
|
{
|
"join_execution": { |
"select_id": 1, |
"steps": [] |
}
|
}
|
]
|
}
|
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 |
INSUFFICIENT_PRIVILEGES: 0 |
1 row in set (0,050 sec) |
|
now with upgrade
after upgrade in 10.11.7
MariaDB [pmacontrol]> explain select count(1) from mysql_server; |
+------+-------------+--------------+-------+---------------+-----------+---------+------+---------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------+-------+---------------+-----------+---------+------+---------+--------------------------+
|
| 1 | SIMPLE | mysql_server | index | NULL | id_client | 4 | NULL | 2599816 | Using where; Using index | |
+------+-------------+--------------+-------+---------------+-----------+---------+------+---------+--------------------------+
|
1 row in set (0,000 sec) |
|
|
|
|
MariaDB [pmacontrol]> analyze format=JSON select count(1) from mysql_server\G |
*************************** 1. row *************************** |
ANALYZE: {
|
"query_optimization": { |
"r_total_time_ms": 0.068646712 |
},
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 1543.966563, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "mysql_server", |
"access_type": "index", |
"key": "id_client", |
"key_length": "4", |
"used_key_parts": ["id_client"], |
"r_loops": 1, |
"rows": 2599817, |
"r_rows": 3090836, |
"r_table_time_ms": 480.1041612, |
"r_other_time_ms": 1063.856876, |
"r_engine_stats": { |
"pages_accessed": 3956 |
},
|
"filtered": 100, |
"r_filtered": 3.235371e-4, |
"attached_condition": "mysql_server.row_end = TIMESTAMP'2038-01-19 04:14:07.999999'", |
"using_index": true |
}
|
}
|
]
|
}
|
}
|
1 row in set (1,543 sec) |
|
|
|
|
MariaDB [pmacontrol]> select version();
|
+-------------------------------------------+
|
| version() |
|
+-------------------------------------------+
|
| 10.11.7-MariaDB-1:10.11.7+maria~deb11-log | |
+-------------------------------------------+
|
1 row in set (0,000 sec) |
MariaDB [pmacontrol]> select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
|
*************************** 1. row *************************** |
QUERY: select * from mysql_server
|
TRACE: {
|
"steps": [ |
{
|
"join_preparation": { |
"select_id": 1, |
"steps": [ |
{
|
"expanded_query": "select mysql_server.`id` AS `id`,mysql_server.id_client AS id_client,mysql_server.id_environment AS id_environment,mysql_server.`name` AS `name`,mysql_server.display_name AS display_name,mysql_server.ip AS ip,mysql_server.hostname AS hostname,mysql_server.login AS login,mysql_server.passwd AS passwd,mysql_server.`database` AS `database`,mysql_server.is_password_crypted AS is_password_crypted,mysql_server.`port` AS `port`,mysql_server.ssh_port AS ssh_port,mysql_server.ssh_login AS ssh_login,mysql_server.is_sudo AS is_sudo,mysql_server.is_root AS is_root,mysql_server.is_monitored AS is_monitored,mysql_server.is_proxy AS is_proxy,mysql_server.is_available AS is_available,mysql_server.is_acknowledged AS is_acknowledged,mysql_server.`error` AS `error`,mysql_server.warning AS warning,mysql_server.date_refresh AS date_refresh,mysql_server.ssh_available AS ssh_available,mysql_server.ssh_date_refresh AS ssh_date_refresh,mysql_server.ssh_error AS ssh_error from mysql_server" |
}
|
]
|
}
|
},
|
{
|
"join_optimization": { |
"select_id": 1, |
"steps": [ |
{
|
"condition_processing": { |
"condition": "WHERE", |
"original_condition": "mysql_server.row_end = TIMESTAMP'2038-01-19 04:14:07.999999'", |
"steps": [ |
{
|
"transformation": "equality_propagation", |
"resulting_condition": "multiple equal(TIMESTAMP'2038-01-19 04:14:07.999999', mysql_server.row_end)" |
},
|
{
|
"transformation": "constant_propagation", |
"resulting_condition": "multiple equal(TIMESTAMP'2038-01-19 04:14:07.999999', mysql_server.row_end)" |
},
|
{
|
"transformation": "trivial_condition_removal", |
"resulting_condition": "multiple equal(TIMESTAMP'2038-01-19 04:14:07.999999', mysql_server.row_end)" |
}
|
]
|
}
|
},
|
{
|
"table_dependencies": [ |
{
|
"table": "mysql_server", |
"row_may_be_null": false, |
"map_bit": 0, |
"depends_on_map_bits": [] |
}
|
]
|
},
|
{
|
"ref_optimizer_key_uses": [] |
},
|
{
|
"rows_estimation": [ |
{
|
"selectivity_for_indexes": [], |
"selectivity_for_columns": [], |
"cond_selectivity": 1 |
},
|
{
|
"table": "mysql_server", |
"table_scan": { |
"rows": 2599822, |
"cost": 55101 |
}
|
}
|
]
|
},
|
{
|
"considered_execution_plans": [ |
{
|
"plan_prefix": [], |
"get_costs_for_tables": [ |
{
|
"best_access_path": { |
"table": "mysql_server", |
"considered_access_paths": [ |
{
|
"access_type": "scan", |
"resulting_rows": 2599822, |
"cost": 55101, |
"chosen": true |
}
|
],
|
"chosen_access_method": { |
"type": "scan", |
"records": 2599822, |
"cost": 55101, |
"uses_join_buffering": false |
}
|
}
|
}
|
]
|
},
|
{
|
"plan_prefix": [], |
"table": "mysql_server", |
"rows_for_plan": 2599822, |
"cost_for_plan": 575065.4 |
}
|
]
|
},
|
{
|
"best_join_order": ["mysql_server"] |
},
|
{
|
"substitute_best_equal": { |
"condition": "WHERE", |
"resulting_condition": "mysql_server.row_end = TIMESTAMP'2038-01-19 04:14:07.999999'" |
}
|
},
|
{
|
"attaching_conditions_to_tables": { |
"attached_conditions_computation": [], |
"attached_conditions_summary": [ |
{
|
"table": "mysql_server", |
"attached": "mysql_server.row_end = TIMESTAMP'2038-01-19 04:14:07.999999'" |
}
|
]
|
}
|
}
|
]
|
}
|
},
|
{
|
"join_execution": { |
"select_id": 1, |
"steps": [] |
}
|
}
|
]
|
}
|
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 |
INSUFFICIENT_PRIVILEGES: 0 |
1 row in set (0,001 sec) |
stupid question how dump a table with system versioning with mysqldump ?
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.
| Component/s | Versioned Tables [ 14303 ] |
| Status | Needs Feedback [ 10501 ] | Open [ 1 ] |
| Assignee | Sergei Petrunia [ psergey ] | Marko Mäkelä [ marko ] |
| Status | Open [ 1 ] | Needs Feedback [ 10501 ] |
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.
| Assignee | Marko Mäkelä [ marko ] | Sergei Petrunia [ psergey ] |
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?
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Resolution | Incomplete [ 4 ] | |
| Status | Needs Feedback [ 10501 ] | Closed [ 6 ] |
MariaDB [pmacontrol]> select version();+-------------------------------------------+| version() |+-------------------------------------------++-------------------------------------------+