Cache Confusion

[brashquido]

Hi All,

Using MONyog and it tells me that my MyISAM Key Cache (key_buffer_size) and my Query Cache (query_cache_size) need to be increased. However looking at MONyog and it says that 56304 of 64000 1k blocks (64MB key_buffer_size) are not in use in my MyISAM Key Cache, and 50.440M of 64MB are free in my Query Cache. If MySQL isn't using the cache it has got, then why would increasing it help?

Also, in the Misc section it says I need 182MB of memory. Just wondering how that is calculated as from guides I've found around the net you calculate the maximum memory MySQL can use with this equation;

key_buffer + innodb_buffer_pool + innodb_log_buffer + innodb_additional_mem_pool + net_buffer + max_connections * (read_buffer + join_buffer + sort_buffer + myisam_sort_buffer + thread_stack + tmp_table_size + read_rnd_buffer)

Which for me results in;

67,108,864 + 50,331,648 + 524,288 + 1,048,576 + 16,384 + 20 x (520,192 + 520,192 + 1,048,568 +8,388,608 + 196,608 + 67,108,864 + 2,093,056) = 9507633181900640

[Rohit]

What is the hit rate of MyISAM Key Cache and Query cache?

[brashquido]

Hi Rohit, thanks for the reply 🙂

My MyISAM Key Cache hitrate is 95.044%, and my Query Cache hitrate is 75.90%.

[peterlaursen]

I think you misunderstand the operator hierachy!

67,108,864 + 50,331,648 + 524,288 + 1,048,576 + 16,384 + 20 * (520,192 + 520,192 + 1,048,568 +8,388,608 + 196,608 + 67,108,864 + 2,093,056) = 1716551.52

and

(67,108,864 + 50,331,648 + 524,288 + 1,048,576 + 16,384 + 20) * (520,192 + 520,192 + 1,048,568 +8,388,608 + 196,608 + 67,108,864 + 2,093,056) = 9509229106.1389

67,108,864 + 50,331,648 + 524,288 + 1,048,576 + 16,384 + 20 * (520,192 + 520,192 + 1,048,568 +8,388,608 + 196,608 + 67,108,864 + 2,093,056)

means

+ 67,108,864

+ 50,331,648

+ 524,288

+ 1,048,576

+ 16,384

+ (20 * (520,192 + 520,192 + 1,048,568 +8,388,608 + 196,608 + 67,108,864 + 2,093,056))

and not

(

+ 67,108,864

+ 50,331,648

+ 524,288

+ 1,048,576

+ 16,384

+ 20

)

* (520,192 + 520,192 + 1,048,568 +8,388,608 + 196,608 + 67,108,864 + 2,093,056) = 1716551.52

[brashquido]

Aha, thanks Peter. That makes a lot more sense.

Any idea about why MONyog is asking me to increase my cache sizes when MySQL isn't using what it has? Do you need to know what my other settings for each cache type are?

[peterlaursen]

about the Query cache: you are correct that if only BOTH hit rate is low AND cache memory is used it makes sense to increase the total cache. Because then incresing the cache will probably also increase the hitrate.

We will release Monyog 0.16 by the end of this week including a fix for this issue and better advisors for the Query Cache in general.

The query cache distinguishes between queries a 'binary' way – even if you change the LETTERCASE the queries are considered different. Only when executing identical queries frequently it makes sense to enable the QC. Sometimes the advisor maybe should be 'disable the QC – you do not get anything in return for the CPU overhead that maintaining it generates'!!. Actually there is no general rule about what is good. On yahoo.com (where users do the same select again and again) anything less than 99% is probably bad – on other type of systems it may be different.

Your data: “that 56304 of 64000 1k blocks are not in use”. Now if every query uses 2 blocks in average you have stored (64000-56304) / 2 = 3848 queries in the cache. How many different queries were executed since the server was restarted? Also note that queries that return a TIMESTAMP is not cached (and there are a couple of handfull of similar exceptions). In your situation you could probably decrease the QC without any problems and without losing performance. But maybe your block size could be 'tuned' to fit your queries. Also note that a very big query executed once may 'prune' you QC completely. That can be avoided by using “SELECT sql_no_cache …” for such queries. What does “show status like 'Qcache_lowmem_prunes';” tell?

A good article at: http://dev.mysql.com/tech-resources/articl…uery-cache.html

about the MyISAM key cache: likely it is a similar issue. But we have posted a ticket with MySQL AB for gettings detailed explanation about some undocumented questions about this one. We will return when we got the answer.

[brashquido]

Hi Peter,

Thanks for the detailed reply 🙂 .

I've been playing around with different settings and restarting my server, but as of the moment I have 2.351K queries in my query cache and 280 that are not. The query cache has had 30.704K hits in the 1.32 hours the server has been up. 9.343K queries have been inserted into the cache, and there is only 284 free blocks left and a total 5.209K 4K blocks. The value of Qcache_lowmem_prunes is 0.

I might look at increasing the block size, but doesn't that corrupt your MyISAM indexes?

[brashquido]

Oh, another thing I've been wondering is would it be a good idea to only use one engine type on a server with very limited memory? I'm hosting MySQL and IIS 6 on one VPS with 512M of memory, so I don't have a lot of room to move. 70% of the statements on my server are SELECT, meaning (if I have taken anything in over the last day or two of reading) that I wouldn't really get any advantage out of InnoDB's row level locking. My websites aren't really high traffic, the server is just low on resources. I also have a few one or two PHP apps that depend on MyISAM indexes, so would it be worth looking at converting all my databases to MyISAM and disabling InnoDB so I can free up the RAM used by InnoDB caches?

[peterlaursen]

If you do not use transactions or Foreign Keys or any other specific InnoDB feature it will make no harm to try!

There is some discussions of InnoDB performance compared to MyIsam. In the beginning (MySQL 3.23 and 4.0) InnoDB could be vesy slow – with the later versions I do not think the difference is very big, but still I think it is slower.

[peterlaursen]

maybe your problem is related to this discussion:

http://www.webyog.com/forums//index.php?showtopic=3060

We are not sure if

1) you are getting your information by the (wrong infoin the) tooltip

2) key_blocks_unused is really returning the value thatyou are claiming

Could you post your “show global variables” and “show global status” dump

for a specific situation, and describe how you understand it

[brashquido]

Hi Peter,

Sorry for my slow reply, I've been a bit busy of late.

Here is my SHOW GLOBAL VARIABLES output;

Code:

+———————————+——————————————————–+
| Variable_name | Value |
+———————————+——————————————————–+
| back_log | 50 |
| basedir | C:MySQLMySQL Server 4.1 |
| bdb_cache_size | 8388600 |
| bdb_home | C:MySQLMySQL Server 4.1Data |
| bdb_log_buffer_size | 513536 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | C:WINDOWSTEMP |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:MySQLMySQL Server 4.1sharecharsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | OFF |
| connect_timeout | 10 |
| datadir | C:MySQLMySQL Server 4.1Data |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 1800 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | YES |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | NO |
| have_csv | NO |
| have_example_engine | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 25165824 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 524288 |
| innodb_log_file_size | 25165824 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | . |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 4 |
| interactive_timeout | 25 |
| join_buffer_size | 1044480 |
| key_buffer_size | 25165824 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | C:MySQLMySQL Server 4.1shareenglish |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| log | OFF |
| log_bin | OFF |
| log_error | .IIS-AID.err |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 2 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 999999 |
| max_connections | 32 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 32 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 524288 |
| myisam_max_sort_file_size | 0 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| named_pipe | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 2048 |
| pid_file | C:MySQLMySQL Server 4.1DataIIS-AID.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 32768 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 520192 |
| read_only | OFF |
| read_rnd_buffer_size | 2093056 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| shared_memory | OFF |
| shared_memory_base_name | MYSQL |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| sort_buffer_size | 1048568 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | Eastern Standard Time |
| table_cache | 1003 |
| table_type | MyISAM |
| thread_cache_size | 16 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.20-community-max-nt-log |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) |
| version_comment | MySQL Community Edition – Max (GPL) |
| version_compile_machine | ia32 |
| version_compile_os | Win32 |
| wait_timeout | 1000 |
+———————————+——————————————————–+

And here is my SHOW STATUS output (I'm using 4.1.20 which does not use the GLOBAL modifier);

Code:

+—————————-+———–+
| Variable_name | Value |
+—————————-+———–+
| Aborted_clients | 68 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 72444083 |
| Bytes_sent | 585989643 |
| Com_admin_commands | 1247 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 193574 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 11133 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 12294 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 425 |
| Com_optimize | 58 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 462084 |
| Com_set_option | 1834 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 3 |
| Com_show_errors | 0 |
| Com_show_fields | 65 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 463 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 10191 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 2 |
| Com_show_variables | 9 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 425 |
| Com_update | 14315 |
| Com_update_multi | 0 |
| Connections | 9432 |
| Created_tmp_disk_tables | 18797 |
| Created_tmp_files | 4 |
| Created_tmp_tables | 29147 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 8340 |
| Handler_discover | 0 |
| Handler_read_first | 43828 |
| Handler_read_key | 2200034 |
| Handler_read_next | 13809428 |
| Handler_read_prev | 83697 |
| Handler_read_rnd | 1062858 |
| Handler_read_rnd_next | 27560049 |
| Handler_rollback | 22 |
| Handler_update | 135426 |
| Handler_write | 772757 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 20889 |
| Key_blocks_used | 2002 |
| Key_read_requests | 6946046 |
| Key_reads | 93754 |
| Key_write_requests | 134341 |
| Key_writes | 56803 |
| Max_used_connections | 14 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 321 |
| Open_streams | 0 |
| Open_tables | 169 |
| Opened_tables | 12339 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 716682 |
| Rpl_status | NULL |
| Select_full_join | 9 |
| Select_full_range_join | 0 |
| Select_range | 9512 |
| Select_range_check | 20 |
| Select_scan | 57473 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 53347 |
| Sort_rows | 1280912 |
| Sort_scan | 36519 |
| Table_locks_immediate | 595596 |
| Table_locks_waited | 20 |
| Threads_cached | 11 |
| Threads_connected | 3 |
| Threads_created | 14 |
| Threads_running | 1 |
| Uptime | 49260 |
+—————————-+———–+

As to how I understand it? Well, I don't really 🙂 . From above you can see that I have now disabled my query cache (as advised by monyog 0.16 ;)). I still have issues with an excessive amount of table scans, which if I understand correctly will come down to query construction and use of table indexes rather than anything to do with the setup of MySQl's various caches and buffers.

Key Cache on the otherhand is confusing me a bit. Above you can see that there is a total of 22891 keyblocks (Key_blocks_unused 20889 + Key_blocks_used 2002) with about 8.75% of them are used (key_cache_block_size = 1024 BTW). The ratio of key writes to disk to total key rights is about 0.422 (Key_writes 56803/Key_write_requests 134341) and my cache hitrate is about 98.67% ((Key_read_requests 6946046 + Key_reads 93754) / 100 * 6946046). Monyog recommends that I increase the size of my key_cache, but I don't understand how that will help if what it has is not even close to being all used. Or am I wrong here?

[Rohit]

Thanks for the data.

We will analyze them and get back to you shortly.

[AnandP]

brashquido wrote on Mar 14 2007, 10:43 PM:

Hi Peter,

Sorry for my slow reply, I've been a bit busy of late.

Here is my SHOW GLOBAL VARIABLES output;

Code:

+———————————+——————————————————–+
| Variable_name | Value |
+———————————+——————————————————–+
| back_log | 50 |
| basedir | C:MySQLMySQL Server 4.1 |
| bdb_cache_size | 8388600 |
| bdb_home | C:MySQLMySQL Server 4.1Data |
| bdb_log_buffer_size | 513536 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | C:WINDOWSTEMP |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:MySQLMySQL Server 4.1sharecharsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | OFF |
| connect_timeout | 10 |
| datadir | C:MySQLMySQL Server 4.1Data |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 1800 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | YES |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | NO |
| have_csv | NO |
| have_example_engine | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 25165824 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 524288 |
| innodb_log_file_size | 25165824 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | . |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 4 |
| interactive_timeout | 25 |
| join_buffer_size | 1044480 |
| key_buffer_size | 25165824 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | C:MySQLMySQL Server 4.1shareenglish |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| log | OFF |
| log_bin | OFF |
| log_error | .IIS-AID.err |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 2 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 999999 |
| max_connections | 32 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 32 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 524288 |
| myisam_max_sort_file_size | 0 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| named_pipe | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 2048 |
| pid_file | C:MySQLMySQL Server 4.1DataIIS-AID.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 32768 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 520192 |
| read_only | OFF |
| read_rnd_buffer_size | 2093056 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| shared_memory | OFF |
| shared_memory_base_name | MYSQL |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| sort_buffer_size | 1048568 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | Eastern Standard Time |
| table_cache | 1003 |
| table_type | MyISAM |
| thread_cache_size | 16 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.20-community-max-nt-log |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) |
| version_comment | MySQL Community Edition – Max (GPL) |
| version_compile_machine | ia32 |
| version_compile_os | Win32 |
| wait_timeout | 1000 |
+———————————+——————————————————–+

And here is my SHOW STATUS output (I'm using 4.1.20 which does not use the GLOBAL modifier);

Code:

+—————————-+———–+
| Variable_name | Value |
+—————————-+———–+
| Aborted_clients | 68 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 72444083 |
| Bytes_sent | 585989643 |
| Com_admin_commands | 1247 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 193574 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 11133 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 12294 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 425 |
| Com_optimize | 58 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 462084 |
| Com_set_option | 1834 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 3 |
| Com_show_errors | 0 |
| Com_show_fields | 65 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 463 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 10191 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 2 |
| Com_show_variables | 9 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 425 |
| Com_update | 14315 |
| Com_update_multi | 0 |
| Connections | 9432 |
| Created_tmp_disk_tables | 18797 |
| Created_tmp_files | 4 |
| Created_tmp_tables | 29147 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 8340 |
| Handler_discover | 0 |
| Handler_read_first | 43828 |
| Handler_read_key | 2200034 |
| Handler_read_next | 13809428 |
| Handler_read_prev | 83697 |
| Handler_read_rnd | 1062858 |
| Handler_read_rnd_next | 27560049 |
| Handler_rollback | 22 |
| Handler_update | 135426 |
| Handler_write | 772757 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 20889 |
| Key_blocks_used | 2002 |
| Key_read_requests | 6946046 |
| Key_reads | 93754 |
| Key_write_requests | 134341 |
| Key_writes | 56803 |
| Max_used_connections | 14 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 321 |
| Open_streams | 0 |
| Open_tables | 169 |
| Opened_tables | 12339 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 716682 |
| Rpl_status | NULL |
| Select_full_join | 9 |
| Select_full_range_join | 0 |
| Select_range | 9512 |
| Select_range_check | 20 |
| Select_scan | 57473 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 53347 |
| Sort_rows | 1280912 |
| Sort_scan | 36519 |
| Table_locks_immediate | 595596 |
| Table_locks_waited | 20 |
| Threads_cached | 11 |
| Threads_connected | 3 |
| Threads_created | 14 |
| Threads_running | 1 |
| Uptime | 49260 |
+—————————-+———–+

As to how I understand it? Well, I don't really 🙂 . From above you can see that I have now disabled my query cache (as advised by monyog 0.16 ;)). I still have issues with an excessive amount of table scans, which if I understand correctly will come down to query construction and use of table indexes rather than anything to do with the setup of MySQl's various caches and buffers.

Key Cache on the otherhand is confusing me a bit. Above you can see that there is a total of 22891 keyblocks (Key_blocks_unused 20889 + Key_blocks_used 2002) with about 8.75% of them are used (key_cache_block_size = 1024 BTW). The ratio of key writes to disk to total key rights is about 0.422 (Key_writes 56803/Key_write_requests 134341) and my cache hitrate is about 98.67% ((Key_read_requests 6946046 + Key_reads 93754) / 100 * 6946046). Monyog recommends that I increase the size of my key_cache, but I don't understand how that will help if what it has is not even close to being all used. Or am I wrong here?

Hi BrashQuido,

Will you kindly also tell me how much memory this particular box has got? Is it a dedicated database server or is a shared box? Currently you have 25M ( 25165824 bytes) of key_buffer_size. I won't recommend setting it larger than 30% of your available memory, as some memory is also required by the OS to cache rows. Also note that this will cache only “myisam” indexes.

query_cache_size and key_buffer_size are two different things. You need to remember that query_cache is common to all storage engines (MyISAM, InnoDB etc) whereas all the key_xxx(variables/parameters) are only for MyISAM storage engines. So increasing/decreasing key_buffer will affect only affect MyISAM performance. And of course, if you area also using InnoDB storage engine, you need to be extra careful because you don't want increase the performance of MyISAM at the cost of InnoDB. If none of your tables are InnoDB, I would recommend you to include “skip-innodb” line in [mysqld] section of config file (my.cnf).

Your cache hitrate of 98.67% is really not bad. I think monyog recommendation to achieve buffer pool hit rate of 99% is rule of thumb (based on best practices).

Looking at the status, I am finding excessive Created_tmp_disk_tables (18797) of total 29147 Created_tmp_tables. This is over 60 percent going on disk. I would recommend to fix this one first because you are generating too much disk I/O and have (-ve) impact on overall performance. Regards,

Anand

[Rohit]

I think we have to fine-tune the advisor for Key cache hit rate. Right now, if the key cache hit rate is below 99%, it advises you to increase the key_cache_size. We have to take other factors (like unused blocks) before coming up with the advice.

I will keep you updated.

[brashquido]

No problems Rohit, I look forward to it :).

Have another question for you about query cache this time, and might not so much be Monyog but rather general MySQL. I have my query cache set to 24MB, and I have 10.8MB free, yet my Qcache_lowmem_prunes value is 3.417K. Any ideas on why my query cache is being pruned so much if I have so much free memory? I also noticed that fragmentation is very high at over 24%. Other stats that might be useful is that my block size is set to 4k and my maximum results cache size is 4MB.

[AnandP]

brashquido wrote on Mar 21 2007, 08:56 AM:

No problems Rohit, I look forward to it :).

Have another question for you about query cache this time, and might not so much be Monyog but rather general MySQL. I have my query cache set to 24MB, and I have 10.8MB free, yet my Qcache_lowmem_prunes value is 3.417K. Any ideas on why my query cache is being pruned so much if I have so much free memory? I also noticed that fragmentation is very high at over 24%. Other stats that might be useful is that my block size is set to 4k and my maximum results cache size is 4MB.

Since you still have plenty of free query_cache_size and still memory getting pruned (because Qcache_lowmem_prunes value is high), I suspect you have lot of queries with small result set . And this is also responsible for memory fragmentation. Actually, this is obvious if you look at significant number of free blocks in your case. Therefore, I would recommend to decrease the value of query_cache_min_res_unit. This value is in byte and default is 4K and you still have default value(4096). Since memory allocation operation is pretty expensive, the query cache allocates blocks with a minimum size given by this variable. So lower this value. Try with 2048 and see how it looks like. Regards,

-Anand

[brashquido]

Cool, thanks 🙂 . Should there be any correlation between key_cache_block_size and query_cache_min_res_unit ?

[AnandP]

brashquido wrote on Mar 22 2007, 06:13 PM:

Cool, thanks 🙂 . Should there be any correlation between key_cache_block_size and query_cache_min_res_unit ?

There is no direct relation.

MyISAM caches only the indexes (in key_buffer_size) and is still dependent for data cachhing on underlying operating system UNLIKE innoDB where both (data+index) are cached in innodb_buffer_pool_size. In fact, you can specify the size of the block buffers for an individual key cache using the key_cache_block_size variable. By doing so, you can tune the IO performance of index files.

Once a query has been cached in query_cache_size ( in multiple of query_cache_min_res_unit setting, the last block gets trimmed), your next “identical” query neither hits database nor any other buffer. It picks up the result set directly from query_cache.

Just a side note, we normally recommend the size of read buffers to be set equal to the size of the under lying operating system I/O buffers to achieve the best IO performance. Thanks,

-Anand

[Rohit]

@Brashquido:

I just wanted to know if MONyog was already advising you on the Temporary Tables Tuning that Anand has suggested. If not, we have a bug in MONyog!

[brashquido]

AnandP wrote on Mar 22 2007, 10:39 PM:

Just a side note, we normally recommend the size of read buffers to be set equal to the size of the under lying operating system I/O buffers to achieve the best IO performance.

Do you mean buffers like read_buffer_size and read_rnd_buffer_size? Can't remember what the Windows 2k3 I/O buffer size is off the top of my head, but I have these buffers set to 1M & 2M respectively.

Rohit wrote:

I just wanted to know if MONyog was already advising you on the Temporary Tables Tuning that Anand has suggested. If not, we have a bug in MONyog!

Yeah, temporary tables and caches was something I was going to ask about next as it doesn't look healthy to me. I don't have many tables on my server (1128 to be exact) and half of those are in beta and/or testing sites that get very little traffic. There are only about 4 sites on my server that get any real traffic, and between them they have 462 tables.

I now have my table_cache set to 512 and I have never seen open_tables above 400, yet my opened_tables value after about 14 hrs of uptime is 11.201K. This seems very high to me. Should I increase my table_cache size to something like 1200 so that all the tables should be able to be cached? What implications does this have on memory usage?

As for temporary tables, I have tmp_table_size set to 32M and max_heap_table_size at the default of 16MB. Here again it seems I'm could have things better as create_tmp_tables (tables created in memory) is 5.535K and create_tmp_disk_tables (tables create on disk) is 3.688K.

Any advice 😮 ?

[Rohit]

With the current values of create_tmp_tables and create_tmp_disk_tables, MONyog should advice you to increase tmp_table_size and max_heap_table_size. It is not doing so?

[brashquido]

Rohit wrote on Mar 23 2007, 10:33 AM:

With the current values of create_tmp_tables and create_tmp_disk_tables, MONyog should advice you to increase tmp_table_size and max_heap_table_size. It is not doing so?

No, the only thing Monyog has flagged is my query cache. I increased max_heap_table_size to 32MB as well to match tmp_table_size, but create_tmp_disk_tables still seems to be very high with a ratio of about 53%. Is 32MB a big value for tmp_table_size and max_heap_table_size? I'll try setting them to 64MB. Also, if these settings are low, will this effect the number of table cache misses?

[brashquido]

One more thing, any idea why my table_cache size value would be being displayed as 1007 when I've set it to 1024? It's not Monyog as when I do a “show variables like 'table_cache';” it returns 1007 as well. I've set the “open-files-limit=4096” which I understand I am ment to set if I increase the table_cache size. I'm wondering if this is something to do with Windows 2003 Server rather than MySQL;

http://dev.mysql.com/doc/refman/4.1/en/table-cache.html

[brashquido]

Seems others have seen this issue too;

http://www.vbulletin.com/forum/showthread.php?t=221979

[newmac]

brashquido wrote on Mar 23 2007, 03:40 AM:

No, the only thing Monyog has flagged is my query cache. I increased max_heap_table_size to 32MB as well to match tmp_table_size, but create_tmp_disk_tables still seems to be very high with a ratio of about 53%. Is 32MB a big value for tmp_table_size and max_heap_table_size? I'll try setting them to 64MB. Also, if these settings are low, will this effect the number of table cache misses?

OK. I think I got the problem. MONyog will show the red flag only if the tempporary disk:mem ratio is greater than 75%. In your case it is less than 75%. In any case, the “alert” value should be much lower.

Also, you should consider changing the architecture to have multiple levels of thresholds/warning between “Very Good” to “Disaster!”

[brashquido]

newmac wrote on Mar 23 2007, 01:44 PM:

Also, you should consider changing the architecture to have multiple levels of thresholds/warning between “Very Good” to “Disaster!”

That is a very good idea. Maybe a simple traffic light system with green = good, yellow = caution, red = critical?

[AnandP]

brashquido wrote on Mar 23 2007, 10:40 AM:

No, the only thing Monyog has flagged is my query cache. I increased max_heap_table_size to 32MB as well to match tmp_table_size, but create_tmp_disk_tables still seems to be very high with a ratio of about 53%. Is 32MB a big value for tmp_table_size and max_heap_table_size? I'll try setting them to 64MB. Also, if these settings are low, will this effect the number of table cache misses?

32MB is adequate for tmp_table_size in most of the applications to my experience. If you want to increase it further. you need to remember that this allocation is per thread basis and it adds up. Some temporary tables (specifically the large one) getting created on disk is acceptable and is pretty natural/normal. You migh have to pay heavy price if you try to get few (big) temporary table creation from disk to memory. Let them go there if the create_tmp_disk_tables is not high. But in your case, more than half going to disk is excessive. Regards,

-Anand

[AnandP]

brashquido wrote on Mar 23 2007, 11:12 AM:

One more thing, any idea why my table_cache size value would be being displayed as 1007 when I've set it to 1024? It's not Monyog as when I do a “show variables like 'table_cache';” it returns 1007 as well. I've set the “open-files-limit=4096” which I understand I am ment to set if I increase the table_cache size. I'm wondering if this is something to do with Windows 2003 Server rather than MySQL;

http://dev.mysql.com/doc/refman/4.1/en/table-cache.html

Very good question. table_cache is actually number of file descriptors. This variable has been renamed as table_open_cache in newer version of MySQL. This value limits the number of open tables for “all threads”. Increasing this value increases the number of file descriptors that mysqld requires. Please note that same table can be opened by more than one thread.

If you look at the “opened_tables” on production boxes, you will notice that this value is very high – way more than total number of tables. So current setting of table_open_cache (table_cache) value is small.

Yes, I also know a customer having same issue using MySQL on Windows 2003 Server. Which version of MySQL (server as well as client) are you using? I know there are some known bugs on open-files-limit. But first make sure you have appropriate open-files-limit setting in “mysqld or mysqld_safe section of your config file”. If the value is 0, mysqld reserves max_connections×5 or max_connections + table_open_cache×2 files (whichever is larger). he value is 0 on systems where MySQL can't change the number of open files. Basically this value is passed to “ulimit -n”.

Regards,

Anand

[brashquido]

AnandP wrote on Mar 23 2007, 07:26 PM:

Which version of MySQL (server as well as client) are you using?

Server version: 4.1.22-community-max-nt-log

MySQL client version: 3.23.49

[Author]

Posts