List:General Discussion« Previous MessageNext Message »
From:Dave Goodrich Date:October 6 2004 10:06pm
Subject:Re: Connection Errors
View as plain text  
Dave Goodrich wrote:

> [Previously posted to MySQL forum]
> 
> Howdy the group,
> 
> I have a cluster of email servers all using mysql for authentication. 
> Using vpopmail and spamassassin, all auth requests, pop, delivery 
> instructions, etc, go to mysql. Currently we process between 50k and 65k 
> messages a day inbound. Each delivery requires a query, and each pop to 
> download messages requires a query. Approximately 50% of those messages 
> will also require a query for spamassassin preferences.
> 
> I am having complaints from users that they are reprompted for their 
> password several times a day. So, I am trying to see if I can identify 
> the failure from the host end.
> 
> 1) I have restarted safe_mysqld with the warning option "--warnings". I 
> can see the server.log shows aborted client errors, but not aborted 
> connection errors. How do I get the aborted connection errors to display 
> in the logs? ( see approx 30 client errors a day, but in excess of 2500 
> connection errors)

Is it not possible to log the reason for an aborted connection?

DAve

> 
> 2) I have read many places that there is potential for communication 
> errors due to ether settings. I currently have the host running 1000fdx 
> to a 1000fdx port on the switch, the clients are running 100fdx to a 
> 100fdx port on the same switch. Should I be running both host and 
> clients at the same speed?
> 
> 3) In my mail logs I have failures logged which state that the user 
> doesn't exist. From the vpopmail maillist, this means the connection 
> failed (provided the user does in fact exist).
> 
> Any advice, help, would be appreciated. Thanks,
> 
> DAve
> 
> 
> 
> 
> Some background,
> 
> ### Host server: Sun Enterprise 450, 2 gb ram. 1000fdx connection to a 
> 1000fdx port on a netgear switch.
> 
> # mysqladmin -uroot -p version
> mysqladmin Ver 8.23 Distrib 3.23.53, for sun-solaris2.8 on sparc
> Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
> This software comes with ABSOLUTELY NO WARRANTY. This is free software,
> and you are welcome to modify and redistribute it under the GPL license
> 
> Server version 3.23.53-log
> Protocol version 10
> Connection Localhost via UNIX socket
> UNIX socket /tmp/mysql.sock
> Uptime: 2 days 11 hours 21 min 9 sec
> 
> Threads: 1 Questions: 1591388 Slow queries: 24 Opens: 0 Flush tables: 1 
> Open tables: 7 Queries per second avg: 7.448
> 
> ### /etc/my.cnf
> # The MySQL server
> [mysqld]
> port = 3306
> socket = /tmp/mysql.sock
> skip-locking
> set-variable = key_buffer=256M
> set-variable = max_allowed_packet=16M
> set-variable = table_cache=256
> set-variable = sort_buffer=1M
> set-variable = record_buffer=1M
> set-variable = myisam_sort_buffer_size=64M
> set-variable = thread_cache=4
> set-variable = connect_timeout=15
> set-variable = back_log=50
> set-variable = max_connections=500
> 
> # Try number of CPU's*2 for thread_concurrency
> set-variable = thread_concurrency=4
> server-id = 1
> 
> # lets have a bin log
> #log-bin
> 
> # Lets have a query log as well
> log
> 
> # Lets save slow queries
> set-variable = long_query_time=20
> log-long-format
> log-slow-queries = /usr/local/mysql/var/slow_queries.log
> 
> 
> ## Status after 30 minutes running
> #mysqladmin -uroot -p extended-status
> +--------------------------+---------+
> | Variable_name | Value |
> +--------------------------+---------+
> | Aborted_clients | 0 |
> | Aborted_connects | 51 | <------------- can I log these?
> | Bytes_received | 1892430 |
> | Bytes_sent | 2955038 |
> | Com_admin_commands | 0 |
> | Com_alter_table | 0 |
> | Com_analyze | 0 |
> | Com_backup_table | 0 |
> | Com_begin | 0 |
> | Com_change_db | 6356 |
> | Com_change_master | 0 |
> | Com_check | 0 |
> | Com_commit | 0 |
> | Com_create_db | 0 |
> | Com_create_function | 0 |
> | Com_create_index | 0 |
> | Com_create_table | 0 |
> | Com_delete | 0 |
> | Com_drop_db | 0 |
> | Com_drop_function | 0 |
> | Com_drop_index | 0 |
> | Com_drop_table | 0 |
> | Com_flush | 0 |
> | Com_grant | 0 |
> | Com_insert | 3 |
> | Com_insert_select | 0 |
> | Com_kill | 0 |
> | Com_load | 0 |
> | Com_load_master_table | 0 |
> | Com_lock_tables | 0 |
> | Com_optimize | 0 |
> | Com_purge | 0 |
> | Com_rename_table | 0 |
> | Com_repair | 0 |
> | Com_replace | 4025 |
> | Com_replace_select | 0 |
> | Com_reset | 0 |
> | Com_restore_table | 0 |
> | Com_revoke | 0 |
> | Com_rollback | 0 |
> | Com_select | 6932 |
> | Com_set_option | 0 |
> | Com_show_binlogs | 0 |
> | Com_show_create | 0 |
> | Com_show_databases | 0 |
> | Com_show_fields | 0 |
> | Com_show_grants | 0 |
> | Com_show_keys | 0 |
> | Com_show_logs | 0 |
> | Com_show_master_status | 0 |
> | Com_show_open_tables | 0 |
> | Com_show_processlist | 0 |
> | Com_show_slave_status | 0 |
> | Com_show_status | 7 |
> | Com_show_innodb_status | 0 |
> | Com_show_tables | 0 |
> | Com_show_variables | 0 |
> | Com_slave_start | 0 |
> | Com_slave_stop | 0 |
> | Com_truncate | 0 |
> | Com_unlock_tables | 0 |
> | Com_update | 6 |
> | Connections | 471898 |
> | Created_tmp_disk_tables | 0 |
> | Created_tmp_tables | 0 |
> | Created_tmp_files | 0 |
> | Delayed_insert_threads | 0 |
> | Delayed_writes | 0 |
> | Delayed_errors | 0 |
> | Flush_commands | 1 |
> | Handler_delete | 0 |
> | Handler_read_first | 0 |
> | Handler_read_key | 8014 |
> | Handler_read_next | 1651 |
> | Handler_read_prev | 0 |
> | Handler_read_rnd | 4102 |
> | Handler_read_rnd_next | 13010 |
> | Handler_update | 4028 |
> | Handler_write | 4028 |
> | Key_blocks_used | 272 |
> | Key_read_requests | 36245 |
> | Key_reads | 0 |
> | Key_write_requests | 12 |
> | Key_writes | 12 |
> | Max_used_connections | 14 |
> | Not_flushed_key_blocks | 0 |
> | Not_flushed_delayed_rows | 0 |
> | Open_tables | 7 |
> | Open_files | 13 |
> | Open_streams | 0 |
> | Opened_tables | 0 |
> | Questions | 1581628 |
> | Select_full_join | 0 |
> | Select_full_range_join | 0 |
> | Select_range | 547 |
> | Select_range_check | 0 |
> | Select_scan | 18 |
> | Slave_running | OFF |
> | Slave_open_temp_tables | 0 |
> | Slow_launch_threads | 0 |
> | Slow_queries | 18 |
> | Sort_merge_passes | 0 |
> | Sort_range | 0 |
> | Sort_rows | 80 |
> | Sort_scan | 16 |
> | Table_locks_immediate | 10966 |
> | Table_locks_waited | 0 |
> | Threads_cached | 2 |
> | Threads_created | 3920 |
> | Threads_connected | 2 |
> | Threads_running | 1 |
> | Uptime | 212961 |
> +--------------------------+---------+

-- 
Systems Administrator
http://www.tls.net
Get rid of Unwanted Emails...get TLS Spam Blocker!

Thread
Connection ErrorsDave Goodrich6 Oct
  • Re: Connection ErrorsDave Goodrich7 Oct