Prajakt Deolasee <bugzilla.prajakt@gmail.com> writes:
> Thanks guys.. I will try of the suggested steps. But one thing that I
> do not understand that why should the performance deteriorate with
> exactly same data with subsequent tests. And its juts gets worse as I
> keep running it.
This is surely a matter of dead-row bloat. Are you vacuuming the tables
between runs? Do you have the FSM parameters set high enough to keep
track of all the dead space you create by UPDATEs or DELETEs?
I was just looking at the "sql-bench" stuff that MySQL distributes. One
of the tests that we look particularly awful on goes like this:
* build table with 300,000 rows.
* full-table update (UPDATE foo SET col = something)
* repeat full-table update 10 times
With a VACUUM after each update, things would be fine, but since they
don't have one in the loop the table bloats to 10 times its normal size
(as do its indexes) and performance goes quickly south ... and not only
on that test, but on the subsequent ones using the same table ...
regards, tom lane