Thank you for your comments!
Ingo Strüwing wrote:
> Misja Op de Coul wrote:
> ...
> > The server has 8GB of
> > memory. Because there is enough memory the
> O.S.
> > doesn’t do any swapping.
>
> Is this your guess or can you prove it? See
> below.
>
It's not a guess, we use MRTG to track the swap memory and the there's never more than a few megabytes.
>
> Are you just using TAR+FTP, or do you FLUSH TABLES
> WITH READ LOCK before and UNLOCK TABLES after
> TAR+FTP? In the first case you might save
> inconsistent tables. In the second case you close
> the tables and thus free all key cache blocks. The
> next SQL statement that uses the tables opens them
> again and loads the key blocks it accesses. In
> other words you need to load the indexes after the
> save.
>
After running just TAR+FTP (on a big file) the query is slow.
We are running mysqldump earlier, but after mysqldump the queryspeed is OK.
>
> > Or is there any other reason why MySQL would
> > delete something from this key cache?
>
> No. I don't believe so (except of the flush tables
> mentioned above). Ah. Wait a moment. MySQL has an
> open table cache. If this becomes full (many
> tables used), some tables can be closed. This has
> the same effect as a flush tables on that tables.
> You can set the open table chache size with the
> system variable 'table_cache'. See
>
http://dev.mysql.com/doc/refman/5.0/en/server-syst
> em-variables.html
>
This might be it, we increased the table_cache setting and will monitor
the query speed during the night to see if this solves the problem.
We will keep you updated.
Thanks again,
Misja