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 )