Converting a MyISAM table to InnoDB - pros and cons
Notes from a MySQL DBA:
With MyISAM tables, updates/insert cause table level locking. Converting to InnoDB will be useful as this uses MVCC + row level locking.
Things that can be done:
Convert to InnoDB
Create a numeric PK (auto_Increment) and UNIQUE KEY for hashid. InnoDB require primary keys (PK) for all your tables. If you don't define one, one will be invisibly defined for you. InnoDB best likes small fast PK. A big alphanumeric key in InnoDB might present performance issues.
InnoDB requires more RAM and hard drive space than MyISAM.
There are real benefits that you will gain by going all InnoDB:
Row level locking
Online snapshots for backups and/or slave provisioning
Tables cannot get corrupted (no REPAIR TABLE)
There are some caveats about InnoDB:
No full text indexing
No GEO Indexes
No merge tables
Table space never shrinks. If your tablespace grows to a point where it fills your current space, Even deleting the data will not shrink this file. An export/Import would be your only option. Of course this can be done on a slave and this server can later be promoted to a master.
More RAM and more HD space. InnoDB is RAM hungry. Its space footprint is roughly around 3x that of MyISAM. That is why with the database your size (62M) every byte in a column counts. A varchar takes the length of the text + 1 byte (in a 32 character long string that would be 33bytes if using single byte charset (latin1)) vs. a 4byte INT size. Multiply this by 64M rows, 2G in a varchar vs. 256M for an INT, on a single field column. So an InnoDB footprint would be 2-3x that size.
Optimizing your field type is especially important when the data grows like yours. Here is a good document about this:
http://dev.mysql.com/doc/refman/5.0/en/data-size.html
MySQL recommends having 2 RAIDS to deal with high IO database servers. 1 RAID1 for OS+binarylogs and 1 RAID10 for data. If you convert your database to InnoDB on the migration server. Once you’re ready to move to the new server, you would just flip the SQL traffic to the new server.
I recommend that we convert to InnoDB in the slave and not the live server as this operation does lock your table. I would not recommend converting to InnoDB without cleaning some unwanted indexes as these take a long time to build with a dataset this large. I would recommend that we make these conversions on a slave, which would be better provisioned for these operations.
--
With MyISAM tables, updates/insert cause table level locking. Converting to InnoDB will be useful as this uses MVCC + row level locking.
Things that can be done:
Convert to InnoDB
Create a numeric PK (auto_Increment) and UNIQUE KEY for hashid. InnoDB require primary keys (PK) for all your tables. If you don't define one, one will be invisibly defined for you. InnoDB best likes small fast PK. A big alphanumeric key in InnoDB might present performance issues.
InnoDB requires more RAM and hard drive space than MyISAM.
There are real benefits that you will gain by going all InnoDB:
Row level locking
Online snapshots for backups and/or slave provisioning
Tables cannot get corrupted (no REPAIR TABLE)
There are some caveats about InnoDB:
No full text indexing
No GEO Indexes
No merge tables
Table space never shrinks. If your tablespace grows to a point where it fills your current space, Even deleting the data will not shrink this file. An export/Import would be your only option. Of course this can be done on a slave and this server can later be promoted to a master.
More RAM and more HD space. InnoDB is RAM hungry. Its space footprint is roughly around 3x that of MyISAM. That is why with the database your size (62M) every byte in a column counts. A varchar takes the length of the text + 1 byte (in a 32 character long string that would be 33bytes if using single byte charset (latin1)) vs. a 4byte INT size. Multiply this by 64M rows, 2G in a varchar vs. 256M for an INT, on a single field column. So an InnoDB footprint would be 2-3x that size.
Optimizing your field type is especially important when the data grows like yours. Here is a good document about this:
http://dev.mysql.com/doc/refman/5.0/en/data-size.html
MySQL recommends having 2 RAIDS to deal with high IO database servers. 1 RAID1 for OS+binarylogs and 1 RAID10 for data. If you convert your database to InnoDB on the migration server. Once you’re ready to move to the new server, you would just flip the SQL traffic to the new server.
I recommend that we convert to InnoDB in the slave and not the live server as this operation does lock your table. I would not recommend converting to InnoDB without cleaning some unwanted indexes as these take a long time to build with a dataset this large. I would recommend that we make these conversions on a slave, which would be better provisioned for these operations.
--
posted by GoInfo Systems at
11:57 PM
0 Comments:
Post a Comment
<< Home