MySQL Forums
Forum List  »  Performance

Re: PLEASE HELP - performance is horrible!
Posted by: mike
Date: November 30, 2005 02:36AM

here's some updated info.

note the 0 aborted connects. it's because i set the instance to "nonguarded" so it's not actively trying to connect every so often :)

~~~

Uptime: 5 hours 47 min 43 sec

Threads: 4 Questions: 4294218 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 195 Queries per second avg: 205.829

~~

mysql> show status;
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 8 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1491 |
| Bytes_sent | 37289 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 0 |
| 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 | 0 |
| 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 | 0 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| 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 | 16 |
| Com_set_option | 0 |
| 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 | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 15 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 502117 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 7 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 1 |
| Delayed_writes | 501471 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 1338 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1462 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 82591 |
| Key_blocks_used | 24658 |
| Key_read_requests | 448884661 |
| Key_reads | 54000 |
| Key_write_requests | 3759504 |
| Key_writes | 1089297 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 42 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 224 |
| Open_streams | 0 |
| Open_tables | 195 |
| Opened_tables | 0 |
| Qcache_free_blocks | 1096 |
| Qcache_free_memory | 130972088 |
| Qcache_hits | 1267439 |
| Qcache_inserts | 1154411 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 263120 |
| Qcache_queries_in_cache | 2670 |
| Qcache_total_blocks | 6451 |
| Questions | 4449503 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 7 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 4 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 3265705 |
| Table_locks_waited | 35217 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 37 |
| Threads_connected | 6 |
| Threads_created | 42 |
| Threads_running | 5 |
| Uptime | 21863 |
+-----------------------------------+-----------+
222 rows in set (0.00 sec)

mysql> show variables;
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| 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 | /usr/local/mysql/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 0 |
| connect_timeout | 5 |
| datadir | /usr/local/mysql/var/ |
| 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 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 7 |
| flush | OFF |
| flush_time | 0 |
| 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 | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| 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 | 10485760 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 104857600 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:1024M:autoextend |
| innodb_data_home_dir | /usr/local/mysql/var/ |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 0 |
| 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 | 1048576 |
| innodb_log_file_size | 5242880 |
| 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_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 20 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 134217728 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 134217728 |
| max_connect_errors | 10 |
| max_connections | 300 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 268434432 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | FORCE |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 134217728 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 8192 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /usr/local/mysql/var/web06.pid |
| port | 3308 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 134217728 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 16773120 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 8388600 |
| 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 | 10 |
| system_time_zone | PST |
| table_cache | 357 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 75 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 268435456 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.16mike |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+----------------------------------------+
209 rows in set (0.00 sec)


right now vmstat actually looks decent. hmm. there's some small spikes - this isn't peak time either... it almost looks good...


[root@web06]# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
5 0 0 292456 12 579864 0 0 19 131 761 348 53 6 41 0
0 0 0 292456 12 579864 0 0 0 0 722 1299 2 3 95 0
0 0 0 292456 12 579868 0 0 0 0 743 1305 1 2 97 0
1 0 0 292456 12 579868 0 0 0 0 626 1053 98 2 0 0
0 0 0 292456 12 579868 0 0 0 0 742 1352 23 4 73 0
0 0 0 292456 12 579868 0 0 0 0 639 1162 1 3 96 0
0 0 0 292464 12 579868 0 0 0 0 757 1333 3 2 95 0
0 0 0 292464 12 579868 0 0 0 0 517 802 1 0 99 0
0 0 0 292464 12 579872 0 0 0 0 702 1191 2 2 96 0
0 0 0 292464 12 579872 0 0 0 8 663 1174 1 2 97 0
1 0 0 292464 12 579872 0 0 0 0 778 1398 5 5 90 0
1 0 0 292464 12 579872 0 0 0 9 831 1463 3 2 95 0
0 0 0 292464 12 579872 0 0 0 0 717 1285 1 2 97 0
0 0 0 292464 12 579872 0 0 0 0 551 859 58 3 39 0
0 0 0 292464 12 579872 0 0 0 1484 906 1385 4 2 94 0
1 0 0 292464 12 579872 0 0 0 0 620 1073 33 3 64 0
0 0 0 292464 12 579872 0 0 0 0 666 1229 30 2 68 0
1 0 0 292464 12 579872 0 0 0 0 689 1152 24 2 75 0
0 0 0 292464 12 579872 0 0 0 0 731 1277 39 3 58 0
0 0 0 292464 12 579872 0 0 0 20 459 593 58 5 37 0
0 0 0 292464 12 579872 0 0 0 8 759 1401 1 1 98 0
0 0 0 292464 12 579872 0 0 0 0 562 836 1 1 98 0
0 0 0 292464 12 579872 0 0 0 0 653 1088 1 2 97 0
0 0 0 292464 12 579872 0 0 0 0 678 1215 2 2 96 0
0 0 0 292464 12 579872 0 0 0 8 684 1276 2 1 97 0
0 0 0 292464 12 579876 0 0 0 0 495 658 2 0 98 0
0 0 0 292464 12 579876 0 0 0 0 568 898 2 1 97 0
0 0 0 292464 12 579876 0 0 0 0 661 1151 1 1 98 0
0 0 0 292464 12 579876 0 0 0 0 429 514 0 1 99 0
0 0 0 292464 12 579876 0 0 0 0 729 1235 2 2 96 0
0 0 0 292464 12 579876 0 0 0 0 653 1179 1 1 98 0
0 0 0 292464 12 579876 0 0 0 0 629 988 61 3 36 0
0 0 0 292464 12 579876 0 0 0 0 656 1135 2 1 97 0
0 0 0 292464 12 579876 0 0 0 0 816 1513 2 3 95 0
2 0 0 292464 12 579876 0 0 0 72 671 1225 54 3 43 0
2 0 0 292464 12 579876 0 0 0 0 637 1053 43 3 54 0
3 0 0 292464 12 579876 0 0 0 0 659 867 96 4 0 0
0 0 0 292464 12 579876 0 0 0 0 805 1513 47 4 49 0
0 0 0 292464 12 579876 0 0 0 0 552 822 1 1 98 0
0 0 0 292464 12 579876 0 0 0 0 644 1087 2 2 96 0
0 0 0 292464 12 579876 0 0 0 0 568 827 1 0 99 0

please reply to my thread about mysql usage - i'd like to collect some data! thanks!
http://forums.mysql.com/read.php?11,57080

Options: ReplyQuote


Subject
Views
Written By
Posted
2522
November 29, 2005 02:40PM
3098
November 29, 2005 06:51PM
1624
November 29, 2005 08:24PM
1568
November 29, 2005 08:31PM
1671
November 29, 2005 09:36PM
Re: PLEASE HELP - performance is horrible!
1456
November 30, 2005 02:36AM
1575
November 29, 2005 09:42PM
1759
November 30, 2005 09:48PM
1705
December 05, 2005 07:13AM
1669
December 08, 2005 06:59AM
1627
December 08, 2005 07:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.