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!
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
