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','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. 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=arch...@jab.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','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 dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: INSERT DELAYED and created_on timestamps
-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