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

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 😮 ?

[brashquido]

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

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

[brashquido]

dc45 wrote on Mar 15 2007, 08:51 PM:

The website that this runs off of is [url=”http://localhost:9999/”]http://localhost:9999/[/url]

Is this accessible from other computers by going to [url=”http://SERVER_NAME:9999/”]http://SERVER_NAME:9999/[/url] ?

I am unable to access it from another computer. Can the port be changed?

As far as I know you are not able to access Monyog from remote servers, and the port is hardcoded at this stage. If you need to monitor a remote MySQL server with Monyog the only way at present that I know of is to install Monyog on your local machine and connect to your MySQL server over the network. You'll need to make sure networking is enabled in MySQL and that you are able to connect to it (e.g, make sure TCP port 3306 is open in your firewall, etc)

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

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

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

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?

[brashquido]

Hi Rohit, thanks for the reply 🙂

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

[Author]

Posts