Max post per thread

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