List:General Discussion« Previous MessageNext Message »
From:Charles Q. Shen Date:April 15 2006 5:27pm
Subject:RE: MySQL cache problem - innodb_buffer_pool_size and FS cache
View as plain text  
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:pooly7@stripped] 
> 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 <qs2005@stripped>:
> > 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/mysql?unsub=1
> 

Thread
MySQL 4.1.11 innodb cache can't be flushed after restart ?Charles Q. Shen7 Apr
  • Re: MySQL 4.1.11 innodb cache can't be flushed after restart ?Philippe Poelvoorde7 Apr
    • RE: MySQL 4.1.11 innodb cache can't be flushed after restart ?Charles Q. Shen7 Apr
      • Re: MySQL 4.1.11 innodb cache can't be flushed after restart ?Philippe Poelvoorde7 Apr
    • RE: MySQL cache problem - innodb_buffer_pool_size and FS cacheCharles Q. Shen15 Apr
      • Re: MySQL cache problem - innodb_buffer_pool_size and FS cacheDan Nelson15 Apr
        • RE: MySQL cache problem - innodb_buffer_pool_size and FS cacheCharles Q. Shen15 Apr
          • Re: MySQL cache problem - innodb_buffer_pool_size and FS cacheDan Nelson16 Apr
            • Should we petition GoDaddy?Nicolas Verhaeghe16 Apr
              • RE: Should we petition GoDaddy?David T. Ashley16 Apr
              • Re: Should we petition GoDaddy?James Eaton17 Apr
                • Re: Should we petition GoDaddy?David Logan17 Apr
              • Re: Should we petition GoDaddy?Dotan Cohen17 Apr