INSERT DELAYED and created_on timestamps

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','Acce ss','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.

My concern is the created_on time.

Is there any difference in the actual timestamp recorded in the database if
I use:

INSERT INTO user_log (id_user) VALUES (3);

INSERT DELAYED INTO user_log (id_user) VALUES (3);

INSERT LOW_PRIORITY INTO user_log (id_user) VALUES (3);

INSERT INTO user_log (id_user, created_on) VALUES (3, NOW());

INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3, NOW());

(or set the date via PHP):

'INSERT INTO user_log (id_user, created_on) VALUES (3, '.gmdate().')';

'INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3,
'.gmdate().')';

My point is, is mySQL smart enough to know what the time WAS when the
INSERT was supposed to be written by default, or if I DELAY it will it
process the NOW() at INSERT time or DELAYED time or what time is NOW() and
lastly if I set it with gmdate() in PHP, then that seems like it's the
exact server time at the right moment??


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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','Acce ss','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@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: INSERT DELAYED and created_on timestamps

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@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','Acce ss
> ','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=gcdmg-mysql-2@m.gmane.org