Re: Implement a logging table; avoiding conflicting inserts

[Baron Schwartz]

Fan, Wellington wrote:
 > > Given: MySQL 4.0.12, I need to implement a pageview log with a 

resolution of 1 day.


If you want to brute-force it, I think I would go this route:

create table hits (
day date not null primary key,
hitcount int unsigned not null,
);
insert ignore into hits(day, hitcount) values (current_date, 0);
update hits set hitcount = hitcount + 1 where day = current_date;

No transactions.  Your application logic can perhaps be smart and avoid 
the first query.  But the transactional method with locking in share 
mode is probably going to have a lot more overhead and lower concurrency 
than my suggestion.


Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Implement a logging table; avoiding conflicting inserts

[Fan, Wellington]

 > > Given: MySQL 4.0.12, I need to implement a pageview log with a 
> > resolution of 1 day.
..
> 
> Would the "REPLACE" method work? 
> 
> David


Hmmm...as I read the docs, the "LOCK IN SHARED MODE" seemed to be the
real key to this.


I created a test script and ran:

$ ab -n100 -c100 localhost/hits.php

Where hits.php looks like:

http://dev.mysql.com/doc/refman/4.1/en/innodb-next-key-locking.html
$sql = "SELECT views FROM pageviews WHERE
date='".mysql_escape_string($date)."' AND
url='".mysql_escape_string($url)."' LOCK IN SHARE MODE";

/**
 * If NO records are returned, we need to INSERT with our first pageview
 */
$rs = mysql_query($sql,$dblink);
if( mysql_num_rows($rs) == 0 ) {
  $sql = "INSERT INTO pageviews SET views=1,
date='".mysql_escape_string($date)."',
url='".mysql_escape_string($url)."'";
} else {
  $sql = "REPLACE INTO pageviews SET
views=".(intval(mysql_result($rs,0,'views'))+1).",
date='".mysql_escape_string($date)."',
url='".mysql_escape_string($url)."'";
  //$sql = "UPDATE pageviews SET views=views+1 WHERE
date='".mysql_escape_string($date)."' AND
url='".mysql_escape_string($url)."'";
}
echo $sql;
$rs = mysql_query($sql,$dblink);

/**
 * Barely error-checking...
 */
if ( mysql_affected_rows($dblink) != 1 ) {
  $err = mysql_error($dblink);
  error_log ($err."\n", 3, '/tmp/errors.log');
}
mysql_query('COMMIT',$dblink);

?>


As you can see, I tried *both* the "REPLACE INTO" and "UPDATE" queries
and received *very* strange results. I sum(views) and get roughly 115
views!! I expected 100 or less, but maybe I do NOT understand 'ab'.

So, I added this:
error_log('foo'."\n", 3, '/tmp/errors.log');
exit;

At the top of my script, and ran:
$ ab -n100 -c100 localhost/hits.php

Again, expecting 100 'foo's -- I get roughly 160!

What the hell? I guess I really *don't* understand ab...

Thoughts?

--
Wellington

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Implement a logging table; avoiding conflicting inserts

[dpgirago]

> Given: MySQL 4.0.12, I need to implement a pageview log with a
> resolution of 1 day.
>
> I propose this table:
>
> CREATE TABLE `pageviews` (
>   `id` int(11) NOT NULL auto_increment,
>  `date` date NOT NULL default '-00-00',
>  `url` char(120) NOT NULL default '',
>  `views` mediumint(9) NOT NULL default '0',
>  PRIMARY KEY  (`id`),
>  UNIQUE KEY `date` (`date`,`url`),
>  KEY `url` (`url`)
>) TYPE=InnoDB;>>
>
>
> So that an update will look like:
>  > UPDATE pageviews SET views=views+1 WHERE date='' AND
> url=''
> 
> Of course I need to INSERT the record if one does not match my WHERE.
> This would be easy if I had 4.1 -- "INSERT ... ON DUPLICATE KEY UPDATE",
> I think -- but I do not. 

Would the "REPLACE" method work?

David