I am running a Mysql server version 4.0.13 with a database
of only one table wich has 4 million registers with
two fields of type varchar(255) that are indexed by a
FULL TEXT index.
The problem is that FULLTEXT queries are extremely slow,
taking between 5 and 30 seconds. The effect is that the
max number of connections is quickly used, and the server
just stops accepting connections. The websites query this
server more than 3 times per second and the keywords are
different enough that rarely fall into the Mysql cache.
The website users are getting connection errors all the
time!! please I need any solution now!!! I am desperate..
Please someone tell me any ideas to better tune the
parameters. I tried tweaking the key_buffer_size getting
to a max of 370M before the server starts to page memory
When the database had 1.5 millon registers it used to
work fine.
The server has 4 Xeon 2.0Ghz processors (but I know that
the CPU isn't the limiting problem, I am pretty sure it is
a memory issue), 2Gb of RAM, 2Gb of swap space, running
RedHat 7.3 with Mysql version 4.0.13 I tried tunning
parameters and now i have in /etc/my.cnf:
[mysqld]
set-variable = query_cache_size=64M
set-variable = query_cache_limit=5M
set-variable = query_cache_type=1
set-variable = key_buffer_size=374M
set-variable = thread_cache_size=20
set-variable = max_connections=140
No matter what number of max connections I use, connections
quickly saturate it. The server is dedicated to mysql and
apache. This is the only database in it.
The mysql processes look now like:
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
16040 mysql 15 0 1996M 1.3G 391M S 1.4 64.7 0:07 mysqld
15877 mysql 15 0 1996M 1.3G 391M S 0.3 64.6 0:07 mysqld
15933 mysql 15 0 1996M 1.3G 391M D 0.3 64.6 0:09 mysqld
...
I don't know why the thread processes use so much memory,
keybuffer is only 370Megs. I don't know how it got to 1996M.
I am limiting the queries, so they are not fetching all
the matches, just the first 20. The queries look like:
SELECT MyTable.* MATCH(Name,Description) AGAINST ('" . $keywords . "') as Relevance
FROM MyTable
WHERE MATCH(Name,Description) AGAINST ('" . $keywords . "') "
LIMIT 0,20
The MYD table file is 1.2Gb whereas the MYI index file
occupies 1.7Gb. The table doen't have any writes, only reads
from the clients.
which paramters should I tune and how?
would an upgrade to mysql 4.0.14 fix this?
please heeelllpp! I love mysql, but mysql seems to hate me!!
juanma
of only one table wich has 4 million registers with
two fields of type varchar(255) that are indexed by a
FULL TEXT index.
The problem is that FULLTEXT queries are extremely slow,
taking between 5 and 30 seconds. The effect is that the
max number of connections is quickly used, and the server
just stops accepting connections. The websites query this
server more than 3 times per second and the keywords are
different enough that rarely fall into the Mysql cache.
The website users are getting connection errors all the
time!! please I need any solution now!!! I am desperate..
Please someone tell me any ideas to better tune the
parameters. I tried tweaking the key_buffer_size getting
to a max of 370M before the server starts to page memory
When the database had 1.5 millon registers it used to
work fine.
The server has 4 Xeon 2.0Ghz processors (but I know that
the CPU isn't the limiting problem, I am pretty sure it is
a memory issue), 2Gb of RAM, 2Gb of swap space, running
RedHat 7.3 with Mysql version 4.0.13 I tried tunning
parameters and now i have in /etc/my.cnf:
[mysqld]
set-variable = query_cache_size=64M
set-variable = query_cache_limit=5M
set-variable = query_cache_type=1
set-variable = key_buffer_size=374M
set-variable = thread_cache_size=20
set-variable = max_connections=140
No matter what number of max connections I use, connections
quickly saturate it. The server is dedicated to mysql and
apache. This is the only database in it.
The mysql processes look now like:
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
16040 mysql 15 0 1996M 1.3G 391M S 1.4 64.7 0:07 mysqld
15877 mysql 15 0 1996M 1.3G 391M S 0.3 64.6 0:07 mysqld
15933 mysql 15 0 1996M 1.3G 391M D 0.3 64.6 0:09 mysqld
...
I don't know why the thread processes use so much memory,
keybuffer is only 370Megs. I don't know how it got to 1996M.
I am limiting the queries, so they are not fetching all
the matches, just the first 20. The queries look like:
SELECT MyTable.* MATCH(Name,Description) AGAINST ('" . $keywords . "') as Relevance
FROM MyTable
WHERE MATCH(Name,Description) AGAINST ('" . $keywords . "') "
LIMIT 0,20
The MYD table file is 1.2Gb whereas the MYI index file
occupies 1.7Gb. The table doen't have any writes, only reads
from the clients.
which paramters should I tune and how?
would an upgrade to mysql 4.0.14 fix this?
please heeelllpp! I love mysql, but mysql seems to hate me!!
juanma