| Bug #6070 | "unauthenticated user" "reading from net" process hangs | ||
|---|---|---|---|
| Submitted: | 13 Oct 2004 18:17 | Modified: | 2 Apr 2012 18:13 |
| Reporter: | Jim Taylor | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.10, 5.0.67, 5.0.84, 5.1 | OS: | FreeBSD (FreeBSD 4.9) |
| Assigned to: | CPU Architecture: | Any | |
[13 Oct 2004 18:17]
Jim Taylor
[13 Oct 2004 18:33]
MySQL Verification Team
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL.
[13 Oct 2004 19:11]
Jim Taylor
What "more information" do you want? "Re-read the instructions" is not helpful--I did read them, and I believe I provided all the necessary information!
[13 Oct 2004 21:38]
Matthew Lord
Hi, Are you using the --skip-name-resolve option? The gethostbyname() function on FreeBSD 4.x and earlier is not thread safe. It can cause the server to hang or eat up 99% of the cpu. If you have not seen this page it is helpful as well as the blogs linked to from the page: http://dev.mysql.com/doc/mysql/en/FreeBSD.html This as well as the other main "gotchas", if you will, with FreeBSD have been addressed in FreeBSD 5. Best Regards
[13 Oct 2004 22:27]
Jim Taylor
Yes, we are using skip-name-resolve; the "Host" column on Show Processlist shows just IP addresses (or "localhost" for local processes). This problem is happening increasingly often (4 times so far today)!
[14 Oct 2004 22:25]
Matthew Lord
Hi, We need to be able to create a repeatable test case to proceed through this avenue. There are a myriad of things that could be causing these symptoms such as duplexing problems. This could take time and effor to track down. If you do not have a support contract we now offer per incident support and you can also use the free community based avenues: lists.mysql.com mysql.com/IRC Best of luck
[15 Oct 2004 8:44]
Sergei Golubchik
Could it be that these ~200 "unauthenticated user" connections came from the same application (I mean, same process, same host) ? How many new connections do you have in, say, a minute ? (you said, 200 connections are active, but I'm asking about the incoming rate). Did I understood correctly that you noticed the problem because MySQL became slow ? (normally, waiting connections shouldn't take CPU time) What binary do you use - our or from the ports ? Was it libc_r or linuxthreads build ?
[15 Oct 2004 21:26]
Jim Taylor
According to our ISP (who installed it) our MySQL install is mysql-standard-4.0.18-unknown-freebsd4.7-i386. We get about 2 connections per minute from a variety of hosts (the "unauthenticated user" is not always from the same host). I don't think "unauthenticated user" "reading from net" by itself slows performance. The problem is that MySQL waits for the "unauthenticated user" to complete the connection before timing out any other processes. If the connection never completes, wait_timeout is ignored and the number of processes grows and grows (and I assume would hit max_connections if we didn't manually kill the "unauthenticated user" process first). I don't expect you guys to debug whatever weird network problem might prevent a connection from completing. But whatever MySQL code terminates timed-out processes should work even if there's an incomplete connection. And if the connection stays incomplete for wait_timeout seconds, it also should be killed. The fact that that doesn't happen is the bug I'm reporting. I'm hoping someone familiar with the process-timeout part of MySQL should be able to fix it relatively easily....
[19 Oct 2004 19:34]
Sergei Golubchik
Sorry, I wasn't able to repeat it (FreeBSD 4.7-RC2, so I'm sure close to 4.9 enough) I modified libmysqlclient to sleep(3600) after it reads an auth packet from the server but before it sends a reply back. 'SHOW PROCESSLIST' indeed shows "unauthenticated user" processes that are "reading from net". But they all timeouts very fast (I was forking them in a loop) - so I failed to repeat the problem.
[19 Oct 2004 20:54]
Jim Taylor
The problem is still happening on our server. Just now, we had 700 processes, I killed the one "unauthenticated user" "reading from net" process and immediately 500 others timed out and went away (leaving us with the expected 200 open processes). Is there anything else we can do to help isolate the problem?
[25 Oct 2004 13:18]
Sergei Golubchik
The only thing I could think of is to provide us with some way to repeat the problem. E.g. a C program, perl or shell script that, being run against mysqld server, makes your situation to appear.
[2 Nov 2004 15:58]
Jim Taylor
We upgraded to version 4.0.21 about a week ago and the problem has not appeared since. Since it was happening on a daily basis before, I'm cautiously optimistic that whatever was causing it got fixed in the new version.
[5 Jan 2005 0:25]
Tadas B.
I have 4.1.7 Version of MySQL, OS is FreeBSD 5.x And sorry but new versions do not help to fix this problem. There is some serious stuff. The DB server worked fine about one mouth, and suddenly it slows down. In process list I see many lines begining width: unauthenticated user... Mysql gets about 2000~ requests in a minute, and `unauthenticated user` lines there are about 200~ periodically.. Interesting is that this server exepts only some local IP adress. Firewall is used and so on.. At night when load is low, we still have these `unauthenticated users` but only few.
[12 May 2005 22:40]
Brandon Schenz
I am using 4.1.11 (upgraded from 4.1.10 where the problem started) on Windows and am having a similar problem. All my applications use a special user I created for access to the appropriate database. Suddenly today my users complained that all the applications had severly slowed down. When I opened MySQL Administrator and looked at the users they are all listed as unauthenticated and status was Login. In my case the data reads and writes are performed, but at EXTREMLY slow rates. I do not know how to recreate the problem either.
[12 May 2005 23:00]
Jim Taylor
We were experiencing a lot of performance problems -- not only the "unauthenticated user" issue, but MySQL CPU utilization would suddenly start growing rapidly and within a few seconds, the database would stop responding. We switched to the "Linux Threads" implementation of MySQL (though we're still running on FreeBSD). We have had no performance problems since (even though our total traffic has continued to increase). I suggest giving that a try....
[18 May 2005 2:07]
Lachlan Mulcahy
I'd like to know if any of the contributers to this bug are still experiencing problems? This is specifically related to the "unauthenticated user"s in SHOW PROCESSLIST output. Everyone should ensure they are using --skip-name-resolve at startup of the mysql server to rule out slow DNS issues and are using the latest version. Also, please ensure you are using the binaries provided by us.
[18 May 2005 12:36]
Jim Taylor
We have always used skip-name-resolve, so DNS is not the cause of this problem, and we use MySQL binaries. We have not experienced the problem since switching to Linux Threads.
[18 Jun 2005 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[20 Oct 2006 20:58]
Colin Anderson
I don't think I'm able to provide any more information than Jim did, but we recently encountered this problem after our hard drive space was 100% utilized (after an upgrade ... the old databases were never removed). One of our ColdFusion servers kept spawning all of these Unauthenticated User processes, with the same behavior (kill one and nearly all of the rest were stopped instantly). After we rebooted the ColdFusion server (not the MySQL server), the problem was solved and all of these processes stopped. So it wasn't necessarily a problem with MySQL itself, but it looked like some problem with the ODBC connectors on the ColdFusion server.
[16 Nov 2006 10:24]
Michael Franklin
We are currently having issues with the "unauthenticated user - Connect - Reading from net". We have implemented the "skip-name-resolve" in the my.cnf file but we are still recieving these "unauthenticated user" coming up every couple of seconds. There are only about 2 present at any time however since I have noticed this user the systems speed slows down considerably and then once killed speeds up again. We have about an average of 10 jobs per second on this server which is a Dual, Dual Core 3.2GHz machine with 4GB of ram. Does anyone have any other ideas?
[21 Nov 2006 23:36]
James Day
See bug #9678 fixed in 4.0.28, 4.1.22, 5.0.25 and 5.1.12 and please report whether the problem still exists with those versions of MySQL or later.
[10 Aug 2007 16:02]
Hui Zhang
We still have the same problem on 5.0.27. We are using the Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads. Did it happen to anyone with 5.0.25 above which the fix should be in? Should I move to a later version? thanks.
[17 Aug 2007 22:47]
James Day
Please do try a later version, if only because of the large number of other changes made since 5.0.27.
[13 Nov 2008 21:13]
Heintz Hugues
Hy everybody ! i have the same problem.. im on gentoo on raid 1 running dev-db/mysql-5.0.60-r1 i have averagely 50 persistant local connexions (normal) but when i get a few connexions from my website averagely 5/sec using skip-name-resolve changed anything ... My showprocesslist gives : Supprimer 184 unauthenticated user 88.191.50.**:46122 aucune Connect Reading from net --- Supprimer 186 unauthenticated user 88.191.50.**:46124 aucune Connect Reading from net --- Supprimer 189 unauthenticated user 88.191.50.**:46130 aucune Connect Reading from net ---
[20 Nov 2008 21:47]
Heintz Hugues
i have reinstalled mysql and now its ok
[20 Nov 2008 22:05]
Heintz Hugues
after a few mins it come back like before, PROBLEM NOT SOLVED !!!! HEEELLLLLPPPP PLZ !!!!
[9 Mar 2009 19:00]
Pavel Baranov
Same problem with 5.0.67 AND 5.1
[10 Mar 2009 15:19]
Sveta Smirnova
Thank you for the feedback. This can be duplicate of already verified feature request: bug #27465. To check this is not new problem in time when you see such behavior next time please compare number of threads connected and value of max_connections which should be equal or higher than connected threads + 1.
[10 Apr 2009 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[10 Sep 2009 16:12]
Dinh Pham
Hi, I already started MySQL with --skip-name-resolve but still found a lot of requests that show "unauthenticated user" in the process list. Here is the information that I copy using mtop. localhost mysqld 5.1.37-log up 5 day(s), 19:59 hrs 22 threads: 2 running, 1 cached. Queries/slow: 15/0 Cache Hit: 99.99% Opened tables: 0 RRN: 237 TLW: 94.8K SFJ: 0 SMP: 0 QPS: 2 ID USER HOST DB TIME COMMAND STATE INFO 7710678 root localhost Query show full processlist 7711295 wsd 17116.2.12:40716 vstco Sleep 7711321 wsd 17116.2.12:40768 vstco Sleep 7711324 wsd 17116.2.12:40772 vstco Sleep 7711329 wsd 17116.2.12:40782 vstco Sleep 7711332 wsd 17116.2.12:40805 vstco Query Sorting resu SELECT ... FROM user AS user 7711331 wsd 17116.2.12:40804 vstco Sleep 7711340 add 17116.2.12:40816 openx Sleep 7711343 wsd 17116.2.12:40820 vstco Sleep 7711346 ada 17116.2.12:40823 openx Query checking pri SELECT ... FROM application_variable 7711345 ada 17116.2.12:40822 openx Sleep 7711349 wsd 17116.2.12:40827 vstco Sleep 7711351 ada 17116.2.12:40831 open2 Sleep 7711352 ada 17116.2.12:40834 open2 Sleep 7711354 ada 17116.2.12:40837 open2 Sleep 7711353 ada 17116.2.12:40836 open2 Sleep 7711355 unauthen 17116.2.12:40840 Connect Reading from 7711356 wsd 17116.2.12:40841 Sleep 7711357 unauthen 17116.2.12:40842 Connect Reading from 7711358 unauthen 17116.2.12:40843 Connect Reading from 7711359 ada 17116.2.12:40844 Sleep 7711360 unauthen 17116.2.12:40845 Connect Reading from
[8 Feb 2010 8:02]
Helen Val
I had the same problem. I spent 2 weeks to find the solution, it was obvious!!! I used MysqlConnection for fetching Datasets, while I was processing dataset, my Connection to server timed out, and when i tried to get next dataset, the connection had the unathedicated user to do the procees and the code was firing exception. So I decided to get my Datsets with MySqlHelper and not using MysqlConnection. My Problem Solved!!!!!! Ebalsami.
[8 Feb 2010 10:10]
James Day
Ebalsami, was it the new connection or the old one that had an unauthenticated user? Was the new one showing as an unauthenticated user while it was working or just while it was connecting and logging in? What does select version() say about the version of MySQL you were using? Dinh, your unauthenticated users are because the server was waiting for a reply from the client - "Reading from net". It's normal to see some of those on a busy server or one with slow connections between client and server.
[14 Feb 2010 0:09]
Helen Val
I 'll describe the error in details, I 'm using asp.net v2.0 and mysqlconnector.net 6.1.2.0. Mysql version is Mysql 5.1, server is microsoft windows 2003. I'm developing a site that displays live scores (basket, tennis, soccer) and odds data from 30 betting companies. The datasets i must display in every page are large. In most of my pages in page_load event, i get a dataset for example all today matches, and put this dataset in an asp:repeater control. inside asp:repeater onItemDatabound i call for every match new dataset in order to get info for scores, results etc. Think every weekend, soccer matches are many and users are many too. I thought that the problem fixed using MySqlHelper, but today, the problem found again!!!!! Anuthedicated users appears again in MySql database. I do not Know what is the problem now. Maybe MySql parapeters is wrong, but what parameters I should look for this problem? Is there a tool which help me optimize these parameters?
[2 Mar 2010 11:53]
Andrii Nikitin
There is theory that problem related to network timeouts when old-passwords used. Could anybody who hit the problem confirm that "old-passwords" parameter is enabled and following statement shows length '16' at least for some users: SELECT user, length(password) FROM mysql.user;
[4 Mar 2010 6:30]
Helen Val
Hello Andrii Nikitin, your select statement shows in my database 41 length (for all users) and the "use old passwords" is disabled Ebalsami
[6 Mar 2010 13:21]
Sveta Smirnova
Helen, thank you for the feedback. Please provide output of SHOW STATUS LIKE 'Threads_connected' and SHOW VARIABLES LIKE 'max_connections" taken in peak time when connection error occur.
[6 Apr 2010 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[7 Oct 2010 13:06]
Michael Kolev
I have same problem! My version of MySQL is 5.1.46 and I have this problem 3-6 times per day The new thing in my problem is: User: unauthenticated , but the host is not local (76.163.252.84:33616) and status: reading from net in my process list Database size is about 40 Gb And that works so slowly The problem is still open...
[9 Oct 2010 5:09]
Sveta Smirnova
Michael, thank you for the feedback. Please provide output of SHOW STATUS LIKE 'Threads_connected' and SHOW VARIABLES LIKE 'max_connections" taken in peak time when connection error occur.
[10 Nov 2010 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[20 Dec 2010 19:09]
Arne Diegenbach
We have the same problem, unauthenticated user and suddenly a lot of connections waiting making MySQL unresponsive for a while. Anyone that found a solution? This bug should be regarded high priority as it makes MySQL useless for high availability sites. There is no relation between traffic and the problems with MySQL.
[20 Dec 2010 19:30]
Sveta Smirnova
Arne, thank you for the feedback. Please provide output of SHOW STATUS LIKE 'Threads_connected' and SHOW VARIABLES LIKE 'max_connections" taken in peak time when connection error occur.
[22 Dec 2010 21:04]
Arne Diegenbach
===== VARIABLES ===== Variable_name Value Threads_connected 115 Variable_name Value max_connections 500 -- The peak/hang duration is about one minute, during that time it seems to buildup connections locking up the machine. Threads_connected the minute before the hang: 12
[22 Dec 2010 21:30]
James Day
What version - from show variables - and what is the query cache size?
[22 Dec 2010 21:32]
Sveta Smirnova
Arne, please answer James's questions. Thanks in advance.
[22 Dec 2010 22:07]
Arne Diegenbach
Sveta, James, thanks for the quick response. Variable_name Value auto_increment_increment 1 auto_increment_offset 1 autocommit ON automatic_sp_privileges ON back_log 50 basedir /usr/ big_tables OFF binlog_cache_size 32768 binlog_direct_non_transactional_updates OFF binlog_format STATEMENT bulk_insert_buffer_size 8388608 character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci completion_type 0 concurrent_insert 1 connect_timeout 30 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 div_precision_increment 4 engine_condition_pushdown ON error_count 0 event_scheduler OFF expire_logs_days 10 flush OFF flush_time 0 foreign_key_checks ON ft_boolean_syntax + -><()~*:""&| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) general_log OFF general_log_file /var/lib/mysql/v4-db1.log group_concat_max_len 1024 have_community_features YES have_compress YES have_crypt YES have_csv YES have_dynamic_loading YES have_geometry YES have_innodb YES have_ndbcluster NO have_openssl DISABLED have_partitioning YES have_query_cache YES have_rtree_keys YES have_ssl DISABLED have_symlink YES hostname v4-db1 identity 0 ignore_builtin_innodb OFF init_connect init_file init_slave innodb_adaptive_hash_index ON innodb_additional_mem_pool_size 2097152 innodb_autoextend_increment 8 innodb_autoinc_lock_mode 1 innodb_buffer_pool_size 4294967296 innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_doublewrite ON innodb_fast_shutdown 1 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_buffer_size 8388608 innodb_log_file_size 1073741824 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_rollback_on_timeout OFF innodb_stats_on_metadata ON innodb_support_xa ON innodb_sync_spin_loops 20 innodb_table_locks ON innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 innodb_use_legacy_cardinality_algorithm ON insert_id 0 interactive_timeout 30 join_buffer_size 33554432 keep_files_on_create OFF key_buffer_size 2147483648 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 language /usr/share/mysql/english/ large_files_support ON large_page_size 0 large_pages OFF last_insert_id 0 lc_time_names en_US license GPL local_infile ON locked_in_memory OFF log OFF log_bin ON log_bin_trust_function_creators OFF log_bin_trust_routine_creators OFF log_error /var/log/mysql/mysqld.log log_output FILE log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries ON log_warnings 1 long_query_time 1.000000 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 1048576 max_binlog_cache_size 18446744073709547520 max_binlog_size 104857600 max_connect_errors 10 max_connections 500 max_delayed_threads 20 max_error_count 64 max_heap_table_size 1073741824 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 500 max_write_lock_count 18446744073709551615 min_examined_row_limit 0 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 10737418240 myisam_mmap_size 18446744073709551615 myisam_recover_options DEFAULT myisam_repair_threads 1 myisam_sort_buffer_size 268435456 myisam_stats_method nulls_unequal myisam_use_mmap OFF net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old OFF old_alter_table OFF old_passwords OFF open_files_limit 20000 optimizer_prune_level 1 optimizer_search_depth 62 optimizer_switch index_merge=on,index_merge_union=on,index_merge_so... pid_file /var/lib/mysql/v4-db1.pid plugin_dir /usr/lib/mysql/plugin port 3306 preload_buffer_size 32768 profiling OFF profiling_history_size 15 protocol_version 10 pseudo_thread_id 2227559 query_alloc_block_size 8192 query_cache_limit 268435456 query_cache_min_res_unit 4096 query_cache_size 536870912 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 rand_seed1 rand_seed2 range_alloc_block_size 4096 read_buffer_size 1048576 read_only OFF read_rnd_buffer_size 1048576 relay_log relay_log_index relay_log_info_file relay-log.info relay_log_purge ON relay_log_space_limit 0 report_host report_password report_port 3306 report_user rpl_recovery_rank 0 secure_auth OFF secure_file_priv server_id 1 skip_external_locking ON skip_name_resolve ON skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_exec_mode STRICT slave_load_tmpdir /tmp slave_net_timeout 3600 slave_skip_errors 1062 slave_transaction_retries 10 slow_launch_time 2 slow_query_log ON slow_query_log_file /var/log/mysql/mysql_error.log socket /var/run/mysqld/mysqld.sock sort_buffer_size 8388608 sql_auto_is_null ON sql_big_selects ON sql_big_tables OFF sql_buffer_result OFF sql_log_bin ON sql_log_off OFF sql_log_update ON sql_low_priority_updates OFF sql_max_join_size 18446744073709551615 sql_mode sql_notes ON sql_quote_show_create ON sql_safe_updates OFF sql_select_limit 18446744073709551615 sql_slave_skip_counter sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine MyISAM sync_binlog 0 sync_frm ON system_time_zone CET table_definition_cache 256 table_lock_wait_timeout 50 table_open_cache 4906 table_type MyISAM thread_cache_size 16 thread_handling one-thread-per-connection thread_stack 196608 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF timestamp 1293055217 tmp_table_size 1073741824 tmpdir /tmp transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ unique_checks ON updatable_views_with_limit YES version 5.1.49-1ubuntu8.1-log version_comment (Ubuntu) version_compile_machine x86_64 version_compile_os debian-linux-gnu wait_timeout 10 warning_count 0
[22 Dec 2010 22:21]
James Day
Arne, sensible values for the query cache size are rarely larger than 20M. Usually settings like 500M are because automate tools see a low hit rate and say make it bigger as their only response. Have a look at the free space in the query cache in SHOW GLOBAL STATUS and cut it back to a size that gives only 5-10M free if it's more, then let us know what size you used and whether it helped with the connection issue. With earlier versions than yours a large query cache could cause long - many minutes - server freezes if a lot of rows had to be removed. We have workarounds in your version but they still don't take care of all possible slowdowns. If you do remove RAM from the query cache, the InnoDB buffer pool is often a good alternative place to use it.
[22 Dec 2010 22:21]
Arne Diegenbach
More:
mysqld Ver 5.1.49-1ubuntu8.1-log for debian-linux-gnu on x86_64 ((Ubuntu))
--------
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
auto-rehash TRUE
character-sets-dir (No default value)
column-type-info FALSE
comments FALSE
compress FALSE
debug-check FALSE
debug-info FALSE
database (No default value)
default-character-set latin1
delimiter ;
vertical FALSE
force FALSE
named-commands FALSE
ignore-spaces FALSE
local-infile FALSE
no-beep FALSE
host (No default value)
html FALSE
xml FALSE
line-numbers TRUE
unbuffered FALSE
column-names TRUE
sigint-ignore FALSE
port 3306
prompt mysql>
quick FALSE
raw FALSE
reconnect TRUE
socket /var/run/mysqld/mysqld.sock
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-verify-server-cert FALSE
table FALSE
user (No default value)
safe-updates FALSE
i-am-a-dummy FALSE
connect_timeout 0
max_allowed_packet 16777216
net_buffer_length 16384
select_limit 1000
max_join_size 1000000
secure-auth FALSE
show-warnings FALSE
------
arne@v4-db1:~$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 26
model name : Intel(R) Xeon(R) CPU E5540 @ 2.53GHz
stepping : 5
cpu MHz : 2527.400
cache size : 8192 KB
physical id : 1
siblings : 8
core id : 0
cpu cores : 4
apicid : 16
initial apicid : 16
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 popcnt lahf_lm ida tpr_shadow vnmi flexpriority ept vpid
bogomips : 5054.80
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
Above for 16 cores ...
-----
Although we have latin1 as a default compiled, most content is utf8.
[22 Dec 2010 22:25]
Arne Diegenbach
Thanks James, we will try that and report here.
[22 Dec 2010 22:34]
Arne Diegenbach
Also, there are only simple single row log insert statements (on innodb) and select statements in the process list at the time of the freeze, no remove statements. After the freeze ends everything seems to be working as normal. We will follow your recommendation and report back. Thanks a lot for the quick response. Current SHOW GLOBAL STATUS Variable_name Value Aborted_clients 23 Aborted_connects 2363 Binlog_cache_disk_use 0 Binlog_cache_use 354948 Bytes_received 4996592362 Bytes_sent 100599255564 Com_admin_commands 11 Com_assign_to_keycache 0 Com_alter_db 0 Com_alter_db_upgrade 0 Com_alter_event 0 Com_alter_function 0 Com_alter_procedure 0 Com_alter_server 0 Com_alter_table 4 Com_alter_tablespace 0 Com_analyze 0 Com_backup_table 0 Com_begin 86813 Com_binlog 0 Com_call_procedure 164805 Com_change_db 2352 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 62419 Com_create_db 0 Com_create_event 0 Com_create_function 0 Com_create_index 0 Com_create_procedure 0 Com_create_server 0 Com_create_table 1 Com_create_trigger 0 Com_create_udf 0 Com_create_user 0 Com_create_view 0 Com_dealloc_sql 0 Com_delete 7611 Com_delete_multi 0 Com_do 0 Com_drop_db 0 Com_drop_event 0 Com_drop_function 0 Com_drop_index 0 Com_drop_procedure 0 Com_drop_server 0 Com_drop_table 1 Com_drop_trigger 0 Com_drop_user 0 Com_drop_view 0 Com_empty_query 0 Com_execute_sql 0 Com_flush 5 Com_grant 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 329794 Com_insert_select 1 Com_install_plugin 0 Com_kill 0 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 291 Com_optimize 319 Com_preload_keys 0 Com_prepare_sql 0 Com_purge 0 Com_purge_before_date 0 Com_release_savepoint 0 Com_rename_table 0 Com_rename_user 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 24394 Com_rollback_to_savepoint 0 Com_savepoint 0 Com_select 2521502 Com_set_option 2249799 Com_show_authors 0 Com_show_binlog_events 0 Com_show_binlogs 112 Com_show_charsets 33 Com_show_collations 33 Com_show_column_types 0 Com_show_contributors 0 Com_show_create_db 1 Com_show_create_event 0 Com_show_create_func 30 Com_show_create_proc 72 Com_show_create_table 778 Com_show_create_trigger 0 Variable_name Value Com_show_databases 118 Com_show_engine_logs 0 Com_show_engine_mutex 0 Com_show_engine_status 0 Com_show_events 0 Com_show_errors 0 Com_show_fields 3224 Com_show_function_status 0 Com_show_grants 34 Com_show_keys 352 Com_show_master_status 79 Com_show_new_master 0 Com_show_open_tables 0 Com_show_plugins 33 Com_show_privileges 0 Com_show_procedure_status 0 Com_show_processlist 2396 Com_show_profile 0 Com_show_profiles 0 Com_show_slave_hosts 0 Com_show_slave_status 76 Com_show_status 2038 Com_show_storage_engines 0 Com_show_table_status 1060 Com_show_tables 1837 Com_show_triggers 402 Com_show_variables 2197 Com_show_warnings 6 Com_slave_start 0 Com_slave_stop 0 Com_stmt_close 0 Com_stmt_execute 0 Com_stmt_fetch 0 Com_stmt_prepare 0 Com_stmt_reprepare 0 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 0 Com_uninstall_plugin 0 Com_unlock_tables 317 Com_update 98076 Com_update_multi 75392 Com_xa_commit 0 Com_xa_end 0 Com_xa_prepare 0 Com_xa_recover 0 Com_xa_rollback 0 Com_xa_start 0 Compression OFF Connections 2255311 Created_tmp_disk_tables 28455 Created_tmp_files 14427 Created_tmp_tables 339644 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 5 Handler_commit 3563289 Handler_delete 22323 Handler_discover 0 Handler_prepare 1026596 Handler_read_first 11329 Handler_read_key 6778020790 Handler_read_next 212279483904 Handler_read_prev 148273137 Handler_read_rnd 1706134317 Handler_read_rnd_next 10649939189 Handler_rollback 56073 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 2208853 Handler_write 10233156395 Innodb_buffer_pool_pages_data 244809 Innodb_buffer_pool_pages_dirty 36 Innodb_buffer_pool_pages_flushed 1434781 Innodb_buffer_pool_pages_free 1 Innodb_buffer_pool_pages_misc 17334 Innodb_buffer_pool_pages_total 262144 Innodb_buffer_pool_read_ahead_rnd 3650 Innodb_buffer_pool_read_ahead_seq 7136 Innodb_buffer_pool_read_requests 75604371648 Innodb_buffer_pool_reads 218382 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 211458466 Innodb_data_fsyncs 812927 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 9987739648 Innodb_data_reads 300272 Innodb_data_writes 1648320 Innodb_data_written 56680790016 Innodb_dblwr_pages_written 1434781 Innodb_dblwr_writes 27105 Innodb_log_waits 0 Innodb_log_write_requests 20562393 Innodb_log_writes 745449 Innodb_os_log_fsyncs 759263 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Variable_name Value Innodb_os_log_written 9658710528 Innodb_page_size 16384 Innodb_pages_created 518875 Innodb_pages_read 609470 Innodb_pages_written 1434781 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 3090242 Innodb_row_lock_time_avg 2592 Innodb_row_lock_time_max 51961 Innodb_row_lock_waits 1192 Innodb_rows_deleted 22323 Innodb_rows_inserted 25598787 Innodb_rows_read 212804935330 Innodb_rows_updated 149150 Key_blocks_not_flushed 0 Key_blocks_unused 1714698 Key_blocks_used 9576 Key_read_requests 4512186 Key_reads 11032 Key_write_requests 293537 Key_writes 8752 Last_query_cost 0.000000 Max_used_connections 501 Not_flushed_delayed_rows 0 Open_files 157 Open_streams 0 Open_table_definitions 256 Open_tables 4906 Opened_files 172548 Opened_table_definitions 1299 Opened_tables 21975 Prepared_stmt_count 0 Qcache_free_blocks 4128 Qcache_free_memory 512010168 Qcache_hits 4980036 Qcache_inserts 2085056 Qcache_lowmem_prunes 0 Qcache_not_cached 435869 Qcache_queries_in_cache 11937 Qcache_total_blocks 28326 Queries 12871764 Questions 12706959 Rpl_status NULL Select_full_join 3301 Select_full_range_join 0 Select_range 300992 Select_range_check 0 Select_scan 19260 Slave_open_temp_tables 0 Slave_retried_transactions 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 17518 Sort_merge_passes 7211 Sort_range 685344 Sort_rows 348608815 Sort_scan 291435 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 19415099 Table_locks_waited 65 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 10 Threads_connected 8 Threads_created 3625 Threads_running 4 Uptime 141599 Uptime_since_flush_status 141599
[22 Dec 2010 22:55]
James Day
Inserts, updates and deletes all cause all entries for queries involving that table to be removed from the query cache. You're using about 24M of query cache, so try 30M. InnoDB buffer pool seems fully used so it's likely that putting the RAM to work there will be useful. Your table_open_cache is too small, look at Opened_tables. table_definition_cache is also a little too small, not by much. Not related to the problem you're having, just a couple of interesting things.
[23 Dec 2010 21:21]
Sveta Smirnova
Set to "Need Feedback" again to wait tests with query cache set to 30M
[27 Dec 2010 15:52]
Arne Diegenbach
Implemented the changed but with no success. We will now focus on the combination of OS/MySQL and (RAID) drivers used. This seems like a (mutex) lock that gets timed-out, the peak is always of a short duration (about a minute). Any other suggestions are welcome, thank you for your response.
[10 Aug 2011 2:51]
chen wu
We have the same problem on 5.1.54. This problem have been puzzled us for a long time.
[10 Aug 2011 9:27]
Arne Diegenbach
It took a long time to convince my customer that this was not a bug in the software we designed and had them change the os to a redhat / fedora / cent based os. This solved the problem permanently. IMHO this problem is related to a wacky shared library (or in the way mysql uses this lib). Sure we tried very advice before changing the os. I spent many hours tracing processes and analyzing logs. We now happily use mysql wiith hundreds of thousands transactions daily and even more lookups.
[10 Aug 2011 9:30]
Arne Diegenbach
It took a long time to convince my customer that this was not a bug in the software we designed and had them change the os to a redhat / fedora / cent based os. This solved the problem permanently. IMHO this problem is related to a wacky shared library (or in the way mysql uses this lib). Sure we tried very advice before changing the os. I spent many hours tracing processes and analyzing logs. We now happily use mysql wiith hundreds of thousands transactions daily and even more lookups.
[3 Mar 2012 7:59]
manish kumar
HI, I found unauthenticated user in show process-list. Anyone can help me about this. show status like %thread_conn% is Threads_connected 22 show variables like %max_conn% is max_connections 400 Manish Kumar
[7 Mar 2012 0:06]
James Day
Manish, it is normal to see that sometimes. Check where they are connecting from - IP or host - and it if is one of your own computers it is probably harmless if you do not see it from the same source regularly. If it is a computer where you do not expect it to be connecting to your server then it may be a problem with broken firewall or other risk. If you see it regularly it could be an attack and you could consider using network logging tools to verify this.
[22 Mar 2012 17:48]
Adair Commodo
Olá à todos, boa tarde! Também encontrei tal problema em nosso servidor MySQL 5.1.49-3-log instalado sobre o Debian 6 - Squeeze instalado via apt-get. Encontrei o mesmo problema: ao executar o comando "SHOW PROCESSLIST" encontrei tais processos: | 31501113 | unauthenticated user | prt02.cancaonova.com:44267 | NULL | Connect | NULL | Reading from net | NULL | | 31501114 | unauthenticated user | prt02.cancaonova.com:44268 | NULL | Connect | NULL | Reading from net | NULL | | 31501115 | unauthenticated user | prt01.cancaonova.com:43652 | NULL | Connect | NULL | Reading from net | NULL | | 31501116 | unauthenticated user | wtv.cancaonova.com:45527 | NULL | Connect | NULL | Reading from net | NULL | | 31501117 | unauthenticated user | wtv.cancaonova.com:45528 |NULL | Connect | NULL | Reading from net | NULL | | 31501118 | unauthenticated user | prt02.cancaonova.com:44270 |NULL| Connect | NULL | Reading from net | NULL | | 31501119 | unauthenticated user | wtv.cancaonova.com:45529 |NULL| Connect | NULL | Reading from net | NULL | | 31501120 | unauthenticated user | con01.cancaonova.com:36328 |NULL| Connect | NULL | Reading from net | NULL | | 31501121 | unauthenticated user | prt02.cancaonova.com:44271 |NULL| Connect | NULL | Reading from net | NULL | | 31501122 | unauthenticated user | con01.cancaonova.com:36330 | NULL | Connect | NULL | Reading from net | NULL mais ou menos cerca de 202 conexões, sendo que o servidor está configurado com "max_connections" maior do que este valor, ao ver tal "anomalia" realizei um "KILL" no 1º processo e os demais sumiram. As perguntas são: "Tem solução?", "A versão 5.5 do MySQL já sanou tal problema?" Grato e desejando-vos sucesso, saúde e paz, ARC via google tradutor: Hello everyone, good afternoon! I also found this problem on our server MySQL 5.1.49-3-log Debian installed on the 6 - Squeeze installed via apt-get. I found the same problem: executing the command "SHOW PROCESSLIST" found such processes: | 31501113 | unauthenticated user | prt02.cancaonova.com:44267 | NULL | Connect | NULL | Reading from net | NULL | | 31501114 | unauthenticated user | prt02.cancaonova.com:44268 | NULL | Connect | NULL | Reading from net | NULL | | 31501115 | unauthenticated user | prt01.cancaonova.com:43652 | NULL | Connect | NULL | Reading from net | NULL | | 31501116 | unauthenticated user | wtv.cancaonova.com:45527 | NULL | Connect | NULL | Reading from net | NULL | | 31501117 | unauthenticated user | wtv.cancaonova.com:45528 |NULL | Connect | NULL | Reading from net | NULL | | 31501118 | unauthenticated user | prt02.cancaonova.com:44270 |NULL| Connect | NULL | Reading from net | NULL | | 31501119 | unauthenticated user | wtv.cancaonova.com:45529 |NULL| Connect | NULL | Reading from net | NULL | | 31501120 | unauthenticated user | con01.cancaonova.com:36328 |NULL| Connect | NULL | Reading from net | NULL | | 31501121 | unauthenticated user | prt02.cancaonova.com:44271 |NULL| Connect | NULL | Reading from net | NULL | | 31501122 | unauthenticated user | con01.cancaonova.com:36330 | NULL | Connect | NULL | Reading from net | NULL more or less than about 202 connections, and the server is configured with "max_connections" greater than this value, seeing this "anomaly" did a "kill" in the 1st case and the others are gone. The questions are: "It's the solution?", "Version 5.5 of MySQL has remedied this problem?" Thanks and wishing you success, health and peace, ARC
[26 Mar 2012 19:42]
Adair Commodo
Caríssimos, boa tarde! Alterei o valor padrão da variável "net_read_timeout" de 30 para 5 segundo, "net_write_timeout" de 60 para 5 segundos e "net_retry_count" de 10 para 2 tentativas. o quando ocorre o "unauthenticated user", rapidamente ele fecha a conexão do não chegando ao ponto de ocorre #DoS (Denial of Service) no Servidor. O MySQL é a versão 5.1.49-3-log sobre o Debian 6 (Squeeze). google tradutor: Dear, good afternoon! I changed the default value of the variable "net_read_timeout" from 30 to 5 seconds, "net_write_timeout" from 60 to 5 seconds and "net_retry_count" from 10 to 2 attempts. occurs when the "unauthenticated user", he quickly closes the connection did not occur to the point of # DoS (Denial of Service) on the server. MySQL is version 5.1.49-3-6 logging on Debian (Squeeze). Adair Rossatto Commodo
[2 Apr 2012 18:13]
Sveta Smirnova
Adair, thank you for update. Closing as "Not a bug" as this was not MySQL failure.
[13 Jul 2022 20:18]
David Blum
This is what we get ALL DAY - I've never had to KILL these processes - they usually disappear eventually by themselves - but - I might try to do that and see if our system runs better when they pop up... 19244655 unauthen 67.200.239.xxx:6 Connect Reading from 19244836 unauthen 67.200.239.xxx:6 Connect Reading from 19244841 unauthen 67.200.239.xxx:6 Connect Reading from 19244874 unauthen 67.200.239.xxx:6 Connect Reading from 19244877 unauthen 67.200.239.xxx:6 Connect Reading from 19244891 unauthen 67.200.239.yyy:5 Connect Reading from 19244893 unauthen 67.200.239.xxx:6 Connect Reading from 19244901 unauthen 67.200.239.xxx:6 Connect Reading from 19244942 unauthen 67.200.239.xxx:6 Connect Reading from 19244943 unauthen 67.200.239.xxx:6 Connect Reading from 19244989 unauthen 50.185.129.zzz:3 Connect Reading from
