Connection errors?
Posted by: ldg
Date: October 06, 2004 10:12AM
Date: October 06, 2004 10:12AM
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)
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?
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 |
| 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 |
+--------------------------+---------+
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)
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?
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 |
| 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 |
+--------------------------+---------+
Subject
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.