Re: High cpu usage after many inserts

On Tue, Feb 24, 2009 at 12:35 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Mon, Feb 23, 2009 at 1:29 AM, Jordan Tomkinson <jordan(at)moodle(dot)com>
> wrote:
> > As per the spreadsheet
> > (http://spreadsheets.google.com/pub?key=pu_k0R6vNvOVP26TRZdtdYw) CPU
> usage
> > is around 50% and starts climbing over 3 hours until we have just under
> > 10,000 rows of data then stays at 99% for the duration of all future
> tests.
> > Once the rows are removed the tests start back down at 50% usage again.
>
> Oh, ok. well that's pretty normal as the indexes grow large enough to
> not fit in cache, then not fit in memory, etc... Are you noticing a
> sharp dropoff in performance?
>

Again as per the spreadsheet, you can see the tests normally take around 130
seconds to complete, but after many rows are inserted they start to take
upwards of 500 seconds. I can leave the server idle for days (over the
weekend infact) and start a new test to reproduce the results (500+
seconds), so i dont think its a delayed write issue (surely 2 days is
enough?)

What configuration options should I be looking at to make sure it fits in
the cache? I have 8GB available and no matter how much I tweak i cannot get
it using any more than 2GB. the DB is almost 4GB in size on disk and as this
is a dedicated sql server for just 1 database, id really like to have the
whole thing in memory if possible.