Hi,
>> 1. I think I need to optimise table_cache. It's currently set to the
>> default of 64. There are a total of 70 tables on the system. The
>> manual says you should increase table_cache if the Opened_tables
>> status variable is big. How large is 'big'? This value is currently
>> 274 on my system. By how much should I increase table_cache? As a
>> guide, the my-large.cnf sample config sets this to 256 - would this
>> suit my setup?
>
> Sounds reasonable. It shouldn't make a big impact, but it shouldn't
> hurt either.
I think I understand this now. The table_cache is the maximum number of
tables MySQL can keep open all the time. If your table_cache is lower than
the actual number of tables in use (in my case 64 versus 70), then when a
table that is not already open needs to be used, MySQL must open it, and so
increments the Opened_tables status variable. In an ideal world,
Opened_tables would be nil (or very low), as MySQL would already have all
the tables open, yes?
My Opened_tables figure goes up by about 150-200 every day, so I would
assume that a table_cache of 64 is no-way enough! So, as you say, 256 can't
hurt! I think I'll do that.
>> 2. I currently have sort_buffer at 8 MB. The sample 'large' config
>> suggests 1 Mb. The manual says this should be increased to improve
>> sorts and grouping performance - which my queries do a lot
>> of. Performance seems fine at the moment, but am I unnecessarily
>> using too much memory?
>
> It is allocated on a per-thread basis but only when needed. You can
> try to increase it and see if you notice a difference. But with 512MB
> and few queries doing sorts, it may not be a big deal. However, if a
> lot of your queries require sorting large amounts of data, you may
> really benefit.
So, I suppose it's best to leave it at 8Mb then.
> Really, it's best to run some benchmarks that reflect the workload on
> your server and tweak the parameters to see what difference (if any)
> the changes make.
Hmm, yes. I might have to do that at some point.
>> 3. My record_buffer is set to 16 Mb (at the suggestion of someone
>> else). The sample 'large' config suggests also 1 Mb. I'm not quite
>> sure I understand what this affects. The manual states that: "if you
>> do many sequential scans, you may want to increase this value" - but
>> what kind of query would make a sequential scan?
>
> Queries that require a full table scan (those which don't or can't use
> indexes). "Sequential scans" can mean "full table scans".
So, basically, a large record_buffer is only useful if your queries operate
in a non-efficient fashion by not using indexes? I try to optimise all of my
queries, and I'm pretty sure that reducing it to something like 4Mb would be
alright.
> You didn't mention the key_buffer. If configured well, you can get a
> great performance boost from it.
My key_buffer is currently at 128Mb, but I'm considering upping that to
192Mb or maybe even 256Mb. Probably the former, as I don't have tables with
hundereds of thousands of records to contend with.
Regards,
------------------------------------------------
Basil Hussain ([EMAIL PROTECTED])
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
