Advertisement:

Author Topic: Max post per thread  (Read 26861 times)

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Max post per thread
« on: April 10, 2006, 06:24:40 AM »
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 ....

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #1 on: April 10, 2006, 06:25:51 AM »
The number of pages shouldn't really cause an issue.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #2 on: April 10, 2006, 08:43:21 AM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #3 on: April 10, 2006, 09:33:28 AM »
My longest topic is 312 pages long @ 40 posts per page.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #4 on: April 10, 2006, 11:24:37 AM »
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 ;)

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #5 on: April 10, 2006, 12:17:19 PM »
Do you have the basic html option enable, if so try turning it off temporarily.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #6 on: April 10, 2006, 01:22:40 PM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #7 on: April 10, 2006, 01:35:40 PM »
Have you checked everything listed in this topic > http://www.simplemachines.org/community/index.php?topic=49999.0
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #8 on: April 10, 2006, 02:52:16 PM »
Yes, but now i dont have innodb tables beacuse i dont known what tables are needed in smf 1.1-rc2

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #9 on: April 10, 2006, 03:59:42 PM »
The same as whats in that thread pretty much plus all the log_search ones, although that wouldn't cause the slowdown,
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #10 on: April 10, 2006, 05:03:09 PM »
thanks for the info and for your patience  :)

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #11 on: April 11, 2006, 06:03:07 AM »
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;


Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #12 on: April 11, 2006, 08:08:15 AM »
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.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #13 on: April 11, 2006, 09:02:10 AM »
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 ... ;)

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #14 on: April 11, 2006, 09:53:23 AM »
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
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #15 on: April 11, 2006, 10:27:09 AM »
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.
« Last Edit: April 11, 2006, 10:38:50 AM by minskog »

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #16 on: April 12, 2006, 05:24:59 AM »
Whats the entire contents of your my.cnf?
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #17 on: April 16, 2006, 08:15:38 AM »
Whats the entire contents of your my.cnf?
Code: [Select]
[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

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #18 on: April 16, 2006, 06:26:13 PM »
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.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #19 on: April 17, 2006, 06:13:49 AM »
i change this parameters and now: 119 (0 sleeping, 3 running, 116 locked)

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #20 on: April 17, 2006, 09:20:18 AM »
If someone is doing a search or using the show posts of a user, then that is going to happen.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #21 on: April 19, 2006, 07:52:34 AM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #22 on: April 19, 2006, 08:16:15 AM »
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.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #23 on: April 19, 2006, 08:41:01 AM »
In apache 200 and mysql with 300, all without persistent conections.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #24 on: April 19, 2006, 08:59:00 AM »
Enable persistent connections, it should help reduce load.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #25 on: April 19, 2006, 09:11:36 AM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #26 on: April 19, 2006, 10:33:51 AM »
Try reducing your max clients in httpd.conf to something like 150.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #27 on: April 19, 2006, 10:48:57 AM »
Try reducing your max clients in httpd.conf to something like 150.

done, persistent active and mysql conections to 300.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #28 on: April 20, 2006, 08:33:33 AM »
Continue breaking but less than before to change the persistent flag. this morning has been break but only twice.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #29 on: April 21, 2006, 05:06:16 AM »
Today status.php

Code: [Select]
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)
« Last Edit: April 21, 2006, 05:08:45 AM by minskog »

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #30 on: April 21, 2006, 05:37:56 AM »
Now the slow querys are like:
Code: [Select]
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

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #31 on: April 21, 2006, 05:40:35 AM »
What search method are you using, id recommend fulltext and force use of an index.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #32 on: April 21, 2006, 05:47:37 AM »
I have it and ...

Sending data     787s

Code: [Select]
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
« Last Edit: April 21, 2006, 05:49:10 AM by minskog »

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #33 on: April 21, 2006, 05:49:48 AM »
I need to install three smf more, and this make me doubt a little ...

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #34 on: April 21, 2006, 09:31:02 AM »
Are your log_search tables deffinatly innodb?
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #35 on: April 21, 2006, 03:58:29 PM »
Are your log_search tables deffinatly innodb?

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

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #36 on: April 27, 2006, 06:38:37 AM »
This continues breaking, but i see that break in manteinance mode too (aka only read)...   :-\

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #37 on: April 27, 2006, 10:24:24 AM »
Which break?
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #38 on: April 27, 2006, 10:39:31 AM »
Goes crazy load, it have 0.40 and goes to 4 or 5 in seconds.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #39 on: April 27, 2006, 10:40:48 AM »
Put it in full manteinance mode (edit Settings.php and change the value to 2) and see what happens.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #40 on: April 27, 2006, 11:21:31 AM »
Put it in full manteinance mode (edit Settings.php and change the value to 2) and see what happens.

Ok

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #41 on: April 29, 2006, 06:15:32 AM »
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.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #42 on: April 30, 2006, 07:36:11 AM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #43 on: April 30, 2006, 10:10:27 AM »
What search method are you using? I have no real problems with 2million posts using fulltext
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #44 on: April 30, 2006, 10:22:30 AM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #45 on: April 30, 2006, 01:38:24 PM »
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.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #46 on: April 30, 2006, 02:17:08 PM »
No, i use this to change.

Code: [Select]
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;


Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #47 on: May 05, 2006, 07:11:07 AM »
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.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #48 on: May 07, 2006, 07:27:02 AM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #49 on: May 07, 2006, 04:06:20 PM »
Fulltext works perfectly fine on my board with almost 2million posts, although my server does have 2GB ram.
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #50 on: May 08, 2006, 05:09:22 AM »
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.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #51 on: May 18, 2006, 10:02:29 AM »
After a lot of tests, the configuration that gives the best perfomance is:

Code: [Select]
[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]
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 ... 

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Max post per thread
« Reply #52 on: May 29, 2006, 10:47:04 AM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Max post per thread
« Reply #53 on: May 29, 2006, 11:57:35 AM »
It's not really a problem, getting everything to be within the recommended values is pretty much impossible.
Liverpool FC Forum with 14 million+ posts.