keeping a fulltext index in memory

[Mark]

Hi,
I'm having problems with a fulltext indexed table where it takes=
a
long time return from a query where many rows match. I noticed=
that
when I run a query like
select count(*) from table where keywords like '%x%';

it takes a long time but after that all fulltext queries are much=

faster, I'm not talking about cached queries here, and I'm=
guessing
that it's because the index has been loaded into memory. but then=

after a while it slows down again. what do I have to do to keep=
this
from happening? my key_buffer is bigger than the total size of my=

MYI's, and my table_cache is higher than my # of tables.

I'm using 4-0-14 standard on Linux.

thanks,
- Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmy...@freebsd.csie.nctu.edu.tw

[Jeremy Zawodny]

On Tue, Sep 02, 2003 at 11:45:21AM -0700, Mark wrote:
> Hi,
>

> I'm having problems with a fulltext indexed table where it takes a
> long time return from a query where many rows match. I noticed that

> when I run a query like
> select count(*) from table where keywords like '%x%';
>
> it takes a long time but after that all fulltext queries are much

> faster, I'm not talking about cached queries here, and I'm guessing

> that it's because the index has been loaded into memory. but then

> after a while it slows down again. what do I have to do to keep this

> from happening? my key_buffer is bigger than the total size of my

> MYI's, and my table_cache is higher than my # of tables.

That's reall weird. Theoretically this should happen. If your
key_buffer is really that big.

Hmm.

Any chance you OS swapped out part of your key_buffer? See if any of
mysqld's memory is sitting in swap.

Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
<Jer...@Zawodny.com> | http://jeremy.zawodny.com/

MySQL 4.0.13: up 32 days, processed 1,383,390,500 queries (495/sec. avg)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw

[Mark]

>Any chance you OS swapped out part of your key_buffer? See if=

any
of
>mysqld's memory is sitting in swap.
>
>Jeremy

Hi,
how exactly do I tell this on linux?

Thanks,
- Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmy...@freebsd.csie.nctu.edu.tw

[Matt W]

Hi Mark,

I'm no Linux expert, but I think you would look at the difference
between the SIZE and RSS values in top (or the equivs. in other
progs...).

Also IIRC, from your first message, I don't think you're using a
full-text index in your query, are you? I think I saw column LIKE
'%word%' and not MATCH(...) AGAINST(...)?

Matt

----- Original Message -----
From: "Mark" <magg...@mminternet.com>
To: <my...@lists.mysql.com>
Sent: Wednesday, September 03, 2003 6:35 PM
Subject: Re: keeping a fulltext index in memory

>Any chance you OS swapped out part of your key_buffer? See if any

of
>mysqld's memory is sitting in swap.
>
>Jeremy

Hi,
how exactly do I tell this on linux?

Thanks,
- Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw