In the last episode (Apr 15), Charles Q. Shen said:
> From: Dan Nelson [mailto:[EMAIL PROTECTED]
> > 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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
