Mysql super slow - Help me analyze my MySQL installation
Posted by: Timothy Martens
Date: August 30, 2013 03:43PM
Date: August 30, 2013 03:43PM
Sometimes mysql seems to get bogged down and just plain old slow...to the point we can't work.
We have 2 tables constantly accessed whith over 20 million rows of data. These cause long running queries of 5-15 seconds. If this seems long to you let me know. This particular query is two tables being joined. I can get more info on this if requested.
Things slow to a crawl for no good reason that I can see.
In top the load average is .61 .50 .58
The CPU hums at arounds 20%
The memory reads: Mem: 33020856k total, 32783552k used, 237304k free, 455040k buffers
nothing jumps out at in in the processlist.
php/apache on another box looks fine...except extreme wait times for apache. (500+ seconds.)
show processlist looks fine for the most part a few long running queries but everything was par for the course.
Wondering if you guys can help me tweak some of the DB settings.
We have 2 tables constantly accessed whith over 20 million rows of data. These cause long running queries of 5-15 seconds. If this seems long to you let me know. This particular query is two tables being joined. I can get more info on this if requested.
Things slow to a crawl for no good reason that I can see.
In top the load average is .61 .50 .58
The CPU hums at arounds 20%
The memory reads: Mem: 33020856k total, 32783552k used, 237304k free, 455040k buffers
nothing jumps out at in in the processlist.
php/apache on another box looks fine...except extreme wait times for apache. (500+ seconds.)
show processlist looks fine for the most part a few long running queries but everything was par for the course.
Wondering if you guys can help me tweak some of the DB settings.
mysql> show global variables; +-----------------------------------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +-----------------------------------------+-------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr/ | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 10 | | datadir | /var/lib/mysql/ | | 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 | ON | | error_count | 0 | | event_scheduler | OFF | | expire_logs_days | 14 | | flush | OFF | | flush_time | 0 | | foreign_key_checks | ON | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | general_log | OFF | | general_log_file | /var/run/mysqld/mysqld.log | | group_concat_max_len | 1024 | | have_community_features | YES | | have_compress | YES | | have_crypt | YES | | have_csv | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_innodb | YES | | have_ndbcluster | NO | | have_openssl | DISABLED | | have_partitioning | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_symlink | YES | | hostname | AIRDB01 | | identity | 0 | | ignore_builtin_innodb | OFF | | init_connect | | | init_file | | | init_slave | | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 3221225472 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | 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_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_rollback_on_timeout | OFF | | innodb_stats_on_metadata | ON | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_sleep_delay | 10000 | | innodb_use_legacy_cardinality_algorithm | ON | | insert_id | 0 | | interactive_timeout | 28800 | | join_buffer_size | 524288 | | keep_files_on_create | OFF | | key_buffer_size | 2097152 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/ | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | last_insert_id | 0 | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_bin_trust_routine_creators | OFF | | log_error | | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | ON | | log_warnings | 1 | | long_query_time | 5.000000 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 16777216 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 209715200 | | max_connect_errors | 10 | | max_connections | 151 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 67108864 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 18446744073709551615 | | min_examined_row_limit | 0 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | BACKUP | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | myisam_use_mmap | OFF | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old | OFF | | old_alter_table | OFF | | old_passwords | OFF | | open_files_limit | 2209 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on | | pid_file | /var/run/mysqld/mysqld.pid | | plugin_dir | /usr/lib/mysql/plugin | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | pseudo_thread_id | 0 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 67108864 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | rand_seed1 | | | rand_seed2 | | | range_alloc_block_size | 4096 | | read_buffer_size | 524288 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_space_limit | 0 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 1 | | skip_external_locking | ON | | skip_name_resolve | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/mysql-slow.log | | socket | /var/run/mysqld/mysqld.sock | | sort_buffer_size | 2097144 | | sql_auto_is_null | ON | | sql_big_selects | ON | | sql_big_tables | OFF | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_log_update | ON | | sql_low_priority_updates | OFF | | sql_max_join_size | 18446744073709551615 | | sql_mode | | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | InnoDB | | sync_binlog | 0 | | sync_frm | ON | | system_time_zone | EDT | | table_definition_cache | 256 | | table_lock_wait_timeout | 50 | | table_open_cache | 1024 | | table_type | InnoDB | | thread_cache_size | 8 | | thread_handling | one-thread-per-connection | | thread_stack | 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | timestamp | 1377897406 | | tmp_table_size | 67108864 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | unique_checks | ON | | updatable_views_with_limit | YES | | version | 5.1.49-3-log | | version_comment | (Debian) | | version_compile_machine | x86_64 | | version_compile_os | debian-linux-gnu | | wait_timeout | 28800 | | warning_count | 0 | +-----------------------------------------+-------------------------------------------------------------------------------------------+ 274 rows in set (0.00 sec)
mysql> show global status; +-----------------------------------+------------------+ | Variable_name | Value | +-----------------------------------+------------------+ | Aborted_clients | 728 | | Aborted_connects | 29793 | | Binlog_cache_disk_use | 69987 | | Binlog_cache_use | 58947027 | | Bytes_received | 1936245439294 | | Bytes_sent | 4908486142476 | | Com_admin_commands | 113 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 41 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 1391904 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 29244436 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 1388399 | | Com_create_db | 0 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 22 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 0 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete | 104 | | Com_delete_multi | 93933 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_event | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_procedure | 0 | | Com_drop_server | 0 | | Com_drop_table | 2 | | Com_drop_trigger | 0 | | Com_drop_user | 0 | | Com_drop_view | 0 | | Com_empty_query | 0 | | Com_execute_sql | 0 | | Com_flush | 104 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 49875775 | | Com_insert_select | 1 | | Com_install_plugin | 0 | | Com_kill | 39 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 6 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_release_savepoint | 0 | | Com_rename_table | 1 | | Com_rename_user | 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 | 3505 | | Com_rollback_to_savepoint | 0 | | Com_savepoint | 0 | | Com_select | 1751973799 | | Com_set_option | 28301146 | | Com_show_authors | 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_contributors | 0 | | Com_show_create_db | 0 | | Com_show_create_event | 0 | | Com_show_create_func | 0 | | Com_show_create_proc | 0 | | Com_show_create_table | 382 | | Com_show_create_trigger | 1 | | Com_show_databases | 100 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 3 | | Com_show_events | 0 | | Com_show_errors | 0 | | Com_show_fields | 1393684 | | Com_show_function_status | 0 | | Com_show_grants | 0 | | Com_show_keys | 14 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_plugins | 0 | | Com_show_privileges | 0 | | Com_show_procedure_status | 0 | | Com_show_processlist | 4053 | | Com_show_profile | 0 | | Com_show_profiles | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 3928 | | Com_show_storage_engines | 0 | | Com_show_table_status | 399 | | Com_show_tables | 30841 | | Com_show_triggers | 382 | | Com_show_variables | 90 | | 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_reprepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_uninstall_plugin | 0 | | Com_unlock_tables | 6 | | Com_update | 69818074 | | Com_update_multi | 7333171 | | 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 | 29274640 | | Created_tmp_disk_tables | 50635504 | | Created_tmp_files | 4297671 | | Created_tmp_tables | 208065975 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 2003106566 | | Handler_delete | 999786 | | Handler_discover | 0 | | Handler_prepare | 245301008 | | Handler_read_first | 1152625905 | | Handler_read_key | 66481095352 | | Handler_read_next | 233388318536 | | Handler_read_prev | 223737789041 | | Handler_read_rnd | 789866066 | | Handler_read_rnd_next | 5824352344435 | | Handler_rollback | 17834 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 77314679 | | Handler_write | 108567358520 | | Innodb_buffer_pool_pages_data | 192052 | | Innodb_buffer_pool_pages_dirty | 1050 | | Innodb_buffer_pool_pages_flushed | 238086928 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 4556 | | Innodb_buffer_pool_pages_total | 196608 | | Innodb_buffer_pool_read_ahead_rnd | 827081402 | | Innodb_buffer_pool_read_ahead_seq | 51120764 | | Innodb_buffer_pool_read_requests | 2589498330994 | | Innodb_buffer_pool_reads | 50414245778 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 1280450349 | | Innodb_data_fsyncs | 129644192 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 1428388511731712 | | Innodb_data_reads | 60117712496 | | Innodb_data_writes | 354694233 | | Innodb_data_written | 7979408238080 | | Innodb_dblwr_pages_written | 238086928 | | Innodb_dblwr_writes | 2886033 | | Innodb_log_waits | 526 | | Innodb_log_write_requests | 298650239 | | Innodb_log_writes | 122917409 | | Innodb_os_log_fsyncs | 123916257 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 177279212032 | | Innodb_page_size | 16384 | | Innodb_pages_created | 3761864 | | Innodb_pages_read | 87182366357 | | Innodb_pages_written | 238086928 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 193192537 | | Innodb_row_lock_time_avg | 15759 | | Innodb_row_lock_time_max | 51977 | | Innodb_row_lock_waits | 12259 | | Innodb_rows_deleted | 999786 | | Innodb_rows_inserted | 54307171 | | Innodb_rows_read | 6144467128576 | | Innodb_rows_updated | 76956722 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 1674 | | Key_blocks_used | 46 | | Key_read_requests | 518851704 | | Key_reads | 30 | | Key_write_requests | 269177212 | | Key_writes | 0 | | Last_query_cost | 0.000000 | | Max_used_connections | 152 | | Not_flushed_delayed_rows | 0 | | Open_files | 30 | | Open_streams | 0 | | Open_table_definitions | 396 | | Open_tables | 1024 | | Opened_files | 218509101 | | Opened_table_definitions | 2409 | | Opened_tables | 9464 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 1688 | | Qcache_free_memory | 5690480 | | Qcache_hits | 1131383165 | | Qcache_inserts | 1467374969 | | Qcache_lowmem_prunes | 828236185 | | Qcache_not_cached | 284490723 | | Qcache_queries_in_cache | 25911 | | Qcache_total_blocks | 53757 | | Queries | 3101485759 | | Questions | 3101485759 | | Rpl_status | NULL | | Select_full_join | 167419523 | | Select_full_range_join | 2660 | | Select_range | 18248067 | | Select_range_check | 199742 | | Select_scan | 350527793 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 567591 | | Sort_merge_passes | 3061715 | | Sort_range | 15722255 | | Sort_rows | 1118691936 | | Sort_scan | 207029788 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 6056896210 | | Table_locks_waited | 72 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 11 | | Threads_cached | 3 | | Threads_connected | 10 | | Threads_created | 163005 | | Threads_running | 5 | | Uptime | 8961459 | | Uptime_since_flush_status | 8961459 | +-----------------------------------+------------------+ 291 rows in set (0.00 sec)
Subject
Views
Written By
Posted
Mysql super slow - Help me analyze my MySQL installation
2015
August 30, 2013 03:43PM
1479
September 01, 2013 10:46AM
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.