Re: Implement a logging table; avoiding conflicting inserts

2007-09-11 Thread 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='DATE' AND
 url='ARTIST'
 
 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

RE: Implement a logging table; avoiding conflicting inserts

2007-09-11 Thread 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:

?php
/**
 * Import db connection parameters
 */

require $_SERVER['DOCUMENT_ROOT'] . '/generic/app_global.inc.php';

$err = null;
($date = $_GET['date']) or ($date = date('Y-m-d'));
($url  = $_GET['url'])  or ($url  = $_SERVER['HTTP_REFERER']);

/**
 * For testing, get a random date and URL
 */
$dates = array(
  '2007-09-11',
  '2007-09-12',
  '2007-09-13',
  );
$urls = array(
  'URL A',
  'URL B',
  'URL C',
  );

shuffle($dates);
shuffle($urls);

$date = pos($dates);
$url  = pos($urls);

/**
 * Connect
 */
$dblink =
mysql_connect($page_options['host_main'],$page_options['host_main_user']
,$page_options['host_main_pass']);
mysql_select_db('articles',$dblink);


/**
 * BEGIN TRANSACTION
 */
$rs = mysql_query('START TRANSACTION',$dblink);
$debug = 'Begin Transaction said:'.mysql_error($dblink).'';
error_log($debug.\n, 3, '/tmp/errors.log');

// see:
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

2007-09-11 Thread 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]

Implement a logging table; avoiding conflicting inserts

2007-09-10 Thread Fan, Wellington 
Hello Listies,

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='DATE' AND
url='ARTIST'

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. 

So, how should I write my queries so that when a new day dawns, I don't
have 2 connections racing to INSERT?

I suspect I could do something like this (in PHP, line numbers added:

01  $link = connect2Db();
02  $sql = SELECT * FROM pageviews WHERE date='DATE' AND
url='ARTIST' LOCK IN SHARE MODE;
03  if ( mysql_num_rows(mysql_query($sql,$link)) ) {
04  // UPDATE
05  } else {
06  // INSERT
07  }


Also, should I explicitly mysql_query('COMMIT',$link) on line 8?

Thanks all!

--
wellington

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