Urgent: IPB with MyISAM, will it run ?

[FabioPaz]

Hello,

I'm running IPB 3.4.0 and already applied that patch that correct some performance issues, but I'm having a lot of performance problems since prior versions. Mostly with locked mysql queries from IPB.

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.

But I need to know if IPB work correctly with that settings changed.

Unfortunately the support is too slow... I already sent a ticket to IPB about 6 hours ago and I'm still waiting a reply.

Anyone know if a can enable those settings without problems ?

Obs: My forum have 100k+ users and 1 million+ posts. My server is running with 4288mb ram, RAID-10 HD, Dual Six-Core Intel CPU with HT enabled.

Thank you for the help and sorry for my english, its not my native language.

[TSP]

I'm running IPB 3.4.0 and already applied that patch that correct some performance issues, but I'm having a lot of performance problems since prior versions. Mostly with locked mysql queries from IPB.

Any specific queries your host see that you're having problems with? Did your performance problems start with IPB 3.4? Have you had a sudden increase in traffic?

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'm pretty sure changing ibf_content_cache_posts to InnoDB shouldn't cause any problems for you. I think the only IP.Board related table you have to be more properly prepared before changing is ibf_posts. We changed quite a few tables over to InnoDB long before we did a complete switch for all tables some years back, due to performance problems that started after we upgraded from version 2.3.6 to 3.0.5.

In order to change ibf_posts you would need to be ready with an alternative search engine, like Sphinx, the one officially supported by IPS.

Anyone know if a can enable those settings without problems ?

If it causes problems after you've switched content_cache_posts to InnoDB I think you can just disable content caching temporarily while you get it converted back to MyISAM again.

Obs: My forum have 100k+ users and 1 million+ posts. My server is running with 4288mb ram, RAID-10 HD, Dual Six-Core Intel CPU with HT enabled.

And how many simultaneous users? Daily post activity? I don't know anything about specific server configurations, so I can't answer you on that.

But I would strongly recommend speaking to IPS or more experienced server and IPB people about it first. Changing that and other tables to InnoDB may help you, but the real reason you're experiencing trouble could be due to something IPS should optimize or other problems you have.

[FabioPaz]

Any specific queries your host see that you're having problems with?

The problem is always in this query:

SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title as member_title, m.warn_level, m.warn_lastwarn, m.members_display_name, m.members_seo_name, m.member_banned, m.has_gallery, m.has_blog, m.members_bitoptions,m.mgroup_others,pp.*,w.wl_id,rep_index.rep_rating as has_given_rep,rep_cache.rep_points, rep_cache.rep_like_cache,cca.*,ccb.cache_content as cache_content_sig, ccb.cache_updated as cache_updated_sig FROM ibf_posts p LEFT JOIN ibf_members m ON ( m.member_id=p.author_id )
LEFT JOIN ibf_profile_portal pp ON ( m.member_id=pp.pp_member_id )
LEFT JOIN ibf_members_warn_logs w ON ( w.wl_content_app='forums' and w.wl_content_id1=p.pid )
LEFT JOIN ibf_reputation_index rep_index ON ( rep_index.app='forums' AND
rep_index.type='pid' AND
rep_index.type_id=p.pid AND
rep_index.member_id=0 )
LEFT JOIN ibf_reputation_cache rep_cache ON ( rep_cache.app='forums' AND rep_cache.type='pid' AND rep_cache.type_id=p.pid )
LEFT JOIN ibf_content_cache_posts cca ON ( cca.cache_content_id=p.pid )
LEFT JOIN ibf_content_cache_sigs ccb ON ( ccb.cache_content_id=m.member_id ) WHERE p.topic_id=26890 AND p.queued=0 ORDER BY p.pid asc LIMIT 6960,20

| 32856 | x | Query | 1 | Locked | SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.las |
| 32858 | x | Query | 1 | Locked | SELECT p.*,m.member_id as mid,m.name,m.member_group_id,

m.email,m.joined,m.posts, m.last_visit, m.las |
| 32859 | x | Query | 1 | Locked | SELECT m.*, m.member_id as my_member_id,p.*,pp.*,g.*,ccb.cache_content FROM ibf_members m LEFT JOIN |
| 32860 | x | Query | 1 | Locked | SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.las |
| 32861 | x | Query | 1 | Locked | SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.las |

But now I'm having a lot of trouble with the ibf_content_cache_posts too:

| Query | 0 | Locked | INSERT INTO ibf_content_cache_posts (`cache_content_id`,`cache_content`,`cache_updated`) VALUES(1132096,'xxxxxxxxxxxxxxx..',1353350578) ON DUPLICATE KEY UPDATE cache_content_id=VALUES(cache_

content_id),cache_content=VALUES(cache_content),cache_updated=VALUES(cache_updated)

Did your performance problems start with IPB 3.4? Have you had a sudden increase in traffic?

I'm having trouble with performance since 3.3.x. My traffic is increasing significantly in the last months.

And how many simultaneous users? Daily post activity? I don't know anything about specific server configurations, so I can't answer you on that.

There's 632 active connections now, but it can go up to 2500+.

Thank you! :D

[duxet]

I would recommend using APC or Memcached, if you don't use it yet. Look here how to configure it in IPB. You can also use mysqltuner to tune mysql server config.

[bfarber]

While I would generally always recommend MyISAM over InnoDB (although you would need to be sure your MySQL server is tuned appropriately either way), I can say that IP.Board works with either table type. Your host would be responsible for changing the MySQL configuration and assisting you with changing the MySQL database tables.

[mat206]

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.

[Rhett]

You can use innob and there can be some gains made there depending on the setup of the server and also what tables you convert. The key to resolve this is to ID the pages causing the issues and what tables those are using. Much of this will also depend on your server setup and the amount of traffic you have.

The above query looks to be the members profile, try disabling viewing members profile for your guest group, and that should have a large impact for you.

[FabioPaz]

Hello,

Profile viewing for guests is already disabled. In the beginning this helped, but not now.

IPS Support is analyzing my slow-queries-log, let's see what happens...

[GreenLinks]

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 , specifically with big boards there are too much performance difference with InnoDB vs MyISAM. I did some tests after import and differences was a lot.

[FabioPaz]

Agreed , specifically with big boards there are too much performance difference with InnoDB vs MyISAM. I did some tests after import and differences was a lot.

Good to know! ^^

[maddog107_merged]

We have a 3mil+ post, 40k member board and everything is on InnoDB except for a few Cache tables which are in memory (I dont mind recreating them on DB bounce). We switched to InnoDB back in the 2.x series due to table locking on the large posts tables and have never looked back.

The only thing as mentioned previously is the full text search will not work, but we have been using Sphinx. We are considering upgrading to MySQL 5.6 which has full text on InnoDB and see what happens but I havent had a chance to test it yet.

[FabioPaz]

Thank you maddog...

I will do the alteration... Until now IPB can't find a solution(again) and my board is still slow.... I can't wait no more.

[FabioPaz]

I converted some tables to InnoDB

ibf_content_cache_posts
ibf_reputation_cache
ibf_sessions

Now, everytime I try to use the search I receive that error: "There's a problem communicating with the database" and Warning: mysqli_query() [function.mysqli-query]: (70100/1317): Query execution was interrupted in /forum/ips_kernel/classDbMysqliClient.php on line 234(looks like a time out error)

Just bad luck because of the mysql instability or the InnoDB conversion caused this ?

Thanks

[bfarber]

You should check the SQL error logs generated in your /cache folder. Most likely, your host will need to tune your database to use innodb.

[FabioPaz]

What exactly I have to do in the db to use InnoDB or I will have to use sphinx for now on ?

Thats the error I'm getting:

Error: 1317 - Query execution was interrupted
IP Address: xx- /forum/index.php?app=core&module=search&do=search&fromMainBar=1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: SELECT p.pid as id, p.post_date, p.topic_id,t.title, t.posts, t.views FROM ibf_posts p LEFT JOIN ibf_topics t ON ( t.tid=p.topic_id ) WHERE t.forum_id IN (3,28,4,6,15,37,38,34,43,53,57,27,32,56,50,54,31,35,42,8,9,12,13,26,40,21,51,44,45,46,49) AND t.tid=87800 AND p.queued=0 AND t.approved=1 AND t.topic_archive_status IN (0,3) AND MATCH( p.post ) AGAINST( '+jeef +sied' IN BOOLEAN MODE ) AND t.state != 'link' ORDER BY post_date desc LIMIT 0,201
.--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
| File | Function | Line No. |
|----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------|
| admin/sources/classes/search/controller.php | [search_engine_forums].search | 544 |
'----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
| admin/applications/core/modules_public/search/search.php | [IPSSearch].search | 661 |
'----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
| admin/applications/core/modules_public/search/search.php | [public_core_search_search].searchResults | 171 |
'----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
| admin/sources/base/ipsController.php | [public_core_search_search].doExecute | 306 |
'----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date: Wed, 05 Dec 2012 01:52:48 +0000
Error: 1317 - Query execution was interrupted
IP Address: xx - /forum/index.php?app=core&module=search&do=search&fromMainBar=1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: SELECT pid FROM ibf_posts WHERE topic_id IN (0,105580,104687,105529,104624,103833,90984,102571,101227,101915,101525,100385,100178,99403,98459,98178,98091,98030,98033,98011,98023,97594,76632,97596,96569,95078,89852,50722,88428,89272,89745,89604,89514,85243,88026,88001,87252,73759,86248,87189,86455,82073,81449,74050,74187,74949,74425,73885,68187) AND new_topic=1
.--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
| File | Function | Line No. |
|----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------|
| admin/applications/forums/extensions/search/engines/sql.php | [search_engine_forums]._buildWhereStatement | 173 |
'----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
| admin/sources/classes/search/controller.php | [search_engine_forums].search | 544 |
'----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
| admin/applications/core/modules_public/search/search.php | [IPSSearch].search | 661 |
'----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
| admin/applications/core/modules_public/search/search.php | [public_core_search_search].searchResults | 171 |
'----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
| admin/sources/base/ipsController.php | [public_core_search_search].doExecute | 306 |
'----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[bfarber]

The error message would seem to indicate MySQL just stopped the query, perhaps because it was taking too long.

The default my.cnf MySQL configuration file is not very optimal for larger data sets, either for MyISAM or InnoDB, however the inefficient configuration is far more noticeable with InnoDB in my opinion. You are almost certainly going to need to tune your MySQL configuration file if you are now using InnoDB to provide more resources to MySQL.

Google for mysqltuner and try running this on your server. Your host may need to do it for you.

[FabioPaz]

Andrew from support, solved the problem for me. For some reason, something bad happened in the DB (Nothing with the InnoDB conversion).

Thank you for the tip about the mysqltuner.

Now I'm fighting to put the sphinx to work.

[Dmacleo]

sphinx is nice...just not fully supported :sad:

I disabled it but am really thinking of re-enabling and not caring about gallery, etc.

the wildcard stuff is really needed for me.

hint hint bfarber :tongue: :smile:

[maddog107_merged]

Andrew from support, solved the problem for me. For some reason, something bad happened in the DB (Nothing with the InnoDB conversion).

Thank you for the tip about the mysqltuner.

Now I'm fighting to put the sphinx to work.

I would suggest you convert the 'posts' table as that is the main one that use to lock for us. I dont think any of the ones you converted would provided you a significant performance impact.

Also you can put your sessions table in memory. Here is my layout, the MyISM are empty tables if I am not mistaken, never changed them over for whatever reason.

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'xxxxxxx';
+-------------------------------+--------+
| TABLE_NAME                    | ENGINE |
+-------------------------------+--------+
| ibf_admin_login_logs          | InnoDB |
| ibf_admin_logs                | InnoDB |
| ibf_admin_permission_rows     | InnoDB |
| ibf_announcements             | InnoDB |
| ibf_api_log                   | InnoDB |
| ibf_api_users                 | InnoDB |
| ibf_attachments               | InnoDB |
| ibf_attachments_type          | InnoDB |
| ibf_badwords                  | InnoDB |
| ibf_banfilters                | InnoDB |
| ibf_bbcode_mediatag           | InnoDB |
| ibf_bulk_mail                 | InnoDB |
| ibf_cache_simple              | InnoDB |
| ibf_cache_store               | InnoDB |
| ibf_cal_calendars             | InnoDB |
| ibf_cal_event_comments        | InnoDB |
| ibf_cal_event_ratings         | InnoDB |
| ibf_cal_event_rsvp            | InnoDB |
| ibf_cal_events                | MyISAM |
| ibf_cal_import_feeds          | InnoDB |
| ibf_cal_import_map            | InnoDB |
| ibf_captcha                   | InnoDB |
| ibf_content_cache_posts       | InnoDB |
| ibf_content_cache_sigs        | InnoDB |
| ibf_converge_local            | InnoDB |
| ibf_core_applications         | InnoDB |
| ibf_core_archive_log          | InnoDB |
| ibf_core_archive_restore      | InnoDB |
| ibf_core_archive_rules        | InnoDB |
| ibf_core_editor_autosave      | InnoDB |
| ibf_core_geolocation_cache    | InnoDB |
| ibf_core_hooks                | InnoDB |
| ibf_core_hooks_files          | InnoDB |
| ibf_core_incoming_email_log   | InnoDB |
| ibf_core_incoming_emails      | InnoDB |
| ibf_core_inline_messages      | InnoDB |
| ibf_core_item_markers         | InnoDB |
| ibf_core_item_markers_storage | InnoDB |
| ibf_core_like                 | InnoDB |
| ibf_core_like_cache           | InnoDB |
| ibf_core_rss_imported         | InnoDB |
| ibf_core_share_links          | InnoDB |
| ibf_core_share_links_caches   | InnoDB |
| ibf_core_share_links_log      | InnoDB |
| ibf_core_soft_delete_log      | InnoDB |
| ibf_core_sys_conf_settings    | InnoDB |
| ibf_core_sys_cp_sessions      | InnoDB |
| ibf_core_sys_lang             | InnoDB |
| ibf_core_sys_lang_words       | InnoDB |
| ibf_core_sys_login            | InnoDB |
| ibf_core_sys_module           | InnoDB |
| ibf_core_sys_settings_titles  | InnoDB |
| ibf_core_tags                 | InnoDB |
| ibf_core_tags_cache           | InnoDB |
| ibf_core_tags_perms           | InnoDB |
| ibf_core_uagent_groups        | InnoDB |
| ibf_core_uagents              | InnoDB |
| ibf_custom_bbcode             | InnoDB |
| ibf_dnames_change             | InnoDB |
| ibf_emoticons                 | InnoDB |
| ibf_error_logs                | InnoDB |
| ibf_faq                       | InnoDB |
| ibf_forum_perms               | InnoDB |
| ibf_forums                    | InnoDB |
| ibf_forums_archive_posts      | MyISAM |
| ibf_forums_recent_posts       | InnoDB |
| ibf_groups                    | InnoDB |
| ibf_ignored_users             | InnoDB |
| ibf_inline_notifications      | InnoDB |
| ibf_ipbwiki_conf              | InnoDB |
| ibf_ipbwiki_discussion_topic  | InnoDB |
| ibf_ipbwiki_group             | InnoDB |
| ibf_ipbwiki_group_rights      | InnoDB |
| ibf_login_methods             | InnoDB |
| ibf_mail_error_logs           | InnoDB |
| ibf_mail_queue                | InnoDB |
| ibf_member_status_actions     | InnoDB |
| ibf_member_status_replies     | InnoDB |
| ibf_member_status_updates     | InnoDB |
| ibf_members                   | InnoDB |
| ibf_members_partial           | InnoDB |
| ibf_members_warn_actions      | InnoDB |
| ibf_members_warn_logs         | InnoDB |
| ibf_members_warn_reasons      | InnoDB |
| ibf_message_posts             | MyISAM |
| ibf_message_text              | InnoDB |
| ibf_message_topic_user_map    | InnoDB |
| ibf_message_topics            | MyISAM |
| ibf_message_topics_old        | InnoDB |
| ibf_mobile_app_style          | InnoDB |
| ibf_mobile_device_map         | InnoDB |
| ibf_mobile_notifications      | InnoDB |
| ibf_mod_queued_items          | InnoDB |
| ibf_moderator_logs            | InnoDB |
| ibf_moderators                | InnoDB |
| ibf_permission_index          | InnoDB |
| ibf_pfields_content           | InnoDB |
| ibf_pfields_data              | InnoDB |
| ibf_pfields_groups            | InnoDB |
| ibf_polls                     | InnoDB |
| ibf_posts                     | InnoDB |
| ibf_posts_fixed               | InnoDB |
| ibf_profile_friends           | InnoDB |
| ibf_profile_friends_flood     | InnoDB |
| ibf_profile_portal            | InnoDB |
| ibf_profile_portal_views      | InnoDB |
| ibf_profile_ratings           | InnoDB |
| ibf_question_and_answer       | InnoDB |
| ibf_rc_classes                | InnoDB |
| ibf_rc_comments               | InnoDB |
| ibf_rc_modpref                | InnoDB |
| ibf_rc_reports                | InnoDB |
| ibf_rc_reports_index          | InnoDB |
| ibf_rc_status                 | InnoDB |
| ibf_rc_status_sev             | InnoDB |
| ibf_reputation_cache          | InnoDB |
| ibf_reputation_index          | InnoDB |
| ibf_reputation_levels         | InnoDB |
| ibf_reputation_totals         | InnoDB |
| ibf_rss_export                | InnoDB |
| ibf_rss_import                | InnoDB |
| ibf_rss_imported              | InnoDB |
| ibf_search_keywords           | InnoDB |
| ibf_search_sessions           | InnoDB |
| ibf_search_visitors           | InnoDB |
| ibf_seo_acronyms              | InnoDB |
| ibf_seo_meta                  | InnoDB |
| ibf_sessions                  | MEMORY |
| ibf_skin_cache                | InnoDB |
| ibf_skin_collections          | InnoDB |
| ibf_skin_css                  | InnoDB |
| ibf_skin_css_previous         | InnoDB |
| ibf_skin_generator_sessions   | InnoDB |
| ibf_skin_merge_changes        | InnoDB |
| ibf_skin_merge_session        | InnoDB |
| ibf_skin_replacements         | InnoDB |
| ibf_skin_templates            | InnoDB |
| ibf_skin_templates_cache      | InnoDB |
| ibf_skin_templates_previous   | InnoDB |
| ibf_skin_url_mapping          | InnoDB |
| ibf_spam_service_log          | InnoDB |
| ibf_sph_counter               | InnoDB |
| ibf_spider_logs               | InnoDB |
| ibf_subscription_currency     | InnoDB |
| ibf_subscription_extra        | InnoDB |
| ibf_subscription_logs         | InnoDB |
| ibf_subscription_methods      | InnoDB |
| ibf_subscription_trans        | InnoDB |
| ibf_subscriptions             | InnoDB |
| ibf_tags                      | InnoDB |
| ibf_tags_index                | InnoDB |
| ibf_task_logs                 | InnoDB |
| ibf_task_manager              | InnoDB |
| ibf_template_sandr            | InnoDB |
| ibf_titles                    | InnoDB |
| ibf_topic_mmod                | InnoDB |
| ibf_topic_ratings             | InnoDB |
| ibf_topic_views               | InnoDB |
| ibf_topics                    | InnoDB |
| ibf_twitter_connect           | InnoDB |
| ibf_upgrade_history           | InnoDB |
| ibf_upgrade_sessions          | InnoDB |
| ibf_validating                | InnoDB |
| ibf_voters                    | InnoDB |
| ibf_warn_logs                 | InnoDB |
| topics_to_topics              | InnoDB |
| users_to_topics               | InnoDB |
+-------------------------------+--------+
167 rows in set (0.05 sec)

[FabioPaz]

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

[bfarber]

InnoDB does not support fulltext indexes. The error is relatively straightforward and accurate. :)

You would need to drop any fulltext indexes on the table before converting it to innodb.

[FabioPaz]

InnoDB does not support fulltext indexes. The error is relatively straightforward and accurate. :smile:

You would need to drop any fulltext indexes on the table before converting it to innodb.

Sorry, I simply don't know about databases. Its not clear for me hehe.

I'm searching on how to do that. Thank you!

[p4guru]

InnoDB does not support fulltext indexes. The error is relatively straightforward and accurate. :smile:

You would need to drop any fulltext indexes on the table before converting it to innodb.

or wait for MySQL 5.6 which has innodb fulltext support heh

[bfarber]

I'm going to make the assumption he does not have control over which version of MySQL is running on his server. If you do, feel free to upgrade MySQL, sure.

[FabioPaz]

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 ?