** CAN A MYSQLD EXPERT PLEASE HELP ME OUT ON THIS ONE **
I am having a problem with my MySQLd on db2.daniweb.com going through periods of 1-2 minutes every couple of hours where it refuses connections from Apache on web.daniweb.com. The error being thrown back is:
mysql_pconnect(): Can't connect to MySQL server on '10.0.0.2'
As you can see, I'm using persistent connections since I understand that they are more efficient when the web and database servers are not on the same machine, to eliminate the constant handshake overhead.
The Aborted_clients variable seems to constantly and continuously increment throughout the day. I am wondering if this is a hacking attempt where someone is constantly trying to connect to the MySQL server with an incorrect username and password. The aborted_connects variable is synonymous with when I encounter downtime, and it represents the number of visitors to my php-driven website who are unable to connect to the MySQL server.
*************************************************************************************
*************************************************************************************
************* IMMEDIATELY AFTER *****************************************************
*************************************************************************************
*************************************************************************************
mysql> show status;
+--------------------------------+--------------+
| Variable_name | Value |
+--------------------------------+--------------+
| Aborted_clients | 97298 |
| Aborted_connects | 1012 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 6708267634 |
| Bytes_sent | 419093372467 |
| Com_admin_commands | 1197973 |
| Com_alter_db | 0 |
| Com_alter_table | 139 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1246714 |
| 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 | 11 |
| Com_dealloc_sql | 0 |
| Com_delete | 7135 |
| 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 | 812477 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 5 |
| Com_optimize | 2 |
| 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 | 30148 |
| 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 | 7973670 |
| Com_set_option | 654 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 4 |
| Com_show_charsets | 45 |
| Com_show_collations | 45 |
| Com_show_column_types | 0 |
| Com_show_create_db | 14 |
| Com_show_create_table | 471 |
| Com_show_databases | 43 |
| Com_show_errors | 0 |
| Com_show_fields | 2753 |
| Com_show_grants | 4 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 14 |
| Com_show_logs | 0 |
| Com_show_master_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 | 81 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 706 |
| Com_show_variables | 200 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 5 |
| Com_update | 1463128 |
| Com_update_multi | 0 |
| Connections | 50109 |
| Created_tmp_disk_tables | 53088 |
| Created_tmp_files | 27291 |
| Created_tmp_tables | 1042080 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 340986 |
| Handler_discover | 0 |
| Handler_read_first | 57528 |
| Handler_read_key | 1011268015 |
| Handler_read_next | 768309745 |
| Handler_read_prev | 3344817 |
| Handler_read_rnd | 118774822 |
| Handler_read_rnd_next | 13472399493 |
| Handler_rollback | 0 |
| Handler_update | 6592194 |
| Handler_write | 140132175 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 295691 |
| Key_blocks_used | 60691 |
| Key_read_requests | 2983753679 |
| Key_reads | 222810 |
| Key_write_requests | 9615985 |
| Key_writes | 791595 |
| Max_used_connections | 370 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 359 |
| Open_streams | 0 |
| Open_tables | 245 |
| Opened_tables | 730 |
| Qcache_free_blocks | 15581 |
| Qcache_free_memory | 70587944 |
| Qcache_hits | 16922980 |
| Qcache_inserts | 7946708 |
| Qcache_lowmem_prunes | 577728 |
| Qcache_not_cached | 26961 |
| Qcache_queries_in_cache | 26829 |
| Qcache_total_blocks | 69676 |
| Questions | 28461517 |
| Rpl_status | NULL |
| Select_full_join | 40186 |
| Select_full_range_join | 0 |
| Select_range | 1115910 |
| Select_range_check | 0 |
| Select_scan | 3983344 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slave_retried_transactions | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 13664 |
| Sort_range | 1525388 |
| Sort_rows | 949543538 |
| Sort_scan | 914705 |
| 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 | 20635946 |
| Table_locks_waited | 18928 |
| Threads_cached | 0 |
| Threads_connected | 370 |
| Threads_created | 808 |
| Threads_running | 1 |
| Uptime | 288921 |
+--------------------------------+--------------+
180 rows in set (0.01 sec)
mysql>
mysql> status;
--------------
mysql Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu (x86_64) using readline 4.3
Connection id: 49902
Current database: techtalkforums
Current user: cscgal@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 4.1.12
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 3 days 8 hours 12 min 47 sec
Threads: 186 Questions: 28457911 Slow queries: 0 Opens: 730 Flush tables: 1 Open tables: 245 Queries per second avg: 98.550
--------------
*************************************************************************************
*************************************************************************************
********** A FEW MINUTES LATER ******************************************************
*************************************************************************************
*************************************************************************************
mysql> show status;
+--------------------------------+--------------+
| Variable_name | Value |
+--------------------------------+--------------+
| Aborted_clients | 97546 |
| Aborted_connects | 1291 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 6717719864 |
| Bytes_sent | 419755259602 |
| Com_admin_commands | 1199607 |
| Com_alter_db | 0 |
| Com_alter_table | 139 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1248498 |
| 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 | 11 |
| Com_dealloc_sql | 0 |
| Com_delete | 7152 |
| 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 | 813569 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 5 |
| Com_optimize | 2 |
| 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 | 30280 |
| 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 | 7985055 |
| Com_set_option | 654 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 4 |
| Com_show_charsets | 45 |
| Com_show_collations | 45 |
| Com_show_column_types | 0 |
| Com_show_create_db | 14 |
| Com_show_create_table | 471 |
| Com_show_databases | 43 |
| Com_show_errors | 0 |
| Com_show_fields | 2753 |
| Com_show_grants | 4 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 14 |
| Com_show_logs | 0 |
| Com_show_master_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 | 82 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 706 |
| Com_show_variables | 200 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 5 |
| Com_update | 1464674 |
| Com_update_multi | 0 |
| Connections | 50180 |
| Created_tmp_disk_tables | 53135 |
| Created_tmp_files | 27331 |
| Created_tmp_tables | 1044002 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 340995 |
| Handler_discover | 0 |
| Handler_read_first | 57594 |
| Handler_read_key | 1012293691 |
| Handler_read_next | 769499630 |
| Handler_read_prev | 3356547 |
| Handler_read_rnd | 119435929 |
| Handler_read_rnd_next | 13488540869 |
| Handler_rollback | 0 |
| Handler_update | 6601935 |
| Handler_write | 140832549 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 295503 |
| Key_blocks_used | 60691 |
| Key_read_requests | 2986988388 |
| Key_reads | 223070 |
| Key_write_requests | 9622453 |
| Key_writes | 793284 |
| Max_used_connections | 371 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 359 |
| Open_streams | 0 |
| Open_tables | 253 |
| Opened_tables | 738 |
| Qcache_free_blocks | 16002 |
| Qcache_free_memory | 68684336 |
| Qcache_hits | 16945536 |
| Qcache_inserts | 7957975 |
| Qcache_lowmem_prunes | 577728 |
| Qcache_not_cached | 27079 |
| Qcache_queries_in_cache | 27133 |
| Qcache_total_blocks | 70794 |
| Questions | 28500030 |
| Rpl_status | NULL |
| Select_full_join | 40212 |
| Select_full_range_join | 0 |
| Select_range | 1117553 |
| Select_range_check | 0 |
| Select_scan | 3988735 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slave_retried_transactions | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 13684 |
| Sort_range | 1527449 |
| Sort_rows | 950393386 |
| Sort_scan | 915799 |
| 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 | 20662417 |
| Table_locks_waited | 18955 |
| Threads_cached | 94 |
| Threads_connected | 38 |
| Threads_created | 809 |
| Threads_running | 1 |
| Uptime | 289238 |
+--------------------------------+--------------+
180 rows in set (0.00 sec)
mysql>
*************************************************************************************
*************************************************************************************
****************** MORE INFO ********************************************************
*************************************************************************************
*************************************************************************************
Essentially, 99% of the day there is an average of 30 threads connected to the MySQL server. However, for some reason I cannot figure out, once or twice a day, this spikes to a ridiculously high number. When this happens, my Apache server is unable to connect to the database server. Additionally, trying to login via SSH to the database server at this time is futile, as well - apparently the server load average brings everything to a halt. Once I finally am able to login, all 3 server load averages displayed in 'top' are very low showing there was no spike in CPU time. However, I am not sure whether the MySQL connected threads reached its 1000 max during the period of downtime ... it was a couple hundred when I successfully established a connection with the server to check. From what I have been reading, it is a very bad idea to set max_connections to anything higher than 1000. Here are my currently set MySQL variables:
mysql> show variables;
+---------------------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------+
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 262144 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| 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/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 5 |
| 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 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 3 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | YES |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | YES |
| have_ndbcluster | NO |
| have_openssl | YES |
| 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 | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| 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_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_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 402653184 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | 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 | 1048576 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 1000 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| 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 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 5010 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 8388608 |
| 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 | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 8384512 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| storage_engine | MyISAM |
| sql_notes | ON |
| sql_warnings | ON |
| sync_binlog | 0 |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| sync_frm | ON |
| system_time_zone | CDT |
| table_cache | 512 |
| table_type | MyISAM |
| thread_cache_size | 100 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.12 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 13, 2005) |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+--------------------------------------------------------+
192 rows in set (0.00 sec)
mysql>
