MySQLTuner

Hi,
I run mysqltuner this morning and I got these warning:
[!!] Key buffer size / total MyISAM indexes: 12.0G/23.2G
[!!] Key buffer hit rate: 76.9%
[!!] Query cache efficiency: 0.0%
[!!] Temporary tables created on disk: 27%
[!!] Table cache hit rate: 5%

And mysqltuner recommends to adjust these setting:
key_buffer_size (> 23.2G)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 64M)
max_heap_table_size (> 128M)
table_cache (> )

My physical RAM is only 16Gb. I am afraid it will freezed the server if =
I set key_buffer_size (> 23.2G). Any suggestion to optimize it? Thanks =
for any response.




sangprabv
sangprabv@gmail.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQLTuner

In infinite wisdom ï£=BFsangprabvï£=BF wrote:

> My physical RAM is only 16Gb. I am afraid it will freezed the server
> if I set key_buffer_size (> 23.2G). Any suggestion to optimize it?
> Thanks for any response.
>=20

IIRC, on Linux, Mysql will scale back the settings it sees that you are
trying to allocate more memory to mysql than what is actually present in
the box. =20

- Check if you have any duplicate indexes
( or
mk-duplicate-key-checker from maatkit toolkit)

- if you have indexes of varchars, check if you are using column
prefixes for indexes, and if not, try using that (i.e. have a index
on name(10), instead if name)

- Analyze your SELECT queries to see which tables are used most often and
increase the key buffer size to hold at least the indexes from those
table in the memory. (the 80/20 rule might be applicable here)

- If you have been doing lots of deletes, you should get some benefit
from optimize table or by doing "myisamchk -f" (in other words,
rebuilding your index)

--=20
Raj Shekhar
-
If there's anything more important than my ego around, I want it
caught and shot now.
-=20
Read the latest at my blog: "casio's g-shock watch" blog/archives/360-casios-g-shock-watch.html>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQLTuner

--0016363b8b3a2b8c3204832a42ea
Content-Type: text/plain; charset=ISO-8859-1

>
> - Analyze your SELECT queries to see which tables are used most often and
> increase the key buffer size to hold at least the indexes from those
> table in the memory. (the 80/20 rule might be applicable here)
>

Additionally, have a look around the documentation for named key buffer
pools, and allocate the most-used indices to a separate pool so they're
always in-memory, even when the default pool is filling up.

Keep in mind, indices will still be faster than a full tablescan, even if
they're read from disk; so it's no disaster to leave some of the lesser-used
ones out of memory.



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016363b8b3a2b8c3204832a42ea--

Re: MySQLTuner

On Wed, Mar 31, 2010 at 9:47 PM, ï£=BFsangprabvï£=BF il.com> wrote:
> Hi,
> I run mysqltuner this morning and I got these warning:
> [!!] Key buffer size / total MyISAM indexes: 12.0G/23.2G
> [!!] Key buffer hit rate: 76.9%
> [!!] Query cache efficiency: 0.0%
> [!!] Temporary tables created on disk: 27%
> [!!] Table cache hit rate: 5%
>
> And mysqltuner recommends to adjust these setting:
> key_buffer_size (> 23.2G)
> query_cache_limit (> 1M, or use smaller result sets)
>  tmp_table_size (> 64M)
>  max_heap_table_size (> 128M)
>  table_cache (> )
>
> My physical RAM is only 16Gb. I am afraid it will freezed the server if I=
set key_buffer_size (> 23.2G). Any suggestion to optimize it? Thanks for a=
ny response.
>
>
>
>
> sangprabv
> sangprabv@gmail.com

I would not worry much at all about the three top lines of what you
posted. Before I became a DBA I worked as a dev on a not small website
where we used the ultra conservative default configuration. If you do
no know what you are doing you can easily cause far more harm than
good by mistuning your configuration. In fact, if you have 16GB of RAM
and have devoted 12GB to key buffer you have probably significantly
over allocated. Keep in mind that MyISAM using the OS file system
cache to cache table data.

1. Are you currently having a performance problem? If so, what queries
are slow? Posting the query + the explain would help us help you.
2. Are you using any other table types other than MyISAM? If not, why
not? MyISAM has horrible characteristics for many workloads due to a
lack of row level locks. MyISAM also can easily corrupt. Keep in mind
that this will change how a server should be tuned
3. It is probably worthwhile to try to tune table and thread cache to
sane levels.
4. Having well indexed and well written queries will probably help you
far more than any server tunning.
5. How are you going to test any changes to configuration?

--=20
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg