List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 29 2010 9:26pm
Subject:Re: INSERT DELAYED and created_on timestamps
View as plain text  
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?

-- 
	Dan Nelson
	dnelson@stripped
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