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.

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

He,

A lot depends on storage engine. You can tune key_buffer_size and query_cache size on the fly as well as per thread default buffers, but these cause less impact. tmp_table_size is another one I would check.

First of all thanks for your help and patience.

And sorry I forgot to mention the storage engine. Since I can't dynamically change the innodb_buffer_pool_size which is the one most important parameter to effect innodb engine's performance. So now I am looking to work with MyISAM tables and like you said I can try to vary key_buffer_size and measure it's effects.

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 ?

Also back to my original question, how can I actually vary key_buffer_size on the fly ? (think that I want to do it programatically) I am guessing that the config file is read only at the startup and changing the values in that file later wont be re-read and used by MySQL so there has to be some other way.

If you want to change variables on the fly, you can use

set global var='value';

Keep in mind that certain variable changes (such as query_cache_type) take effect only for new sessions, and others (such as query_cache_limit) require a mysql restart.

set global var='value';

this syntax is for which language/interface ?

Please consider that I am totally new to MySQL.

And I am only interested in changing key_buffer_size I hope that doesn't require a MySQL restart ?

Any language )

It is the same as you would run SELECT statement etc.

You can use mysql -e "SET GLOBAL key_buffer_size=10000000" from the shell as well.

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 ?

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.

Thanks for the reply.

One follow up question. I am working on a single table which is basically the 'customer' table from the TPCW benchmark. And the size of the data and index are as follows:

scspc092jr:/usr/local/mysql/data/TPCW # ls -lhS
total 3.6G
-rw-rw---- 1 mysql mysql 3.4G Dec 5 02:35 customer.MYD
-rw-rw---- 1 mysql mysql 253M Dec 5 02:39 customer.MYI
-rw-rw---- 1 mysql mysql 9.0K Dec 5 02:29 customer.frm

Now I was trying to setup the SQL to probe the data and then measure performance by changing the amount of total memory avail to the virtual machine and automatically adapting the size of 'key_buffer_size' to the new memory. The thing that i noticed is that whenever I change the value of 'key_buffer_size' the status variables relating to the key_buffer are reset. Which would mean that every time we change the value of 'key_buffer_size' the key cache would go from a 'warm' state to 'cold' ? which can actually impact performance negatively 'always' as the cache will start to re-build from scratch every time. Is that true or I am mis-interpreting the variables here?

Before we change 'key_buffer_size':

mysql> show status like '%key%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Com_preload_keys | 0 |
| Com_show_keys | 3 |
| Handler_read_key | 65 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 8968 |
| Key_blocks_used | 22 |
| Key_read_requests | 28 |
| Key_reads | 22 |
| Key_write_requests | 0 |
| Key_writes | 0 |
+------------------------+-------+
10 rows in set (0.00 sec)

After we change 'key_buffer_size':

mysql> show status like '%key%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Com_preload_keys | 0 |
| Com_show_keys | 3 |
| Handler_read_key | 65 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 93070 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
+------------------------+-------+
10 rows in set (0.00 sec)

That is right.

Resizing key buffer allocates new empty cache and flushes dirty buffers from the old one.