List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:October 19 2011 7:54pm
Subject:Re: Inefficient query is melting my server!!
View as plain text  
----- Original Message -----
> From: "Brian Dunning" <brian@stripped>
> 
> Can someone tell me if what I'm trying to do can be done more
> efficiently? I just got off the phone with Rackspace when my server
> was hung up, and they found a whole bunch of this one same query was
> all stacked up with waiting queries and locked.

Yes, but locked by *what*? You can have a kazillion hung queries, but it's the one right
before them that is holding the lock that's the important one.

> $query = "insert ignore into stats (ip,id,type,date,time) values
> ('".$_SERVER['REMOTE_ADDR']."','$id','listen',NOW(),NOW())";

Switching to InnoDB will allow concurrent inserts, but may impact your performance and
application on other points. MyISAM also allows concurrent inserts provided you have no
gaps in the table (that is, only ever inserted into it). Neither of those is going to
help if the troublesome lock is held by a statement that locks the entire table, though.

> CREATE TABLE IF NOT EXISTS `stats` (

A stats table? Are any automated maintenance jobs running on that? I recently had a run-in
with Cacti, which did an OPTIMIZE TABLE every day at midnight - presumably to rebalance
the indices after cleanup jobs - which, due to the sheer volume of data, took over an
hour. That's an hour that table is fully locked. Not saying that's your problem, just a
pointer.

Remote hands are useful, but not as much as being able to get onto a machine and see
what's happening for yourself.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Thread
Inefficient query is melting my server!!Brian Dunning19 Oct
  • Re: Inefficient query is melting my server!!Johan De Meersman19 Oct
    • Re: Inefficient query is melting my server!!Basil Daoust20 Oct