[轉貼] my.ini: key_buffer_size, table_cache

主題已鎖定
頭像
心靈捕手
默默耕耘的老師
默默耕耘的老師
文章: 8223
註冊時間: 2003-01-01, 09:01
來自: Taiwan

[轉貼] my.ini: key_buffer_size, table_cache

文章 心靈捕手 »

When tuning MySQL, the two most important variables to configure are key_buffer_size and table_cache. You should first feel confident that you have these set appropriately before trying to optimize any other variables. Ideally, key_buffer_size will be large enough to contain all the indexes (i.e. at least the total size of all .MYI files on the server) of your MyISAM tables.

MySQL recommendations (which appears to be on the conservative side...):
256MB system: key_buffer_size=64M table_cache=256 sort_buffer_size=4M read_buffer_size=1M
128MB system: key_buffer_size=16M sort_buffer_size=1M

How to determine, if you need to increase or decrease key_buffer_size:
Look at the "key_reads" (actual reads from the disk) and "key_read_requests" status variables. You typically want "key_read_requests"/"key_reads" > 100. The more "key_read_requests" is larger then "key_reads" the more theses requests do not require a hard disk access. After the server has been running for a few days, look at the current used "key memory space" = Key_blocks_used * key_cache_block_size (default: 1024). See value my running values after 2 weeks.

For table_cache the ideal value will be enough to cache all tables used during pick time. Note that MySQL may be running many queries on the same table at one time, and each of these will use its own "open table". So "table_cache" will benefit from being more then simply the total of your opened MySQL tables.
At pick time, look at the "Open_tables" and "Opened_tables" in status variables. If "Open_tables" is equal to your "table_cache" value and "Opened_tables" is increasing quickly then you might need a larger "table_cache".

My values:
  • key_buffer_size = 220M (180M before)
  • table_cache (default 64) = 400 (350 before)
[/b]
This value can be a lot higher 850? for some (Unix/Linux?). For us, on Windows 2000 Server, the MySQL service would not start when this value was above around 550. See remark 1a, following, for a potential explanation...

Remark 1a:
open-files-limit=
To change the number of file descriptors available to mysqld. If this is not set or set to 0, then mysqld will use this value to reserve file descriptors. If this value is 0 then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of files. You should try increasing this if mysqld gives you the error 'Too many open files'.

My running Status Variables under "Performance-->Keys" (after 2 weeks):
Key_Blocks_used: 175088
Key_read_requests: 248381708
Key_reads: 1440483
Key_write_requests: 1692914
Key_writes: 1110103
Not_flushed_key_blocks: 0
Since Key_Blocks_used * (default) key_cache_block_size = key_buffer_size (after 2 weeks), I will raise key_buffer_size by a little 180M to 220M.

Notes for the future (i.e InnoDB):
If you use InnoDB, it's buffer pool is controlled by: innodb_buffer_pool_size (this cache also holds row level data). This is the equivalent of key_buffer_size for MyISAM key buffers.
innodb_additional_mem_pool_size
This variable stores the internal data structure. Make sure it is big enough to store data about all your InnoDB tables (you will see warnings in the error log if the server is using OS memory instead).
Since MySQL 4.1.1, the buffer block size is available with the key_cache_block_size server variable. Default 1024.

--
資料來源:
http://www.sitebuddy.com/mysql/key_buff ... able_cache
主題已鎖定

回到「架站技術」