Issue with UPDATE queries stalling SELECT queries

[donmcnellis]

I am currently using mySQL 4.1x, though that should not matter.

I have a sizable database--1.8 gigs with 50 million records in it. The table with which we are having problems is about 300 megs in size and has 2 million records. The database is fully normalized and indexed and all of our queries are written to make best use of the indexes (they have been individually checked).

The database is accessed by a web server that executes mostly SELECTs on the data and a shell script that runs 24/7 importing data into the database. The shell script is split into two scripts that run simultaneously. The first checks various RSS feeds to see if they have new links to be imported. If they do, it adds the link's ID to the table. The other script selects the new links then, gets the data, and runs UPDATEs to add the rest of the data.

What seems to be happening is this:

(1) A SELECT query is sent to mySQL by my website and obtains a write lock on the table. This SELECT query takes a long time (a long time being more than 10 seconds or so) to execute--it has a number of joins and has to process a lot of records to generate aggregates.

(2) An UPDATE query is sent to mySQL by a shell script to update a record in that table. mySQL waits until the above SELECT query is finished to process this UPDATE.

(3) Subsequent SELECT queries are sent to mySQL. mySQL waits until after the UPDATE query is finished to process these.

I'm not sure what's happening here. Is the thread processing the UPDATE query queuing up a WRITE LOCK so the subsequent SELECTs are unable to obtain READ LOCKS until that WRITE LOCK has been unlocked? Or is there something else causing this?

This problem does not seem to happen with any queries other than UPDATEs. I know that mySQL can process INSERTS without obtaining a WRITE LOCK so long as the table is contiguous. If we rewrite the application to only use INSERTS (we can use a cache table to achieve this--we've already investigated the architectural changes needed), then will that solve our problems?

As an aside, is it possible to use mySQL replication between two databases on the same server (so that we could process all updates to one of the databases then feed those updates en masse to a second database using mySQL replication?) That was our other thought, but we have only one mySQL database server (and cannot afford another).

Thanks in advance (or AtDhVaAnNkCsE )

[longneck]

(1) A SELECT query is sent to mySQL by my website and obtains a write lock on the table. This SELECT query takes a long time (a long time being more than 10 seconds or so) to execute--it has a number of joins and has to process a lot of records to generate aggregates.

(2) An UPDATE query is sent to mySQL by a shell script to update a record in that table. mySQL waits until the above SELECT query is finished to process this UPDATE.

(3) Subsequent SELECT queries are sent to mySQL. mySQL waits until after the UPDATE query is finished to process these.

this behavior is correct. mysql has a lock queue (actually, there multiple queues, but i'm simplifying) that is proccessed pretty much FIFO. an update requires a write lock, which also prevents a read lock.

This problem does not seem to happen with any queries other than UPDATEs. I know that mySQL can process INSERTS without obtaining a WRITE LOCK so long as the table is contiguous. If we rewrite the application to only use INSERTS (we can use a cache table to achieve this--we've already investigated the architectural changes needed), then will that solve our problems?

it could.

As an aside, is it possible to use mySQL replication between two databases on the same server (so that we could process all updates to one of the databases then feed those updates en masse to a second database using mySQL replication?) That was our other thought, but we have only one mySQL database server (and cannot afford another).

yes, but i don't think that will solve your problem. updates on a slave still require write locks. not to mention that you will need twice as much disk space.

[donmcnellis]

Thanks a lot for confirming my thoughts.

My thought with the replication is that we could do the updates in bulk once every hour or so--the data is not time-critical, so updates don't need to be immediate. And we have plenty of disk space. Having two instances of the same database on one server didn't seem like a good option to me, though, because I assumed it would decrease performance.

Do you have an alternative suggestion perhaps of how this problem might best be resolved?

Again, thanks.

James.

[longneck]

let me try and summarize: you grab detailed data fairly frequently. you need to be able to get summaries of that data with an immediacy of about an hour.

that lends three similar options:

1) collect the data less frequently, like every 45 minutes. turn on the query cache.
2) create a second set of tables in a separate database. collevt frequently to those tables. once an hour, move the records from the other database to the original database. turn on the query cache.
3) create a set of summary tables that are updated only once an hour or so. rewrite your reporting queries to draw from the summary tables.