One of our engineers first installed MySQL on one of our Sun boxes
which was doing nothing more than MySQL... It seems we also put it on
the server and turned it on... it behaved very badly. Essentially
when we started to investigate MySQL and find out if we could use it
we discovered that our Sun box with four processors and 4Gbytes of
ram was running MySQL in 64M of memory... it's an easy mistake to
make, and the lesson here is that out of the box (as it were) MySQL
settings are a little on the low side for performance... but work
well for a shared environment where you may have web server, mail
server and more all running on the same box. If you want MySQL to
sing... you are going to have to do a lot of tuning.
On the table_cache issue... We have about 40 tables per database, and
some of our servers have 30 databases. Our servers have as many as
500 connections... one server at random which has an uptime of 60
days shows:
mysql1 (none): show status like 'open%_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2748 |
| Opened_tables | 3288 |
+---------------+-------+
2 rows in set (0.01 sec)
mysql1 (none): show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 4096 |
+---------------+-------+
1 row in set (0.01 sec)
So we have a table cache value, but it's clearly on the high side and
could be lower. Yours at 64 is on the low side.
While I won't ignore table cache as being important, there are many
many performance tuning things that need to get done to have an
impact on the server.
Firstly (knowing the type of things you are doing) InnoDB will likely
be a far better choice for most of your tables than the default
database engine, myisam. You need to tune the machine to run InnoDB,
and then convert your data to InnoDB. If you are using MySQL 4.1
(which I can't remember) I would advise using innodb_file_per_table.
If you are looking at upgrading to 4.1 I'd do that first before
switching to innodb_file_per_table... it's a little hard to claim
back the shared table space after the fact. If not 4.1 then go with
InnoDB and build a big enough shared table space file set to hold all
your data with room to spare. We typically build it with 20 2Gbyte
files... for 40Gbytes of InnoDB table space.
Decide how much memory you have to run MySQL... i the server does
only MySQL, this is easy... if it's also a web server running Apache
and so on, then you have to decide the mix. Assuming MySQL only give
InnoDB 80% of the server's total memory, up to certain limits with 32
bit operating systems and the like... For OS X we found these are
pretty much the magic numbers for max values if you have more than
2Gbytes of ram but can't handle 64 bit:
innodb_buffer_pool_size=1850M
innodb_additional_mem_pool_size=256M
innodb_log_files_in_group=2
innodb_log_file_size=250M
innodb_log_buffer_size=20M
innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=0
innodb_lock_wait_timeout=30
Once you convert everything to InnoDB the regular MySQL buffers have
less importance, but should still have some values.
InnoDB or not the query_cache is a good thing, but don't set it too
high... We are at 128Mbytes and that's a little higher than we
need... it appears we could live in under 64Mbytes, and our query
cache handles about 25% of our queries... properly set it's a good
thing.
So... most likely switching to InnoDB will improve performance...
Managing all your memory settings and caches so that the ones that
matter have values that will help will make a great deal of
difference... of course to do this you'll need to make a my.cnf file
and install it where MySQL will look for it.
Also important for tuning is watching the slow queries, finding out
if there are moe things you can do with indexes, or if there are
other ways to optimize the queries. Turn on the slow query log...
leave it set to the default 10 seconds... find out what queries are
running longer than 10 seconds and figure out how to optimize them...
changing indexes, changing the query etc... Once you have worked that
out and your slow query log gets few hits, reduce it to 5 seconds and
work through those queries... again reduce it further as you work out
the performance issues and you'll find that you are streaming along.
There's a lot more that can be done with specific memory settings and
so on... but I think I've given you a handful of things to get
started on, and you can come back for more when you have made some
headway on this part.
Best Regards, Bruce
On Sep 7, 2005, at 4:28 PM, Scott Haneda wrote:
Unless I am totally off base here, the mysql docs tell very little in
regards to how to performance tune mysql.
So far I have found this article to help:
<http://www.databasejournal.com/features/mysql/article.php/
10897_1402311_3>
But it still leaves me with a ton of questions.
For starters, and don't laugh, I just installed mysql and let it run,
started adding sites to it and such. The machine has 1 gig of ram
in in,
but at times, I could ask it were a little snappier. It is a 1.2Ghz
machine.
First thing I did last night was to try to get a hnalde on this.
From what
I can tell, I have no my.cnf file in place, so there must be some
default
settings that are compiled in. I set up a cron job to run the
following:
show variables like 'table_cache'
show status like 'open%_tables'
I have been running this once a minute for a day now, how long
should I run
it to get an idea of what I need to change to better suit my
servers load
usage?
First entry after a mysql restart:
Wed Sep 7 03:18:00 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 1050
-----------
Last entry as of now:
-----------
Wed Sep 7 16:27:01 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 4407
So it looks like Opened_tables is going to increase forever at the
rate I
have it, which I am guessing is not a good thing, but not sure what
I need
to do to fix this.
I am really looking for some pretty detailed docs on exactly what I
can do
in my.cnf to make this work out better.
Thanks again, and if there are any questions that would help me get
a better
answer, please let me know.
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
