Max post per thread

[minskog]

SMF Version: SMF 1.1 RC2
What is the recomended max number of pages in a thread in smf 1.1-rc2?

I have threads over 200 pages ....

[Ben_S]

The number of pages shouldn't really cause an issue.

[minskog]

The number of pages shouldn't really cause an issue.

thank you, (I just asked this) to make sure I'm covering the basics
I updated to 1.1-rc2 and still the forum keeps crashing.

[Ben_S]

My longest topic is 312 pages long @ 40 posts per page.

[minskog]

616 pages @ 15 per page-> http://www.masquechicos.com/foro/index.php/topic,37106.0.html
401 pages @ 15 per page -> http://www.masquechicos.com/foro/index.php/topic,41620.0.html
374 pages @ 15 per page -> http://www.masquechicos.com/foro/index.php/topic,38539.0.html

The biggest ones, then you have more post than I, other thing discarted ... im lost

[Ben_S]

Do you have the basic html option enable, if so try turning it off temporarily.

[minskog]

I only permit bbcode. I have bbdd biggest than this forum and i don't have any problems. I dont known what happened with this site.

[Ben_S]

Have you checked everything listed in this topic > http://www.simplemachines.org/community/index.php?topic=49999.0

[minskog]

Yes, but now i dont have innodb tables beacuse i dont known what tables are needed in smf 1.1-rc2

[Ben_S]

The same as whats in that thread pretty much plus all the log_search ones, although that wouldn't cause the slowdown,

[minskog]

thanks for the info and for your patience 

[minskog]

I detected that all slow querys are the same:

CREATE TEMPORARY TABLE smf_topics_posted_in (
                                PRIMARY KEY (ID_TOPIC)
                        )
                        SELECT ID_TOPIC, ID_BOARD
                        FROM smf_messages
                        WHERE ID_MEMBER = 450
                        GROUP BY ID_TOPIC;

[Ben_S]

Thats the horrible show posts of a user feature, I'm hoping those querys will be rewritten by the time 1.1 goes final, as it currently is with a large number of posts it's a server killer.

[minskog]

Thats the horrible show posts of a user feature, I'm hoping those querys will be rewritten by the time 1.1 goes final, as it currently is with a large number of posts it's a server killer.

thanks. I've just have migrated to innodb. As a result, right now the load has gone down a little. I continue looking for the cause of the load

In this forum we'll never walk alone ...

[Ben_S]

You may want to disable the show users posts feature when the load is high, on mine I disable it when the load is above 2, see http://www.simplemachines.org/community/index.php?topic=76249.0

[minskog]

You may want to disable the show users posts feature when the load is high, on mine I disable it when the load is above 2, see http://www.simplemachines.org/community/index.php?topic=76249.0

Thanks, but now are burnig the server again:Load average: 14.32 8.03 6.18

With this feature active:

Lock wait timeout exceeded; try restarting transaction
File: /usr/var/www/virtual/web1/web/foro/Sources/Display.php
Línea: 140


My innodb conf:

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


Then, this need more than 50 seconds to execute transactions.

[Ben_S]

Whats the entire contents of your my.cnf?

[minskog]

Whats the entire contents of your my.cnf?

Code Select Expand


[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock


[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[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
max_allowed_packet      = 16M
thread_stack            = 128K
max_connections=500
thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2
query_cache_limit       = 1048576
query_cache_size        = 16777216
query_cache_type        = 1
log-slow-queries        = /var/log/mysql/mysql-slow.log
log-bin                 = /var/log/mysql/mysql-bin.log
max_binlog_size         = 104857600
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

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M


[Ben_S]

Are you using MySQL replication, I assume not so comment out these lines

log-bin                 = /var/log/mysql/mysql-bin.log
max_binlog_size         = 104857600

Increase table_cache to 900

Reduce sort_buffer_size to 512K, this is a per connection setting so that should save some ram and you only have 500MB.

[minskog]

i change this parameters and now: 119 (0 sleeping, 3 running, 116 locked)

[Ben_S]

If someone is doing a search or using the show posts of a user, then that is going to happen.

[minskog]

If someone is doing a search or using the show posts of a user, then that is going to happen.

I detect that when mysql goes slower, apache cant drop conections, this could be the big problem, i think. Eats 200 conections and cant drop them.

[Ben_S]

Whats your max clients set to in httpd.conf, I personally set mine quite low to limit the number of people connection, think mines set to 170 or so.

This way apache will queue connections whilst limiting the load on MySQL without causing the horrible too many connections error you get with setting the mysql max connections limit lower.

[minskog]

In apache 200 and mysql with 300, all without persistent conections.

[Ben_S]

Enable persistent connections, it should help reduce load.

[minskog]

Enable persistent connections, it should help reduce load.

I try it and works very bad in this site, IMHO here are something bad configured or corrupt, but i dont find   it.

[Ben_S]

Try reducing your max clients in httpd.conf to something like 150.

[minskog]

Try reducing your max clients in httpd.conf to something like 150.

done, persistent active and mysql conections to 300.

[minskog]

Continue breaking but less than before to change the persistent flag. this morning has been break but only twice.

[minskog]

Today status.php

Code Select Expand

Connections per second:  0.0739
Kilobytes received per second: 2.6205
Kilobytes sent per second: 14.7567
Queries per second: 9.2965
Percentage of slow queries: 0.0028
Opened vs. Open tables: (table_cache) 27.0656 (should be <= 80)
Table cache usage: (table_cache) 1 (should be >= 0.5 and <= 0.9) <- ***
Key buffer read hit rate: (key_buffer_size) 0.0039 (should be <= 0.01)
Key buffer write hit rate: (key_buffer_size) 0.0485 (should be <= 0.5)
Thread cache hit rate: (thread_cache_size) 5.0829 (should be >= 30 ) <- ***
Thread cache usage: (thread_cache_size) 0.84 (should be >= 0.7 and <= 0.9)
Temporary table disk usage: (tmp_table_size) 0.4572 (should be <= 0.5)
Sort merge pass rate: (sort_buffer) 0 (should be <= 0.001)
Query cache enabled: (query_cache_type) 1 (should be >= 1 and <= 1)
Query cache miss rate: (query_cache_limit) 0.0463 (should be <= 0.1)
Query cache prune rate: (query_cache_size) 0.0199 (should be <= 0.05)


[minskog]

Now the slow querys are like:

Code Select Expand


INSERT IGNORE INTO smf_log_search_results
(ID_SEARCH, relevance, ID_TOPIC, ID_MSG, num_matches)
SELECT
217,
1000 * (30 * COUNT(m.ID_MSG) / (t.numReplies + 1) + 25 * IF(MAX(m.ID_MSG) < 0, 0, (MAX(m.ID_MSG) - 0) / 1534796) + 20 * IF(t.numReplies < 200, t.numReplies / 200, 1) + 15 * 0 + 10 * IF(MIN(m.ID_MSG) = t.ID_FIRST_MSG, 1, 0) + 0 * t.isSticky) / 100 AS relevance,
t.ID_TOPIC,
IF(COUNT(m.ID_MSG) = 0, t.ID_FIRST_MSG, MAX(m.ID_MSG)) AS ID_MSG,
COUNT(m.ID_MSG) AS num_matches
FROM (smf_topics AS t, smf_messages AS m)
WHERE t.ID_TOPIC = m.ID_TOPIC
AND (m.body RLIKE '%s' AND m.body RLIKE '%s')
AND m.ID_BOARD IN (2, 23, 35, 3, 33, 4, 5, 14, 40, 21, 18, 20, 6, 15, 34, 30, 9, 12, 16, 17, 11, 10, 32, 22, 26, 28, 42, 7, 37, 31, 36, 43, 44)
GROUP BY t.ID_TOPIC
LIMIT 6000


Sending data     168s

[Ben_S]

What search method are you using, id recommend fulltext and force use of an index.

[minskog]

I have it and ...

Sending data     787s

Code Select Expand


INSERT IGNORE INTO smf_log_search_results
(ID_SEARCH, relevance, ID_TOPIC, ID_MSG, num_matches)
SELECT
217,
1000 * (30 * COUNT(m.ID_MSG) / (t.numReplies + 1) + 25 * IF(MAX(m.ID_MSG) < 0, 0, (MAX(m.ID_MSG) - 0) / 1534796) + 20 * IF(t.numReplies < 200, t.numReplies / 200, 1) + 15 * 0 + 10 * IF(MIN(m.ID_MSG) = t.ID_FIRST_MSG, 1, 0) + 0 * t.isSticky) / 100 AS relevance,
t.ID_TOPIC,
IF(COUNT(m.ID_MSG) = 0, t.ID_FIRST_MSG, MAX(m.ID_MSG)) AS ID_MSG,
COUNT(m.ID_MSG) AS num_matches
FROM (smf_topics AS t, smf_messages AS m)
WHERE t.ID_TOPIC = m.ID_TOPIC
AND (m.body RLIKE '%s' AND m.body RLIKE '%s')
AND m.ID_BOARD IN (2, 23, 35, 3, 33, 4, 5, 14, 40, 21, 18, 20, 6, 15, 34, 30, 9, 12, 16, 17, 11, 10, 32, 22, 26, 28, 42, 7, 37, 31, 36, 43, 44)
GROUP BY t.ID_TOPIC
LIMIT 6000


and continues growing: Sending data     958s

[minskog]

I need to install three smf more, and this make me doubt a little ...

[Ben_S]

Are your log_search tables deffinatly innodb?

[minskog]

Are your log_search tables deffinatly innodb?

Yes, all log tables, i ask for this in other thread

[minskog]

This continues breaking, but i see that break in manteinance mode too (aka only read)...   

[Ben_S]

Which break?

[minskog]

Goes crazy load, it have 0.40 and goes to 4 or 5 in seconds.

[Ben_S]

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

[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.