Hello,
We operate a forums website and use Phorum web application (PHP/MySQL
backend), more info: http://www.phorum.org
After operating for two years now, we are facing a serious problem.
Giving the fact that the forums are full of content, ONE (1) search
operation on a single forum is draining a "Xeon 933-Mhz 1GB RAM" of its
power entirely! The MySQL daemon goes to 97% CPU usage, and all other
queries are delayed until the search query finishes. This usually takes
up from few tens of seconds to minutes until the search is over.
The messages themselves that are added to the different forums are
inserted into a TEXT field. We use MySQL 3.23.32, so I recently added a
FULLTEXT index for this field on all forums and changed the source code
to make use of the MATCH() AGAINST() MySQL call.
This still doesn't help much, and the CPU usage after one search
operation is unbearable.
I need solutions, and I would like to get yours if you have any. The
my.cnf file that we use is as follows:
----------------------------------------------
[client]
port = 3306
socket = /mysql/mysql.socket
[mysqld]
port = 3306
socket = /mysql/mysql.socket
pid-file = /mysql/mysql.pid
tmpdir = /mysql/tmp/
set-variable = key_buffer=256M
set-variable = max_allowed_packet=2M
set-variable = table_cache=256
set-variable = sort_buffer=64M
set-variable = tmp_table_size=8M
set-variable = record_buffer=4M
set-variable = myisam_sort_buffer_size=32M
set-variable = thread_cache=16
set-variable = thread_concurrency=2 # Try number of CPU's*2
set-variable = max_connections=256
skip-locking
skip-name-resolve
log
log-slow-queries
----------------------------------------------
By the way, one of the solutions that were suggested is to turn into a
commercial SQL solution, such as Oracle or DB2, and solve this problem
for good, but I still like MySQL and its speed and simplicity.
Thank you for your help.
Noor
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
