Max post per thread

[minskog]

Put it in full manteinance mode (edit Settings.php and change the value to 2) and see what happens.

Ok

[minskog]

Definitively, the problem is the searchs (the famous querys of: 'INSERT IGNORE INTO smf_log_search_results
   (ID_SEARCH, relevance, ID_TOPIC, ID_MSG, num_matches)
SELECT
   119,
   1000 * (30 * COUNT(m.ID_MSG) .....'), and the creation of big temporary tables ,in these minimize the impact raising tmp_table size 64M, by default is 32M, but the problem continues there.

[minskog]

I put the forum in maitenance 1, and try to make searchs with myisam and innodb and the time to make them is the same, because in maintenance there are not write locks, only for sessions i think.  The time to make the search querys is over 700 seconds ever, then the problem is not use myisam or innodb, innodb help to minimize the impact, but the search system have problems with big forums i think.

I use mytop to monitorize the querys.

[Ben_S]

What search method are you using? I have no real problems with 2million posts using fulltext

[minskog]

im using fultext, creating it in the forum option. It have 230 mb.

I am making benchmarkings, and i am detecting that the server with myisam tables have 0.2 of average load, and with innodb have 0.8 of average load.  The time to generate de searchs is very similar, and i having locks with innodb.

[Ben_S]

Just to be absolutly sure, you aren't changing the messages table to InnoDB are you? InnoDB doesn't support fulltext searching so you will loose the benefits of that.

With InnoDB you should see less locks  because it has row level locking which means only the rows being accessed are locked whereas MyISAM have to lock the entire table which causes a build up of queries to be waiting.

[minskog]

No, i use this to change.

Code Select Expand


ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
TYPE=InnoDB;
ALTER TABLE smf_log_actions
TYPE=InnoDB;
ALTER TABLE smf_log_boards
TYPE=InnoDB;
ALTER TABLE smf_log_errors
TYPE=InnoDB;
ALTER TABLE smf_log_karma
TYPE=InnoDB;
ALTER TABLE smf_log_mark_read
TYPE=InnoDB;
ALTER TABLE smf_log_online
TYPE=InnoDB;
ALTER TABLE smf_log_topics
TYPE=InnoDB;
ALTER TABLE smf_members
TYPE=InnoDB;
ALTER TABLE smf_pm_recipients
TYPE=InnoDB;
ALTER TABLE smf_sessions
TYPE=InnoDB;
ALTER TABLE smf_settings
TYPE=InnoDB;
ALTER TABLE smf_topics
TYPE=InnoDB;
ALTER TABLE smf_log_search_messages
TYPE = InnoDB;
ALTER TABLE smf_log_search_results
TYPE=InnoDB;
ALTER TABLE smf_log_search_subjects
TYPE=InnoDB;
ALTER TABLE smf_log_search_topics
TYPE=InnoDB;


[minskog]

Could be a good idea to make a tutorial for try to explain how can interpretate the status.php script. Because we can see if a parameter are in the limits, but if this parameter gives bad numbers, we dont known what to check, i am not saying that explain how to repair, i am saying to explain what parameters we need to check if is lower or bigger than the correct number.

[minskog]

I deactivate the searchs and dont break anymore, but if i do a search (only admin can do them) , goes crazy. I think that the search system is not optimized for big sites, or sites with big posts.

[Ben_S]

Fulltext works perfectly fine on my board with almost 2million posts, although my server does have 2GB ram.

[minskog]

Fulltext works perfectly fine on my board with almost 2million posts, although my server does have 2GB ram.

The problem are not the searchs, the problem is the insert into search_logs tables,  the index works perfectly.

[minskog]

After a lot of tests, the configuration that gives the best perfomance is:

Code Select Expand


[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
old_passwords   = 1
bind-address            = 127.0.0.1
key_buffer              = 40M
max_allowed_packet      = 16M
thread_stack            = 128K
table_cache=512
tmp_table_size=128M
thread_cache_size=50
thread_concurrency=2
sort_buffer_size=768k
read_buffer_size=512k
read_rnd_buffer_size=512k
query_cache_limit       = 1048576
query_cache_size        = 16777216
query_cache_type        = 1
skip-bdb
innodb_buffer_pool_size         = 16M
innodb_additional_mem_pool_size = 2M
innodb_data_file_path           = ibdata1:100M;ibdata2:100M:autoextend
innodb_log_file_size            = 5M
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 1
innodb_lock_wait_timeout        = 50


But i can't find corrects values for this variables:

Code Select Expand


Table cache usage: 1
(table_cache) (should be >= 0.5 and <= 0.9)
Thread cache usage: 0.46
(thread_cache_size) (should be >= 0.7 and <= 0.9)



Table cache gives 1 always, with 512, 756, 900 ... 

[minskog]

The problem with log_search tables could be have a 'solution'.  If i limit the number of results, for example with 500 for this forum, the problem gone away.

I didnt find any solution for the tmp tables problem.

[Ben_S]

It's not really a problem, getting everything to be within the recommended values is pretty much impossible.