TokuDB Hot Column Expansion
I need to expand a varchar field length from 255 to 4000.
I am using tokudb_version: tokudb-7.5.8 running on Linux 3.16.0-60-generic #80~14.04.1-Ubuntu SMP Wed Jan 20 13:37:48 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
I know TokuDB supports Hot Column operations but this is not working for me ( number of rows ~ 210 million)
Show variables file: https://drive.google.com/file/d/0B5noFLrbjDjzSW9wdnVjb095Q0U/view?usp=sharing
Alter command
alter table test_table modify test_column varchar(4000);
Show processlist:
mysql> show processlist;
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
| 6 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |
| 7 | root | localhost | test | Query | 461 | Queried about 2445001 rows, Inserted about 2445000 rows | alter table test_table modify test_column varchar(4000) | 0 | 0 |
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
2 rows in set (0.00 sec)
Any idea which options I might need to set because it's currently processing at ~ 6k per second(which might take me ~10 hours)
mysql alter-table tokudb
edited Jul 26 '16 at 9:32
Philᵀᴹ
25.6k65591
asked Jul 26 '16 at 9:16
KyaloKyalo
11
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 1 more comment
I need to expand a varchar field length from 255 to 4000.
I am using tokudb_version: tokudb-7.5.8 running on Linux 3.16.0-60-generic #80~14.04.1-Ubuntu SMP Wed Jan 20 13:37:48 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
I know TokuDB supports Hot Column operations but this is not working for me ( number of rows ~ 210 million)
Show variables file: https://drive.google.com/file/d/0B5noFLrbjDjzSW9wdnVjb095Q0U/view?usp=sharing
Alter command
alter table test_table modify test_column varchar(4000);
Show processlist:
mysql> show processlist;
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
| 6 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |
| 7 | root | localhost | test | Query | 461 | Queried about 2445001 rows, Inserted about 2445000 rows | alter table test_table modify test_column varchar(4000) | 0 | 0 |
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
2 rows in set (0.00 sec)
Any idea which options I might need to set because it's currently processing at ~ 6k per second(which might take me ~10 hours)
mysql alter-table tokudb
edited Jul 26 '16 at 9:32
Philᵀᴹ
25.6k65591
asked Jul 26 '16 at 9:16
KyaloKyalo
11
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are you sure your table is using the TokuDB storage engine and not InnoDB?
– tmcallaghan
Jul 26 '16 at 11:04
Hi Tim, the table isENGINE=TokuDB AUTO_INCREMENT=22912421809 DEFAULT CHARSET=utf8
– Kyalo
Jul 26 '16 at 12:51
How did you get to ai=22B if there are only 0.2B rows? Lots ofDELETEs?REPLACE?
– Rick James
Jul 26 '16 at 16:25
Auto-Increment at 100 steps
– Kyalo
Jul 27 '16 at 4:41
Any ideas guys?
– Kyalo
Jul 28 '16 at 8:03
|
show 1 more comment
I need to expand a varchar field length from 255 to 4000.
I am using tokudb_version: tokudb-7.5.8 running on Linux 3.16.0-60-generic #80~14.04.1-Ubuntu SMP Wed Jan 20 13:37:48 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
I know TokuDB supports Hot Column operations but this is not working for me ( number of rows ~ 210 million)
Show variables file: https://drive.google.com/file/d/0B5noFLrbjDjzSW9wdnVjb095Q0U/view?usp=sharing
Alter command
alter table test_table modify test_column varchar(4000);
Show processlist:
mysql> show processlist;
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
| 6 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |
| 7 | root | localhost | test | Query | 461 | Queried about 2445001 rows, Inserted about 2445000 rows | alter table test_table modify test_column varchar(4000) | 0 | 0 |
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
2 rows in set (0.00 sec)
Any idea which options I might need to set because it's currently processing at ~ 6k per second(which might take me ~10 hours)
mysql alter-table tokudb
edited Jul 26 '16 at 9:32
Philᵀᴹ
25.6k65591
asked Jul 26 '16 at 9:16
KyaloKyalo
11
I need to expand a varchar field length from 255 to 4000.
I am using tokudb_version: tokudb-7.5.8 running on Linux 3.16.0-60-generic #80~14.04.1-Ubuntu SMP Wed Jan 20 13:37:48 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
I know TokuDB supports Hot Column operations but this is not working for me ( number of rows ~ 210 million)
Show variables file: https://drive.google.com/file/d/0B5noFLrbjDjzSW9wdnVjb095Q0U/view?usp=sharing
Alter command
alter table test_table modify test_column varchar(4000);
Show processlist:
mysql> show processlist;
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
| 6 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |
| 7 | root | localhost | test | Query | 461 | Queried about 2445001 rows, Inserted about 2445000 rows | alter table test_table modify test_column varchar(4000) | 0 | 0 |
+----+------+-----------+---------------+---------+------+---------------------------------------------------------+------------------------------------------------------------+-----------+---------------+
2 rows in set (0.00 sec)
Any idea which options I might need to set because it's currently processing at ~ 6k per second(which might take me ~10 hours)
mysql alter-table tokudb
mysql alter-table tokudb
edited Jul 26 '16 at 9:32
Philᵀᴹ
25.6k65591
asked Jul 26 '16 at 9:16
KyaloKyalo
11
edited Jul 26 '16 at 9:32
Philᵀᴹ
25.6k65591
asked Jul 26 '16 at 9:16
KyaloKyalo
11
edited Jul 26 '16 at 9:32
Philᵀᴹ
25.6k65591
edited Jul 26 '16 at 9:32
Philᵀᴹ
25.6k65591
edited Jul 26 '16 at 9:32
Philᵀᴹ
25.6k65591
25.6k65591
asked Jul 26 '16 at 9:16
KyaloKyalo
11
asked Jul 26 '16 at 9:16
KyaloKyalo
11
asked Jul 26 '16 at 9:16
KyaloKyalo
11
11
bumped to the homepage by Community♦ 10 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♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are you sure your table is using the TokuDB storage engine and not InnoDB?
– tmcallaghan
Jul 26 '16 at 11:04
Hi Tim, the table isENGINE=TokuDB AUTO_INCREMENT=22912421809 DEFAULT CHARSET=utf8
– Kyalo
Jul 26 '16 at 12:51
How did you get to ai=22B if there are only 0.2B rows? Lots ofDELETEs?REPLACE?
– Rick James
Jul 26 '16 at 16:25
Auto-Increment at 100 steps
– Kyalo
Jul 27 '16 at 4:41
Any ideas guys?
– Kyalo
Jul 28 '16 at 8:03
|
show 1 more comment
Are you sure your table is using the TokuDB storage engine and not InnoDB?
– tmcallaghan
Jul 26 '16 at 11:04
Hi Tim, the table isENGINE=TokuDB AUTO_INCREMENT=22912421809 DEFAULT CHARSET=utf8
– Kyalo
Jul 26 '16 at 12:51
How did you get to ai=22B if there are only 0.2B rows? Lots ofDELETEs?REPLACE?
– Rick James
Jul 26 '16 at 16:25
Auto-Increment at 100 steps
– Kyalo
Jul 27 '16 at 4:41
Any ideas guys?
– Kyalo
Jul 28 '16 at 8:03
Are you sure your table is using the TokuDB storage engine and not InnoDB?
– tmcallaghan
Jul 26 '16 at 11:04
Are you sure your table is using the TokuDB storage engine and not InnoDB?
– tmcallaghan
Jul 26 '16 at 11:04
Hi Tim, the table is
ENGINE=TokuDB AUTO_INCREMENT=22912421809 DEFAULT CHARSET=utf8– Kyalo
Jul 26 '16 at 12:51
Hi Tim, the table is
ENGINE=TokuDB AUTO_INCREMENT=22912421809 DEFAULT CHARSET=utf8– Kyalo
Jul 26 '16 at 12:51
How did you get to ai=22B if there are only 0.2B rows? Lots of
DELETEs? REPLACE?– Rick James
Jul 26 '16 at 16:25
How did you get to ai=22B if there are only 0.2B rows? Lots of
DELETEs? REPLACE?– Rick James
Jul 26 '16 at 16:25
Auto-Increment at 100 steps
– Kyalo
Jul 27 '16 at 4:41
Auto-Increment at 100 steps
– Kyalo
Jul 27 '16 at 4:41
Any ideas guys?
– Kyalo
Jul 28 '16 at 8:03
Any ideas guys?
– Kyalo
Jul 28 '16 at 8:03
|
show 1 more comment
1 Answer
1
active
oldest
votes
If you only want online operations you should consider setting tokudb_disable_slow_alter=ON as per https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_variables.html#tokudb_disable_slow_alter.
answered Jul 26 '16 at 11:04
tmcallaghantmcallaghan
5262511
Hi Tim, as expected setting this variable tokudb_disable_slow_alter=ON givesERROR 1112 (42000): Table 'test_table' uses an extension that doesn't exist in this MySQL version
– Kyalo
Jul 26 '16 at 13:03
@Kyalo - is your table calledtest_table?
– Rick James
Jul 26 '16 at 16:27
@RickJames It's a place holder
– Kyalo
Jul 27 '16 at 4:43
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%2f144919%2ftokudb-hot-column-expansion%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
If you only want online operations you should consider setting tokudb_disable_slow_alter=ON as per https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_variables.html#tokudb_disable_slow_alter.
answered Jul 26 '16 at 11:04
tmcallaghantmcallaghan
5262511
Hi Tim, as expected setting this variable tokudb_disable_slow_alter=ON givesERROR 1112 (42000): Table 'test_table' uses an extension that doesn't exist in this MySQL version
– Kyalo
Jul 26 '16 at 13:03
@Kyalo - is your table calledtest_table?
– Rick James
Jul 26 '16 at 16:27
@RickJames It's a place holder
– Kyalo
Jul 27 '16 at 4:43
add a comment |
If you only want online operations you should consider setting tokudb_disable_slow_alter=ON as per https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_variables.html#tokudb_disable_slow_alter.
answered Jul 26 '16 at 11:04
tmcallaghantmcallaghan
5262511
Hi Tim, as expected setting this variable tokudb_disable_slow_alter=ON givesERROR 1112 (42000): Table 'test_table' uses an extension that doesn't exist in this MySQL version
– Kyalo
Jul 26 '16 at 13:03
@Kyalo - is your table calledtest_table?
– Rick James
Jul 26 '16 at 16:27
@RickJames It's a place holder
– Kyalo
Jul 27 '16 at 4:43
add a comment |
If you only want online operations you should consider setting tokudb_disable_slow_alter=ON as per https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_variables.html#tokudb_disable_slow_alter.
answered Jul 26 '16 at 11:04
tmcallaghantmcallaghan
5262511
If you only want online operations you should consider setting tokudb_disable_slow_alter=ON as per https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_variables.html#tokudb_disable_slow_alter.
answered Jul 26 '16 at 11:04
tmcallaghantmcallaghan
5262511
answered Jul 26 '16 at 11:04
tmcallaghantmcallaghan
5262511
answered Jul 26 '16 at 11:04
tmcallaghantmcallaghan
5262511
answered Jul 26 '16 at 11:04
tmcallaghantmcallaghan
5262511
5262511
Hi Tim, as expected setting this variable tokudb_disable_slow_alter=ON givesERROR 1112 (42000): Table 'test_table' uses an extension that doesn't exist in this MySQL version
– Kyalo
Jul 26 '16 at 13:03
@Kyalo - is your table calledtest_table?
– Rick James
Jul 26 '16 at 16:27
@RickJames It's a place holder
– Kyalo
Jul 27 '16 at 4:43
add a comment |
Hi Tim, as expected setting this variable tokudb_disable_slow_alter=ON givesERROR 1112 (42000): Table 'test_table' uses an extension that doesn't exist in this MySQL version
– Kyalo
Jul 26 '16 at 13:03
@Kyalo - is your table calledtest_table?
– Rick James
Jul 26 '16 at 16:27
@RickJames It's a place holder
– Kyalo
Jul 27 '16 at 4:43
Hi Tim, as expected setting this variable tokudb_disable_slow_alter=ON gives
ERROR 1112 (42000): Table 'test_table' uses an extension that doesn't exist in this MySQL version– Kyalo
Jul 26 '16 at 13:03
Hi Tim, as expected setting this variable tokudb_disable_slow_alter=ON gives
ERROR 1112 (42000): Table 'test_table' uses an extension that doesn't exist in this MySQL version– Kyalo
Jul 26 '16 at 13:03
@Kyalo - is your table called
test_table?– Rick James
Jul 26 '16 at 16:27
@Kyalo - is your table called
test_table?– Rick James
Jul 26 '16 at 16:27
@RickJames It's a place holder
– Kyalo
Jul 27 '16 at 4:43
@RickJames It's a place holder
– Kyalo
Jul 27 '16 at 4:43
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%2f144919%2ftokudb-hot-column-expansion%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
Are you sure your table is using the TokuDB storage engine and not InnoDB?
– tmcallaghan
Jul 26 '16 at 11:04
Hi Tim, the table is
ENGINE=TokuDB AUTO_INCREMENT=22912421809 DEFAULT CHARSET=utf8– Kyalo
Jul 26 '16 at 12:51
How did you get to ai=22B if there are only 0.2B rows? Lots of
DELETEs?REPLACE?– Rick James
Jul 26 '16 at 16:25
Auto-Increment at 100 steps
– Kyalo
Jul 27 '16 at 4:41
Any ideas guys?
– Kyalo
Jul 28 '16 at 8:03