Professional Web Applications Themes

Hung processes - MySQL

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...

  1. #1

    Default Hung processes

    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

    Sharon11 Guest

  2. #2

    Default Re: Hung processes

    == Quote from Sharon11 (com)'s article 

    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
    lark Guest

  3. #3

    Default Re: Hung processes

    On Jun 26, 10:51 pm, lark <net> wrote: 
    >
    > 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[/ref]

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

    Sharon11 Guest

  4. #4

    Default Re: Hung processes

    Sharon11 <com> wrote in
    news:googlegroups.com:
     
    >>
    >> 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. "[/ref]
    >
    > I don't. I use the same params all the time...
    > Any other ideas...?[/ref]

    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.




    Good Guest

  5. #5

    Default Re: Hung processes

    On Jun 26, 11:11 pm, Good Man <com> wrote: [/ref]
    > [/ref]
    > [/ref]

    >
    > 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 -[/ref]

    I have a website with thousands of simultaneous users 

    Sharon11 Guest

  6. #6

    Default Re: Hung processes

    == Quote from Sharon11 (com)'s article 
    > >
    > > in general pconnect keeps the conx handler in the database. this from php's[/ref][/ref]
    website: [/ref]
    process. " 
    > I don't. I use the same params all the time...
    > Any other ideas...?[/ref]


    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
    lark Guest

  7. #7

    Default Re: Hung processes

    Sharon11 <com> wrote in
    news:googlegroups.com:

     
    >
    > I have a website with thousands of simultaneous users [/ref]

    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...

    Good Guest

  8. #8

    Default Re: Hung processes

    On Jun 26, 11:33 pm, Good Man <com> wrote: [/ref]
    > [/ref]
    >
    > 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...[/ref]

    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 Guest

  9. #9

    Default Re: Hung processes

    Sharon11 <com> wrote in
    news:googlegroups.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...[/ref]
    >
    > 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)[/ref]

    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. :)

     

    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.


    Good Guest

  10. #10

    Default Re: Hung processes

    Sharon11 <com> wrote:
     
     
    ^^^^^^^^
    Do NOT use pconnect in PHP. Never! NEVER!

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

    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/
    Axel Guest

  11. #11

    Default Re: Hung processes

    >> no way!!!! that would mean each user would be using the same connection to 
    >
    >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)[/ref]

    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.

    Gordon Guest

  12. #12

    Default Re: Hung processes

    >I am using php to run queries on my SQL 

    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.

    Gordon Guest

Similar Threads

  1. Replies: 31
    Last Post: December 9th, 11:06 PM
  2. Hung/rogue/defunct/ processes
    By snogfest hosebeast in forum AIX
    Replies: 0
    Last Post: July 28th, 02:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139