The problem was solved by reloading the Table. the query now takes only 3 seconds. But that is not a solution.
The problem is that such phenomenon obscures our judgement used in optimising queries and database.
If a query runs slow we really cant tell if its a problem with query itself , hardware or dead rows.
I already did vacumm full on the table but it still did not have that effect on performance. In fact the last figures were after doing a vacuum full.
Can there be any more elegent solution to this problem.
Richard Huxton wrote:
On Thursday 15 April 2004 08:10, Rajesh Kumar Mallah wrote:
The problem is that i want to know if i need a Hardware upgrade at the moment.
Eg i have another table rfis which contains ~ .6 million records.
SELECT count(*) from rfis where sender_uid > 0;
Time: 117560.635 ms
Which is approximate 4804 records per second. Is it an acceptable performance on the hardware below:
RAM: 2 GB
DISKS: ultra160 , 10 K , 18 GB
Processor: 2* 2.0 Ghz Xeon
Hmm - doesn't seem good, does it? If you run it again, is it much faster (since the data should be cached then)? What does "vmstat 10" show while you're running the query?
One thing you should have done is read the performance tuning guide at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php The default values are very conservative, and you will need to change them.
What kind of upgrades shoud be put on the server for it to become
If you've only got one disk, then a second disk for OS/logging. Difficult to say more without knowing numbers of users/activity etc.
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]