Thanks! I did not know that.
Just tried it and indeed the Created_tmp_disk_tables did not increase, just
the Created_tmp_tables increased by +1. Still not perfect, but it's better
than I thought and at least in memory.
And for the previous mails, I'm not sure why I ever had the sort_buffer_size
t
When the explain output says "Using filesort", it doesn't necessarily mean
it is sorting on disk. It could still be sorting in memory and, thus, be
reasonably fast. You might check the value of Created_tmp_disk_tables
before and after your query to see for sure.
-Travis
-Original Message---
You make an excellent point. If there are a lot of connections to
that server, many sort buffers may be in use and can squeeze ram out
of the rest of the system. 2M is a pretty good choice.
- md
On Thu, Sep 9, 2010 at 4:08 PM, Ananda Kumar wrote:
> Its not advisiable...as this size will be al
Its not advisiable...as this size will be allocated to all the session and
cause system running out of memory.
It should be set at session and in my.cnf it should be around 2 MB.
Please correct if i am wrong.
regards
anandkl
On Fri, Sep 10, 2010 at 1:26 AM, Phil wrote:
> It's in my.cnf. There
It's in my.cnf. There is 12Gb in the database server and I watch it fairly
carefully and have not gone into swap yet in the past few years.
On Thu, Sep 9, 2010 at 3:43 PM, Ananda Kumar wrote:
> have u set sort_buffer_size at session level or in my.cnf.
> Setting high value in my.cnf, will cause
have u set sort_buffer_size at session level or in my.cnf.
Setting high value in my.cnf, will cause mysql to run out off MEMORY and
paging will happen
regards
anandkl
On Fri, Sep 10, 2010 at 1:10 AM, Phil wrote:
> Even prior to the group by it's still not likely to ever be more than 200
> or
>
Even prior to the group by it's still not likely to ever be more than 200 or
so maximum.
I have the sort_buffer_size at 256Mb so I don't believe it's that either :(
On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman wrote:
> How many rows before the GROUP BY? Group by is, in effect a sorting
> pro
How many rows before the GROUP BY? Group by is, in effect a sorting
process.. perhaps that contains enough data to justify going to disk.
What is the value of the variable sort_buffer_size?
show variables like '%sort%';
- md
On Thu, Sep 9, 2010 at 3:04 PM, Phil wrote:
> On average i
On average it would be between 10 and 40, certainly no more than 100.
On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman wrote:
> The filesort is probably necessary because of the number of rows in
> the result set to be ordered. How many rows do you get out of this
> query?
>
> - michael dykman
The filesort is probably necessary because of the number of rows in
the result set to be ordered. How many rows do you get out of this
query?
- michael dykman
On Thu, Sep 9, 2010 at 1:53 PM, Phil wrote:
> I wonder if anyone could help with a query which I've been unable to prevent
> from using
10 matches
Mail list logo
