Re: [ SOLVED ] select count(*) very slow on an already

On Apr 16, 2004, at 4:23 AM, Rajesh Kumar Mallah wrote:

>
>
>  I am running an update on the same table
>
>  update rfis set inquiry_status='APPROVED' where inquiry_status='a';
>
>  Its running for past 20 mins. and top output is below.
>  The PID which is executing the query above is 6712. Can anyone
>  tell me why it is in an uninterruptable sleep and does it relate
>  to the apparent poor performance? Is it problem with the disk
>  hardware. I know at nite this query will run reasonably fast.
>

I've had this problem recently.  The problem is simply that the disk
cannot keep up.  Most likely you don't see it at night because traffic
is lower.  There are only 2 solutions: 1. get more disks 2. write to
the db less

The machine I was running on had a single(!) disk.  It was a quad xeon
so there was plenty of cpu.   I'd see 8-9 processes stuck in the "D"
state.  Doing a simple ls -l somefile would take 10-15 seconds and of
course, db performance was abysmal.

I had a lowly P2 with a few disks in it that was able to run circles
around it for the simple fact the machine was not waiting for disk.
Again, proof that disk is far more important than CPU in a db.

good luck.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/