UPDATE query duplication

Codes here !

Moderators: macek, egami, gesf

Hadleigh
New php-forum User
New php-forum User
Posts: 32
Joined: Tue Feb 04, 2003 10:32 am
Location: UK
Contact:

UPDATE query duplication

Postby Hadleigh » Thu Jun 12, 2003 4:08 am

In a statistics system recording the number of visitors to a busy site (3000 + per day) I have noticed a duplication taking place using an update query similar to the one below.

UPDATE stats SET count=count+1

I have proven that this is happening by stamping the query into a seperate table. To do so I had to alter it slightly:

SELECT count FROM stats FOR UPDATE

$count++;

$query="UPDATE stats SET count='$count'";

INSERT INTO dup_proof (proof) VALUES ('$query')

- then seperately:

SELECT DISTINCT proof, COUNT(proof) AS count GROUP BY proof ORDER BY count DESC

I am not sure how or why this is happening. As I understood it, MySQL locked the row for update and qeued the updates for action by default. Can anyone shed any light please?

Thanks,

Hadleigh.

User avatar
mike
New php-forum User
New php-forum User
Posts: 73
Joined: Sun May 04, 2003 4:26 am
Location: Athens
Contact:

Postby mike » Fri Jun 13, 2003 5:21 pm

Do you have 1 or many rows? I think that the "WHERE" statement is missing :?

liquedus
php-forum Active User
php-forum Active User
Posts: 266
Joined: Tue Apr 08, 2003 5:18 am
Location: Ottawa, Canada
Contact:

Postby liquedus » Sat Jun 14, 2003 6:02 am

mysql does lock the table for an update query,

can u post the php code?

Hadleigh
New php-forum User
New php-forum User
Posts: 32
Joined: Tue Feb 04, 2003 10:32 am
Location: UK
Contact:

Postby Hadleigh » Tue Jun 17, 2003 12:42 am

Thanks for the response guys, I left the WHERE clause off the example but have it in the actual code.

Here it is

Code: Select all

$query = "UPDATE stats_page_date SET views=views+1 WHERE (date='$xydate' AND cat1='$cat1' AND cat2='$cat2' AND cat3='$cat3')";
$result = mysql_query($query);


I'm still no closer to knowing why this is happening and am still getting duplication

cheers,

Hadleigh

User avatar
Joan Garnet
Moderator
Moderator
Posts: 387
Joined: Sat Aug 03, 2002 2:56 am
Location: Mars
Contact:

Postby Joan Garnet » Tue Jun 17, 2003 1:03 am

This is a normal update query.. I don't see anything wrong 8O
Is it working fine locally? I mean, is this a matter of the number of people who is connecting?

Hadleigh
New php-forum User
New php-forum User
Posts: 32
Joined: Tue Feb 04, 2003 10:32 am
Location: UK
Contact:

Postby Hadleigh » Tue Jun 17, 2003 1:16 am

There are a lot of concurrent connections, but I'm sure that the amount there are should not hamper the performance. I would estimate that the UPDATE query is executing anything between three and ten times every second (this site is the official site for a Formula One racing team).

I have this stats system running on other sites but they do not create an amount of trafffic significant enough to detect the same problem.

Thanks for your continuing help,

Hadleigh

User avatar
Joan Garnet
Moderator
Moderator
Posts: 387
Joined: Sat Aug 03, 2002 2:56 am
Location: Mars
Contact:

Postby Joan Garnet » Tue Jun 17, 2003 1:36 am

The queries per second average on Mysql server goes up to 8.963 ... so that's not the problem. :D
Maybe we need more info..
database structure, php code relating that part of the script...

Hadleigh
New php-forum User
New php-forum User
Posts: 32
Joined: Tue Feb 04, 2003 10:32 am
Location: UK
Contact:

Postby Hadleigh » Tue Jun 17, 2003 1:53 am

The PHP code is as simple as is above, there is nothing more to it apart from passing the $cat1, $cat2 and $cat3 values to it.

Here's the database structure:

Code: Select all

CREATE TABLE stats_page_date (
  date varchar(80) NOT NULL default '',
  week int(2) unsigned NOT NULL default '0',
  month varchar(10) NOT NULL default '',
  year varchar(10) NOT NULL default '',
  views int(11) unsigned NOT NULL default '0',
  cat1 varchar(80) NOT NULL default '',
  cat2 varchar(80) NOT NULL default '',
  cat3 varchar(80) NOT NULL default '',
  leave_empty tinyint(2) NOT NULL default '0',
  KEY views (views)
) TYPE=MyISAM;


Here's also some results from my testing (see the first post in this thread for more info (please also note the change to the UPDATE query I had to make to enable testing to take place - this could have a bearing on my results)).

Using the following query on a table set up to test the update query:

Code: Select all

SELECT DISTINCT query_string, COUNT(views_to) AS count FROM stats_assess GROUP BY query_string ORDER BY count DESC


I produced proof of the duplication

Code: Select all


UPDATE stats_page_date SET views='494' WHERE (date='2003-05-27' AND cat1='ENGLISH' AND cat2='HOME' AND cat3='HOME')  3
UPDATE stats_page_date SET views='1527' WHERE (date='2003-05-31' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  3
UPDATE stats_page_date SET views='658' WHERE (date='2003-05-21' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='455' WHERE (date='2003-05-22' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='131' WHERE (date='2003-05-23' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='578' WHERE (date='2003-05-23' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='683' WHERE (date='2003-05-23' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='333' WHERE (date='2003-05-26' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='510' WHERE (date='2003-05-27' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='709' WHERE (date='2003-05-27' AND cat1='ENGLISH' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='803' WHERE (date='2003-05-27' AND cat1='ENGLISH' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='33' WHERE (date='2003-05-28' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='83' WHERE (date='2003-05-28' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='183' WHERE (date='2003-05-29' AND cat1='ENGLISH' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='435' WHERE (date='2003-05-29' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='581' WHERE (date='2003-05-29' AND cat1='JAPANESE' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='311' WHERE (date='2003-05-29' AND cat1='ENGLISH' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='325' WHERE (date='2003-05-29' AND cat1='ENGLISH' AND cat2='HOME' AND cat3='HOME')  2
UPDATE stats_page_date SET views='360' WHERE (date='2003-05-29' AND cat1='ENGLISH' AND cat2='HOME' AND cat3='HOME')  2



The first part is my query and the second part (the number) is the number of times it executed. This second number should be one in each case, as it is more, duplication is occuring.

Hadleigh.

User avatar
Joan Garnet
Moderator
Moderator
Posts: 387
Joined: Sat Aug 03, 2002 2:56 am
Location: Mars
Contact:

Postby Joan Garnet » Tue Jun 17, 2003 2:13 am

I would change some things with the database structure:

Code: Select all

CREATE TABLE stats_page_date (
  date DATE NOT NULL,
  week TINYINT(2) unsigned NOT NULL default '0',
  month CHAR(10) NOT NULL default '',
  year CHAR(10) NOT NULL default '',
  views INT(10) unsigned NOT NULL default '0',
  cat1 CHAR(80) NOT NULL default '',
  cat2 CHAR(80) NOT NULL default '',
  cat3 CHAR(80) NOT NULL default '',
  leave_empty TINYINT(2) NOT NULL default '0',
  KEY views (views)
) TYPE=MyISAM;


CHAR --> Faster than VARCHAR.
MySQL changes CHAR to VARCHAR when used in the same table with TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT.

Views --> INT can't be bigger than 10, if you want 11, then use BIGINT

Compare this one with the one you created and try changing it to see if you get a better performance and no errors.

Hadleigh
New php-forum User
New php-forum User
Posts: 32
Joined: Tue Feb 04, 2003 10:32 am
Location: UK
Contact:

Postby Hadleigh » Tue Jun 17, 2003 2:26 am

Okay, thanks Juan. I've changes the INT value (you learn something every day) and left the VARCHARS. I tried to change them but it had no effect - even though the SQL ran correctly. I can't re-create the table as the existing stats need to remain.

I'll let it run for a day or so and then have another look at whether duplication still exists. Hopefully this little change will do the trick but I'll let you know either way through this thread.

Cheers once again,

Hadleigh.

Hadleigh
New php-forum User
New php-forum User
Posts: 32
Joined: Tue Feb 04, 2003 10:32 am
Location: UK
Contact:

Postby Hadleigh » Tue Jun 17, 2003 5:39 am

Unfortunately, I have another duplicate so changing the data type didn't work.

Can you think of anything else it may be? I was lookig at mySQL cache but don't think I'm on the right road with that.

Hadleigh

User avatar
Joan Garnet
Moderator
Moderator
Posts: 387
Joined: Sat Aug 03, 2002 2:56 am
Location: Mars
Contact:

Postby Joan Garnet » Tue Jun 17, 2003 11:06 am

Weird 8O
I read something about a Mysql function that prevents duplication when two people (or more) click an update - insert link at the same time, but can't remember the name....

I have found this
http://www.mysql.com/doc/en/DISTINCT_optimisation.html
mysql.com wrote:When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows


So yoou say it happens all the time, even when the site is not busy ? even at home in your machine ?
A user clicks and the same query is exectuted several times.
Like:
UPDATE stats_page_date SET views='494' WHE.......
UPDATE stats_page_date SET views='494' WHE.......
UPDATE stats_page_date SET views='494' WHE.......
...

Hadleigh
New php-forum User
New php-forum User
Posts: 32
Joined: Tue Feb 04, 2003 10:32 am
Location: UK
Contact:

Postby Hadleigh » Wed Jun 18, 2003 1:23 am

I think I know what you mean, there's a MySQL SELECT statement that is:

SELECT ..... FOR UPDATE

My understanding is that MySQL queues its queries, executing them in order of their priority. An UPDATE has a higher priority than a SELECT forinstance, but the SELECT above knows what it is to do next and keeps the resultset for the users sid.

This problem is happening all the time, but I don't have MySQL installed locally, I have only realised that this problem exists because of the high volume of traffic to this site.

I think your example below, though very interesting, doesn't explain it. I was using the DISTINCT query to analyse whether duplication exists, it is not the cause of the duplication.

Thanks once again for your continuing help,

Hadleigh.

User avatar
Joan Garnet
Moderator
Moderator
Posts: 387
Joined: Sat Aug 03, 2002 2:56 am
Location: Mars
Contact:

Postby Joan Garnet » Wed Jun 18, 2003 12:18 pm

I'm sorry :?
I can't see it...
If I find something I will let you know.

Bye!
:D


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 2 guests

cron