Sfrgttk

Fundamental Theorem of Calculus I help!

If I have Haste cast on me, does it reduce the casting time for my spells that normally take more than a turn to cast?

Are encryption algorithms with fixed-point free permutations inherently flawed?

How does the spell Slow affect freefall?

Can a Hydra make multiple opportunity attacks at once?

Have any astronauts or cosmonauts died in space?

Euler and minus sign

What does an unprocessed RAW file look like?

Log to console in a Lightning Web Component

What is formjacking?

Can I combine Divination spells with Arcane Eye?

SQL Server 2017 crashes when backing up because filepath is wrong

How to achieve physical gender equality?

Last Reboot commands don't agree

How can I make my enemies feel real and make combat more engaging?

How do I avoid the "chosen hero" feeling?

Aligning Systems of Equations

What's the function of the word "ли" in the following contexts?

What if you do not believe in the project benefits?

Manager has noticed coworker's excessive breaks. Should I warn him?

Calculating total "on row" bytes for each row ... the easy way

Why is Bernie Sanders maximum accepted donation on actblue $5600?

How can changes in personality/values of a person who turned into a vampire be explained?

80-bit collision resistence because of 80-bit x87 registers?

Moving to innodb_file_per_table?

What is the best way to reduce the size of ibdata in mysql?Users complain that system runs slow when mysqldump is in progress. Restore the data from Snapshot Backup when the data spans over multiple directories?Move ibdata1, set innodb_data_file_pathMySQL: reducing ibdata file size for MyISAM tablesMySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)my phpMyAdmin data is hashed or hexed?Row size error with MySQLMinimizing database size (Many small tables)shrink ibdata1 in Galera ClusterSuggestions for large DB maintenance

2

3

I have just noticed (unfortunately while trying to restore from backup), that on my production server there is a 35GB ibdata1 file!

As I understand I can reduce its size by moving to innodb_file_per_table.
However following the answers I have seen here (link below), you have to dump and drop all databases before you can start the conversion.

How long would it take to do this convert?

Is it possible to stagger the convert and do it one database at a time?

Thanks.

What is the best way to reduce the size of ibdata in mysql?

mysql innodb

share|improve this question

edited Apr 13 '17 at 12:43

Community♦

1

asked Nov 25 '15 at 12:37

pwnyrainbowpwnyrainbow

111

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not possible for one database at a time... mysqldump is a simplest way... dump everything and start a fresh with file-per-table ON. What is your total data size and system configuration!! I guess that's required for an estimate. time-to-convert = time-to-dump+time-to-restore+misc an alternate way is: convert everything to myisam -> stop mysql -> move ibdata1 file -> start mysql -> convert everything to innodb. This will allow you to do one database at a time but you know myisam caveats...
  
  – mysql_user
  Nov 25 '15 at 12:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the reply, you have given me a rough idea of what is involved. Looks like the cumulative size of the databases is ~19GB. Which configuration options should I be looking at? the Innodb specific ones?
  
  – pwnyrainbow
  Nov 25 '15 at 14:41
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's your system config (ram/cpu) ? innodb bufferpool? is this dedicated mysql server? You can perform dump load tests and do the estimates!!
  
  – mysql_user
  Nov 25 '15 at 16:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Plan on a few hours. It's really a question of disk speed. And you need to read and write the data at least twice.
  
  – Rick James
  Dec 14 '15 at 1:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The cumulative size of the Data and indexes is 19GB? So, upwards of 16GB is "free"? How much "free" is shown in SHOW TABLE STATUS? If it is closer to 36G for data+index, then you won't save any disk space by file_per_table.
  
  – Rick James
  Dec 14 '15 at 1:24
  

  
  
  
  

add a comment | 

2

3

I have just noticed (unfortunately while trying to restore from backup), that on my production server there is a 35GB ibdata1 file!

As I understand I can reduce its size by moving to innodb_file_per_table.
However following the answers I have seen here (link below), you have to dump and drop all databases before you can start the conversion.

How long would it take to do this convert?

Is it possible to stagger the convert and do it one database at a time?

Thanks.

What is the best way to reduce the size of ibdata in mysql?

mysql innodb

share|improve this question

edited Apr 13 '17 at 12:43

Community♦

1

asked Nov 25 '15 at 12:37

pwnyrainbowpwnyrainbow

111

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not possible for one database at a time... mysqldump is a simplest way... dump everything and start a fresh with file-per-table ON. What is your total data size and system configuration!! I guess that's required for an estimate. time-to-convert = time-to-dump+time-to-restore+misc an alternate way is: convert everything to myisam -> stop mysql -> move ibdata1 file -> start mysql -> convert everything to innodb. This will allow you to do one database at a time but you know myisam caveats...
  
  – mysql_user
  Nov 25 '15 at 12:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the reply, you have given me a rough idea of what is involved. Looks like the cumulative size of the databases is ~19GB. Which configuration options should I be looking at? the Innodb specific ones?
  
  – pwnyrainbow
  Nov 25 '15 at 14:41
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's your system config (ram/cpu) ? innodb bufferpool? is this dedicated mysql server? You can perform dump load tests and do the estimates!!
  
  – mysql_user
  Nov 25 '15 at 16:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Plan on a few hours. It's really a question of disk speed. And you need to read and write the data at least twice.
  
  – Rick James
  Dec 14 '15 at 1:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The cumulative size of the Data and indexes is 19GB? So, upwards of 16GB is "free"? How much "free" is shown in SHOW TABLE STATUS? If it is closer to 36G for data+index, then you won't save any disk space by file_per_table.
  
  – Rick James
  Dec 14 '15 at 1:24
  

  
  
  
  

add a comment | 

I have just noticed (unfortunately while trying to restore from backup), that on my production server there is a 35GB ibdata1 file!

As I understand I can reduce its size by moving to innodb_file_per_table.
However following the answers I have seen here (link below), you have to dump and drop all databases before you can start the conversion.

How long would it take to do this convert?

Is it possible to stagger the convert and do it one database at a time?

Thanks.

What is the best way to reduce the size of ibdata in mysql?

mysql innodb

share|improve this question

edited Apr 13 '17 at 12:43

Community♦

1

asked Nov 25 '15 at 12:37

pwnyrainbowpwnyrainbow

111

share|improve this question

edited Apr 13 '17 at 12:43

Community♦

1

asked Nov 25 '15 at 12:37

pwnyrainbowpwnyrainbow

111

share|improve this question

edited Apr 13 '17 at 12:43

Community♦

1

asked Nov 25 '15 at 12:37

pwnyrainbowpwnyrainbow

111

edited Apr 13 '17 at 12:43

Community♦

1

edited Apr 13 '17 at 12:43

Community♦

1

asked Nov 25 '15 at 12:37

pwnyrainbowpwnyrainbow

111

asked Nov 25 '15 at 12:37

pwnyrainbowpwnyrainbow

111

1 Answer
1

active

oldest

votes

0

You already linked rolando's post on how to do the migration. You can change the setting online to make use of per table files, but this will not fix your huge ibdata file size, even though single files are used afterwards.

To discern the time needed, try doing it on another installation you recreate from your backups. That way you will get reasonable numbers (as long as the systems have similar specs and hardware being used) how long this process will take, also you can try it out before laying hands onto the production system.

If you have hard uptime requirements another way to do this online might be through replication. To create a slave, you have create an initial mysqldump with special parameters, but this shouldn't be a problem since I guess you create your backups via dumps anyway?

When setting up the replication, you can let the slave run with innodb_file_per_table even though the master does not. Once all things are set, put the masters' ip onto the slave.

If all this is not possible due to the lack of hardware, then your system with the harsh uptime requirements was maybe sold too cheaply and you now have the ungrateful job to make something work with risks that should have been adressed in the initial planning by getting an adequate test setup besides your production setup.

For getting your mysqldumps to speed up, try piping them directly into parallel bzip, to get the cores to do more work and save time by reducing the needed disk accesses. Rougly like this:

# DUMP

mysqldump ... | pbzip2 -c > MYSQLDUMP.sql

# REPLAY

pbzip2 -dkc MYSQLDUMP.sql | mysql (...)

share|improve this answer

answered Jun 15 '17 at 19:52

sjassjas

1155

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
});


}
});

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f122076%2fmoving-to-innodb-file-per-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

You already linked rolando's post on how to do the migration. You can change the setting online to make use of per table files, but this will not fix your huge ibdata file size, even though single files are used afterwards.

To discern the time needed, try doing it on another installation you recreate from your backups. That way you will get reasonable numbers (as long as the systems have similar specs and hardware being used) how long this process will take, also you can try it out before laying hands onto the production system.

If you have hard uptime requirements another way to do this online might be through replication. To create a slave, you have create an initial mysqldump with special parameters, but this shouldn't be a problem since I guess you create your backups via dumps anyway?

When setting up the replication, you can let the slave run with innodb_file_per_table even though the master does not. Once all things are set, put the masters' ip onto the slave.

If all this is not possible due to the lack of hardware, then your system with the harsh uptime requirements was maybe sold too cheaply and you now have the ungrateful job to make something work with risks that should have been adressed in the initial planning by getting an adequate test setup besides your production setup.

For getting your mysqldumps to speed up, try piping them directly into parallel bzip, to get the cores to do more work and save time by reducing the needed disk accesses. Rougly like this:

# DUMP

mysqldump ... | pbzip2 -c > MYSQLDUMP.sql

# REPLAY

pbzip2 -dkc MYSQLDUMP.sql | mysql (...)

share|improve this answer

answered Jun 15 '17 at 19:52

sjassjas

1155

add a comment | 

0

You already linked rolando's post on how to do the migration. You can change the setting online to make use of per table files, but this will not fix your huge ibdata file size, even though single files are used afterwards.

To discern the time needed, try doing it on another installation you recreate from your backups. That way you will get reasonable numbers (as long as the systems have similar specs and hardware being used) how long this process will take, also you can try it out before laying hands onto the production system.

If you have hard uptime requirements another way to do this online might be through replication. To create a slave, you have create an initial mysqldump with special parameters, but this shouldn't be a problem since I guess you create your backups via dumps anyway?

When setting up the replication, you can let the slave run with innodb_file_per_table even though the master does not. Once all things are set, put the masters' ip onto the slave.

If all this is not possible due to the lack of hardware, then your system with the harsh uptime requirements was maybe sold too cheaply and you now have the ungrateful job to make something work with risks that should have been adressed in the initial planning by getting an adequate test setup besides your production setup.

For getting your mysqldumps to speed up, try piping them directly into parallel bzip, to get the cores to do more work and save time by reducing the needed disk accesses. Rougly like this:

# DUMP

mysqldump ... | pbzip2 -c > MYSQLDUMP.sql

# REPLAY

pbzip2 -dkc MYSQLDUMP.sql | mysql (...)

share|improve this answer

answered Jun 15 '17 at 19:52

sjassjas

1155

add a comment | 

You already linked rolando's post on how to do the migration. You can change the setting online to make use of per table files, but this will not fix your huge ibdata file size, even though single files are used afterwards.

To discern the time needed, try doing it on another installation you recreate from your backups. That way you will get reasonable numbers (as long as the systems have similar specs and hardware being used) how long this process will take, also you can try it out before laying hands onto the production system.

If you have hard uptime requirements another way to do this online might be through replication. To create a slave, you have create an initial mysqldump with special parameters, but this shouldn't be a problem since I guess you create your backups via dumps anyway?

When setting up the replication, you can let the slave run with innodb_file_per_table even though the master does not. Once all things are set, put the masters' ip onto the slave.

If all this is not possible due to the lack of hardware, then your system with the harsh uptime requirements was maybe sold too cheaply and you now have the ungrateful job to make something work with risks that should have been adressed in the initial planning by getting an adequate test setup besides your production setup.

For getting your mysqldumps to speed up, try piping them directly into parallel bzip, to get the cores to do more work and save time by reducing the needed disk accesses. Rougly like this:

# DUMP

mysqldump ... | pbzip2 -c > MYSQLDUMP.sql

# REPLAY

pbzip2 -dkc MYSQLDUMP.sql | mysql (...)

share|improve this answer

answered Jun 15 '17 at 19:52

sjassjas

1155

# DUMP

mysqldump ... | pbzip2 -c > MYSQLDUMP.sql

# REPLAY

pbzip2 -dkc MYSQLDUMP.sql | mysql (...)

share|improve this answer

answered Jun 15 '17 at 19:52

sjassjas

1155

answered Jun 15 '17 at 19:52

sjassjas

1155

answered Jun 15 '17 at 19:52

sjassjas

1155