MySQL Forums
Forum List  »  Full-Text Search

boolean query slow & no processor load balance
Posted by: fdo gl
Date: September 08, 2008 04:55AM

Hi


We have MYISAM tables

In our database, we have a field "fullContents", with 0.5 GB
(50000 rows, 1800 words/row, 5 bytes/word)

describe mytable;
Field | Type | Null | Key | Default
...
fullContents | mediumtext | YES | MUL | NULL
...
mysql> show indexes from mytable;
+---------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
...
| mytable | 1 | fullContents | 1 | fullContents |
NULL | 52518 | NULL | NULL | YES | FULLTEXT | NULL |
...



This's the boolean query:
Exequeted Query: select keyId from mytable where match (fullContents) against
('"jose luis" biblio*' in boolean mode) order by discovery desc limit 5000
5000 rows in set (1 min 21.08 sec)


We have 2 problems:
- no processor "load balance"
- the execution time is very slow.


With top utility, we see that one processor is near 100% user time, and the
others processors are free, there is no load balance.

top - 12:27:10 up 87 days, 2:51, 3 users, load average: 0.27, 0.14, 0.12
Tasks: 183 total, 1 running, 182 sleeping, 0 stopped, 0 zombie
Cpu0 : 1.7%us, 0.0%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 1.0%us, 0.3%sy, 0.0%ni, 98.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 1.7%us, 0.0%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 0.7%us, 0.0%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 97.0%us, 2.3%sy, 0.0%ni, 0.0%id, 0.3%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu6 : 0.3%us, 0.3%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 10234920k total, 7535012k used, 2699908k free, 226528k buffers
Swap: 2097144k total, 1360k used, 2095784k free, 4808336k cached


QUESTIONS:
- Why no processor "load balance"?
- A "processor cluster" solve the problem?
- "memcache" solve the problem?
- Any MYSQL solution (even if payment) for "load balance" and accelerate the
search

Cheers

Options: ReplyQuote


Subject
Views
Written By
Posted
boolean query slow & no processor load balance
4178
September 08, 2008 04:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.