Skip to main content
added H. to list of observations.
Source Link
Wilson Hauck
  • 1
  • 1
  • 11
  • 13

'inconsistent during the day' Mave Nov 14 at 20:30

my.cnf things to do:

in [mysqld] section, 

max_heap_table_size = 24M # from 16M
tmp_table_size = 24M # from 16M  to reduce 38% HDD used today
innodb_io_capacity = 2000 # from 200 for additional capacity
innodb_io_capacity = 20000 # from 2000 for additional capacity
read_rnd_buffer_size = 128K # from 256K to reduce 4738 RPS
delay_key_write = OFF # from ON to speed storage and minimize corruption exposure
key_buffer_size = 100K # from 500+ M, used only by MyISAM
innodb_change_buffer_max_size = 10 # from 25%, less than 3% max used in 20 days
innodb_read_ahead_threshold = 8 # from 56 rather than waiting
innodb_stats_sample_pages = 32 # from 8 to improve cardinality data for optimizer
max_seeks_for_key = 64 # from a huge number to limit wasted dives
max_write_lock_count = 5 # from a huge number to give RD a chance quicker
query_cache_limit = 1M # from ~ 104K for EACH QC candidate size limit 
query_cache_min_res_unit = 512 # from 4096 to pack more QC results into QC
query_prealloc_size = 32K # from 8K to avoid acquiring RAM all day for parsing

Typical HARD rules, only one a day and be sure before moving to next attempt to smooth your performance.

Problems to be addressed related to the 20 days of status information, A. why 446,000 com_rollback_to savepoints ? B. why 2,444 com_rollback ? C. normally Com_begin has a companion Com_commit. 4,283 short on commit. D. 196 com_flush side effect is destruction of much of Query Cache results E. Failure to have innodb_print_all_deadlocks = ON means you will never know what caused the problem or figure it out. F. ~ 5 million select_scan usually means an index is missing that could speed process G. only 1 of each 7 candidate query results made it into Query Cache. One minute of your general log will enable analysis to determine if this ratio could be improved significantly. H. Handler_write high count implies you are storing new data most of the time.

Hoping you are making progress on raising ulimit capacity within Linux. This will allow more 'hits' than current ulimit's.

So much of your effort has been EXCELLENT and these suggestions in no way are critical of where you are today. Need your feedback, when time permits. Not every DAY.

'inconsistent during the day' Mave Nov 14 at 20:30

my.cnf things to do:

in [mysqld] section, 

max_heap_table_size = 24M # from 16M
tmp_table_size = 24M # from 16M  to reduce 38% HDD used today
innodb_io_capacity = 2000 # from 200 for additional capacity
innodb_io_capacity = 20000 # from 2000 for additional capacity
read_rnd_buffer_size = 128K # from 256K to reduce 4738 RPS
delay_key_write = OFF # from ON to speed storage and minimize corruption exposure
key_buffer_size = 100K # from 500+ M, used only by MyISAM
innodb_change_buffer_max_size = 10 # from 25%, less than 3% max used in 20 days
innodb_read_ahead_threshold = 8 # from 56 rather than waiting
innodb_stats_sample_pages = 32 # from 8 to improve cardinality data for optimizer
max_seeks_for_key = 64 # from a huge number to limit wasted dives
max_write_lock_count = 5 # from a huge number to give RD a chance quicker
query_cache_limit = 1M # from ~ 104K for EACH QC candidate size limit 
query_cache_min_res_unit = 512 # from 4096 to pack more QC results into QC
query_prealloc_size = 32K # from 8K to avoid acquiring RAM all day for parsing

Typical HARD rules, only one a day and be sure before moving to next attempt to smooth your performance.

Problems to be addressed related to the 20 days of status information, A. why 446,000 com_rollback_to savepoints ? B. why 2,444 com_rollback ? C. normally Com_begin has a companion Com_commit. 4,283 short on commit. D. 196 com_flush side effect is destruction of much of Query Cache results E. Failure to have innodb_print_all_deadlocks = ON means you will never know what caused the problem or figure it out. F. ~ 5 million select_scan usually means an index is missing that could speed process G. only 1 of each 7 candidate query results made it into Query Cache. One minute of your general log will enable analysis to determine if this ratio could be improved significantly.

Hoping you are making progress on raising ulimit capacity within Linux. This will allow more 'hits' than current ulimit's.

So much of your effort has been EXCELLENT and these suggestions in no way are critical of where you are today. Need your feedback, when time permits. Not every DAY.

'inconsistent during the day' Mave Nov 14 at 20:30

my.cnf things to do:

in [mysqld] section, 

max_heap_table_size = 24M # from 16M
tmp_table_size = 24M # from 16M  to reduce 38% HDD used today
innodb_io_capacity = 2000 # from 200 for additional capacity
innodb_io_capacity = 20000 # from 2000 for additional capacity
read_rnd_buffer_size = 128K # from 256K to reduce 4738 RPS
delay_key_write = OFF # from ON to speed storage and minimize corruption exposure
key_buffer_size = 100K # from 500+ M, used only by MyISAM
innodb_change_buffer_max_size = 10 # from 25%, less than 3% max used in 20 days
innodb_read_ahead_threshold = 8 # from 56 rather than waiting
innodb_stats_sample_pages = 32 # from 8 to improve cardinality data for optimizer
max_seeks_for_key = 64 # from a huge number to limit wasted dives
max_write_lock_count = 5 # from a huge number to give RD a chance quicker
query_cache_limit = 1M # from ~ 104K for EACH QC candidate size limit 
query_cache_min_res_unit = 512 # from 4096 to pack more QC results into QC
query_prealloc_size = 32K # from 8K to avoid acquiring RAM all day for parsing

Typical HARD rules, only one a day and be sure before moving to next attempt to smooth your performance.

Problems to be addressed related to the 20 days of status information, A. why 446,000 com_rollback_to savepoints ? B. why 2,444 com_rollback ? C. normally Com_begin has a companion Com_commit. 4,283 short on commit. D. 196 com_flush side effect is destruction of much of Query Cache results E. Failure to have innodb_print_all_deadlocks = ON means you will never know what caused the problem or figure it out. F. ~ 5 million select_scan usually means an index is missing that could speed process G. only 1 of each 7 candidate query results made it into Query Cache. One minute of your general log will enable analysis to determine if this ratio could be improved significantly. H. Handler_write high count implies you are storing new data most of the time.

Hoping you are making progress on raising ulimit capacity within Linux. This will allow more 'hits' than current ulimit's.

So much of your effort has been EXCELLENT and these suggestions in no way are critical of where you are today. Need your feedback, when time permits. Not every DAY.

Source Link
Wilson Hauck
  • 1
  • 1
  • 11
  • 13

'inconsistent during the day' Mave Nov 14 at 20:30

my.cnf things to do:

in [mysqld] section, 

max_heap_table_size = 24M # from 16M
tmp_table_size = 24M # from 16M  to reduce 38% HDD used today
innodb_io_capacity = 2000 # from 200 for additional capacity
innodb_io_capacity = 20000 # from 2000 for additional capacity
read_rnd_buffer_size = 128K # from 256K to reduce 4738 RPS
delay_key_write = OFF # from ON to speed storage and minimize corruption exposure
key_buffer_size = 100K # from 500+ M, used only by MyISAM
innodb_change_buffer_max_size = 10 # from 25%, less than 3% max used in 20 days
innodb_read_ahead_threshold = 8 # from 56 rather than waiting
innodb_stats_sample_pages = 32 # from 8 to improve cardinality data for optimizer
max_seeks_for_key = 64 # from a huge number to limit wasted dives
max_write_lock_count = 5 # from a huge number to give RD a chance quicker
query_cache_limit = 1M # from ~ 104K for EACH QC candidate size limit 
query_cache_min_res_unit = 512 # from 4096 to pack more QC results into QC
query_prealloc_size = 32K # from 8K to avoid acquiring RAM all day for parsing

Typical HARD rules, only one a day and be sure before moving to next attempt to smooth your performance.

Problems to be addressed related to the 20 days of status information, A. why 446,000 com_rollback_to savepoints ? B. why 2,444 com_rollback ? C. normally Com_begin has a companion Com_commit. 4,283 short on commit. D. 196 com_flush side effect is destruction of much of Query Cache results E. Failure to have innodb_print_all_deadlocks = ON means you will never know what caused the problem or figure it out. F. ~ 5 million select_scan usually means an index is missing that could speed process G. only 1 of each 7 candidate query results made it into Query Cache. One minute of your general log will enable analysis to determine if this ratio could be improved significantly.

Hoping you are making progress on raising ulimit capacity within Linux. This will allow more 'hits' than current ulimit's.

So much of your effort has been EXCELLENT and these suggestions in no way are critical of where you are today. Need your feedback, when time permits. Not every DAY.