Wow, your going pass the 2 GB barrier on a 32 bit server. I would not do that. Reduce your key buffer to 1700 or you will eventually crash.
Then type repair table PC1_Text quick, or ALTER TABLE PC1_TEXT ENGINE=myISAM. This helps with rebuilding full text indexes. DVP ---- Dathan Vance Pattishall http://www.friendster.com > -----Original Message----- > From: Dan Salzer [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 19, 2005 7:48 AM > To: mysql@lists.mysql.com > Subject: Troubleshooting FullText Slowness > > Hello all, > I'm having a bit of trouble with a full-text query being > slow. At first I thought it was a problem with a join, then I > thought it was a problem with a sort - but I've boiled down > the query and it seems like plain-old slowness. > This is the table: > > CREATE TABLE `PC1_Text` ( > `AssetID` int(11) NOT NULL default '0', > `Content` text NOT NULL, > PRIMARY KEY (`AssetID`), > FULLTEXT KEY `Content` (`Content`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > And here is its status: > Name: PC1_Text > Engine: MyISAM > Version: 9 > Row_format: Dynamic > Rows: 5906 > Avg_row_length: 15849 > Data_length: 93608372 > Max_data_length: 4294967295 > Index_length: 49875968 > Data_free: 0 > Auto_increment: NULL > Create_time: 2005-04-09 12:25:41 > Update_time: 2005-05-19 10:22:14 > Check_time: 2005-05-19 09:58:00 > Collation: latin1_swedish_ci > Checksum: NULL > Create_options: > Comment: > The table has only 5900 rows of text and I'm trying to use > the full-text index to find hits on a search term using: > SELECT SQL_NO_CACHE AssetID FROM PC1_Text as FT WHERE > MATCH(Content) AGAINST('+"after dinner" ' IN BOOLEAN MODE); > This query takes about 1.6 seconds to execute. More general > queries take even longer. Performing an explain on the query > shows exactly what I would > expect: > +----+-------------+-------+----------+---------------+------- > --+---------+------+------+-------------+ > | id | select_type | table | type | possible_keys | key | > key_len | ref > | | > rows | Extra | > +----+-------------+-------+----------+---------------+------- > --+---------+------+------+-------------+ > | 1 | SIMPLE | FT | fulltext | Content | Content | 0 | | 1 | > Using where > | | > +----+-------------+-------+----------+---------------+------- > --+---------+------+------+-------------+ > I've even taken the time to run: > LOAD INDEX INTO CACHE PC1_Text; > And it doesn't make a difference. > All this leads me to believe I am dealing with a system > configuration issue. The server is a Dual Xeon 2.8 EM64T, it > has 4GB of memory and 15K Drives. And I am currently using > --memlock to ensure that mysql stays in real memory. My > config looks like the following: > [mysqld] > key_buffer_size=2500M > tmp_table_size=128M; > max_heap_table_size=128M; > max_allowed_packet = 1M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > read_rnd_buffer_size = 8M > thread_concurrency = 8 > thread_cache = 8 > query_cache_size = 64M > max_connections=10000 > ft_min_word_len=3 > Anyone have any idea what I might try to increase the > performance of this query? I'm quite certain it should be > able to do better than this. > Thanks as always! > -Dan > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
