Percona-Server: slow TokuDB queries after upgrading from 5.6 to 5.7. ANALYZE TABLE doesn't resolve the...
What is this mysterious *green square* on my Content Editor?
Can someone explain what a key is?
Is the tritone (A4 / d5) still banned in Roman Catholic music?
What could cause an entire planet of humans to become aphasic?
Why write a book when there's a movie in my head?
How to transport 10,000 terrestrial trolls across ocean fast?
Why does a single AND gate need 60 transistors?
Why does this quiz question say that protons and electrons do not combine to form neutrons?
Manager has noticed coworker's excessive breaks. Should I warn him?
What is an efficient way to digitize a family photo collection?
Buying a "Used" Router
How do I add a strong "onion flavor" to the biryani (in restaurant style)?
Is layered encryption more secure than long passwords?
What is an explicit bijection in combinatorics?
Can a planet be tidally unlocked?
Is there any danger of my neighbor having my wife's signature?
How can guns be countered by melee combat without raw-ability or exceptional explanations?
Partial derivative with respect to three variables
How many copper coins fit inside a cubic foot?
Have any astronauts or cosmonauts died in space?
Sets that are both Sum-free and Product-free
When does a person lose diplomatic status?
Missing a connection and don't have money to book next flight
Is there a way to pause a running process on Linux systems and resume later?
Percona-Server: slow TokuDB queries after upgrading from 5.6 to 5.7. ANALYZE TABLE doesn't resolve the problem
Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosenHigh Disk IO, How to mitigate?EXPLAIN output suggests that my index is not being usedIdentical query, tables, but different EXPLAIN and performanceWhy does IN (subquery) perform bad when = (subquery) is blazing fast?Deciding which MySQL execution plan is betterOptimizing a simple query on a large tableMySQL COUNT(*) performanceSpeed slow down on a UNION ALL viewselect MAX() from MySQL view (2x INNER JOIN) is slowPerformance of mysql equi-join observed in HDD and SSD
After upgrading from Percona-TokuDB 5.6.29-76.2 to 5.7.19-17 we see some very slow queries on some tables without primary keys, but multiple non-unique indexes. The box we migrated to is pretty well equipped (768 GB RAM, PCIe SSDs). We used mysql_upgrade after migration.
After investigating Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen we tried ANALYZE TABLE, even with RECOUNT_ROWS, REPAIR TABLE, ALTER TABLE *** FORCE without any effect.
Typical table structure:
CREATE TABLE `letter_archiv_12375` (
`user_id` int(12) unsigned NOT NULL DEFAULT '0',
`letter_id` mediumint(6) unsigned NOT NULL DEFAULT '0',
`crypt_id` bigint(12) unsigned NOT NULL DEFAULT '0',
`mailerror` tinyint(1) unsigned NOT NULL DEFAULT '0',
`unsubscribe` tinyint(1) unsigned NOT NULL DEFAULT '0',
`send_date` date NOT NULL,
`code` varchar(255) NOT NULL DEFAULT '',
KEY `crypt_id` (`crypt_id`),
KEY `letter_id` (`letter_id`),
KEY `user_id` (`user_id`)
) ENGINE=TokuDB
A simple query like that takes 4 seconds on a table with 200m rows.
UPDATE hoovie_1.letter_archiv_14167
SET unsubscribe = 1
WHERE letter_id = "784547"
AND user_id = "2881564";
The cardinality values are correct. EXPLAIN will result in:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE letter_archiv_14167 NULL range letter_id,user_id letter_id 3 const 1 100.00 Using where
The only solution is to remove and re-create at least one index. After dropping and re-creating the index letter_id the table will perform well (in 0.01 s).
The EXPLAIN will change to
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE letter_archiv_14167 NULL range user_id,letter_id user_id 4 const 99 100.00 Using where
We have some thousands of TokuDB tables in production - a performance loss of factor 300-500 is a problem.
So we are unsure to migrate to 5.7 - this behaviour could occur even after re-creating all indexes again.
Any ideas?
mysql upgrade percona tokudb
asked Dec 18 '17 at 17:55
Ralf EnglerRalf Engler
61
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
After upgrading from Percona-TokuDB 5.6.29-76.2 to 5.7.19-17 we see some very slow queries on some tables without primary keys, but multiple non-unique indexes. The box we migrated to is pretty well equipped (768 GB RAM, PCIe SSDs). We used mysql_upgrade after migration.
After investigating Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen we tried ANALYZE TABLE, even with RECOUNT_ROWS, REPAIR TABLE, ALTER TABLE *** FORCE without any effect.
Typical table structure:
CREATE TABLE `letter_archiv_12375` (
`user_id` int(12) unsigned NOT NULL DEFAULT '0',
`letter_id` mediumint(6) unsigned NOT NULL DEFAULT '0',
`crypt_id` bigint(12) unsigned NOT NULL DEFAULT '0',
`mailerror` tinyint(1) unsigned NOT NULL DEFAULT '0',
`unsubscribe` tinyint(1) unsigned NOT NULL DEFAULT '0',
`send_date` date NOT NULL,
`code` varchar(255) NOT NULL DEFAULT '',
KEY `crypt_id` (`crypt_id`),
KEY `letter_id` (`letter_id`),
KEY `user_id` (`user_id`)
) ENGINE=TokuDB
A simple query like that takes 4 seconds on a table with 200m rows.
UPDATE hoovie_1.letter_archiv_14167
SET unsubscribe = 1
WHERE letter_id = "784547"
AND user_id = "2881564";
The cardinality values are correct. EXPLAIN will result in:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE letter_archiv_14167 NULL range letter_id,user_id letter_id 3 const 1 100.00 Using where
The only solution is to remove and re-create at least one index. After dropping and re-creating the index letter_id the table will perform well (in 0.01 s).
The EXPLAIN will change to
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE letter_archiv_14167 NULL range user_id,letter_id user_id 4 const 99 100.00 Using where
We have some thousands of TokuDB tables in production - a performance loss of factor 300-500 is a problem.
So we are unsure to migrate to 5.7 - this behaviour could occur even after re-creating all indexes again.
Any ideas?
mysql upgrade percona tokudb
asked Dec 18 '17 at 17:55
Ralf EnglerRalf Engler
61
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
After upgrading from Percona-TokuDB 5.6.29-76.2 to 5.7.19-17 we see some very slow queries on some tables without primary keys, but multiple non-unique indexes. The box we migrated to is pretty well equipped (768 GB RAM, PCIe SSDs). We used mysql_upgrade after migration.
After investigating Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen we tried ANALYZE TABLE, even with RECOUNT_ROWS, REPAIR TABLE, ALTER TABLE *** FORCE without any effect.
Typical table structure:
CREATE TABLE `letter_archiv_12375` (
`user_id` int(12) unsigned NOT NULL DEFAULT '0',
`letter_id` mediumint(6) unsigned NOT NULL DEFAULT '0',
`crypt_id` bigint(12) unsigned NOT NULL DEFAULT '0',
`mailerror` tinyint(1) unsigned NOT NULL DEFAULT '0',
`unsubscribe` tinyint(1) unsigned NOT NULL DEFAULT '0',
`send_date` date NOT NULL,
`code` varchar(255) NOT NULL DEFAULT '',
KEY `crypt_id` (`crypt_id`),
KEY `letter_id` (`letter_id`),
KEY `user_id` (`user_id`)
) ENGINE=TokuDB
A simple query like that takes 4 seconds on a table with 200m rows.
UPDATE hoovie_1.letter_archiv_14167
SET unsubscribe = 1
WHERE letter_id = "784547"
AND user_id = "2881564";
The cardinality values are correct. EXPLAIN will result in:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE letter_archiv_14167 NULL range letter_id,user_id letter_id 3 const 1 100.00 Using where
The only solution is to remove and re-create at least one index. After dropping and re-creating the index letter_id the table will perform well (in 0.01 s).
The EXPLAIN will change to
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE letter_archiv_14167 NULL range user_id,letter_id user_id 4 const 99 100.00 Using where
We have some thousands of TokuDB tables in production - a performance loss of factor 300-500 is a problem.
So we are unsure to migrate to 5.7 - this behaviour could occur even after re-creating all indexes again.
Any ideas?
mysql upgrade percona tokudb
asked Dec 18 '17 at 17:55
Ralf EnglerRalf Engler
61
After upgrading from Percona-TokuDB 5.6.29-76.2 to 5.7.19-17 we see some very slow queries on some tables without primary keys, but multiple non-unique indexes. The box we migrated to is pretty well equipped (768 GB RAM, PCIe SSDs). We used mysql_upgrade after migration.
After investigating Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen we tried ANALYZE TABLE, even with RECOUNT_ROWS, REPAIR TABLE, ALTER TABLE *** FORCE without any effect.
Typical table structure:
CREATE TABLE `letter_archiv_12375` (
`user_id` int(12) unsigned NOT NULL DEFAULT '0',
`letter_id` mediumint(6) unsigned NOT NULL DEFAULT '0',
`crypt_id` bigint(12) unsigned NOT NULL DEFAULT '0',
`mailerror` tinyint(1) unsigned NOT NULL DEFAULT '0',
`unsubscribe` tinyint(1) unsigned NOT NULL DEFAULT '0',
`send_date` date NOT NULL,
`code` varchar(255) NOT NULL DEFAULT '',
KEY `crypt_id` (`crypt_id`),
KEY `letter_id` (`letter_id`),
KEY `user_id` (`user_id`)
) ENGINE=TokuDB
A simple query like that takes 4 seconds on a table with 200m rows.
UPDATE hoovie_1.letter_archiv_14167
SET unsubscribe = 1
WHERE letter_id = "784547"
AND user_id = "2881564";
The cardinality values are correct. EXPLAIN will result in:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE letter_archiv_14167 NULL range letter_id,user_id letter_id 3 const 1 100.00 Using where
The only solution is to remove and re-create at least one index. After dropping and re-creating the index letter_id the table will perform well (in 0.01 s).
The EXPLAIN will change to
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE letter_archiv_14167 NULL range user_id,letter_id user_id 4 const 99 100.00 Using where
We have some thousands of TokuDB tables in production - a performance loss of factor 300-500 is a problem.
So we are unsure to migrate to 5.7 - this behaviour could occur even after re-creating all indexes again.
Any ideas?
mysql upgrade percona tokudb
mysql upgrade percona tokudb
asked Dec 18 '17 at 17:55
Ralf EnglerRalf Engler
61
asked Dec 18 '17 at 17:55
Ralf EnglerRalf Engler
61
asked Dec 18 '17 at 17:55
Ralf EnglerRalf Engler
61
asked Dec 18 '17 at 17:55
Ralf EnglerRalf Engler
61
asked Dec 18 '17 at 17:55
Ralf EnglerRalf Engler
61
61
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Add this composite index:
INDEX(letter_id, user_id) -- in either order
Do you deliberately not have a PRIMARY KEY?
answered Dec 23 '17 at 18:04
Rick JamesRick James
42.9k22259
Dropping and re-creating the index will solve the problem - i just wanted to focus on that particular problem when upgrading TokuDB from 5.6.x to 5.7.x. And off course: primary keys would be the best solution, but from time to time there are historical reasons...
– Ralf Engler
Jan 4 '18 at 12:23
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f193428%2fpercona-server-slow-tokudb-queries-after-upgrading-from-5-6-to-5-7-analyze-tab%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Add this composite index:
INDEX(letter_id, user_id) -- in either order
Do you deliberately not have a PRIMARY KEY?
answered Dec 23 '17 at 18:04
Rick JamesRick James
42.9k22259
Dropping and re-creating the index will solve the problem - i just wanted to focus on that particular problem when upgrading TokuDB from 5.6.x to 5.7.x. And off course: primary keys would be the best solution, but from time to time there are historical reasons...
– Ralf Engler
Jan 4 '18 at 12:23
add a comment |
Add this composite index:
INDEX(letter_id, user_id) -- in either order
Do you deliberately not have a PRIMARY KEY?
answered Dec 23 '17 at 18:04
Rick JamesRick James
42.9k22259
Dropping and re-creating the index will solve the problem - i just wanted to focus on that particular problem when upgrading TokuDB from 5.6.x to 5.7.x. And off course: primary keys would be the best solution, but from time to time there are historical reasons...
– Ralf Engler
Jan 4 '18 at 12:23
add a comment |
Add this composite index:
INDEX(letter_id, user_id) -- in either order
Do you deliberately not have a PRIMARY KEY?
answered Dec 23 '17 at 18:04
Rick JamesRick James
42.9k22259
Add this composite index:
INDEX(letter_id, user_id) -- in either order
Do you deliberately not have a PRIMARY KEY?
answered Dec 23 '17 at 18:04
Rick JamesRick James
42.9k22259
answered Dec 23 '17 at 18:04
Rick JamesRick James
42.9k22259
answered Dec 23 '17 at 18:04
Rick JamesRick James
42.9k22259
answered Dec 23 '17 at 18:04
Rick JamesRick James
42.9k22259
42.9k22259
Dropping and re-creating the index will solve the problem - i just wanted to focus on that particular problem when upgrading TokuDB from 5.6.x to 5.7.x. And off course: primary keys would be the best solution, but from time to time there are historical reasons...
– Ralf Engler
Jan 4 '18 at 12:23
add a comment |
Dropping and re-creating the index will solve the problem - i just wanted to focus on that particular problem when upgrading TokuDB from 5.6.x to 5.7.x. And off course: primary keys would be the best solution, but from time to time there are historical reasons...
– Ralf Engler
Jan 4 '18 at 12:23
Dropping and re-creating the index will solve the problem - i just wanted to focus on that particular problem when upgrading TokuDB from 5.6.x to 5.7.x. And off course: primary keys would be the best solution, but from time to time there are historical reasons...
– Ralf Engler
Jan 4 '18 at 12:23
Dropping and re-creating the index will solve the problem - i just wanted to focus on that particular problem when upgrading TokuDB from 5.6.x to 5.7.x. And off course: primary keys would be the best solution, but from time to time there are historical reasons...
– Ralf Engler
Jan 4 '18 at 12:23
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f193428%2fpercona-server-slow-tokudb-queries-after-upgrading-from-5-6-to-5-7-analyze-tab%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
