Dathan, Thanks for the suggestion - I was under the impression that 4GB is the limit for 32bit machines. The server is running Redhat Enterprise Linux 3.0 ES for EM64T and MySQL has also been compiled for EM64T. I'd already tried running 'table opitmize' and that didn't seem to help, table repair doesn't seem to help performance either. :( I should also mention that this is version 4.1.10 Thanks, Dan On 5/19/05, Dathan Pattishall <[EMAIL PROTECTED]> wrote: > > 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 > > >
