--lphbijnihdmlhhlegjol
Content-Type: text/plain; charset=us-ascii


perl Digest 26 Sep 2003 12:49:27 -0000 Issue 423

Topics (messages 2421 through 2426):

Re: Msql Question
2421 by: Jochen Wiedmann

error TCP/IP sockets
2422 by: mark
2423 by: Jochen Wiedmann
2424 by: Jochen Wiedmann

very slow "Sending data" phase
2425 by: Kevin

environment variable values
2426 by: mark

Administrivia:

To subscribe to the digest, e-mail:
<perl-digest-subscribe@lists.mysql.com>

To unsubscribe from the digest, e-mail:
<perl-digest-unsubscribe@lists.mysql.com>

To post to the list, e-mail:
<perl@lists.mysql.com>


----------------------------------------------------------------------

--lphbijnihdmlhhlegjol
Content-Type: message/rfc822
Content-Disposition: inline; filename="perl_2421.ezm"

Message-ID: <3F71746A.7050204@ispsoft.de>
Date: Wed, 24 Sep 2003 12:39:38 +0200
From: Jochen Wiedmann <joe@ispsoft.de>
MIME-Version: 1.0
To: Delly@Willard-Oh.com
CC: perl@lists.mysql.com
Subject: Re: FW: Msql Question
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit


Do not ask me for router configuration issues. Basically the router needs to
be configured to enable client side requests to the server, but that's all I
can say.


Jochen


--lphbijnihdmlhhlegjol
Content-Type: message/rfc822
Content-Disposition: inline; filename="perl_2422.ezm"

Date: Wed, 24 Sep 2003 16:59:14 +0100
From: mark <mark.r.o'sullivan@aib.ie>
Subject: error TCP/IP sockets To: perl@lists.mysql.com, win32@lists.mysql.com
Message-id: <0HLQ00LFS76KYO@sims.isec.aib.ie>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit

Hi,
I know I've already asked this but I'm very stuck. Here is my code:
Operating System: Windows NT
Using Tomcat:
Have DBI and mysql driver installed.

my $database = 'mysql';
my $hostname = "localhost";
my $port = '';
my $user = 'root';
my $password = 'NO';

my $dsn = "DBI:mysql:database=$database;host=$hostname;port= $port";
my $dbh = DBI->connect($dsn,$user,$password);

getting error in tomcat's log files:
cgi: runCGI (stderr)BI
connect('database=mysql;host=127.0.0.1;port=','roo t',...) failed: Can't
create TCP/IP socket (10106) at
C:\jakarta-tomcat-4.1.24\webapps\pds\WEB-INF\cgi\example.pl line 50

have done the following:
(1)granted permissions for root with user password "NO" to database 'mysql'
(2)have this working through perl on command line.
(3)no port suppled, therefore uses default port.
(4)Looked at sockets, localhost defined as 127.0.0.1, pinged this and still
worked.


Any ideas is this an issue with tomcat, window's NT or perl database
connectivity.


************************************************** ********************
This do***ent is strictly confidential and is intended for use by
the addressee unless otherwise indicated.
Allied Irish Banks
************************************************** ********************


--lphbijnihdmlhhlegjol
Content-Type: message/rfc822 Content-Disposition: inline; filename="perl_2423.ezm"
Message-ID: <3F71C242.5070308@ispsoft.de>
Date: Wed, 24 Sep 2003 18:11:46 +0200
From: Jochen Wiedmann <joe@ispsoft.de> MIME-Version: 1.0
To: mark <mark.r.o'sullivan@aib.ie>
CC: perl@lists.mysql.com, win32@lists.mysql.com
Subject: Re: error TCP/IP sockets
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit mark wrote:


What does your program show, if you are using DBI->trace(2) before connecting?

--lphbijnihdmlhhlegjol
Content-Type: message/rfc822 Content-Disposition: inline; filename="perl_2424.ezm"
Message-ID: <3F71C2CE.3080101@ispsoft.de>
Date: Wed, 24 Sep 2003 18:14:06 +0200
From: Jochen Wiedmann <joe@ispsoft.de> MIME-Version: 1.0
To: mark <mark.r.o'sullivan@aib.ie>
CC: perl@lists.mysql.com, win32@lists.mysql.com
Subject: Re: error TCP/IP sockets
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit mark wrote:


What does your program show, if you are using DBI->trace(2) before connecting?

--lphbijnihdmlhhlegjol
Content-Type: message/rfc822 Content-Disposition: inline; filename="perl_2425.ezm"
From: "Kevin" <kevin@allpoetry.com>
To: <perl@lists.mysql.com>
Subject: very slow "Sending data" phase
Date: Wed, 24 Sep 2003 21:39:07 -0700
Message-ID: <001101c3831e$f4cdc750$6500a8c0@pillars>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hello All,

I run a mod_perl/mysql website, and have been noticing increasingly
often lately that queries will get stuck for a very long time in the
sending data phase:

3652 allpoetry localhost allpoetry Query 291
Sending data SELECT
amt,bid,bids.lid,bids.mid,owner,name,title,bids.ty pe,bids.preferred,cat1
,cat2,cat3,poems.mid as poemowner,poets.preferred as p_pref, poems.type
as p_type, poems.image FROM bids left join poets on bids.owner =
poets.mid left join poems on poems.lid = bids.lid WHERE remaining > 0
ORDER BY amt DESC LIMIT 5
3653 allpoetry localhost allpoetry Query 291
Sending data SELECT
amt,bid,bids.lid,bids.mid,owner,name,title,bids.ty pe,bids.preferred,cat1
,cat2,cat3,poems.mid as poemowner,poets.preferred as p_pref, poems.type
as p_type, poems.image FROM bids left join poets on bids.owner =
poets.mid left join poems on poems.lid = bids.lid WHERE remaining > 0
ORDER BY amt DESC LIMIT 5

... 10 more of the same thing ...

Often these are the *only* queries shown running for 10-20-30 seconds in
"show full processlist", though other things are running, just very
quickily. Eventually things start to wait for writes and it locks up
and drags the system to slowness for 5-10 minutes.

The long 'sending data' phase seems to happen for many different
queries. The explain for the top query shows:
----------------------------+
| table | type | possible_keys | key | key_len | ref |
rows |
+-------+--------+---------------+-----------+---------+------------+---
--
| bids | range | remaining | remaining | 2 | NULL |
192 | Using where; Using filesort |
| poets | eq_ref | PRIMARY | PRIMARY | 3 | bids.owner |
1 | |
| poems | eq_ref | PRIMARY | PRIMARY | 3 | bids.lid |
1 | |
+-------+--------+---------------+-----------+---------+------------+---
+-------+--------+---------------+-----------+---------+------------+---

Sure, it's a filesort, but only 192 rows so should be no big deal.
(Side note: the 'remaining' key is on (remaining, amt), so shouldn't it
be using it and not filesorting?).

During these times apache doesn't seem to be using any more than usual
processor power, and I've done lots of 'strace -p' but without really
learning anything.

Any ideas what could be causing such a long sending-data phase? Ram is
a little short, but not dipping into swap.

total: used: free: shared: buffers: cached:
Mem: 650891264 636760064 14131200 0 7864320 347262976
Swap: 806068224 20066304 786001920


I'm running the latest 4.0x redhat rpm version (4.0.15-standard-log),
with all myisam tables.

My process list looks like:
## PID UID Size Share VSize Rss TTY St Command
1 12085 mysql 22M 2.1M 132M 22M 34816 S mysqld
2 12087 mysql 22M 2.1M 132M 22M 34816 S mysqld
3 12088 mysql 22M 2.1M 132M 22M 34816 S mysqld
... repeat exactly until ...
49 12367 mysql 22M 2.1M 132M 22M 34816 S mysqld

1 12056 root 988K 820K 4.1M 988K 34816 S mysqld_safe

Which is strange too because it has way more processes than apache can
be using (should be only one persistent connection per process): # ps
aux | grep /sbin/httpd | wc -l
22
# ps aux | grep mysql | wc -l
53

My relevant my.cnf variables are:
set-variable = key_buffer=100M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=4M
set-variable = record_buffer=1M
set-variable = myisam_max_sort_file_size=600M
set-variable = myisam_max_extra_sort_file_size=600M
set-variable = myisam_sort_buffer_size=32M
set-variable = thread_cache=40
set-variable = thread_concurrency=2

Thanks for any help you can suggest! Sorry for including so much
information, I'm not sure what could be causing it. I can't narrow down
any recent changes to what could be causing it.

Ciao,
Kevin Watt
Community Manager, Allpoetry.com
How do really small people call each other ? On Microphones
How do you fix a broken chimp? With a monkey wrench

--lphbijnihdmlhhlegjol
Content-Type: message/rfc822
Content-Disposition: inline; filename="perl_2426.ezm"

Date: Fri, 26 Sep 2003 13:46:54 +0100
From: mark <mark.r.o'sullivan@aib.ie>
Subject: environment variable values To: perl@lists.mysql.com
Message-id: <0HLT00I89NM27U@sims.isec.aib.ie>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit

Hi,
I'm currently trying to connect to mysql using DBD:ODBC because I have
difficulty getting the DBD-mysql module to work. Any ideas how I set the
following environment variable values?
DBI_DSN
DBI_USER
DBI_PASS.


************************************************** ********************
This do***ent is strictly confidential and is intended for use by
the addressee unless otherwise indicated.
Allied Irish Banks
************************************************** ********************


--lphbijnihdmlhhlegjol--