Mysql ISAM vs InnoDB

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Mysql ISAM vs InnoDB

Jim Hu
$wgDBtransactions gets set to true if using InnoDB tables.  Is there  
an advantage to using InnoDB tables?
The disadvantage is that with MySQL there is a file, ibdata1, that  
seems to grow endlessly if InnoDB tables are used.  See

http://bugs.mysql.com/bug.php?id=1341

We're wondering if we should just convert everything to MyISAM.  Any  
thoughts?

=====================================
Jim Hu
Associate Professor
Dept. of Biochemistry and Biophysics
2128 TAMU
Texas A&M Univ.
College Station, TX 77843-2128
979-862-4054


_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Mysql ISAM vs InnoDB

Brion Vibber
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jim Hu wrote:
> $wgDBtransactions gets set to true if using InnoDB tables.  Is there  
> an advantage to using InnoDB tables?
> The disadvantage is that with MySQL there is a file, ibdata1, that  
> seems to grow endlessly if InnoDB tables are used.  See
>
> http://bugs.mysql.com/bug.php?id=1341
>
> We're wondering if we should just convert everything to MyISAM.  Any  
> thoughts?

MyISAM tables are subject to a much higher likelihood of data
corruption, and cannot be read consistently (eg for backups) without
locking the database.

You may notice that the majority of complaints about corrupt tables
involving MediaWiki are about the 'searchindex' table, which is created
as MyISAM due to the requirements of the fulltext index.


In most wiki situations your database will indeed only grow, so the
table space not reclaiming disk space on deletions is usually not a
problem. In the wiki, all editing history is retained, and the space
from the rare small records that are actually removed will simply be
taken up by further edits.


If you for some reason want to import a lot of data, then delete it all,
then never work with any database data ever again, well... that's pretty
weird. ;) But as suggested in your link you can use per-table InnoDB
spaces in recent versions of MySQL, or you can use the more fragile
MyISAM tables, or you can "defragment" the table space by dumping it
out, deleting the space, and reimporting it.

In some situations MyISAM tables may also be faster, which could be
useful for certain kinds of statistical or other use.

- -- brion vibber (brion @ pobox.com)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFdi+SwRnhpk1wk44RAp9sAKCVsoRRbxcgZ3D6VGWHSYKIdL4GjwCeKwr1
Br4tC50Y57MGjTnMhmak5Tg=
=Psfe
-----END PGP SIGNATURE-----
_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Mysql ISAM vs InnoDB

Eric K-2
When I installed my wiki for the first time a couple of years ago, MyISAM was chosen as the default option so I went with that. I was looking up the history of this list for comments on InnoDB vs MyISAM. I've seen people, including Brion (below), recommending InnoDB. Rob Church also had the same opinion:
http://wikimedia.7.n6.nabble.com/MYISAM-or-InnoDB-best-for-Mediawiki-td691725.html#a691729
However for example on the following article, some disadvantages of InnoDB are talked about:
http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/


So it seems like both of them have advantages and disadvantages and so I'm really confused about what to do - whether to stay where I am, or convert to InnoDB. So my first questions are:
- I keep regular backups and haven't had DB problems in the past. Should I really move to InnoDB? If I had to convert the DB, I can always do it in the future without any problems, correct?
- Will I have any problems later if I stay on MyISAM?  

I have MW 1.16.15 and am going to upgrade to 1.18.0. I have Shell access. Now the DB has mixed tables (Semantic MW did its setup using InnoDB). Its about 1GB in size. The wiki isnt too big, but say the DB size could go to a few GBs in a few years.


Suppose I decided to move to InnoDB. My next questions were:
- Whats the procedure for this conversion? I will be working on a copy of the database.
- I'm assuming there will be no long term issues about website speed and reliability or anything else.

thanks
Eric







________________________________
 From: Brion Vibber <[hidden email]>
To: MediaWiki announcements and site admin list <[hidden email]>
Sent: Tuesday, December 5, 2006 8:48 PM
Subject: Re: [Mediawiki-l] Mysql ISAM vs InnoDB
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jim Hu wrote:
> $wgDBtransactions gets set to true if using InnoDB tables.  Is there 
> an advantage to using InnoDB tables?
> The disadvantage is that with MySQL there is a file, ibdata1, that 
> seems to grow endlessly if InnoDB tables are used.  See
>
> http://bugs.mysql.com/bug.php?id=1341
>
> We're wondering if we should just convert everything to MyISAM.  Any 
> thoughts?

MyISAM tables are subject to a much higher likelihood of data
corruption, and cannot be read consistently (eg for backups) without
locking the database.

You may notice that the majority of complaints about corrupt tables
involving MediaWiki are about the 'searchindex' table, which is created
as MyISAM due to the requirements of the fulltext index.


In most wiki situations your database will indeed only grow, so the
table space not reclaiming disk space on deletions is usually not a
problem. In the wiki, all editing history is retained, and the space
from the rare small records that are actually removed will simply be
taken up by further edits.


If you for some reason want to import a lot of data, then delete it all,
then never work with any database data ever again, well... that's pretty
weird. ;) But as suggested in your link you can use per-table InnoDB
spaces in recent versions of MySQL, or you can use the more fragile
MyISAM tables, or you can "defragment" the table space by dumping it
out, deleting the space, and reimporting it.

In some situations MyISAM tables may also be faster, which could be
useful for certain kinds of statistical or other use.

- -- brion vibber (brion @ pobox.com)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFdi+SwRnhpk1wk44RAp9sAKCVsoRRbxcgZ3D6VGWHSYKIdL4GjwCeKwr1
Br4tC50Y57MGjTnMhmak5Tg=
=Psfe
-----END PGP SIGNATURE-----
_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
_______________________________________________
MediaWiki-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Mysql ISAM vs InnoDB

Eric K-2
Ok I got this resolved. I found out I can change the database type in myPHPadmin and it was executing the same shell command (ALTER table).
If the leaders of Mediawiki are recommending InnoDB and thats the type of DB that it now defaults to for a new installation, I should convert it to InnoDB and trust that its a good decision and wont cause any problems later.

So I was able to change the database type in MyPHPAdmin. I did it for each table. The options are available in MyPHPadmin:
Select the table you want to change -> "Operations" -> Change type to InnoDB
I'm also changing the "collation" from "utf8_unicode_ci" to "binary", for a few tables.

What I did was install the latest MW in a test database and see what options it was using for all the tables and then change the old tables to match the options in the test database. I saw there are two tables that have their own set up in the new version too (hit counter, and search index) so I left them alone.






________________________________
 From: Eric K <[hidden email]>
To: MediaWiki announcements and site admin list <[hidden email]>
Sent: Saturday, December 3, 2011 11:18 PM
Subject: Re: [Mediawiki-l] Mysql ISAM vs InnoDB
 
When I installed my wiki for the first time a couple of years ago, MyISAM was chosen as the default option so I went with that. I was looking up the history of this list for comments on InnoDB vs MyISAM. I've seen people, including Brion (below), recommending InnoDB. Rob Church also had the same opinion:
http://wikimedia.7.n6.nabble.com/MYISAM-or-InnoDB-best-for-Mediawiki-td691725.html#a691729
However for example on the following article, some disadvantages of InnoDB are talked about:
http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/


So it seems like both of them have advantages and disadvantages and so I'm really confused about what to do - whether to stay where I am, or convert to InnoDB. So my first questions are:
- I keep regular backups and haven't had DB problems in the past. Should I really move to InnoDB? If I had to convert the DB, I can always do it in the future without any problems, correct?
- Will I have any problems later if I stay on MyISAM?  

I have MW 1.16.15 and am going to upgrade to 1.18.0. I have Shell access. Now the DB has mixed tables (Semantic MW did its setup using InnoDB). Its about 1GB in size. The wiki isnt too big, but say the DB size could go to a few GBs in a few years.


Suppose I decided to move to InnoDB. My next questions were:
- Whats the procedure for this conversion? I will be working on a copy of the database.
- I'm assuming there will be no long term issues about website speed and reliability or anything else.

thanks
Eric







________________________________
From: Brion Vibber <[hidden email]>
To: MediaWiki announcements and site admin list <[hidden email]>
Sent: Tuesday, December 5, 2006 8:48 PM
Subject: Re: [Mediawiki-l] Mysql ISAM vs InnoDB

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jim Hu wrote:
> $wgDBtransactions gets set to true if using InnoDB tables.  Is there 
> an advantage to using InnoDB tables?
> The disadvantage is that with MySQL there is a file, ibdata1, that 
> seems to grow endlessly if InnoDB tables are used.  See
>
> http://bugs.mysql.com/bug.php?id=1341
>
> We're wondering if we should just convert everything to MyISAM.  Any 
> thoughts?

MyISAM tables are subject to a much higher likelihood of data
corruption, and cannot be read consistently (eg for backups) without
locking the database.

You may notice that the majority of complaints about corrupt tables
involving MediaWiki are about the 'searchindex' table, which is created
as MyISAM due to the requirements of the fulltext index.


In most wiki situations your database will indeed only grow, so the
table space not reclaiming disk space on deletions is usually not a
problem. In the wiki, all editing history is retained, and the space
from the rare small records that are actually removed will simply be
taken up by further edits.


If you for some reason want to import a lot of data, then delete it all,
then never work with any database data ever again, well... that's pretty
weird. ;) But as suggested in your link you can use per-table InnoDB
spaces in recent versions of MySQL, or you can use the more fragile
MyISAM tables, or you can "defragment" the table space by dumping it
out, deleting the space, and reimporting it.

In some situations MyISAM tables may also be faster, which could be
useful for certain kinds of statistical or other use.

- -- brion vibber (brion @ pobox.com)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFdi+SwRnhpk1wk44RAp9sAKCVsoRRbxcgZ3D6VGWHSYKIdL4GjwCeKwr1
Br4tC50Y57MGjTnMhmak5Tg=
=Psfe
-----END PGP SIGNATURE-----
_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
_______________________________________________
MediaWiki-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
_______________________________________________
MediaWiki-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Mysql ISAM vs InnoDB

Olivier Beaton
don't forget to change the MyISAM in your LocalSettings to InnoDB, and if
it says TYPE= change it to ENGINE=

On Sun, Dec 4, 2011 at 10:50 AM, Eric K <[hidden email]> wrote:

> Ok I got this resolved. I found out I can change the database type in
> myPHPadmin and it was executing the same shell command (ALTER table).
> If the leaders of Mediawiki are recommending InnoDB and thats the type of
> DB that it now defaults to for a new installation, I should convert it to
> InnoDB and trust that its a good decision and wont cause any problems later.
>
> So I was able to change the database type in MyPHPAdmin. I did it for each
> table. The options are available in MyPHPadmin:
> Select the table you want to change -> "Operations" -> Change type to
> InnoDB
> I'm also changing the "collation" from "utf8_unicode_ci" to "binary", for
> a few tables.
>
> What I did was install the latest MW in a test database and see what
> options it was using for all the tables and then change the old tables to
> match the options in the test database. I saw there are two tables that
> have their own set up in the new version too (hit counter, and search
> index) so I left them alone.
>
>
>
>
>
>
> ________________________________
>  From: Eric K <[hidden email]>
> To: MediaWiki announcements and site admin list <
> [hidden email]>
> Sent: Saturday, December 3, 2011 11:18 PM
> Subject: Re: [Mediawiki-l] Mysql ISAM vs InnoDB
>
> When I installed my wiki for the first time a couple of years ago, MyISAM
> was chosen as the default option so I went with that. I was looking up the
> history of this list for comments on InnoDB vs MyISAM. I've seen people,
> including Brion (below), recommending InnoDB. Rob Church also had the same
> opinion:
>
> http://wikimedia.7.n6.nabble.com/MYISAM-or-InnoDB-best-for-Mediawiki-td691725.html#a691729
> However for example on the following article, some disadvantages of InnoDB
> are talked about:
>
> http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/
>
>
> So it seems like both of them have advantages and disadvantages and so I'm
> really confused about what to do - whether to stay where I am, or convert
> to InnoDB. So my first questions are:
> - I keep regular backups and haven't had DB problems in the past. Should I
> really move to InnoDB? If I had to convert the DB, I can always do it in
> the future without any problems, correct?
> - Will I have any problems later if I stay on MyISAM?
>
> I have MW 1.16.15 and am going to upgrade to 1.18.0. I have Shell access.
> Now the DB has mixed tables (Semantic MW did its setup using InnoDB). Its
> about 1GB in size. The wiki isnt too big, but say the DB size could go to a
> few GBs in a few years.
>
>
> Suppose I decided to move to InnoDB. My next questions were:
> - Whats the procedure for this conversion? I will be working on a copy of
> the database.
> - I'm assuming there will be no long term issues about website speed and
> reliability or anything else.
>
> thanks
> Eric
>
>
>
>
>
>
>
> ________________________________
> From: Brion Vibber <[hidden email]>
> To: MediaWiki announcements and site admin list <[hidden email]
> >
> Sent: Tuesday, December 5, 2006 8:48 PM
> Subject: Re: [Mediawiki-l] Mysql ISAM vs InnoDB
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Jim Hu wrote:
> > $wgDBtransactions gets set to true if using InnoDB tables.  Is there
> > an advantage to using InnoDB tables?
> > The disadvantage is that with MySQL there is a file, ibdata1, that
> > seems to grow endlessly if InnoDB tables are used.  See
> >
> > http://bugs.mysql.com/bug.php?id=1341
> >
> > We're wondering if we should just convert everything to MyISAM.  Any
> > thoughts?
>
> MyISAM tables are subject to a much higher likelihood of data
> corruption, and cannot be read consistently (eg for backups) without
> locking the database.
>
> You may notice that the majority of complaints about corrupt tables
> involving MediaWiki are about the 'searchindex' table, which is created
> as MyISAM due to the requirements of the fulltext index.
>
>
> In most wiki situations your database will indeed only grow, so the
> table space not reclaiming disk space on deletions is usually not a
> problem. In the wiki, all editing history is retained, and the space
> from the rare small records that are actually removed will simply be
> taken up by further edits.
>
>
> If you for some reason want to import a lot of data, then delete it all,
> then never work with any database data ever again, well... that's pretty
> weird. ;) But as suggested in your link you can use per-table InnoDB
> spaces in recent versions of MySQL, or you can use the more fragile
> MyISAM tables, or you can "defragment" the table space by dumping it
> out, deleting the space, and reimporting it.
>
> In some situations MyISAM tables may also be faster, which could be
> useful for certain kinds of statistical or other use.
>
> - -- brion vibber (brion @ pobox.com)
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.2.2 (Darwin)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFFdi+SwRnhpk1wk44RAp9sAKCVsoRRbxcgZ3D6VGWHSYKIdL4GjwCeKwr1
> Br4tC50Y57MGjTnMhmak5Tg=
> =Psfe
> -----END PGP SIGNATURE-----
> _______________________________________________
> MediaWiki-l mailing list
> [hidden email]
> http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
> _______________________________________________
> MediaWiki-l mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
> _______________________________________________
> MediaWiki-l mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
>
_______________________________________________
MediaWiki-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l