-Original Message-
From: Dan Nelson [mailto:dnel...@allantgroup.com]
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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org