Hi,

I have allocated 500MB to key_buffer_size, but only 324MB is in use
(64%).

Am I right to assume that this can mean one of the following: (1) all
indexes have already been cached and together they are just 324 MB, (2)
there is a limiting variable (open_files, inodb_open_files, for example)
that prevents the key buffer to be more fully utilized.

Key buffer usage is often hitting 100%, and just 0.01 tables are opened
per second. Number of queries/second is 269.


Setup:

5.0.18-standard-log
Solaris 8
2 CPU, 4GB RAM


Regards,

Martijn

--
The information contained in this communication and any attachments is confidential
and may be privileged, and is for the sole use of the intended recipient(s).. Any
unauthorized review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please notify the sender immediately by replying tothis
message and destroy all copies of this message and any attachments. ASML isneither
liable for the proper and complete transmission of the information contained in this
communication, nor for any delay in its receipt.

On 2007-02-20 Martijn van den Burg wrote:
> Hi,
>
> I have allocated 500MB to key_buffer_size, but only 324MB is in use
> (64%).
>
> Am I right to assume that this can mean one of the following: (1) all
> indexes have already been cached and together they are just 324 MB,
Do "find /var/lib/mysql -name "*.MYI" -ls" (if you're using only MyISAM)
to get a first hint on how much indices you actually have.

> (2)
> there is a limiting variable (open_files, inodb_open_files, for example)
> that prevents the key buffer to be more fully utilized.
You can check at least open_files in "SHOW STATUS" IIRC.

> Key buffer usage is often hitting 100%, and just 0.01 tables are opened
> per second. Number of queries/second is 269.
BTW, try "mysqlreport" to find other bottlenecks.


bye,

-christian-

> > Hi,
> >
> > I have allocated 500MB to key_buffer_size, but only 324MB is in use
> > (64%).
> >
> > Am I right to assume that this can mean one of the
> following: (1) all
> > indexes have already been cached and together they are just 324 MB,
> Do "find /var/lib/mysql -name "*.MYI" -ls" (if you're using
> only MyISAM)
> to get a first hint on how much indices you actually have.

Heh, thanks. I could have thought about that out myself. </sheepish>

> > (2)
> > there is a limiting variable (open_files, inodb_open_files,
> for example)
> > that prevents the key buffer to be more fully utilized.
> You can check at least open_files in "SHOW STATUS" IIRC.
>
> > Key buffer usage is often hitting 100%, and just 0.01
> tables are opened
> > per second. Number of queries/second is 269.
> BTW, try "mysqlreport" to find other bottlenecks.

Really good tool


Martijn

--
The information contained in this communication and any attachments is confidential
and may be privileged, and is for the sole use of the intended recipient(s).. Any
unauthorized review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please notify the sender immediately by replying tothis
message and destroy all copies of this message and any attachments. ASML isneither
liable for the proper and complete transmission of the information contained in this
communication, nor for any delay in its receipt.