The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL crashes every night

Discussion in 'General Discussion' started by 1EightT, Nov 15, 2006.

  1. 1EightT

    1EightT Member

    Joined:
    Jul 2, 2003
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    Hey guys, I have some questions I hope can be answered. We've got a dual Xeon with 4gb of ram that is giving us a hard time in the past few days. Every night around 1:30 am the server kicks back errors to users of too many mysql connections, and it refuses any further connections. This problem lasts untill about 4:30 am or so then stops. I've tweaked the my.cnf to allow for 1000 connections, and have upped my table_cache to over 5000 to make sure there are PLENTY of connections ready.

    Does cpanel backup lock a database during backup? Problem is we have a 19GB database that has hundreds of users accessing it. The complaints are stacking up and i'm running out of ideas as to what could be wrong.

    Any suggestions? I can provide any further data you guys might need.

    Thanks

    Brian
     
  2. AndyReed

    AndyReed Well-Known Member
    PartnerNOC

    Joined:
    May 29, 2004
    Messages:
    2,222
    Likes Received:
    3
    Trophy Points:
    38
    Location:
    Minneapolis, MN
    It is really hard to say without looking into your server. But make sure your query cache is working. Did you check the log files to see who is exahusting all MySQL concurrent connections? It also might be a cronjob running at that time by one of your clients killing MySQL.
     
  3. 1EightT

    1EightT Member

    Joined:
    Jul 2, 2003
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    Query cache appears to be fine. I at least don't see anything out of the ordinary.

    Here are some of my stats for yesterday before the crash:

    MyTOP

    MySQL on localhost (4.1.21-standard) up 0+05:50:55 [13:19:15]
    Queries: 1.3M qps: 63 Slow: 12.0 Se/In/Up/De(%): 55/22/04/01
    qps now: 49 Slow qps: 0.0 Threads: 4 ( 1/ 18) 53/02/07/01
    Cache Hits: 237.2k Hits/s: 11.5 Hits now: 13.0 Ratio: 33.4% Ratio now: 49.6%
    Key Efficiency: 99.9% Bps in/out: 18.9k/139.2k Now in/out: 18.4k/405.9k

    Id User Host/IP DB Time Cmd Query or State
    -- ---- ------- -- ---- --- ----------
    19321 root localhost simplsec_s 0 Query show full processlist
    64718 highalti_ localhost highalti_f 1 Sleep
    64647 simplsec localhost simplsec_s 13 Sleep
    64560 simplsec localhost simplsec_s 38 Sleep


    Extended stats

    Tue Nov 14 13:19:58 MST 2006


    top - 13:19:58 up 14 days, 9:48, 1 user, load average: 4.26, 5.41, 4.73
    Tasks: 141 total, 3 running, 138 sleeping, 0 stopped, 0 zombie
    Cpu(s): 25.4% us, 2.5% sy, 0.3% ni, 69.5% id, 2.3% wa, 0.0% hi, 0.0% si
    Mem: 4090528k total, 3382732k used, 707796k free, 63676k buffers
    Swap: 2048276k total, 144k used, 2048132k free, 2622124k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    6685 nobody 25 0 47188 38m 5060 R 99.2 1.0 0:18.20 httpd
    6724 nobody 18 0 49256 40m 5124 R 75.9 1.0 0:00.73 httpd
    6778 nobody 15 0 2516 884 672 R 3.9 0.0 0:00.03 top


    Http processes currently running = 38
    Mysql processes currently running = 4

    Netstat information summary
    1 LAST_ACK
    1 7 FIN_WAIT1
    1 9 FIN_WAIT1
    1 9 TIME_WAIT
    1 99 FIN_WAIT2
    1 :3516 ESTABLISHED
    1 :3631 ESTABLISHED
    2 2 TIME_WAIT
    2 3 TIME_WAIT
    2 5 TIME_WAIT
    2 6 TIME_WAIT
    2 7 FIN_WAIT2
    2 7 TIME_WAIT
    2 8 FIN_WAIT2
    3 FIN_WAIT1
    3 8 TIME_WAIT
    4 1 TIME_WAIT
    4 4 TIME_WAIT
    5 0 TIME_WAIT
    8 ESTABLISHED
    8 FIN_WAIT2
    48 LISTEN
    262 TIME_WAIT

    +----------------------------+------------+
    | Variable_name | Value |
    +----------------------------+------------+
    | Aborted_clients | 24 |
    | Aborted_connects | 40 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 407665036 |
    | Bytes_sent | 3025375424 |
    | 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 | 168625 |
    | 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 | 2 |
    | Com_dealloc_sql | 0 |
    | Com_delete | 13651 |
    | 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 | 350 |
    | Com_grant | 0 |
    | Com_ha_close | 0 |
    | Com_ha_open | 0 |
    | Com_ha_read | 0 |
    | Com_help | 0 |
    | Com_insert | 288510 |
    | 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 | 1 |
    | 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 | 5563 |
    | 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 | 485618 |
    | Com_set_option | 6993 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 3 |
    | Com_show_charsets | 45 |
    | Com_show_collations | 45 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 0 |
    | Com_show_create_table | 5 |
    | Com_show_databases | 180 |
    | Com_show_errors | 0 |
    | Com_show_fields | 14 |
    | Com_show_grants | 28 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 4 |
    | 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 | 3928 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 3881 |
    | Com_show_storage_engines | 0 |
    | Com_show_tables | 453 |
    | Com_show_variables | 133 |
    | 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 | 0 |
    | Com_update | 47712 |
    | Com_update_multi | 0 |
    | Connections | 64892 |
    | Created_tmp_disk_tables | 542 |
    | Created_tmp_files | 2 |
    | Created_tmp_tables | 4748 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 3633 |
    | Handler_discover | 0 |
    | Handler_read_first | 43540 |
    | Handler_read_key | 46827932 |
    | Handler_read_next | 65994662 |
    | Handler_read_prev | 25811719 |
    | Handler_read_rnd | 3954986 |
    | Handler_read_rnd_next | 40123227 |
    | Handler_rollback | 0 |
    | Handler_update | 4193113 |
    | Handler_write | 4223512 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 342632 |
    | Key_blocks_used | 121288 |
    | Key_read_requests | 214016490 |
    | Key_reads | 121742 |
    | Key_write_requests | 1007842 |
    | Key_writes | 946950 |
    | Max_used_connections | 22 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 933 |
    | Open_streams | 0 |
    | Open_tables | 517 | 13% of table_cache in use
    | Opened_tables | 524 |
    | Qcache_free_blocks | 3006 |
    | Qcache_free_memory | 36917488 |
    | Qcache_hits | 243476 |
    | Qcache_inserts | 443109 |
    | Qcache_lowmem_prunes | 0 |
    | Qcache_not_cached | 42491 |
    | Qcache_queries_in_cache | 5873 |
    | Qcache_total_blocks | 15633 |
    | Questions | 1334242 |
    | Rpl_status | NULL |
    | Select_full_join | 34 |
    | Select_full_range_join | 1 |
    | Select_range | 32313 |
    | Select_range_check | 0 |
    | Select_scan | 20962 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 0 |
    | Slow_queries | 12 | (execution time > 10 secs)
    | Sort_merge_passes | 1 |
    | Sort_range | 17937 |
    | Sort_rows | 1395272 |
    | Sort_scan | 5358 |
    | Table_locks_immediate | 948697 |
    | Table_locks_waited | 491 |
    | Threads_cached | 19 |
    | Threads_connected | 3 |
    | Threads_created | 22 |
    | Threads_running | 1 |
    | Uptime | 21099 | 5 hrs 51 mins 39 secs
    +----------------------------+------------+


    Key Reads/Key Read Requests = 0.000569 (Cache hit = 99.999431%)
    Key Writes/Key Write Requests = 0.939582
    Connections/second = 3.076 (/hour = 11072.146)
    KB received/second = 18.869 (/hour = 67927.200)
    KB sent/second = 99.396 (/hour = 357824.712)
    Temporary Tables Created/second = 0.225 (/hour = 810.124)
    Opened Tables/second = 0.025 (/hour = 89.407)
    Slow Queries/second = 0.001 (/hour = 2.047)
    % of slow queries = 0.001%
    Queries/second = 63.237 (/hour = 227653.974)
    MySQL Query Cache hits = 243476/729076(33%)

    my.cnf

    [mysqld]
    skip-locking
    skip-innodb
    query_cache_size=64M
    query_cache_type=1
    max_connections=4000
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=128
    key_buffer=512M
    join_buffer=16M
    max_allowed_packet=16M
    table_cache=4096
    record_buffer=1M
    sort_buffer_size=4M
    read_buffer_size=2M
    max_connect_errors=10

    thread_concurrency=4
    myisam_sort_buffer_size=64M
    server-id=1

    [mysql.server]
    user=mysql
    basedir=/var/lib
    old-passwords = 1

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    open_files_limit=8192

    [mysqldump]
    quick
    max_allowed_packet=16M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [mysqlhotcopy]
    interactive-timeout

     
  4. nwilkens

    nwilkens Well-Known Member

    Joined:
    May 4, 2006
    Messages:
    59
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Monroe MI
    cPanel Access Level:
    DataCenter Provider
    MySQL crash

    What is our output of:

    mysql> show variables like '%connections';
    +----------------------+-------+
    | Variable_name | Value |
    +----------------------+-------+
    | max_connections | 100 |
    | max_user_connections | 0 |
    +----------------------+-------+
    2 rows in set (0.63 sec)

    ?
    Also,
    mysql> select user, max_connections, max_updates,max_questions from mysql.user;
    +-----------------+-------------+---------------+
    | max_connections | max_updates | max_questions |
    +-----------------+-------------+---------------+
    | 0 | 0 | 0 |
    | 0 | 0 | 0 |
    | 0 | 0 | 0 |
    | 0 | 0 |

    This may show something useful.

    You could also setup an alert for yourself to warn you, and then find out what is really going on. Use the script below.. change the commands as required..


    #!/bin/bash

    ALERT_DEST=email.addr@email.add
    NUM_PROCESSES=`/usr/local/mysql/bin/mysql -e "show processlist;"|wc -l`

    MAX_BEFORE_ALERT=25

    if [ ${NUM_PROCESSES} -gt ${MAX_BEFORE_ALERT} ]
    then
    echo |/usr/bin/mail -s "WARNING: MYSQL CONNECTIONS=${NUM_PROCESSES}" ${ALERT_DEST}
    fi
     
    #4 nwilkens, Nov 15, 2006
    Last edited: Nov 16, 2006
  5. Pashio

    Pashio Active Member

    Joined:
    Nov 26, 2005
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Hello 1EightT,

    Please run the command bellow and find the database which is causing the problem. :)

    mysqladmin -i2 processlist status

    Thanks!
     
Loading...

Share This Page