| changing innodb_buffer_pool_size dynamically [message #370] |
Mon, 20 November 2006 14:06  |
umafm13
Messages: 6
Registered: November 2006 |
Junior Member |
|
|
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
|
|
|
|
| Re: changing innodb_buffer_pool_size dynamically [message #404 is a reply to message #371 ] |
Wed, 22 November 2006 16:25  |
umafm13
Messages: 6
Registered: November 2006 |
Junior Member |
|
|
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
|
|
|
|
| Re: changing innodb_buffer_pool_size dynamically [message #407 is a reply to message #405 ] |
Thu, 23 November 2006 02:47 |
umafm13
Messages: 6
Registered: November 2006 |
Junior Member |
|
|
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.
|
|
|
|
| Re: changing innodb_buffer_pool_size dynamically [message #413 is a reply to message #408 ] |
Thu, 23 November 2006 12:16 |
umafm13
Messages: 6
Registered: November 2006 |
Junior Member |
|
|
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 ?
|
|
|
|
|
|
| Re: changing innodb_buffer_pool_size dynamically [message #447 is a reply to message #435 ] |
Mon, 04 December 2006 22:08 |
umafm13
Messages: 6
Registered: November 2006 |
Junior Member |
|
|
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)
|
|
|
|
Members
Search
Help
Register
Login
Home
