tokudb very long query time on mysql Percona 5.7.21 TokuDB engine
91 views
Skip to first unread message
Igor Kremin'
Mar 29, 2018, 2:01:49 PM3/29/18
to percona-d...@googlegroups.com
Hello, I have dificults in query which executes on mysql Percona 5.7.21 TokuDBengine
The real query a little bit longer, but I shortet it to make it easy to reproduce, and real one takes ~5min and 0.068s on myisam engine
the query is picked up from log, and is generated by ORM of Django.
SELECT `product`.`id`, `product_manufacturer`.`id`, `product_source`.`id`
FROM `product`
INNER JOIN `product_manufacturer` ON ( `product`.`manufacturer_id` = `product_manufacturer`.`id` )
INNER JOIN `product_source` ON ( `product`.`source_id` = `product_source`.`id` )
ORDER BY `product`.`id` LIMIT 100
TokuDB: executes 1.25 m
MyISAM: executes 0.059s
TokuDB: without ORDER BY `product`.`id` executes 0.059s
Here are the tables structure.
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(54) COLLATE utf8_unicode_ci NOT NULL,
`num` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`number` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`manufacturer_id` int(11) NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`type` int(11) NOT NULL,
`group_id` int(11) DEFAULT NULL,
`union_id` int(11) DEFAULT NULL,
`images` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
`have_cars` tinyint(1) NOT NULL,
`weight` decimal(10,3) NOT NULL,
`price_min` decimal(10,2) NOT NULL,
`price_max` decimal(10,2) NOT NULL,
`created` date NOT NULL,
`modified` date NOT NULL,
`source_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `product_product_3aac1984` (`num`),
KEY `product_product_4ac7f441` (`manufacturer_id`),
KEY `product_product_425ae3c4` (`group_id`),
KEY `product_7607617b` (`source_id`),
KEY `product_65da3d2c` (`code`),
KEY `product_index_1` (`num`,`manufacturer_id`),
KEY `product_f429bc7` (`type`),
KEY `product_6c1a5129` (`union_id`),
KEY `only_number` (`number`),
KEY `weight` (`weight`),
KEY `price_min` (`price_min`),
KEY `manufacturer_price_min` (`manufacturer_id`,`price_min`),
KEY `price_max` (`price_max`),
KEY `manufacturer_price_max` (`manufacturer_id`,`price_max`)
) ENGINE=TokuDB AUTO_INCREMENT=7577491 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
has 7577280 records
CREATE TABLE `product_source` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`product_count` int(11) NOT NULL,
`cross_count` int(11) NOT NULL,
`is_active` tinyint(1) NOT NULL,
`group` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`comment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `product_source_55f56498` (`is_active`)
) ENGINE=TokuDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
has only 5 records
CREATE TABLE `product_manufacturer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`code` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
`slug` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`country` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`cars_count` int(11) NOT NULL,
`count` int(11) NOT NULL,
`products_count` int(11) NOT NULL,
`cross_count` int(11) NOT NULL,
`is_active` tinyint(1) NOT NULL,
`is_brand` tinyint(1) NOT NULL,
`make_car` tinyint(1) NOT NULL,
`make_truck` tinyint(1) NOT NULL,
`make_engine` tinyint(1) NOT NULL,
`oe` tinyint(1) NOT NULL,
`description` longtext COLLATE utf8_unicode_ci NOT NULL,
`image` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`h1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`text` longtext COLLATE utf8_unicode_ci,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `slug` (`slug`),
KEY `manufacturer_manufacturer_52094d6e` (`name`),
KEY `manufacturer_manufacturer_65da3d2c` (`code`),
KEY `manufacturer_7b4398b1` (`is_brand`),
KEY `manufacturer_3514e692` (`make_car`),
KEY `manufacturer_71a5415f` (`make_truck`),
KEY `manufacturer_6a707003` (`make_engine`),
KEY `product_manufacturer_17256f31` (`oe`),
KEY `product_manufacturer_1fe41c97` (`is_active`),
KEY `product_manufacturer_516bb1bd` (`count`),
KEY `product_manufacturer_ec9ad377` (`modified`),
KEY `name` (`name`),
KEY `products_count` (`products_count`),
KEY `cross_count` (`cross_count`),
KEY `id__is_active` (`id`,`is_active`),
KEY `is_brand_is_active` (`is_active`,`is_brand`)
) ENGINE=TokuDB AUTO_INCREMENT=11925 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
has 3081 records
The tables on myisam a little bit modifed thay are not not fully equivalent, in this case it is no matter.
And here is TOKUDB explain of these queries:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "8908393.67"
},
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "2620115.00"
},
"nested_loop": [
{
"table": {
"table_name": "product_source",
"access_type": "index",
"possible_keys": [
"PRIMARY"
],
"key": "product_source_55f56498",
"used_key_parts": [
"is_active"
],
"key_length": "1",
"rows_examined_per_scan": 5,
"rows_produced_per_join": 5,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1.67",
"eval_cost": "1.00",
"prefix_cost": "2.67",
"data_read_per_join": "5K"
},
"used_columns": [
"id"
]
}
},
{
"table": {
"table_name": "product",
"access_type": "ref",
"possible_keys": [
"product_product_4ac7f441",
"product_7607617b",
"manufacturer_price_min",
"manufacturer_price_max"
],
"key": "product_7607617b",
"used_key_parts": [
"source_id"
],
"key_length": "4",
"ref": [
],
"rows_examined_per_scan": 524023,
"rows_produced_per_join": 2620115,
"filtered": "100.00",
"cost_info": {
"read_cost": "2620115.00",
"eval_cost": "524023.00",
"prefix_cost": "3144140.67",
"data_read_per_join": "10G"
},
"used_columns": [
"id",
"manufacturer_id",
"source_id"
]
}
},
{
"table": {
"table_name": "product_manufacturer",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"id__is_active"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"gvein5.product.manufacturer_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2620115,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "2620115.00",
"eval_cost": "524023.00",
"prefix_cost": "6288278.67",
"data_read_per_join": "11G"
},
"used_columns": [
"id"
]
}
}
]
}
}
}
MYISAM explain
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "18448056.13"
},
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "14674803.22"
},
"nested_loop": [
{
"table": {
"table_name": "product",
"access_type": "ALL",
"possible_keys": [
"product_product_4ac7f441",
"product_7607617b",
"manufacturer_price_min",
"manufacturer_price_max"
],
"rows_examined_per_scan": 7577355,
"rows_produced_per_join": 7577355,
"filtered": "100.00",
"cost_info": {
"read_cost": "262380.09",
"eval_cost": "1515471.00",
"prefix_cost": "1777851.09",
"data_read_per_join": "30G"
},
"used_columns": [
"id",
"manufacturer_id",
"source_id"
]
}
},
{
"table": {
"table_name": "product_source",
"access_type": "index",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 5,
"rows_produced_per_join": 7577355,
"filtered": "20.00",
"using_index": true,
"using_join_buffer": "Block Nested Loop",
"cost_info": {
"read_cost": "24.01",
"eval_cost": "1515471.02",
"prefix_cost": "9355230.11",
"data_read_per_join": "7G"
},
"used_columns": [
"id"
],
"attached_condition": "(`etalon`.`product_source`.`id` = `etalon`.`product`.`source_id`)"
}
},
{
"table": {
"table_name": "product_manufacturer",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"id__is_active"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"etalon.product.manufacturer_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 7577355,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "7577355.00",
"eval_cost": "1515471.02",
"prefix_cost": "18448056.13",
"data_read_per_join": "32G"
},
"used_columns": [
"id"
]
}
}
]
}
}
}
I can give the dump of these tables to reproduce it.
As I can see the first table in explain in case of myisam is product and in tokudb is product_source.
Is any way to speed up this query?
Roel Van de Paar
Apr 6, 2018, 9:57:01 AM4/6/18
to Percona Discussion
One idea that came to mind when reading your question was to force an index. But I am not sure if TokuDB supports this.
https://dev.mysql.com/doc/refman/5.7/en/index-hints.html
Unless the query was faster in older releases, this seems to be a question which would suit well into Percona's (paid) support services offerings.
https://dev.mysql.com/doc/refman/5.7/en/index-hints.html
Unless the query was faster in older releases, this seems to be a question which would suit well into Percona's (paid) support services offerings.
Igor Kremin'
Apr 7, 2018, 9:59:30 AM4/7/18
to Percona Discussion
I tried this query
SELECT `product`.`id`,
`product_manufacturer`.`id`,
`product_source`.`id`
FROM `product` USE INDEX (`PRIMARY`)
INNER JOIN `product_manufacturer` ON ( `product`.`manufacturer_id` = `product_manufacturer`.`id` )
INNER JOIN `product_source` ON ( `product`.`source_id` = `product_source`.`id` )
ORDER BY `product`.`id` DESC LIMIT 100
Now it takes ~ 17s but it is seems to be also long.
Roel Van de Paar
Apr 9, 2018, 6:09:59 AM4/9/18
to Percona Discussion
Hi Igor,
That is a great improvement!
For further help, I recommend a Percona support services offering;
George Lorch
Apr 10, 2018, 1:27:31 PM4/10/18
to Percona Discussion
It is not terribly useful to try to compare TokuDB (or InnoDB) performance to MyISAM. MyISAM is not ACID compliant, is not durable, nor transactional. All of these things have significant impact on an engines performance. It is more 'fair' to compare TokuDB to InnoDB. Also, TokuDB is a write optimized engine, not read optimized, so, there are almost no cases where the read (SELECT) performance of TokuDB would be equal to or faster than InnoDB and MyISAM.
That said, there are a few bugs that have been recently fixed regarding index cardinality and row count accuracy. If you have been hit by these, it is very possible that the optimizer is basically guessing and performing full table scans.
If you can, on the TokuDB tables involved, provide a "SHOW INDEXES ON <table>" and "SHOW TABLE STATUS LIKE 'table'" for each and I can try to determine if you have some of these cardinality and ow count issues.
--
George O. Lorch III
Principal Software Engineer, Percona
US/Arizona (GMT -7)
Igor Kremin'
Apr 11, 2018, 2:32:50 PM4/11/18
to Percona Discussion
Thank you for reply.
I attached the data which you asked.
I am worring that all joins are on primary index and only first table has huge data other two have 3081 and 5 records accordingly.
I made some changes to query to test perfomance, so I replace INNER JOIN to LEFT JOIN and it takes 0.063s even without USE INDEX (`PRIMARY`)
SELECT `product`.`id`,
`product_manufacturer`.`id`,
`product_source`.`id`
FROM `product`
LEFT JOIN `product_manufacturer` ON ( `product`.`manufacturer_id` = `product_manufacturer`.`id` )
LEFT JOIN `product_source` ON ( `product`.`source_id` = `product_source`.`id` )
ORDER BY `product`.`id` DESC LIMIT 100
Whether the difference between INNER JOIN and LEFT JOIN should be so terrible? 0.063 and 17s
Also I tried it with innodb engine: the same behaviour as wtih tokudb so it is not engine problem.
вівторок, 10 квітня 2018 р. 20:27:31 UTC+3 користувач George Lorch написав:
Roel Van de Paar
May 1, 2018, 7:46:28 PM5/1/18
to Percona Discussion
Hi Igor,
I discussed your issue with George this morning. This is a reply from both of us.
The earlier query re-write suggestion showed an improvement in your result, along with no 'cardinality 0' in index stats points to some sub-optimal query against mysql that will be problematic regardless of engine.
PS. 'force index' or index hints is not a function of the storage engine, but the optimizer code, so it should always work the same across engines unless an engine is doing something incorrectly (which is always a possibility, but shouldn't be in this case).
We recommend a Percona support services offering;
Thank you & God bless
Reply all
Reply to author
Forward
0 new messages