List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 16 2006 3:51am
Subject:Re: MySQL cache problem - innodb_buffer_pool_size and FS cache
View as plain text  
In the last episode (Apr 15), Charles Q. Shen said:
> From: Dan Nelson [mailto:dnelson@stripped] 
> > In the last episode (Apr 15), Charles Q. Shen said:
> > > 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.
> > 
> > For a 50000-record test using the primary key to look up values, it
> > may not make a difference at all.
> 
> Sorry I did not make it clear, the 50000-record tests are based on a
> secondary-index, not the the primary one. And I repeated the tests
> for the same 50000-records set several times.
> 
> > You can basically ignore the index_length, since for innodb that
> > only applies to secondary indexes.  The primary index is counted as
> > data.
> 
> If the primary index is counted as data and become part of the
> data_length and as I understand the secondary indexes are stored with
> the primary key value for the row. Could you please explain why the
> index_length could be larger than the data_length? ( Does that mean
> you have everything, data, primary, secondary index mixed together??)
> thanks.

If you have multiple indexes, it's possible that their total size could
exceed the size of the original data.  InnoDB B-tree pages try to stay
less than 15/16ths full, and if you haven't optimized the table in a
while, they'll end up between 1/2 and 15/16ths, so you may have a lot
of slack space in there.  None of the Mysql engines tell you how big a
particular index is, and you can only get a packing percentage for
myisam tables (with myisamchk -i).  If you have the disk space to
spare, you can make a copy of the table and create just the primary and
secondary index used in the query.  Then index_length will tell you how
big the secondary index is.
 
> > If you were to graph lookup latency verses buffer_pool size, you
> > would probably see two bends: the first happens when your cache is
> > large enough to store most of the levels of the index you're using
> > (since you have to seek for each level),
> 
> Is this the size of index_length or is this merely the size of index
> itself ( for example, 600M primary keys as you've shown below, plus
> whatever size of secondary keys)?

Just the size of the index you're interested in.
 
> > and the second happens when your cache is large enough to store the
> > data pages as well (so you go to 0 seeks per record).
> 
> So given that my data_length is 9G and index_length is 9.5G, what do
> you think would be the memory threshold for this to happen (9G, 9.5G,
> 18.5G) ?

Depends on how much of that 9.5G is the index you're using in the
query.  It's usually a losing race to actually want to cache the entire
table, though, since I assume your table's going to grow.

-- 
	Dan Nelson
	dnelson@stripped
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