List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:September 29 2010 9:31pm
Subject:RE: INSERT DELAYED and created_on timestamps
View as plain text  
 

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped] 
> Sent: Wednesday, September 29, 2010 2:26 PM
> To: Daevid Vincent
> Cc: 'MySQL'
> Subject: Re: INSERT DELAYED and created_on timestamps
> 
> In the last episode (Sep 29), Daevid Vincent said:
> > I'm doing some reading on INSERT DELAYED
> > http://dev.mysql.com/doc/refman/5.0/en/insert.html
> > 
> > I have a user_log table:
> > 
> > CREATE TABLE `user_log` (
> >   `id_user_log` bigint(20) unsigned NOT NULL auto_increment,
> >   `id_user` int(10) unsigned default '0',
> >   `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP 
> on update CURRENT_TIMESTAMP,
> >   `type` 
> enum('View','Action','Admin','Search','Login','Logout','Access
> ','General',' API') NULL,
> >   `source` enum('web','mobile') character set latin1 
> collate latin1_general_ci default 'web',
> >   `body` text character set latin1 collate latin1_general_ci,
> > ) ENGINE=InnoDB
> >
> > We are noticing a lot of these in the logs however:
> > 
> > Sep 29 03:05:02 pse05 mysqld[14640]: TRANSACTION 0 
> 62715480, ACTIVE 0 sec, process no 14639, OS thread id 
> 2904791952 inserting
> > Sep 29 03:05:02 pse05 mysqld[14640]: mysql tables in use 1, locked 1
> > Sep 29 03:05:02 pse05 mysqld[14640]: LOCK WAIT 4 lock 
> struct(s), heap size 320, undo log entries 1
> > Sep 29 03:05:02 pse05 mysqld[14640]: MySQL thread id 8330, 
> query id 799424 10.10.10.46 OMT_Master update
> > Sep 29 03:05:02 pse05 mysqld[14640]: INSERT INTO user_log 
> (`id_user`, `type`, `source`, `body`) VALUES ...)
> > Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR 
> THIS LOCK TO BE GRANTED:
> > 
> > So I'm thinking we could use the DELAYED or LOW_PRIORITY.
> 
> INSERT DELAYED only works on MyISAM, MEMORY, and ARCHIVE 
> tables.  You'll get
> a 1616 error if you try it on InnoDB.  MySQL 5.5 is supposed 
> to have a lot
> of concurrency improvements in; can you test your application 
> on that and
> see if it's any faster than 5.0?

Yeah, I just discovered that. However LOW_PRIORITY works on InnoDB tables
it seems (at least, no error). But my original question still applies (even
if for curiosity sake). Does mySQL account for the "DELAY" or
"LOW_PRIORITY" time it took to write to the DB and adjust the timestamp
accordingly or does it do the timestamp at the time of actual write vs. the
time it was originally called?

Thread
INSERT DELAYED and created_on timestampsDaevid Vincent29 Sep
  • Re: INSERT DELAYED and created_on timestampsDan Nelson29 Sep
    • RE: INSERT DELAYED and created_on timestampsDaevid Vincent29 Sep