On Tue, May 01, 2001 at 04:46:39PM -0700, Bryan Coon wrote:
>
> Our database is large, and getting larger, with several tables
> approaching the 1gig mark. In addition, the database will be moving
> to a dedicated node on a beowulf cluster.
Cool... :-)
> For our users, we are not particulary interested in squeezing every
> last drop of performance out of MySQL, but I would be interested to
> know if there are obvious things that I should do to optimize our
> performace.
>
> For example, compiling my own mysql with certain flags, or perhaps
> startup options, etc.
>
> I searched the usenet and mysql site, and found some information but
> would like to hear some experienced advice before I jump in with
> both feet.
Well, just wait a few weeks for my article in the next issue of Linux
Magazine. :-)
But before that happens, here are some ideas... There are two
approaches to optimization, and you should use both.
First is optimizing your application. This is generally just making
sure your queries are fast (well indexed), you're only retrieving the
data you need, you aren't indexing columns which will never benefit
from indexes, you're caching data in your app which can be cached,
etc.
Second is server tuning. You can look at increasing the size if the
key_buffer, record_buffer, and so on in your /etc/my.cnf (or similar)
file. Try to get an idea how efficient things are currently. I often
use mytop (http://public.yahoo.com/~jzawodn/mytop/) to gather some
info about my system before, during, and after tuning. It doesn't give
you everything you'll need (yet!), but it's a decent start. You'll
probably want to look closely at the output of "SHOW VARIABLES" and
"SHOW STATUS" and learn more about what some of them mean.
And, of course, we're glad to field specific questions on this list.
(This reminds me... I'm thinking of another patch to the MySQL manual
which explains some more of this stuff. Just need to find the time to
do it. Things are getting more, uh... "interesting" as the number of
table handlers expand. With ISAM, MyISAM, InnoDB, BDB, Gemini, and
HEAP, there is more room for both improvement and error.)
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 439-9951
MySQL 3.23.29: up 118 days, processed 734,376,106 queries (71/sec. avg)
---------------------------------------------------------------------
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
