This blog post is part of blog series
Well besides just saying do what Peter says, lets review examples of how they can solve this themselves.
For starters, you can compare you my.cnf file against the Q&A version that is available for free via Percona. It this an ideal solution? No, but it will allow you to take a fresh look at your configuration file after you answers all of their questions, via their configuration wizard.
innodb_buffer_pool_size –
Setting the innodb_buffer_pool_size is by far one of the most important settings for a MySQL InnoDB database.Some good reading on this topic is below:
It is safe to say that if you have the default 8M set as your innodb_buffer_pool_size variable then this should be one of the first settings you adjust.
Basically, allow this to have all of the available memory you can afford to give it. This of course takes into account several factors, like what else needs to allocate memory on the server ? Keep in mind that Disk I/O is important to performance and the more memory you allow this variable to have the less disk I/O should be required for table access.
Let us also consider this....
Since a general recommendation is to set
For example, following the logic from
Now when you compare these results to the "guidelines"
You get a different result. While Baron's check is dependent on the time frame it is executed within (which is why you should check this during peak traffic) it can give you a more realistic view on YOUR traffic and use. I would look into setting the innodb_buffer_pool_size to 2G not 1G in this simple example.
SET @sequence1=3982683217;
SET @sequence2=3991367755;
select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;
innodb_log_file_size–
I have found that I enjoy Baron's concepts on how to set the innodb_log_file_size. Remember to execute his suggestions during peak times to get true readings. The larger the size of these files the better performance with large data sets but nothing comes for free, you will increase the recovery times. Increased recovery times might not sound like a major concern to some, until it is a revenue dependent database that you are waiting and watching process forever. A general recommendation is to set it at 25 % of the buffer pool size.
innodb_log_buffer_size –
I recall I once had this set on a server to innodb_log_buffer_size=128M. Was it a good choice? No it means I wanted memory when I likely did not have to. It was a very write heavy database but this setting was very likely to high. Focus on the defaults first and then double it ( 8MB - 16MB ) max.
innodb_additional_mem_pool_size–
Peter addresses this setting in his blog post. You can address it and evaluate options after other settings have been addressed in my opinion.
innodb_flush_log_at_trx_commit–
> set GLOBAL innodb_flush_log_at_trx_commit =2;
Peter addresses this setting in his blog post as well. This is a commonly overlooked variable and worth testing performance improvement by adjusting from 0 - 2. Keep in mind the risks.
thread_cache –
Keep an eye on this status : show status like 'threads_created';
If it climbs then you have it set too low and it needs to be raised.
query_cache_size –
The permissible values are multiples of 1024.
Pay attention to this setting the more read dependent you application becomes. I used to run with it set to key_buffer=16M and I could and likely should have doubled that.
key_buffer_size –
With MySQL switching to InnoDB as the default storage engine it is likely some we could see fewer and fewer tables created as MyISAM. That is not a fact just an opinion. The key_buffer_size will very important to you if you do utilize MyISAM tables.
Review Shlomi Noach's post to help you find which easily which tables which engine type.
"See per table size (almost exactly as presented in INFORMATION_SCHEMA)" -- Shlomi Noach.
table_cache –
According to the manual this is a good formula to help determine the
A value of 8 or 16 is recommended on systems that routinely use 16 or more cores, the default is 1.
Hopefully this exposes the variables and settings that need to be reviewed to help you get the best from your MySQL database.
Well besides just saying do what Peter says, lets review examples of how they can solve this themselves.
For starters, you can compare you my.cnf file against the Q&A version that is available for free via Percona. It this an ideal solution? No, but it will allow you to take a fresh look at your configuration file after you answers all of their questions, via their configuration wizard.
innodb_buffer_pool_size –
select @@innodb_buffer_pool_size;Setting the innodb_buffer_pool_size is by far one of the most important settings for a MySQL InnoDB database.Some good reading on this topic is below:
- http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
- http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS
FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B;
It is safe to say that if you have the default 8M set as your innodb_buffer_pool_size variable then this should be one of the first settings you adjust.
Basically, allow this to have all of the available memory you can afford to give it. This of course takes into account several factors, like what else needs to allocate memory on the server ? Keep in mind that Disk I/O is important to performance and the more memory you allow this variable to have the less disk I/O should be required for table access.
Let us also consider this....
Since a general recommendation is to set
the innodb_log_file_size at 25 % of the buffer pool size we should evaluate what we have for the innodb_log_file_size per Baron's concepts then look to see how the relates to the innodb_buffer_pool_size.For example, following the logic from
> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 3982683217
1 row in set (0.01 sec)
1 row in set (59.99 sec)
Log sequence number 3991367755
1 row in set (0.01 sec)
> SET @sequence1=3982683217;
Query OK, 0 rows affected (0.00 sec)
> SET @sequence2=3991367755;
Query OK, 0 rows affected (0.00 sec)
> select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 8.28222084 |
+------------+
1 row in set (0.00 sec)
> select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
+--------------+
| MB_per_hour |
+--------------+
| 496.93325043 |
+--------------+
1 row in set (0.00 sec)
> select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
+-----------------------+
| estimated_buffer_pool |
+-----------------------+
| 1987.73300171 |
+-----------------------+
1 row in set (0.00 sec)
> select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;
+--------------------------+
| estimated_buffer_pool_GB |
+--------------------------+
| 1.941145509481 |
+--------------------------+
1 row in set (0.00 sec)
SELECT CONCAT(ROUND(KBS/POWER(1024,
-> IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
-> SUBSTR(' KMG',IF(PowerOf1024<0,0,
-> IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
-> FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
-> WHERE engine='InnoDB') A,
-> (SELECT 3 PowerOf1024) B;
+-------------------------------------+
| recommended_innodb_buffer_pool_size |
+-------------------------------------+
| 1G |
+-------------------------------------+
You get a different result. While Baron's check is dependent on the time frame it is executed within (which is why you should check this during peak traffic) it can give you a more realistic view on YOUR traffic and use. I would look into setting the innodb_buffer_pool_size to 2G not 1G in this simple example.
SET @sequence1=3982683217;
SET @sequence2=3991367755;
select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;
innodb_log_file_size–
select @@innodb_log_file_size;I have found that I enjoy Baron's concepts on how to set the innodb_log_file_size. Remember to execute his suggestions during peak times to get true readings. The larger the size of these files the better performance with large data sets but nothing comes for free, you will increase the recovery times. Increased recovery times might not sound like a major concern to some, until it is a revenue dependent database that you are waiting and watching process forever. A general recommendation is to set it at 25 % of the buffer pool size.
select ( @@innodb_buffer_pool_size * .25 ) - http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
- http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/
innodb_log_buffer_size –
select @@innodb_log_buffer_size; I recall I once had this set on a server to innodb_log_buffer_size=128M. Was it a good choice? No it means I wanted memory when I likely did not have to. It was a very write heavy database but this setting was very likely to high. Focus on the defaults first and then double it ( 8MB - 16MB ) max.
innodb_additional_mem_pool_size–
select @@innodb_additional_mem_pool_size; Peter addresses this setting in his blog post. You can address it and evaluate options after other settings have been addressed in my opinion.
innodb_flush_log_at_trx_commit–
select @@innodb_flush_log_at_trx_commit; > set GLOBAL innodb_flush_log_at_trx_commit =2;
Peter addresses this setting in his blog post as well. This is a commonly overlooked variable and worth testing performance improvement by adjusting from 0 - 2. Keep in mind the risks.
thread_cache –
select @@thread_cache_size;
+---------------------+
| @@thread_cache_size |
+---------------------+
| 50 |
+---------------------+
>show status like 'threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 4 |
+-----------------+-------+
1 row in set (0.00 sec)
> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 962 |
+---------------+-------+
1 row in set (0.00 sec)
> SELECT 100 - ((4 / 962) * 100);
+-------------------------+
| 100 - ((4 / 962) * 100) |
+-------------------------+
| 99.5842 |
+-------------------------+
If it climbs then you have it set too low and it needs to be raised.
query_cache_size –
select ((@@query_cache_size / 1024) / 1024); The permissible values are multiples of 1024.
Pay attention to this setting the more read dependent you application becomes. I used to run with it set to key_buffer=16M and I could and likely should have doubled that.
key_buffer_size –
select @@key_buffer_size; With MySQL switching to InnoDB as the default storage engine it is likely some we could see fewer and fewer tables created as MyISAM. That is not a fact just an opinion. The key_buffer_size will very important to you if you do utilize MyISAM tables.
Review Shlomi Noach's post to help you find which easily which tables which engine type.
"See per table size (almost exactly as presented in INFORMATION_SCHEMA)" -- Shlomi Noach.
> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 15728640 |
+-----------------+----------+
table_cache –
show variables like '%table%cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_definition_cache | 4096 |
| table_open_cache | 4096 |
| table_open_cache_instances | 1 |
+----------------------------+-------+
According to the manual this is a good formula to help determine the
table_definition_cache size. SELECT 400 + (@@table_open_cache / 2);
> SHOW status like '%Opened_tables%';
table_open_cache_instancesA value of 8 or 16 is recommended on systems that routinely use 16 or more cores, the default is 1.
Hopefully this exposes the variables and settings that need to be reviewed to help you get the best from your MySQL database.
