On Tue, Jan 30, 2001 at 10:56:56AM +0000, Basil Hussain wrote:
>
> Having just upgraded my MySQL server to the latest 3.23.32 version,
> I thought it would be a good idea to have a review of the
> configuration and try to optimise the setup further. I hope someone
> can help me by answering a few queries I have.
>
> Here's a bit of info about the spec. of the server:
>
> * Pentium III 600 Mhz
> * 512 Mb RAM
> * Data directories stored on RAID 0 partition
> * RedHat Linux 6.2 (with 2.2.16-22 kernel, retrofitted from RH7)
Got it.
> The majority of the queries to the server are from a few web servers
> using PHP4, connecting using persistant connections (it normally
> runs with approx. 100-150 idle threads because of this).
>
> So, I have a few questions:
>
> 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.
> 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.
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.
> 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".
> Any answers gratefully accepted!
You didn't mention the key_buffer. If configured well, you can get a
great performance boost from it.
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878 Fax: (408) 530-5454
Cell: (408) 439-9951
---------------------------------------------------------------------
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
