Hi,
This question is about tuning the innodb_buffer_pool_size in an experiment
related to my earlier post. I am running MySQL 4.1.11 with innoDB on
RedHatEnterprise Linux, kernel 2.6.9-22. The table contains 50M records,
with a total data_length of 9GB and index_length of 9.5GB. I measured
repeatedly the query latency of 50000 randomly selected records. The latency
remains relatively small and are about the same regardless of whether I
restart MySQL or not. As pointed out earlier by Philippe, this could be
caused by hitting the FS cache.
1) Does that mean the MySQL innodb_buffer_pool_size setting will virtually
have no effect at all? (if it is true, then it should happen quite
commonly). In my test, I tried to change the innodb_buffer_pool_size from
its default value (8MB) to half of the physical memory (4GB). I found almost
no difference in latency in these two cases.
2) The free command shows the following information. Is it valid to infer
that the FS caches about 6.6G of the total 9.5G index file for the innodb
table? (assuming the server is dedicated to mysql)
%free
total used free shared buffers
cached
Mem: 8162048 7589836 572212 0 184572 6591900
-/+ buffers/cache: 813364 7348684
Swap: 2097144 160 2096984
Thanks!
Charles
> -----Original Message-----
> From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED]
> Sent: Friday, April 07, 2006 2:43 AM
> To: MySQL General
> Subject: Re: MySQL 4.1.11 innodb cache can't be flushed after
> restart ?
>
> 2006/4/7, Charles Q. Shen <[EMAIL PROTECTED]>:
> > Hi all,
> >
> > I am running MySQL 4.1.11 with an innoDB table holding
> about 17GB of
> > records. I took a few hundreds of randomly selected records
> from the
> > table and measured the average access time:
> >
> > 1st test: average access time is 600ms 2nd test: average
> access time
> > is 30ms 3rd test: average access time is 15ms Stop and
> restart MySQL
> > 4th test: average access time is 15ms
> >
> > Note that I stopped and restarted mysql between the 3rd and
> 4th test
> > but the average access time does not change.
>
> What OS do you use ? It's quiet likely you hit the FS cache,
> not the MySQL one.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
