GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Performance issue with MySQL (failed or lost connection)

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance issue with MySQL (failed or lost connection)

    Hello,

    We recently had many failed or lost connection problem to the mysql server. About 2-3 per hour during heavy peaks. We have a dedicated mysql server with 1 gig of RAM and 2 processor. The server is not swapping and the load is not a problem, 95% of the time under 0.05. We only have MyISAM tables.

    I changed the default value of these 2 variables to:
    net_read_timeout=60
    connect_timeout=15

    And it solved the problem. But this is not a very good thing to do. I am sure there is a problem which I am not seeing here. If you could help me find it!


    my my.cnf is:
    =================================
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    old_passwords=1
    log-bin=/mysqlreplog/bin-log
    server_id=1
    log_slow_queries=/mysqlreplog/slow-queries.log
    long_query_time=4

    skip-locking
    key_buffer = 256M
    max_allowed_packet = 1M
    table_cache = 256
    net_read_timeout=60
    connect_timeout=15
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 16M
    #set-variable = max_connections=500
    max_connections=500
    ft_min_word_len=3
    expire_logs_days = 7

    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 4

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    =================================

    The current status of the server:
    +-----------------------------------+------------+
    | Variable_name | Value |
    +-----------------------------------+------------+
    | Aborted_clients | 1449 |
    | Aborted_connects | 16 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 4086824150 |
    | Bytes_sent | 205346791 |
    | Com_admin_commands | 2699 |
    | Com_alter_db | 0 |
    | Com_alter_table | 2 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_call_procedure | 0 |
    | Com_change_db | 506467 |
    | 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_create_user | 0 |
    | Com_dealloc_sql | 0 |
    | Com_delete | 1852 |
    | 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 | 22385 |
    | Com_insert_select | 13 |
    | Com_kill | 0 |
    | Com_kill | 0 |
    | Com_load | 0 |
    | Com_load_master_data | 0 |
    | Com_load_master_table | 0 |
    | Com_lock_tables | 0 |
    | Com_optimize | 7 |
    | 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 | 98458 |
    | 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 | 317449 |
    | Com_set_option | 106257 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 14 |
    | Com_show_charsets | 1086 |
    | Com_show_collations | 1086 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 0 |
    | Com_show_create_table | 383 |
    | Com_show_databases | 1086 |
    | Com_show_errors | 0 |
    | Com_show_fields | 1185 |
    | Com_show_grants | 563 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 491 |
    | 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 | 1 |
    | Com_show_storage_engines | 0 |
    | Com_show_tables | 4541 |
    | Com_show_triggers | 0 |
    | Com_show_variables | 3878 |
    | Com_show_warnings | 60 |
    | 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 | 37100 |
    | Com_update_multi | 1 |
    | 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 | 808474 |
    | Created_tmp_disk_tables | 7460 |
    | Created_tmp_files | 119 |
    | Created_tmp_tables | 34222 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 13597 |
    | Handler_discover | 0 |
    | Handler_prepare | 0 |
    | Handler_read_first | 189631 |
    | Handler_read_key | 26450608 |
    | Handler_read_next | 1130806215 |
    | Handler_read_prev | 2350760 |
    | Handler_read_rnd | 896052 |
    | Handler_read_rnd_next | 1100564513 |
    | Handler_rollback | 0 |
    | Handler_savepoint | 0 |
    | Handler_savepoint_rollback | 0 |
    | Handler_update | 560077 |
    | Handler_write | 4616671 |
    | Innodb_buffer_pool_pages_data | 20 |
    | Innodb_buffer_pool_pages_dirty | 0 |
    | Innodb_buffer_pool_pages_flushed | 0 |
    | Innodb_buffer_pool_pages_free | 492 |
    | Innodb_buffer_pool_pages_misc | 0 |
    | Innodb_buffer_pool_pages_total | 512 |
    | Innodb_buffer_pool_read_ahead_rnd | 1 |
    | Innodb_buffer_pool_read_ahead_seq | 0 |
    | Innodb_buffer_pool_read_requests | 135 |
    | Innodb_buffer_pool_reads | 13 |
    | Innodb_buffer_pool_wait_free | 0 |
    | Innodb_buffer_pool_write_requests | 0 |
    | Innodb_data_fsyncs | 3 |
    | Innodb_data_pending_fsyncs | 0 |
    | Innodb_data_pending_reads | 0 |
    | Innodb_data_pending_writes | 0 |
    | Innodb_data_read | 2510848 |
    | Innodb_data_reads | 26 |
    | Innodb_data_writes | 3 |
    | Innodb_data_written | 1536 |
    | Innodb_dblwr_pages_written | 0 |
    | Innodb_dblwr_writes | 0 |
    | Innodb_log_waits | 0 |
    | Innodb_log_write_requests | 0 |
    | Innodb_log_writes | 1 |
    | Innodb_os_log_fsyncs | 3 |
    | Innodb_os_log_pending_fsyncs | 0 |
    | Innodb_os_log_pending_writes | 0 |
    | Innodb_os_log_written | 512 |
    | Innodb_page_size | 16384 |
    | Innodb_pages_created | 0 |
    | Innodb_pages_read | 20 |
    | 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 | 226896 |
    | Key_blocks_used | 12952 |
    | Key_read_requests | 206683288 |
    | Key_reads | 191859 |
    | Key_write_requests | 214773 |
    | Key_writes | 104894 |
    | Last_query_cost | 0.000000 |
    | Max_used_connections | 200 |
    | Ndb_cluster_node_id | 0 |
    | Ndb_config_from_host | |
    | Ndb_config_from_port | 0 |
    | Ndb_number_of_data_nodes | 0 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 489 |
    | Open_streams | 0 |
    | Open_tables | 256 |
    | Opened_tables | 18937 |
    | Prepared_stmt_count | 0 |
    | Qcache_free_blocks | 1565 |
    | Qcache_free_memory | 4547720 |
    | Qcache_hits | 860770 |
    | Qcache_inserts | 299052 |
    | Qcache_lowmem_prunes | 75791 |
    | Qcache_not_cached | 40669 |
    | Qcache_queries_in_cache | 3828 |
    | Qcache_total_blocks | 9896 |
    | Questions | 2781187 |
    | Rpl_status | NULL |
    | Select_full_join | 7195 |
    | Select_full_range_join | 0 |
    | Select_range | 2366 |
    | Select_range_check | 0 |
    | Select_scan | 123291 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 0 |
    | Slow_queries | 5 |
    | Sort_merge_passes | 57 |
    | Sort_range | 23450 |
    | Sort_rows | 4872396 |
    | Sort_scan | 45272 |
    | 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 | 747608 |
    | Table_locks_waited | 1349 |
    | Tc_log_max_pages_used | 0 |
    | Tc_log_page_size | 0 |
    | Tc_log_page_waits | 0 |
    | Threads_cached | 7 |
    | Threads_connected | 22 |
    | Threads_created | 55958 |
    | Threads_running | 2 |
    | Uptime | 166654 |
    +-----------------------------------+------------+

    I think I should try to improve table_locking, but how?
    I should also increase the key_buffer and table_cache which seems a bit low.

    Any advice?

    Sincerely,

    py

  • #2
    Looking at these:

    | Select_full_join | 7195 || Select_scan | 123291 |

    I would say that you have a couple of queries that could do with putting proper indexes in place.
    The select_full_join indicates that you have 7195 queries that can't use an index to join two tables, this means that it has to scan the entire second table as many times as there are rows in the primary table.
    Turn on the slow_query_log and find the queries that causes this problem and create indexes to solve these.


    | Created_tmp_disk_tables | 7460 |

    Indicates that you could probably increase the sort_buffer_size a bit to avoid that mysql needs to write the temporary table to disk when it doesn't fit in the sort_buffer.

    Comment

    Working...
    X