SEO

Hi,

Key_blocks_unused 952405
Key_blocks_used 395539

Key_blocks_used is increasing day by day and Key_blocks_unused is
decreasing day by day. Ater a month Key_blocks_unused will reach to 0. Does
it mean that i need to increase the key_buffer_size. Already
key_buffer_size=1G.

While going through forums and lists , i found that when key_blocks_unused
is less. then select query will become slow.
So whats the right solution.


Thanks,
--
Krishna Chandra Prajapati

In the last episode (Apr 30), Krishna Chandra Prajapati said:
> Key_blocks_unused 952405
> Key_blocks_used 395539
>
> Key_blocks_used is increasing day by day and Key_blocks_unused is
> decreasing day by day. Ater a month Key_blocks_unused will reach to
> 0. Does it mean that i need to increase the key_buffer_size. Already
> key_buffer_size=1G.
>
> While going through forums and lists , i found that when
> key_blocks_unused is less. then select query will become slow. So
> whats the right solution.

In a perfect world, you would set key_buffer_size to the total size of
all your .MYI files. Depending on the size of your tables, that may
simply not be possible. Luckily, it's usually not necessary, either.
What is more important is the hit rate. Run "SHOW STATUS LIKE 'key%'",
and compare Key_read_requests (how many times mysql asked for a key)
against Key_reads (how many times mysql actually had to go to disk to
fetch a key). 1-(Key_reads/Key_read_requests) is your read hit rate.
If it's 0.99 or greater, that means that 99% of your key reads are
already coming from the key buffer, and adding more key buffers is
unlikely to give you a measurable performance increase.

Don't worry too much about your write hit rate (
1-(Key_writes/Key_write_requests) ); it's always going to be lower
because mysql tries to keep the on-disk copy of the index up to date.

--
Dan Nelson
dnelson@allantgroup.com

Hi Dan,

I am worried about Key_blocks_unused. when Key_blocks_unused reach to 0.
There will be no free blocks then how insert query will work.

Thanks
Krishna Chandra Prajapati

On Wed, Apr 30, 2008 at 10:42 AM, Dan Nelson <dnelson@allantgroup.com>
wrote:

> In the last episode (Apr 30), Krishna Chandra Prajapati said:
> > Key_blocks_unused 952405
> > Key_blocks_used 395539
> >
> > Key_blocks_used is increasing day by day and Key_blocks_unused is
> > decreasing day by day. Ater a month Key_blocks_unused will reach to
> > 0. Does it mean that i need to increase the key_buffer_size. Already
> > key_buffer_size=1G.
> >
> > While going through forums and lists , i found that when
> > key_blocks_unused is less. then select query will become slow. So
> > whats the right solution.
>
> In a perfect world, you would set key_buffer_size to the total size of
> all your .MYI files. Depending on the size of your tables, that may
> simply not be possible. Luckily, it's usually not necessary, either.
> What is more important is the hit rate. Run "SHOW STATUS LIKE 'key%'",
> and compare Key_read_requests (how many times mysql asked for a key)
> against Key_reads (how many times mysql actually had to go to disk to
> fetch a key). 1-(Key_reads/Key_read_requests) is your read hit rate.
> If it's 0.99 or greater, that means that 99% of your key reads are
> already coming from the key buffer, and adding more key buffers is
> unlikely to give you a measurable performance increase.
>
> Don't worry too much about your write hit rate (
> 1-(Key_writes/Key_write_requests) ); it's always going to be lower
> because mysql tries to keep the on-disk copy of the index up to date.
>
> --
> Dan Nelson
> dnelson@allantgroup.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=p...tikc@gmail.com
>
>


--
Krishna Chandra Prajapati

Krishna Chandra Prajapati schrieb:
> Hi Dan,
>
> I am worried about Key_blocks_unused. when Key_blocks_unused reach to 0.
> There will be no free blocks then how insert query will work.

it is like any other cache system, if the cache is not usable (full, not
accessible or whatever) the cache will be omitted, and writes/reads go
directly to the disk and not the RAM (cache)

you do not need to worry about loosing data caused by a full cache

--
Sebastian Mendel

In the last episode (Apr 30), Krishna Chandra Prajapati said:
> I am worried about Key_blocks_unused. when Key_blocks_unused reach to
> 0. There will be no free blocks then how insert query will work.

The oldest block will be written to disk if it is dirty, and
discarded.

--
Dan Nelson
dnelson@allantgroup.com