Response from ZeldorBlat <zeldorblat@gmail.com>:

> On Sep 26, 10:23 am, Brian Kendig <br...@enchanter.net> wrote:
>> On Sep 26, 9:46 am, Brian Kendig <br...@enchanter.net> wrote:
>>
>> > When I query against a view (SELECT * FROM myView) in my test
>> > environment, it comes back immediately. When I do the same
>> > query in my production environment, it takes 2-3 minutes to
>> > come back with the same data.
>>
>> We solved the problem - the buffer pool size on the production
>> server, where the query was running slowly, was set to 256MB.
>> When we *reduced* this to 128MB, the time required by the query
>> went from 199 seconds down to a tenth of a second.
>>
>> Yes, it's counterintuitive that reducing the size of the buffer
>> would speed things up, but this is a server with 2GB RAM being
>> shared by a few vservers, and we're figuring that the host was
>> having trouble with the amount of memory the database vserver was
>> trying to use.
>
> It is counterintuitive and it isn't. I've seen cases where the
> increased buffer pool causes the optimizer to come up with a sub-
> optimal query plan. In one that I saw, the optimizer thought
> that, because of the increased memory available, it should load an
> entire table into memory first (and it was a fairly large table)
> and then join to the worktable rather than join to the table
> itself. This caused the query to run excruciatingly slow.

Granted it may be due to lack of real-world experience, but I have
never understood the recommendation that the InnoDB buffer pool
should be 75% of available RAM.

That is a 1.5GB buffer in the above example.

I can only hope that it was the ideal situation for a dedicated MySQL
server.

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.