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.

The environments are running on identical hardware with identical
software revisions (MySQL 5.1.17 on Linux) and almost-identical data
(we refreshed the test environment with production data a few days
ago).

How can I diagnose this, and find out why the query is taking so long
in production?

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.

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.

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.