[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

[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 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

[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

[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

[Author]

Posts