MySQL crashes every night

[1EightT]

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

[AndyReed]

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.

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.

[1EightT]

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

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.

[nwilkens]

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

[Pashio]

Hello 1EightT,

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

mysqladmin -i2 processlist status

Thanks!