changing innodb_buffer_pool_size dynamically

MySQL & MariaDB Other MySQL® Questions
1

Hi everyone,

I am just starting to develop with MySQL. What I want to be able to do is to change the size of innodb_buffer_pool_size through some programming interface(preferably in c) on the fly. Is it possible to do so at all ? Are there multiple ways to do it ? Any easy alternatives ? Can anyone point me in the right direction ? I’ll appreciate your help.

Thank you.

-Umar

2

No it is not possible in MySQL 5.0

In MySQL 5.1 it may be possible - there is a patch by Innobase to do so but I do not know if it is accepted already.

3

Thanks for your reply.

I have another question. Can you tell me what parameters can I can actually tune on the fly to effect the performance of an OLTP load (just an example) running on MySQL, if I can vary the amount of memory/cpu that is available to MySQL dynamically ? To clarify, I am working on a project with virtualization using Xen hypervisor. Xen has a control domain called dom0 and user defined number of user domains called domUs. So you may know that it is possible to vary the amount of memory and cpu_weight that is allocated to a particular domU from dom0. So I have a scenario where I have a domU with MySQL running inside, what I want to be able to do is to come up with a MySQL tunning configuration as a function of changing memory/cpu. Think of it as a ‘dynamic control problem’.

I’ll appreciate your reply.

-Umar

4

Thanks, that’s what I was looking for. )

Peter, can you please comment on my second question:

Can you please tell me what kind of characteristics the data and indexes created on that data should be for MyISAM table such that we can actually see the effects of varying key_buffer_size ? What I am thinking is a single large MyISAM table with may be an index created on a field containing strings ?

5

Right. It could be any index - it should just be large enough to need large key_buffer and you need queries which touch large enough portion of that index to see performance improvement.