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.