Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)

Started by Vekseid, February 16, 2009, 06:29:50 AM

Previous topic - Next topic

vakfikebirli

.:Bilgi paylaştıkça çoğalır:.
Bürokrasi | Bilginin Adresi

Francisco1986

Can I move my avatars manually when doing that little avatar trick? I'm having a "can't write to directory" error when trying to apply the hidden option URL. Tried 755, 775 and 777 chmods, to no effect, and my paths are correct.

Is there a way to move them manually and not break the avatar links?

butch2k

i won't recommand converting smf_log_activity to InnoDB format if innodb_flush_log_at_trx_commit is set to 1 (value of 0 is ok and 2 not tested yet).

Indeed the stat related update query is much slower in this case.
UPDATE smf_log_activity
SET
   hits = hits + 1
WHERE date = '2011-03-24'


0.11303592 seconds with InnoDB
0.00067019 seconds with InnoDB and innodb_flush_log_at_trx_commit = 0
0.00062704 seconds with MyISAM

Something like that

Quote from: butch2k on March 24, 2011, 07:01:56 AM
i won't recommand converting smf_log_activity to InnoDB format if innodb_flush_log_at_trx_commit is set to 1 (value of 0 is ok and 2 not tested yet).

2 is safer/better than 0. But it doesn't really make a difference with SMF (it doesn't use transactions, so any crash is going to cause issues anyway).

If you have a battery-backed disk/raid controller, or SSD, leave it set to 1.

butch2k

Yes I have a battery backed raid on my production server, but not on my preproduction server on which I noticed the issue. 
You lose ACID compliance but since the message table is not innodb it does not mater either...

Something like that

Quote from: butch2k on March 24, 2011, 10:42:23 AM
Yes I have a battery backed raid on my production server, but not on my preproduction server on which I noticed the issue. 
You lose ACID compliance but since the message table is not innodb it does not mater either...

I converted all my tables to InnoDB (I use Sphinx for search, so I don't need fulltext search, which is bad for performance anyway). The only table that will cause issue with InnoDB is smf_boards (at least in SMF 1.1 for sure), because SMF relies on MyISAM's ability to store rows in a specified order (board sort order) where InnoDB always stores in primary key order. That can be fixed by changing the primary key of smf_boards, or just leave that one table as MyISAM.

butch2k

I was referencing the original recommendation my SMF is full innodb on my production server.

HunterP

Quote from: Vekseid on February 16, 2009, 06:29:50 AM
DROP TABLE smf_log_floodcontrol;
CREATE TABLE smf_log_floodcontrol (
  ip char(16) NOT NULL,
  logTime int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (ip),
  KEY logTime (logTime)
) ENGINE=MEMORY;


For SMF 2.0 the create statements for this is a bit different:


CREATE TABLE `smf_log_floodcontrol` (
  `ip` char(16) NOT NULL default '',
  `log_time` int(10) unsigned NOT NULL default '0',
  `log_type` varchar(8) NOT NULL default 'post',
  PRIMARY KEY  (`ip`,`log_type`)
) ENGINE=MEMORY;


Doesn't this table need to be dropped first in SMF 2.0 ?

Arantor

Yes, it's sort of assumed when it just refers to the 'create statement' being different.

HunterP

Quote from: Arantor on March 28, 2011, 06:58:45 PM
Yes, it's sort of assumed when it just refers to the 'create statement' being different.

Ok, just wanted to make sure :)

Joshua Dickerson

Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

chep

QuoteFor most tables this is perfectly reasonable, but for smf_personal_messages and smf_messages, as well as similar "Post and forget" tables like a Shoutbox, this can actually be counterproductive to performance, particularly with a good-sized key_buffer and concurrent_insert = 2 in your my.cnf config.

I actually found that it was a requirement to convert smf_messages to InnoDB because I was getting a lot of table locking going on. Recently upgraded to 2.0 RC5. I would recommend you check your sql commands for locking on this particular table. Not sure why we got into that situation but once I made the change to InnoDB the problems dropped away and we got a slight increase in performance (faster page load times). We run a board with about 7500 members and 850,000 topics.

Vekseid

If your members do a lot of editing, then yes, InnoDB is the only solution.
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

oOo--STAR--oOo

This tutorial helped me alot..
I have applied changes to sql to change things to innodb as stated in the 1st topic.
It does seem to of made a difference :D

uses about 300mb more ram due to the pool_limit which I set to 128 but that cool I have 4gb

I also wanna convert the rest of my forum to innodb other than those that would NOT benefit.
I do have a large amount of mods and the main ones would obviously be the portal and other mods that possibly would benefit loading times. So please can some one help me in which would improve.. I am looking at ways to improve the speed and performance of my forums when its gets busy.

Here is a list of database tables

smf_admin_info_files
smf_aeva_albums
smf_aeva_comments
smf_aeva_fields
smf_aeva_field_data
smf_aeva_files
smf_aeva_log_media
smf_aeva_log_ratings
smf_aeva_media
smf_aeva_perms
smf_aeva_quotas
smf_aeva_settings
smf_aeva_variables
smf_affiliates
smf_affiliates_banners
smf_affiliates_rating
smf_affiliate_report
smf_ajaxchat_bans
smf_ajaxchat_invitations
smf_ajaxchat_messages
smf_ajaxchat_online
smf_ajaxshout_bans
smf_ajaxshout_invitations
smf_ajaxshout_messages
smf_ajaxshout_online
smf_approval_queue
smf_arcade_categories
smf_arcade_favorite
smf_arcade_games
smf_arcade_personalbest
smf_arcade_rates
smf_arcade_scores
smf_arcade_settings
smf_arcade_shouts
smf_arcade_tournament
smf_arcade_tournament_players
smf_arcade_tournament_rounds
smf_arcade_tournament_scores
smf_arcade_v3temp
smf_attachments
smf_awards
smf_awards_categories
smf_awards_members
smf_ban_groups
smf_ban_items
smf_battle_explore
smf_battle_graveyard
smf_battle_history
smf_battle_monsters
smf_battle_quest
smf_battle_quest_hist
smf_battle_shop
smf_battle_shouts
smf_boards
smf_board_permissions
smf_buddies
smf_calendar
smf_calendar_holidays
smf_categories
smf_cf_fields
smf_cf_forms
smf_collapsed_categories
smf_custom_fields
smf_down_cat
smf_down_catperm
smf_down_comment
smf_down_creport
smf_down_custom_field
smf_down_custom_field_data
smf_down_file
smf_down_groupquota
smf_down_rating
smf_down_report
smf_down_userquota
smf_feedbot
smf_feedbot_log
smf_group_moderators
smf_helpdesk_attachments
smf_helpdesk_log_action
smf_helpdesk_log_read
smf_helpdesk_tickets
smf_helpdesk_ticket_replies
smf_links
smf_links_cat
smf_links_catperm
smf_links_rating
smf_log_actions
smf_log_activity
smf_log_banned
smf_log_boards
smf_log_comments
smf_log_digest
smf_log_enotify_pms
smf_log_enotify_replies
smf_log_errors
smf_log_floodcontrol
smf_log_group_requests
smf_log_karma
smf_log_mark_read
smf_log_member_notices
smf_log_notify
smf_log_online
smf_log_packages
smf_log_polls
smf_log_reported
smf_log_reported_comments
smf_log_rssfeeds
smf_log_scheduled_tasks
smf_log_search_messages
smf_log_search_results
smf_log_search_subjects
smf_log_search_topics
smf_log_spider_hits
smf_log_spider_stats
smf_log_subscribed
smf_log_topics
smf_log_topic_view
smf_mail_queue
smf_membergroups
smf_members
smf_member_notes
smf_messages
smf_message_icons
smf_moderators
smf_openid_assoc
smf_package_servers
smf_permissions
smf_permission_profiles
smf_personal_messages
smf_picture_comments
smf_pm_attachments
smf_pm_recipients
smf_pm_rules
smf_polls
smf_poll_choices
smf_portamx_blocks
smf_portamx_settings
smf_post_drafts
smf_pretty_topic_urls
smf_pretty_urls_cache
smf_profile_albums
smf_profile_comments
smf_profile_pictures
smf_redirect_expire
smf_rssfeeds
smf_scheduled_tasks
smf_sessions
smf_settings
smf_shop_category
smf_shop_item
smf_shop_property
smf_shop_purchHis
smf_smileys
smf_spiders
smf_subscriptions
smf_talkbox
smf_talkbox_archive
smf_talkbox_banned
smf_talkbox_media
smf_team
smf_thank_you_post
smf_themes
smf_topics


I would appreciate and help given. Thank you for this tutorial.


You can't fool a sufficiently talented fool.

http://www.uniquez-home.com
In Design Phase!

Mods I am designing,  No refresh Collapse Categories , Poll Redesign , Pure CSS Breadcrumb , Profile Statuses, Profile Views.

HunterP


QuoteALTER TABLE smf_members ENGINE = InnoDB
#1214 - The used table type doesn't support FULLTEXT indexes
Any suggestions?

Arantor

Go to the search options in the admin panel, remove the fulltext index, then do it again. After, switch to a Custom index, or better, to Sphinx.

HunterP

Quote from: Arantor on April 19, 2011, 05:34:48 PM
Go to the search options in the admin panel, remove the fulltext index, then do it again. After, switch to a Custom index, or better, to Sphinx.

Has been removed, the error remains...

Arantor

Hang on, I read that as smf_messages not smf_members...

Why is there a fulltext index on the members table? You'll have to manually remove that in phpMyAdmin.

Forum Labs

is it safe to convert smf_log_digest to innodb?


I just changed my smf_messages to innodb and my load increased from 1 to 3. Is that normal?
Premium Support & Services for SMF

HunterP

Quote from: Arantor on April 19, 2011, 06:13:56 PM
Why is there a fulltext index on the members table?

Don't know??

When I compare the indexes with another forum I see lots of differences :

This forum:

PRIMARY    PRIMARY    3576     id_member
posts    INDEX    447     posts
dateRegistered    INDEX    3576     date_registered
lastLogin    INDEX    3576     last_login
posts_2    INDEX    447     posts
dateRegistered_2    INDEX    3576     date_registered
lastLogin_2    INDEX    3576     last_login
memberName_2    INDEX    3576     member_name    30
membername    INDEX    3576     member_name    30
ID_GROUP    INDEX    6     id_group
birthdate    INDEX    894     birthdate
ID_POST_GROUP    INDEX    5     id_post_group
lngfile    INDEX    3     lngfile    24
warning    INDEX    6     warning
total_time_logged_in    INDEX    3576     total_time_logged_in
id_theme    INDEX    2     id_theme
real_name    INDEX    3576     real_name
member_name    INDEX    3576     member_name
memberIP    FULLTEXT    1     member_ip

Other forum:

PRIMARY    PRIMARY    1810     id_member
member_name    INDEX    1810     member_name
real_name    INDEX    1810     real_name
date_registered    INDEX    1810     date_registered
id_group    INDEX    4     id_group
birthdate    INDEX    14     birthdate
posts    INDEX    201     posts
last_login    INDEX    1810     last_login
lngfile    INDEX    2     lngfile    30
id_post_group    INDEX    4     id_post_group
warning    INDEX    1     warning
total_time_logged_in    INDEX    258     total_time_logged_in
id_theme    INDEX    2     id_theme

I'll remove the bolded one first. Does the first one have too many indexes?

Advertisement: