|
|
Joined: Oct 2006
Posts: 37
newbie
|
|
newbie
Joined: Oct 2006
Posts: 37 |
I have a problem in UBB.threads 7.4.2 with searches. To kill my board, all someone has to do is click "Search" in the top menu and type anything and click GO. The board is dead for the next seven minutes. In the middle of the night (just now), I turned on --log-slow-queries and captured a slow search:
# Query_time: 425 Lock_time: 0 Rows_sent: 100 Rows_examined: 949796
use ubb;
SELECT p.POST_ID
FROM ubbt_POSTS p LEFT JOIN ubbt_POSTS pp on p.POST_ID=pp.POST_ID,
ubbt_TOPICS t
WHERE p.POST_IS_APPROVED = '1'
AND t.TOPIC_STATUS <> 'M'
AND t.FORUM_ID IN ('1','2','3','5','6','7','8','11','12','13','16','17','18','19','21','22','24','23')
AND p.TOPIC_ID = t.TOPIC_ID
AND MATCH (p.POST_SUBJECT,p.POST_DEFAULT_BODY) AGAINST ('searchword' IN BOOLEAN MODE)
ORDER BY p.POST_POSTED_TIME DESC
LIMIT 100;Yeah, I know I have a lot of posts (I keep them all) but maybe there's some way to speed this up. For one thing, shouldn't the search be limited to one year, like I have it in my Feature Settings? Maybe there could be a way to make the default search (non-advanced) search topics only? Is there any way to let this search proceed at a lower priority without hanging up the entire board? Maybe someone can think of something I could do. I've done some tuning already.
|
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
You have some valid points, tho I wonder if some of the issue lies with the mysql setup. I have a client with nearly 40k members and nearly 1.2million posts. When I type in anything in the drop-down search box results are almost instantaneous. He's running a pretty beefy server, but possibly your mysql needs to be tuned a bit to help with these search times.
|
|
|
|
|
Joined: Mar 2007
Posts: 522
Addict
|
|
Addict
Joined: Mar 2007
Posts: 522 |
I'm at 1.7 million posts (but on 7.2.2), and searches come up very quickly. My current server is new, but nothing spectacular... single quad core at 2.4 GHz, and 4MB RAM.
A few years ago when I was closer to probably 1.2 million posts, the site was on an old server with only 512 MB of RAM. A search would bring it right to its knees. I can't tell you if mysql was setup properly, but it was a very bad situation.
Steve
UBB.classic from 2000-2003
UBB.threads from 2003-present!
|
|
|
|
|
Joined: Oct 2006
Posts: 37
newbie
|
|
newbie
Joined: Oct 2006
Posts: 37 |
Thanks, Allen and Steve, that's good to know. The problem really just started less than a year ago (or was first noticed then), so I think it is probably something that's fixable.
Last night, I did some checks on the database tables and most of them come back with "Table upgrade required. Please do 'REPAIR TABLE...'" messages. Tonight, late, I will do repairs on all of them and see if that helps.
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
yes, that is fixable (if you really have hit a mysql wall) you can do a coupla things.. run mysqltuner.pl (freely available on the web) and follow its recommendations as to tuning your my.cnf or i've suggested this to Rick and it definitely increases the query speed and decreases the load 10 times at least! that would be to add another index to the ubbt_POSTS table equal too a large number MINUS the Post posted time... this would make the query NOT have to do that awful reverse (DESC) sort after the WHERE is satisfied, since the new index would be naturally reverse in nature  in most cases, it is the sort that causes the massive headache in very large installs.. I've given Rick the info on this, and i even think he said 'good idea bla bla..' but it isn't in stock yet.. I have 2 extremely large sites where it has been running for years and can be confident it will help.. note: it would be a modification to stock tho.. <insert all that stuff giz types about voiding warranty etc etc>
|
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
you can do a coupla things.. run mysqltuner.pl (freely available on the web) and follow its recommendations as to tuning your my.cnf SD you keep mentioning tuning MySQL Are there any caveats involved with just running the mysqltuner.pl script? I never tried it so was a little uncomfortable trying same. Also how do I determine I have access to my.cnf to make changes.(Can't seem to find it)
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116 |
<insert all that stuff giz types about voiding warranty etc etc> CC and Nav beat it into me... :cries: the beatings... I liked them 
|
|
|
|
|
Joined: Oct 2006
Posts: 37
newbie
|
|
newbie
Joined: Oct 2006
Posts: 37 |
I've been running mysqltuner from http://mysqltuner.com/ for several months now and have been making adjustments. Running it is pretty simple. It gives a report with change recommendations at the end. MySQL configuration file should be: /etc/my.cnf Mine looks like this right now:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
query_cache_size=256M
join_buffer_size=150K
tmp_table_size=256M
max_heap_table_size=256M
thread_cache_size=4
table_cache=450
key_buffer_size=512M
log-slow-queries=/var/log/mysqld-slow-query.log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
|
|
|
Bots
by Outdoorking - 04/13/2024 5:08 PM
|
|
|
|
|
|
|
|
|
|
|
0 members (),
868
guests, and
467
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
