Can you rotate that table daily, thus keeping it small?
You can then move yesterdays data somewhere for post processing.
try an insert if it fails generate the new table from the template, done.
Thus no checking other then once a day when the insert fails.
On Wed, Oct 19, 2011 at 2:54 PM, Johan De Meersman <vegiv...@tuxera.be>wrote:
> ----- Original Message -----
> > From: "Brian Dunning" <br...@briandunning.com>
> >
> > 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=bdao...@lemonfree.com
>
>
