sunnyhappylg — Fri, 16 Oct 2009 03:21:00 GMT

原文作者： Peter Zaitsev
原文来源�Q?http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation
译者：叉��荣（Email:�Q�，转蝲��h��明译者和出处�Q��ƈ且不能用于商业用途，�q�者必�I��?/span>

My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

在面试MySQL DBA或者那些打��做MySQL性能优化的�h�Ӟ��我最喜欢问题是：MySQL服务器按照默认设�|�安装完之后�Q�应该做哪些斚w��的调节呢�Q?/p>

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

令我很惊讶的是，有多��h对这个问题无法给出合理的�{�案�Q�又有多��服务器都运行在默认的设�|�下�?/p>

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

��管你可以调节很多MySQL服务器上的变量，但是在大多数通常的工作负载下�Q�只有少数几个才真正重要。如果你把这些变量设�|�正��了�Q�那么修改其他变量最多只能对�pȝ��性能改善有一定提升�?/p>

key_buffer_size - Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload - remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time - it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

key_buffer_size - �q�对MyISAM表来说非帔R��要。如果只是��用MyISAM表，可以把它讄��为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负蝲 -- ��C��Q�MyISAM表会使用操作�pȝ��的缓存来�~�存数据�Q�因此需要留出部分内存给它们�Q�很多情况下数据比烦引大多了。尽��如此，需要��L��查是否所有的 key_buffer 都被利用�?-- .MYI 文�g只有 1GB�Q��?key_buffer 却设�|��ؓ 4GB 的情冉|��非常��的。这么做太浪费了。如果你很少使用MyISAM表，那么也保留低�?16-32MB �?key_buffer_size 以适应�l�予��盘的��时表索引所需�?/p>

innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply - if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_buffer_pool_size - �q�对Innodb表来说非帔R��要。Innodb相比MyISAM表对�~�冲更�ؓ敏感。MyISAM可以在默认的 key_buffer_size 讄��下运行的可以�Q�然而Innodb在默认的 innodb_buffer_pool_size 讄��下却跟蜗牛似的。由于Innodb把数据和索引都缓存�v来，无需留给操作�pȝ��太多的内存，因此如果只需要用Innodb的话则可以设�|�它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 -- 如果你的数据量不大，�q�且不会暴增�Q�那么无需�?innodb_buffer_pool_size 讄��的太大了�?/p>

innodb_additional_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_additional_pool_size - �q�个选项�Ҏ��能影响�q�不太多�Q�至��在有差不多��_��内存可分配的操作�pȝ��上是�q�样。不�q�如果你仍然惌��|��ؓ 20MB(或者更�?�Q�因此就需要看一下Innodb其他需要分配的内存有多��?/p>

innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

innodb_log_file_size 在高写入负蝲��其是大数据集的情况下很重要。这个��D��大则性能相对��高�Q�但是要注意到可能会增加恢复旉��。我�l�常讄��?64-512MB�Q�跟据服务器大小而异�?/p>

innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory - it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

innodb_log_buffer_size 默认的设�|�在中等强度写入负蝲以及较短事务的情况下�Q�服务器性能�q�可以。如果存在更新操作峰值或者负载较大，��应该考虑加大它的��g��。如果它的��D��|�太高了�Q�可能会��费内存 -- 它每�U�都会刷��C��ơ，因此无需讄��过1�U�所需的内存空间。通常 8-16MB ��p��够了。越��的�pȝ��它的��D��?/p>

innodb_flush_logs_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM�?000倍而头大？看来也许你忘了修改这个参��C��。默认值是 1�Q�这意味着每次提交的更��C��务（或者每个事务之外的语句�Q�都会刷新到��盘中，而这相当耗费资源�Q�尤其是没有甉|��备用�~�存时。很多应用程序，��其是从 MyISAM转变�q�来的那些，把它的��D��|��ؓ 2 ��可以了�Q�也��是不把日志��h��到磁盘上�Q�而只��h��到操作系�l�的�~�存上。日志仍然会每秒��h��到磁盘中去，因此通常不会丢失每秒1-2�ơ更新的消耗。如果设�|��ؓ 0 ��快很多了，不过也相对不安全�?-- MySQL服务器崩溃时��׃��丢失一些事务。设�|��ؓ 2 指挥丢失��h��到操作系�l�缓存的那部分事务�?/p>

table_cache - Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

table_cache -- 打开一个表的开销可能很大。例如MyISAM把MYI文�g头标志该表正在��用中。你肯定不希望这�U�操作太频繁�Q�所以通常要加大缓存数量，使得��以最大限度地�~�存打开的表。它需要用到操作系�l�的资源以及内存�Q�对当前的硬仉��|�来说当然不是什么问题了。如果你�?00多个表的话，那么讄��?1024 也许比较合适（每个�U�程都需要打开表）�Q�如果连接数比较大那么就加大它的倹{��我曄��见过讄��?100,000 的情��c�?/p>

thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

thread_cache -- �U�程的创建和销毁的开销可能很大�Q�因为每个线�E�的�q�接/断开都需要。我通常臛_��讄��?16。如果应用程序中有大量的跌��q�发�q�接�q�且 Threads_Created 的��g��比较大，那么我就会加大它的倹{��它的目的是在通常的操作中无需创徏新线�E��?/p>

query_cache If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

query_cache -- 如果你的应用�E�序有大量读�Q�而且没有应用�E�序�U�别的缓存，那么�q�很有用。不要把它设�|�太大了�Q�因为想要维护它也需要不��开销�Q�这会导致MySQL变慢。通常讄��?32-512Mb。设�|�完之后最好是跟踪一�D�|��_��查看是否�q�行良好。在一定的负蝲压力下，如果�~�存命中率太低了�Q�就启用它�?/p>

Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

注意�Q?/strong>��像你看到的上面�q�些全局表量�Q�它们都是依据硬仉��|�以及不同的存储引擎而不同，但是会话变量通常是根据不同的负蝲来设定的。如果你只有一些简单的查询�Q�那么就无需增加 sort_buffer_size 的��g��Q�尽��你�?64GB 的内存。搞不好也许会降低性能�?br /> 我通常在分析系�l�负载后才来讄��会话变量�?/p>

P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.

P.S�Q�MySQL的发行版已经包含了各�U?my.cnf 范例文�g了，可以作�ؓ配置模板使用。通常�q�比你��用默认设�|�好的多了�?/p>

sunnyhappylg 2009-10-16 11:21 发表评论

涩涩视频在线观看免费,国产伦久视频在线观看,色影院视频在线