List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 13 1999 12:16am
Subject:Re: MySQL lockups revisited
View as plain text  
Hi again!

>>>>> "Orlando" == Orlando Andico <orly@stripped> writes:

Orlando> On Sun, 24 Oct 1999 sinisa@stripped wrote:
Orlando> ..
>> Hi1
>> 
>> These lockups are quite rare.
>> 
>> Can you elucidate on the first query in porc output ??

Orlando> It's a delayed-insert handler thread. I did this so that inserts into the
Orlando> radius call-detail-record are handled quickly. The table in question has 3
Orlando> to 4 indexes so inserts aren't too fast..

Orlando> The lockups have happened again (3.22.21) and mysqladmin refresh locks up.
Orlando> Here's the complete processlist (much smaller than previously though):

Orlando> Note: mysqld is unkillable via mysqladmin shutdown or a normal kill. I had
Orlando> to kill -9 it because our Radius authentication was dying a horrible slow
Orlando> query death. I did an isamchk -r -q -q afterwards (anyway this is Solaris
Orlando> so there shouldn't be any problems right?)

isamchk -rq is safe (but isamchk -r isn't as in this case MySQL
creates a temporary file to recover the rows)

Orlando>
> +-------+---------+--------------------+-----------+----------------+-------+--------------------------+------------------------------------------------------------------------------------------------------+
Orlando> | Id    | User    | Host               | db        | Command        | Time  |
> State                    | Info                                                           
>                                      |
Orlando>
> +-------+---------+--------------------+-----------+----------------+-------+--------------------------+------------------------------------------------------------------------------------------------------+
Orlando> | 13    |         |                    | mosaic_db | Killed         | 12639 |
> Waiting on cond          | radius_acct_19991001                                           
>                                      |
Orlando> | 997   | root    |                    | mosaic_db | Query          | 12638 |
> waiting for handler lock | INSERT DELAYED IGNORE INTO radius_acct_19991001
Orlando> (Client_Id, Client_Port_Id, User_Name, Acct_Sessio |
Orlando> | 2433  |         |                    | mosaic_db | Killed         | 9083  |
> Waiting on cond          | radius_acct_19991001                                           
>                                      |
Orlando> | 6488  | root    |                    | mosaic_db | Query          | 9083  |
> waiting for handler lock | INSERT DELAYED IGNORE INTO radius_acct_19991001
Orlando> (Client_Id, Client_Port_Id, User_Name, Acct_Sessio |
Orlando> | 6724  |         |                    | mosaic_db | Delayed_insert | 2     |
> Waiting on cond          | radius_acct_19991001                                           
>                                      |
Orlando> | 14835 | root    | asterix.mozcom.com | mosaic_db | Query          | 5178  |
> Locked                   | INSERT INTO radius_acct_19991001 (Client_Id,
Orlando> Client_Port_Id, User_Name, Acct_Session_Id, Acct_Stat |
Orlando> | 24869 | root    | jaguar.mozcom.com  | mosaic_db | Query          | 39    |
> Locked                   | SELECT SUM(acct_session_time) AS total FROM
> radius_acct_19991001 WHERE    
Orlando> user_name = 'small' |
Orlando> | 25212 | root    | localhost          | mosaic_db | Query          | 43    |
> Locked                   | select sum(Acct_Session_Time) from radius_acct_19991001 where
> User_Name='orly'                       |
Orlando> | 25266 | billing | eden.mozcom.com    | mosaic_db | Sleep          | 6     | 
>                         |                                                                 
>                                     |
Orlando> | 25312 | root    | localhost          |           | Processes      | 0     | 
>                         |                                                                 
>                                     |
Orlando>
> +-------+---------+--------------------+-----------+----------------+-------+--------------------------+------------------------------------------------------------------------------------------------------+


Orlando> Here's the complete query (from the Radiator Radius server):

Orlando> # note DELAYED IGNORE semantics
Orlando> $sth = $dbh->prepare ("INSERT DELAYED IGNORE INTO $tablename
Orlando> (Client_Id, Client_Port_Id, User_Name, Acct_Session_Id,
Orlando> Acct_Status_Type, Acct_Session_Time, Acct_Delay_Time,
Orlando> Acct_Timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())");
Orlando> $sth->execute ($client_id, $client_port_id, $user_name,
Orlando> $acct_session_id, $acct_status_type, $acct_session_time,
Orlando> $acct_delay_time);
Orlando> $sth->finish;

>> Do you use LOCK TABLES ????? 

Orlando> Nope.


>> Linux works quite fine with Solaris, and it is OS, mostly used by
>> us. Can you try binary version, please ??

Orlando> I will try that. However, I must say at this point that my boss is
Orlando> basically finished with what he calls my experiments with free software. I
Orlando> sure hope ORAC-DBA can substitute for an Oracle DBA..  :)


One big difference between linux and solaris/freebsd is that by
default MySQL uses external locking on Solaris/freebsd.  There is a
change that it's the lockd deamon that causes problems.

Can you try starting mysqld with --skip-locking to check this out ?

My guess would however be that there is a bug when you combine INSERT
DELAYED together with INSERT without DELAYED on the same table.  Can
you check that all statements really uses INSERT DELAYED ?

The best option would of course be to modify fork_test.pl to also
crash, in which case it would be easy for us to locate and fix this
problem.

Regards,
Monty

PS: Sorry for the long delay before replying, but I just come back from
    a 2 weeks vacation trip.
Thread
MySQL lockups revisitedOrlando Andico23 Oct
  • Re: MySQL lockups revisitedsinisa24 Oct
    • Re: MySQL lockups revisitedOrlando Andico25 Oct
      • Re: MySQL lockups revisitedsinisa25 Oct
        • Re: MySQL lockups revisitedOrlando Andico25 Oct
          • Re: MySQL lockups revisitedsinisa25 Oct
      • Re: MySQL lockups revisitedMichael Widenius13 Nov
        • Re: MySQL lockups revisitedOrlando Andico20 Nov
          • Re: MySQL lockups revisitedMichael Widenius20 Nov
  • MySQL lockups revisitedMichael Widenius13 Nov