I am using php to run queries on my SQL
For some reason I get many "sleep"ing processes in my DB
I am using the following lines to run the queries:
$link = mysql_pconnect($db_server, $db_user, $db_password) or
die('err');
$res  = mysql_db_query($db_database, $sql_string, $link);

Any idea what can be done to avoid those hung processes?
Thanks in advance
Sharon

== Quote from  Sharon11 (2good2b@gmail.com)'s article
> I am using php to run queries on my SQL
> For some reason I get many "sleep"ing processes in my DB
> I am using the following lines to run the queries:
> $link = mysql_pconnect($db_server, $db_user, $db_password) or
> die('err');
> $res  = mysql_db_query($db_database, $sql_string, $link);
> Any idea what can be done to avoid those hung processes?
> Thanks in advance
> Sharon

in general pconnect keeps the conx handler in the database. this from php's website:

"Be warned if you use different parameters for mysql_pconnect() in different
scripts on server: PHP can create single persistent connection for every set of
parameters in each process up to mysql.max_persistent (PHP directive) per process. "

--
POST BY: lark with PHP News Reader

On Jun 26, 10:51 pm, lark <ham...@sbcglobal.net> wrote:
> == Quote from  Sharon11 (2goo...@gmail.com)'s article
>
> > I am using php to run queries on my SQL
> > For some reason I get many "sleep"ing processes in my DB
> > I am using the following lines to run the queries:
> > $link = mysql_pconnect($db_server, $db_user, $db_password) or
> > die('err');
> > $res  = mysql_db_query($db_database, $sql_string, $link);
> > Any idea what can be done to avoid those hung processes?
> > Thanks in advance
> > Sharon
>
> in general pconnect keeps the conx handler in the database. this from php's website:
>
> "Be warned if you use different parameters for mysql_pconnect() in different
> scripts on server: PHP can create single persistent connection for every set of
> parameters in each process up to mysql.max_persistent (PHP directive) per process. "
>
> --
> POST BY: lark with PHP News Reader

I don't. I use the same params all the time...
Any other ideas...?

Sharon11 <2good2b@gmail.com> wrote in
news:1182891550.169757.86820@m36g2000hse.googlegroups.com: 

>> > I am using the following lines to run the queries:
>> > $link = mysql_pconnect($db_server, $db_user, $db_password) or
>> > die('err');
>> > $res  = mysql_db_query($db_database, $sql_string, $link);
>> > Any idea what can be done to avoid those hung processes?
>> > Thanks in advance
>> > Sharon
>>
>> in general pconnect keeps the conx handler in the database. this from
>> php's website: 
>>
>> "Be warned if you use different parameters for mysql_pconnect() in
>> different scripts on server: PHP can create single persistent
>> connection for every set of parameters in each process up to
>> mysql.max_persistent (PHP directive) per process. " 
>
> I don't. I use the same params all the time...
> Any other ideas...?

Yes... what happens when you just use "mysql_connect" instead of 
"mysql_pconnect"?  I'm not familiar with your exact situation, but 
persistant connections usually provide hassles, and should be avoided 
unless your application specifically needs that type of connection.

On Jun 26, 11:11 pm, Good Man <h...@letsgo.com> wrote:
> Sharon11 <2goo...@gmail.com> wrote innews:1182891550.169757.86820@m36g2000hse.googlegroups.com:
>
>
>
>
>
> >> > I am using the following lines to run the queries:
> >> > $link = mysql_pconnect($db_server, $db_user, $db_password) or
> >> > die('err');
> >> > $res  = mysql_db_query($db_database, $sql_string, $link);
> >> > Any idea what can be done to avoid those hung processes?
> >> > Thanks in advance
> >> > Sharon
>
> >> in general pconnect keeps the conx handler in the database. this from
> >> php's website:
>
> >> "Be warned if you use different parameters for mysql_pconnect() in
> >> different scripts on server: PHP can create single persistent
> >> connection for every set of parameters in each process up to
> >> mysql.max_persistent (PHP directive) per process. "
>
> > I don't. I use the same params all the time...
> > Any other ideas...?
>
> Yes... what happens when you just use "mysql_connect" instead of
> "mysql_pconnect"?  I'm not familiar with your exact situation, but
> persistant connections usually provide hassles, and should be avoided
> unless your application specifically needs that type of connection.- Hide quoted text -
>
> - Show quoted text -

I have a website with thousands of simultaneous users
>From what i understoon, it *must* have pconnect, no?.

== Quote from  Sharon11 (2good2b@gmail.com)'s article
> On Jun 26, 10:51 pm, lark <ham...@sbcglobal.net> wrote:
> > == Quote from  Sharon11 (2goo...@gmail.com)'s article
> >
> > > I am using php to run queries on my SQL
> > > For some reason I get many "sleep"ing processes in my DB
> > > I am using the following lines to run the queries:
> > > $link = mysql_pconnect($db_server, $db_user, $db_password) or
> > > die('err');
> > > $res  = mysql_db_query($db_database, $sql_string, $link);
> > > Any idea what can be done to avoid those hung processes?
> > > Thanks in advance
> > > Sharon
> >
> > in general pconnect keeps the conx handler in the database. this from php's
website:
> >
> > "Be warned if you use different parameters for mysql_pconnect() in different
> > scripts on server: PHP can create single persistent connection for every set of
> > parameters in each process up to mysql.max_persistent (PHP directive) per
process. "
> >
> > --
> > POST BY: lark with PHP News Reader
> I don't. I use the same params all the time...
> Any other ideas...?


if your tables are innodb, the pconnect does not play well with that; however,
decreasing the timeout parameter can help the problem.

do this on the cmd line:

show global variables like 'wait%';

it should be something like 28800. then run this:
set global wait_timeout := 600;

this will reset your timeout globally.

let me know if this helps out.
--
POST BY: lark with PHP News Reader

Sharon11 <2good2b@gmail.com> wrote in
news:1182892748.509537.321130@g4g2000hsf.googlegroups.com: 


>> - Show quoted text -
> 
> I have a website with thousands of simultaneous users
>>From what i understoon, it *must* have pconnect, no?.

no way!!!!  that would mean each user would be using the same connection to 
the database... definitely not what you want.  yes, they all connect to the 
same database, but you don't want them sharing the connection TO the 
database...

On Jun 26, 11:33 pm, Good Man <h...@letsgo.com> wrote:
> Sharon11 <2goo...@gmail.com> wrote innews:1182892748.509537.321130@g4g2000hsf.googlegroups.com:
>
> >> - Show quoted text -
>
> > I have a website with thousands of simultaneous users
> >>From what i understoon, it *must* have pconnect, no?.
>
> no way!!!!  that would mean each user would be using the same connection to
> the database... definitely not what you want.  yes, they all connect to the
> same database, but you don't want them sharing the connection TO the
> database...

But they all need to see the same data, so why use a different
connection for each of them?
(Again sorry for my ignorance, I don't come from the DBA field)

I am currently using myISAM (due to SQL version), will innoDB be
better in case of 1.many selects, very little updates 2. many selects
many updates
Thanks again for all you help guys :)

Sharon11 <2good2b@gmail.com> wrote in
news:1182894202.537752.254020@o61g2000hsh.googlegroups.com: 

>> > I have a website with thousands of simultaneous users
>> >>From what i understoon, it *must* have pconnect, no?.
>>
>> no way!!!!  that would mean each user would be using the same
>> connection to the database... definitely not what you want.  yes,
>> they all connect to the same database, but you don't want them
>> sharing the connection TO the database...
> 
> But they all need to see the same data, so why use a different
> connection for each of them?
> (Again sorry for my ignorance, I don't come from the DBA field)

just change it to mysql_connect and watch your problems go away.  
pconnect is NOT what you want.  with regular 'connect', every request 
for datbase info will open a connection, do the query, then close the 
connection. Otherwise, you have your thousands of users waiting for a 
pconnect to close before they can access the database.  just DO IT!!  
yes, it seems a little counterintuitive, but i think a lot of people 
just starting out choose 'pconnect' before really understanding it, run 
into problems like you have, then change it to mysql_connect. Believe 
me, you do not want mysql_pconnect for your situation. :)


> I am currently using myISAM (due to SQL version), will innoDB be
> better in case of 1.many selects, very little updates 2. many selects
> many updates

Do some digging about MyISAM vs InnoDB for that answer.  Generally, if 
you don't require transactions or row-level locking, MyISAM will be 
faster all around.

Sharon11 <2good2b@gmail.com> wrote:

> I am using php to run queries on my SQL
> For some reason I get many "sleep"ing processes in my DB
> I am using the following lines to run the queries:

> $link = mysql_pconnect($db_server, $db_user, $db_password)
                ^^^^^^^^
Do NOT use pconnect in PHP. Never! NEVER!

Google groups should find an article on that topic in this
newsgroup from me.

> Any idea what can be done to avoid those hung processes?

Those server threads are not "hung". They are just waiting
(and wasting memory) for the associated Apache/PHP client
to get a new request.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums:  http://forums.mysql.com/

>> no way!!!!  that would mean each user would be using the same connection to
>> the database... definitely not what you want.  yes, they all connect to the
>> same database, but you don't want them sharing the connection TO the
>> database...
>
>But they all need to see the same data, so why use a different
>connection for each of them?
>(Again sorry for my ignorance, I don't come from the DBA field)

If *I* am calling AT&T customer service, and *you* are calling AT&T
customer service, can we share phones that are extensions of each
other to make the call at the same time?  No, I'll get the answers
to your questions as well as my own.  And you'll confuse the customer
service rep about what account you're talking about.  No, we need to
talk to separate reps, or make calls at different times.

No, you can't have two web pages running at the same time share the
same connection.  Not even if the queries are all strictly read-only.

>I am using php to run queries on my SQL
>For some reason I get many "sleep"ing processes in my DB
>I am using the following lines to run the queries:
>$link = mysql_pconnect($db_server, $db_user, $db_password) or
>die('err');
>$res  = mysql_db_query($db_database, $sql_string, $link);
>
>Any idea what can be done to avoid those hung processes?

You have a strange definition of "hung" processes.

Is a process that has responded and is waiting for the next command
/ mouse click "hung" while you go to lunch?  No, it's waiting for
input.  Or, in your case, perhaps it's off doing something besides
using the database, like sending a response to the user with the
web browser.

A connection that's not being used at the moment (because of
mysql_pconnect()) will be in the Sleep state.  This is expected.

Don't use mysql_pconnect().  It's a bad idea if you have a lot of
queries.  It's a bad idea if you have only a few queries.  It's a
bad idea if you have NO queries.  It might make sense if you have
a $10,000,000 database server with 512GB of memory, and since you
spent all the budget on the database server, a $1000 web server
with 128MB of memory, so you desparately need to improve the
performance of the web server at the expense of huge resources in
the database server.