UPDATE query taking 5+ seconds?!

[meman1188]

I have a field that counts the number of times a page is viewed. So when the page is loaded i have a script that does `views` = `views` + 1.

 

Here is an example query:

UPDATE LOW_PRIORITY table SET `views` = `views`+1 WHERE ID = 123

 

views is not an indexed field so there should be no updating of indexes at all. Simply a primary key update (ID is the primary key).

 

Sometimes when I run it in phpMyAdmin it takes subsecond (like .0008sec), sometimes it takes over 5 seconds. Can anyone help me optimize this so that it does not take that long?

 

Thanks so much!  It's really drag when the page takes 5 seconds to load when its just waiting on an update.

[Mchl]

Don't use LOW_PRIORITY?

[meman1188]

yea, the low_priority modifier is a recent edition i made to it and it really had no effect at all... I want what the low_priority does, but I don't want mysql to wait for the response.  I guess that would be the optimal situation; I send this query to some cue but the script continues on (even finishes) before it is completed.  Is this possible?

[fenway]

No... only inserts can be truly delayed, AFAIK.

[nankoweap]

yea, the low_priority modifier is a recent edition i made to it and it really had no effect at all... I want what the low_priority does, but I don't want mysql to wait for the response.  I guess that would be the optimal situation; I send this query to some cue but the script continues on (even finishes) before it is completed.  Is this possible?

 

queuing asynchronous work is only logical when the time required to process that work takes longer than it does to queue the work itself and the work is truly independent of the current thread. as such, queuing something as simple as updating a counter is illogical, a waste of time and adds complication where it just isn't required.

 

remove the low_priority modifier, update the counter inline and be done with it.

 

jason

[xtopolis]

Is this even a query problem?  The query looks fine...  Are you on a shared host?

[meman1188]

It's a simple enough query that I don't think there is much improvement to be made on that (although I did take off the low_priority flag).

 

I'm mostly curious if there is anything I can do in the mysql configuration or the table configuration to improve this query.  It seems like a pretty straight forward thing to me that shouldn't be taking 5 seconds to complete.

 

It is a shared host but I have a dedicated resources for MySQL so it shouldn't be affected as much by others.

 

Thanks for the responses!

[fenway]

Run the equivalent select and show us the EXPLAIN.

[meman1188]

SQL query: EXPLAIN SELECT views FROM `feeditems` WHERE ITEMID = 3281268;

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE feeditems const PRIMARY PRIMARY 3 const 1

[fenway]

Hmm... nothing wrong there...

[PFMaBiSmAd]

dedicated resources for MySQL

What exactly does that mean, server wise, and where is that located in relation to the web server?

 

Does this 5 second time occur after a period of database inactivity? It almost sounds like the database server's hard disk is configured to turn off, rather than run continuously like it should on a server and the extra time is what it takes for the disk to come up to speed so that it can be accessed.

[meman1188]

I'm running it on a mysql container at media temple using their grid service.  This is a description of the container:

 

A dedicated MySQL Server with isolated resources and (mt) optimized configuration. Full insight and customization with built-in reporting and control panel tools. Works seamlessly with existing web sites on the (gs).

 

I'm pretty sure its not actually a dedicated box, but actually a dedicate VM running with several other instances.

 

It's possible that this is the first non-cached call on the page.  Everything before this call is a SELECT that could be in the cache.  I would say this is unlikely but possible.  Is there anything I can do about it (besides just get more hits haha)?  Or test to see if it is in fact disc IO.

[meman1188]

This is what I got from an inquiry into the physical setup of the mysql container:

 

The MySQL containers are a dedicated VM running with other instances in a larger database server.  Each of the instances has their own dedicated resources assigned to it.  The instances would share disk I/O since they are on a shared physical server.  These database servers are not a local part of the server and are attached through a network connection.

 

Sounds like to me that it would be unlikely that it would be disk spin up or connection because there are probably other mysql instances reading/writing to drives almost constantly.  Only thing would be if my mysql db was stored on a different NAS drive.