Bug #8000 Threads_connected very high value with hyperthreading technology
Submitted: 19 Jan 2005 2:25 Modified: 30 Jun 2005 16:03
Reporter: Ricardo Creasy Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.9 OS:Linux (Fedora Core 2)
Assigned to: Assigned Account CPU Architecture:Any

[19 Jan 2005 2:25] Ricardo Creasy
Description:
I have had a very busy week testing on differents pc's and servers 
to try to replicate the problem and now I can document it, to see 
if someone found a solution for this.

The enviroment, Local network with fixed ip's, same subnet 

I have test this with these odbc clients

MyODBC 3.51.07, 3.51.08, 3.51.09, 3.51.10 

The same problems with all, 

The tested client pcs is XP and XP SP2, 

The Operating System of the Server where MySQL is running 
is Fedora Core 2, Tested with MySQL Server 4.1.5, 4.1.7, 4.1.8, 4.1.9, 

The problem is that if I try with the same MySQL Version and Operating 
System on differentents server platforms it behaves different, 
with a Pentium IV with HyperThreading or Xeon With HyperThreading 
enabled or disabled( both ways) , it keeps increasing the number 
of threads_connected, even with one pc connected and every thread_connected
gets a TCP/IP Socket, so after a while it gives the error 'Too many connections'
and uses all the resources available, (cpu and memory) 
even if the  set-variable=max_connections=1000, and just two pcs connected
doing queries,  the global variable  threads_cache_size=20 did not help, 
neither  wait_time_out=120, nor thread_concurrency=2, innodb_thread_concurrency=2, i tried with MyISAM and INNODB tables types, 
The CPU and memory usage becomes not sufficient and gets very slow, 
eventually disconnecting or not allowing any other pc to connect to the 
database. 

If same operating system, same MySQL Server version, I test on a server Pentium III or Pentium IV with no HyperThreading technology 
it just has threads_connected = 2 when two pc's connected, I also get a very 
slow connection from the test datasource of MyODBC when changing to 
new hyperthreading platform

I did several testing and find no valid reason for this behavior, I find no 
information of this issue over the internet, 

Has anyone had this problem? I wonder what is wrong with MyODBC and HyperThreading technology if that is the case? 

Any help is welcome, 

Regards,

How to repeat:

Last version of MySQL Server installed on HyperThreading Tecnology Server, 
and doing a cicle of queries from MyODBC last versions, 
observe 

threads_connected
threads_created
connections
threads_running

Suggested fix:

kill threads that are not in use,
[20 Jan 2005 16:34] Ricardo Creasy
Display of the command  SHOW PROCESSLIST  on testing replicating the problem with 8 pc's with ODBC

Attachment: show_processlist.zip (application/x-zip-compressed, text), 44.44 KiB.

[21 Jan 2005 2:20] Dean Ellis
I have tried as many variations/loads as I can think of and have so far not been able to repeat the problem.

ie: threads_connected reports an accurate number, that being the number of threads that are in fact connected.

Check SHOW PROCESSLIST and see if you do happen to have a large number of connections held open and idling.

Otherwise, we will need a specific set of steps by which we can reproduce this behavior.
[23 Jan 2005 1:20] Ricardo Creasy
Please notice that I have added a file with show processlist, is a zip,
[23 Jan 2005 1:23] Ricardo Creasy
The name of the file added to the file on january 20, is show_processlist.zip and it contains an example of the result of show processlist, 

Try to send a select query and a couple of insert to differents tables, keep doing this for two to five minutes and you would notice the number of threads connected raising, 

Are you testing on a server with hyperthreading techonology?  and FC2 installed?
[31 May 2005 14:14] Jorge del Conde
I was unable to reproduce this behaviour using Hyperthreading & Win32
[31 May 2005 16:03] Jorge del Conde
Ricardo, Can you please check that you don't have any DNS problems in your system ?
[1 Jul 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".