Formatting code for MysqlMemoryTuning
{{parent page="MysqlTuning"}}
===Excellent MySQL memory tuning guide===
Extracts from a document originally written by Paul Procacci <pprocacci@datapipe.com>
Often, MySQL is using a lot of CPU. A simple **show processlist** does not give you any idea what MySQL is doing.
%%(text)
10152 mysql 19 96 0 719M 691M ucond 0 31.3H 82.81% mysqld
%%
"This led me to try to investigate which queries on your machine where causing the cpu to be eaten up like this. However, after 20 minutes I
realized that queries weren't physically causing the problem. Observe:"
%%(text)
Old Machine ::
[root@www mysql]# du -shc */*.MYI | tail -1
280M total
New Machine:
[root@www /var/db/mysql]# du -shc */*.MYI | tail -1
760M total
%%
"As you can see, the total size of all indexes on the new machine is 3 times larger than what was on the old machine. Under normal
circumstances this wouldn't be a problem, however the key_buffer configuration variable located in /etc/my.cnf was tailored to the
original size of the migrated content. It's current value is 384 Megs, much too small for storing all the indexes on the current production
box. Here is the pertinent mysql variables:"
%%(text)
mysql> show status like 'key_%';
+------------------------+------------+
| Variable_name | Value |
+------------------------+------------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 8 |
| Key_blocks_used | 348900 |
| Key_read_requests | 1313635963 |
| Key_reads | 379127261 |
| Key_write_requests | 35924731 |
| Key_writes | 21114936 |
+------------------------+------------+
7 rows in set (0.00 sec)
%%
"First thing to note: Key_blocks_unused is currently 8. This value varies from 0 - 8, but never more. What this means is that there is no
buffer space available to add any additional indexes from files into memory. Your database needs to go to disk a lot. How often? Read on.
The second thing to note are the two variables "Key_read_requests" and "Key_reads". By dividing "Key_read_requests" by "Key_reads" the result will give you a ratio of how often mysql needs to go to disk to retrieve a key. In this example ceil(1313635963 / 379127261) = ratio :: 1 : 4. That's, 1 in 4 queries needs to get it's key from the disk. This is extremely bad and is causing performance loss. I've read a bunch of articles which state that this ratio should be no more than 1:100.
There are two options to fixing this. They are as follows:
a) The first option is to increase the key_buffer variable in your my.cnf to account for the total size of indexes currently stored within
your database. Again that's currently 780 Megs. If you remembered, I've set the BSD kernel to allow processes sizes up to 1G, therefore
increasing the key_buffer size to 780 Megs could affect mysql in a negative way. The reason for this is that we would need to account for
all the other buffers that get allocated for mysql, like query caching, per connection buffers, etc. By setting this value too high, we could
literally cause mysql to crash.
b) The second option is to allow a process to grow past the 1G mark. Again by setting the values in /boot/loader.conf to say, 1.5 Gigs, that would certainly allow us to use a key_buffer size of 780 Megs and still allow room for the other buffers mentioned in "option a" above that mysql uses. This will work, but has consequences that would need to be considered. For instance, this specific settings allow for ALL processes to use 1.5 Gigs of memory. Consider this: A 1.5 G mysql process and 3 1.5 G php processes (unlikely but could happen). You are now out of ram, and the machine starts swapping. Once the swapping begins, the machine slows down to a crawl. Now a certainly don't know if you have any processes other than the mysql process that can potentially get this big, but certainly you and/or your developers would know better than I.
c) The third option which is an option that I believe should be investigated first is answering the following questions:
1) Why are my indexes 3 times the size prior to the migration.
2) Did I add unnecessary indexes causing this 'inflation'?
3) Are there any unused mysql databases that can be tar'd up and
stored (i.e. can we relocate data that is no longer being used? Moving the data from the mysql data directory will effectively remove the
ability for mysql to see it. If it can't see it, it won't store the indexes.
Given the options above, "option c" should most certainly be observed first, followed by "option b". "option a" is more information and quite
frankly isn't an option at all."
===Excellent MySQL memory tuning guide===
Extracts from a document originally written by Paul Procacci <pprocacci@datapipe.com>
Often, MySQL is using a lot of CPU. A simple **show processlist** does not give you any idea what MySQL is doing.
%%(text)
10152 mysql 19 96 0 719M 691M ucond 0 31.3H 82.81% mysqld
%%
"This led me to try to investigate which queries on your machine where causing the cpu to be eaten up like this. However, after 20 minutes I
realized that queries weren't physically causing the problem. Observe:"
%%(text)
Old Machine ::
[root@www mysql]# du -shc */*.MYI | tail -1
280M total
New Machine:
[root@www /var/db/mysql]# du -shc */*.MYI | tail -1
760M total
%%
"As you can see, the total size of all indexes on the new machine is 3 times larger than what was on the old machine. Under normal
circumstances this wouldn't be a problem, however the key_buffer configuration variable located in /etc/my.cnf was tailored to the
original size of the migrated content. It's current value is 384 Megs, much too small for storing all the indexes on the current production
box. Here is the pertinent mysql variables:"
%%(text)
mysql> show status like 'key_%';
+------------------------+------------+
| Variable_name | Value |
+------------------------+------------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 8 |
| Key_blocks_used | 348900 |
| Key_read_requests | 1313635963 |
| Key_reads | 379127261 |
| Key_write_requests | 35924731 |
| Key_writes | 21114936 |
+------------------------+------------+
7 rows in set (0.00 sec)
%%
"First thing to note: Key_blocks_unused is currently 8. This value varies from 0 - 8, but never more. What this means is that there is no
buffer space available to add any additional indexes from files into memory. Your database needs to go to disk a lot. How often? Read on.
The second thing to note are the two variables "Key_read_requests" and "Key_reads". By dividing "Key_read_requests" by "Key_reads" the result will give you a ratio of how often mysql needs to go to disk to retrieve a key. In this example ceil(1313635963 / 379127261) = ratio :: 1 : 4. That's, 1 in 4 queries needs to get it's key from the disk. This is extremely bad and is causing performance loss. I've read a bunch of articles which state that this ratio should be no more than 1:100.
There are two options to fixing this. They are as follows:
a) The first option is to increase the key_buffer variable in your my.cnf to account for the total size of indexes currently stored within
your database. Again that's currently 780 Megs. If you remembered, I've set the BSD kernel to allow processes sizes up to 1G, therefore
increasing the key_buffer size to 780 Megs could affect mysql in a negative way. The reason for this is that we would need to account for
all the other buffers that get allocated for mysql, like query caching, per connection buffers, etc. By setting this value too high, we could
literally cause mysql to crash.
b) The second option is to allow a process to grow past the 1G mark. Again by setting the values in /boot/loader.conf to say, 1.5 Gigs, that would certainly allow us to use a key_buffer size of 780 Megs and still allow room for the other buffers mentioned in "option a" above that mysql uses. This will work, but has consequences that would need to be considered. For instance, this specific settings allow for ALL processes to use 1.5 Gigs of memory. Consider this: A 1.5 G mysql process and 3 1.5 G php processes (unlikely but could happen). You are now out of ram, and the machine starts swapping. Once the swapping begins, the machine slows down to a crawl. Now a certainly don't know if you have any processes other than the mysql process that can potentially get this big, but certainly you and/or your developers would know better than I.
c) The third option which is an option that I believe should be investigated first is answering the following questions:
1) Why are my indexes 3 times the size prior to the migration.
2) Did I add unnecessary indexes causing this 'inflation'?
3) Are there any unused mysql databases that can be tar'd up and
stored (i.e. can we relocate data that is no longer being used? Moving the data from the mysql data directory will effectively remove the
ability for mysql to see it. If it can't see it, it won't store the indexes.
Given the options above, "option c" should most certainly be observed first, followed by "option b". "option a" is more information and quite
frankly isn't an option at all."
