generating a "fake" LRU dump?

[David Stainton]

Greetings,

I have been using the Dump/Restore LRU feature of Percona for several
months now on production servers and I love this feature!

http://www.percona.com/docs/wiki/percona-server:features:innodb_lru_dump_restore

However at this time I have the need to warm up the entire innodb
(xtradb) data set.
Our innodb buffer pool size is bigger than our on disk data set.

I think it would be VERY useful to run a Python script which generates
a "fake" LRU dump of the entire current innodb data set.
Then I load this "fake LRU dump" and our buffer pool would be
completely warmed up.

Has anyone ever done this?

The reason I need this is because I've got a couple slaves that are
behind in replication due to a maintenance procedure.
The slaves appear to not be catching up in replication due to the
serialized IO...
No doubt we are very close to the serialized IO limit on the slaves.
The slaves also have notably less buffer pool usage than the master
which is why I think warming up the buffer pool will fix the problem
here.

Another possible solution:

1. record binary log position on the master
2. dump the master LRU
3. wait for the slaves to reach the recorded binary log position
4. load this LRU dump onto the slaves

Any suggestions?

Cheers,

David

[Stewart Smith]

On Wed, 1 Jun 2011 16:23:07 -0700, David Stainton <dstain...@gmail.com> wrote:
> 1. record binary log position on the master
> 2. dump the master LRU
> 3. wait for the slaves to reach the recorded binary log position
> 4. load this LRU dump onto the slaves

LRU from master won't make sense on slave due to the InnoDB data files
looking different. remember that replication is above the InnoDB
layer. There's a tool in maatkit that may help though (I forget the name
of it).

--
Stewart Smith

[Vadim Tkachenko]

David,

We are about to push new feature into Percona Server with code name
"innodb_fake_update"

This is exactly to fight with slave lags.

How one can use it ?

You need script that reads statements from binary logs, and
then executes them in parallel in "innodb_fake_update" mode
before replication thread executes them.

In this mode InnoDB only reads needed pages in buffer_pool, but not
updates them.
and replication thread works with data in memory, not on disk.

You may try it when feature and script are available in Percona Server.

> --
> You received this message because you are subscribed to the Google Groups "Percona Dev" group.
> To post to this group, send email to perco...@googlegroups.com.
> To unsubscribe from this group, send email to percona-dev...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/percona-dev?hl=en.
>
>

--
Vadim Tkachenko, CTO, Percona Inc.
Phone +1-888-401-3403,  Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

Flat-rate 24x7 support for MySQL <http://percona.com/mysql-support>

[Henrik Ingo]

This sounds like it is doing the same thing as slavereadahead:

http://karlssonondatabases.blogspot.com/2011/03/want-to-accellerate-mysql-slave-here-is.html
http://sourceforge.net/projects/slavereadahead/

henrik

--
henri...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

[David Stainton]

Hi Vadmin,

This sounds like a very smart feature.
I look forward to trying it out.

Thanks!

David

[David Stainton]

Henrik,

Seems like a pretty good solution to the problem.
Very similar in concept to Vadim's proposed innodb_fake_update.

Also it seems this here program might be slightly better since it
reads the relay logs directly instead of using "SHOW RELAYLOG EVENTS"
:
http://www.maatkit.org/doc/mk-slave-prefetch.html

I am using percona mysql 5.1.x

David

[David Stainton]

Vadim,

I think if we warmed up the entire buffer pool then it would stay warm
forever because our dataset fits entirely into memoy.
For my situation, warming up the entire buffer pool seems like the
optimal solution.

For different situations where the dataset is bigger than the buffer
pool size then it would be better to use innodb_fake_update.

David

[Vadim Tkachenko]

David,

So it seems you would benefit if we just implement
preloading of all .ibd files in sequential way.

We did not plan this feature in near future, but it is not
so complicated to implement it. Actually I remember
someone in community has it done in some way.

If you interested, I suggest you contact Percona
http://www.percona.com/contact/sales/
to discuss implementation.

Thanks,
Vadim

> You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
> To post to this group, send email to percona-d...@googlegroups.com.
> To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.