Average big(1.7mln posts) forum setting HELP needed

Started by evgenydeep, September 05, 2008, 01:31:50 PM

Previous topic - Next topic


I have average big forum on my dedicated server

I have converted me forum recently from Yabb 2.1 to SMF 1.1.5
It was long process... convertor has some bugs :)... etc

After installing all looks ok, BUT! until real users come to my forum.

It was nightmare... server almost died.
CPU load was too big.
After some monkey job, some settings... server become workable, but still slow with very big lags and timeouts.

the main reason - due to MySQL.

Here is my settings for mysql
long_query_time = 5
query-cache-type = 1
key_buffer = 100M
set-variable = max_connections=800
old-passwords = 1

Right now i have very small amount of users online(is about 100 users online), but mysql load my cpu 40-70%.

My server is P4 3.2GHz/2GB RAM, more detailed below:

My forum is http://forum.grodno.net/
My status.php is here: http://forum.grodno.net/status.php
My PHP-Info is http://forum.grodno.net/info.php
My forum is on my dedicated server. There is is about 30 small websites, except forum.
Forum have is ~1,7mln posts, ~15000 registred users, ~30000threads.
My peak load is ~200 users online, and ~500 during half hour.
2 mods installed right now: "ad management" and "images on board".
I use only default theme, and yes, perfomance issue exist.

I'm not converted any of my tables to InnoDB

Please, advise what settings should be set to make my forum really optimized, stable and workable?

青山 素子

First, look over the my-huge.cnf file that comes with most MySQL installs. Some of your existing config values are very high and probably causing some memory issues - unless you happen to have a terabyte of memory in the system.

Next, look over the stickies in here for some scripts and guidance that will help in working out better settings for your database. MySQL optimization is an art, so it will probably take some time to get really good settings for your load and database size.
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Thank you Motoko-chan!

Yesterday I readed some topics here and decided to convert all recommended here "    
Convert your tables to InnoDB " http://www.simplemachines.org/community/index.php?topic=50217.0 tables to InooDB.

These are following tables:
ALTER TABLE smf_attachments TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_collapsed_categories TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_actions TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_boards TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_errors TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_karma TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_mark_read TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_online TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_messages TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_results TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_subjects TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_topics TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_topics TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_members TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_pm_recipients TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_sessions TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_settings TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_topics TYPE=InnoDB;

But as you understand, - this is not decided my main problem - CPU load. Not it is about 20%, but now is very small amout of users on my forum - is about 50 users and 20 guests. Later this numbers will be much more... I expect is about 150-200 users online at the same moment.


I have used my.cnf for large systems, and here is my.cnf which is used on my server

Please, advise optimal settings.


PS:  I have installed AD mod, which add table smf_ads. I decided to make it INNODB also.

ALTER TABLE forumgr_forum.smf_ads TYPE=InnoDB

pps: but now I disable any reporting in this mod, so if not using reports about ads shows this converting not worth.


are you using persitent connections? try to disable/enable

in my.cnf
comment out the log-bin (logging can use much CPU and memory)

key_buffer = 384M (seems extremly high)
try to reduce this, i would start with

key_buffer = 64M

and from the conversion: install a blank, fresh SMF and compare all indexes. maybe during the conversion some table indexes got lost ..


I disabled persitent connections, since i read somewhere here that this is not good for performance.

Ok, I tried to enable it right now, I'll look for server behavior now...

I commented #log-bin and set key_buffer = 200M

... resarted server... for now load is not too much - is only about 100-130 users online
load is around 10%-20% which is still high for such amount of users.

Thank you TE! I did exactly as you suggest :-) I already understand this during conversion :) I added some indexes after convertion manually. I checked all tables and all indexes - they are correct. So, I'm sure DB structure is correct.


After 1 hour of working forum with "persitent connection" I disabled it again, becasue CPU load become more than 50%. I'm unsure that this is due to "persitent connection", but it look so.


don't know something about your users .. maybe they are using the forum search a lot? it's strongly recommended creating a search-Index for big forums.. (I prefer the Full Text Index)


I noticed that I got very much queries like
SELECT variable, value, ID_MEMBER, ID_THEME\n            FROM smf_themes\n            WHERE ID_MEMBER IN (-1, 0, 3066)\n 



also, I saw, sometimes I get locked almost all my queries
SELECT\n                m.ID_MSG, m.subject, m.posterName, m.posterEmail, m.posterTime, m.ID_MEMBER,\n                m.icon,
SELECT b.ID_BOARD, b.permission_mode, bp.addDeny\n        FROM (smf_boards AS b, smf_board_permissions AS b
SELECT ID_MSG, ID_MEMBER\n        FROM smf_messages\n        WHERE ID_TOPIC = 3204\n        ORDER BY ID_MSG DESC\n        LIMIT
SELECT ID_MSG, ID_MEMBER\n        FROM smf_messages\n        WHERE ID_TOPIC = 21163\n        ORDER BY ID_MSG DESC\n        LIMIT
UPDATE smf_messages\n        SET ID_MSG_MODIFIED = 1724428\n        WHERE ID_MSG = 1724428
SELECT\n            c.name AS catName, c.ID_CAT, b.ID_BOARD, b.name AS boardName, b.description, b.img_filenam
... and similar


Quote from: TE on September 06, 2008, 02:15:35 PM
don't know something about your users .. maybe they are using the forum search a lot? it's strongly recommended creating a search-Index for big forums.. (I prefer the Full Text Index)
Thank you for the advise. In SMF Admin -> Search says that "Custom index" more stable, and FASTER, QUICKER, even if it weight in 2-3 times more. On my forum:
Space used by forum messages in the database: 494843.12 KB
Space used to index messages in the database: 135268 KB
what option I should choose, how do you think? I think i can immolate some hundreds of Mb on server for "Custom index" to make my forum a little faster ... 


On my expectation, custom index will take 1.6GB on my forum...
Now I'm on the way to do it.


I noticed that search query like
INSERT IGNORE INTO smf_log_search_results\n                        (ID_SEARCH, relevance, ID_TOPIC, ID_MSG, num_matches
making Locked all other queries.

How to avoid this?


You could do with running sphinx for search really.
Liverpool FC Forum with 14 million+ posts.