Andy Millne Posted December 10, 2012 Posted December 10, 2012 I'm using the 5.1.65-cll version. (I guess). Dumb question: If I drop the indexes of a table(Ex: ibf_posts) to convert to InnoDB, it won't damage the table right ? The only thing that will change is that I'll have to use sphinx to search ? That's correct. Take a backup regardless as there's always the possibility of data corruption when altering large tables.
blair Posted December 11, 2012 Posted December 11, 2012 Back to the OP, I disagree with the second part of his hosts recommendation (bolded) Talking with my webhost(Knownhost) and after doing a lot of tweaks in the server to minimize the problems, they told me to talk with IPB to enable the "innodb_file_per_table" option in MySQL configuration and switch ibf_content_cache_posts from MyISAM to InnoDB, to increase performance. I've run InnoDB (Percona) for a couple of years. I currently have an 18GB database, but it was even larger when using exclusively InnoDB. I now run content_cache_posts, and core_item_markers as MyISAM (ibf_sessions in memory). There's no reason you can't run both, It reduces the size of the database. Has better read performance. Greatly reduces chances of corruption of your InnoDB tablespace. My server has 32GB of RAM so the reduced database size allows InnoDB tables to run from memory. content_cache_posts can be a huge table. Also, it's a cache that can easily be rebuilt, so there's no need for additional data integrity. Page generation times, page load times, and server load loads were all lower after the conversion to MyISAM. core_item_markers was added to MyISAM because when I was experiencing a hardware issue, it was a table consistently marked as corrupt. Simply the path of least resistance. Your results may vary. One last word about InnoDB, it's very robust and far less likely to suffer corruption than MyISAM. Data integrity, and no row locking are probably the best reasons to switch. However, If you're accustomed to running repairs on MyISAM tables, at first it's a bit jarring. Data corruption can and does occur with InnoDB. When it does, "recovery" usually means lost data in the corrupt table. Standard cPanel backups also won't backup InnoDB (recommend XtraDB). A good backup plan is a must. Looking forward to testing 5.6 and fulltext search on a 3.4.x dev server.
PatrickRQ Posted January 6, 2013 Posted January 6, 2013 When I try to convert posts, topics, message_posts, etc... I got this error: ERROR 1214 (HY000) at line 1: The used table type doesn't support FULLTEXT indexes Any ideas ? Thanks You do not have to drop whole table.. just do.. show create table table_name; then look at the key_name of fulltext then do alter table table_name drop index key_name then convert it to InnoDB.. List of fulltext keys.. blog_entries -> FULLTEXT KEY `entry_name` (`entry_name`) ccs_custom_database_1 -> FULLTEXT KEY `field_1` (`field_1`) -> FULLTEXT KEY `field_3` (`field_3`) ccs_custom_database_3 -> FULLTEXT KEY `field_15` (`field_15`) -> FULLTEXT KEY `field_18` (`field_18`) nexus_packages -> FULLTEXT KEY `p_name` (`p_name`) -> FULLTEXT KEY `p_desc` (`p_desc`) nexus_support_replies -> FULLTEXT KEY `reply_post` (`reply_post`) nexus_support_requests -> FULLTEXT KEY `r_title` (`r_title`) posts -> FULLTEXT KEY `post` (`post`) topics -> FULLTEXT KEY `title` (`title`)
Ohio Riders Posted January 11, 2013 Posted January 11, 2013 bfarber may have had a much different experience with it than I did at least. In truth, MyISAM offers almost no tangible benefits other than full text indexing, especially with MySQL 5.6.. the generally available 5.5 is much better with InnoDB even. Any benefits that you could state will most likely come from resources that are years old. The performance is worse with MyISAM, the lack of row-level locking causes performance problems with tables that require frequent updates (sessions).. scaling is even worse. Having a transaction log as well makes it super easy to restore a corrupted database from the last backup with InnoDB. Here is your best bet for a db right now: http://www.percona.com/software/percona-server Utilize the Xtradb storage engine for your tables. Agreed. There's no way I'd go back to MyISAM for anything. Not sure about Percona though. I'd rather use MariaDB than Percona if I weren't using plain Jane MySQL. For scaling, MySQL 5.6 has some excellent improvements in replication and failover. Look on YouTube for "mysql 5.6 replication" if you haven't already seen the demo video. Great stuff.
Ohio Riders Posted January 11, 2013 Posted January 11, 2013 Sorry, I simply don't know about databases. Its not clear for me hehe. I'm searching on how to do that. Thank you! Back up everything before you do anything else. If you're unsure what you're doing, it's VERY easy to shoot yourself in the foot.
teacher Posted January 14, 2013 Posted January 14, 2013 A question (or two): If I decide to change engine of some tables to Innodb and rest of the tables leave it to Myisam, what I should write to conf_global as a table engine: Inno or myisam? And what do that memory type of engine? Write all to RAM or something on the disk?
GreenLinks Posted January 14, 2013 Posted January 14, 2013 Agreed. There's no way I'd go back to MyISAM for anything. Not sure about Percona though. I'd rather use MariaDB than Percona if I weren't using plain Jane MySQL. For scaling, MySQL 5.6 has some excellent improvements in replication and failover. Look on YouTube for "mysql 5.6 replication" if you haven't already seen the demo video. Great stuff. I don't deny the fact that MySQL 5.6 has nice improvements but still in my own tests Persona always performed faster compared to MySQL 5.6 and MariaDB. However your results may vary and i totally understand that as each databases/website requirements are different
BigStamp Posted February 3, 2013 Posted February 3, 2013 A question (or two): If I decide to change engine of some tables to Innodb and rest of the tables leave it to Myisam, what I should write to conf_global as a table engine: Inno or myisam? And what do that memory type of engine? Write all to RAM or something on the disk? Did you ever find the answer to this?
Grumpy Posted February 3, 2013 Posted February 3, 2013 Did you ever find the answer to this? There is no real answer when you go splitting some into innodb and another into myisam. At least not with this much generalization.
Marcher Technologies Posted February 3, 2013 Posted February 3, 2013 There is no real answer when you go splitting some into innodb and another into myisam. At least not with this much generalization. INNODB. otherwise, IPB will attempt to make fulltext queries on INNODB tables.
sobrenome Posted June 23, 2014 Posted June 23, 2014 Now that MySQL 5.6 is out there, is it recommend for performance to change all tables from Myisam to innoDB?
GreenLinks Posted June 24, 2014 Posted June 24, 2014 Now that MySQL 5.6 is out there, is it recommend for performance to change all tables from Myisam to innoDB? I will suggest waiting IPB 4 to do that also instead of default MySQL 5.6 you shall choose either MariaDB or Percona 5.6
sobrenome Posted June 24, 2014 Posted June 24, 2014 I would like to use MariaDB, but I am stuck in Cpanel and they are still considering support for MariaDB in a future release.
Dmacleo Posted June 24, 2014 Posted June 24, 2014 I would like to use MariaDB, but I am stuck in Cpanel and they are still considering support for MariaDB in a future release. I ran maria on cpanel http://blog.cpanel.net/mysql-mariadb/
sobrenome Posted June 25, 2014 Posted June 25, 2014 What about the performance difference? Is it really that amazing? Even if compared to MySQL 5.6?
Dmacleo Posted June 25, 2014 Posted June 25, 2014 was better for me with no tuning done. going to probably convert my directadmin mysql over this week.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.