Protected: msg 0129

This content is password protected. To view it please enter your password below:

Categories JOB

MySQL performance tunning

重要參數1 = key_buffer_size 這個值只針對 MYISAM engine 有作用 , 這個值用來設定 index 使用多少 memory , mysql 把 index 儘可能的放在 memory 中, 這 key_buffer_size 決定 index 的使用效率 , 效率高的話就是多數的 索引 index 都是存在 memory 中, 那就 select 會快, 一般這個值是 16M , 這兩個狀態值可以用來判斷: Key_reads , Key_read_requests

看以下的例子:
DBM53 的 
show global status like ‘Key_read%’
Key_read_requests 36,479,348,961 
Key_reads 1,009,874,537 

Key_reads / Key_read_requests = 0.02 這樣偏高, 不太好 , 這個值正常是 0.01 , 0.001 最佳

好朋友 phpmyadmin 裡面有很好的解釋:

Key_read_requests 36 G The number of requests to read a key block from the cache.
Key_reads 1,010 M The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

這台的 show global variables like ‘key_%’ 是 128MB
key_buffer_size 134,217,728

set global key_buffer_size=1024M

重要參數2 = table_cache

show global status like ‘Open%’

Open_tables 2,048 

若等於

show global variables like ‘%table%cache%’

table_cache 2,048 

並且 Opened_tables 一直增加 , 那要增加 table_cache 的值 , server 1G RAM 推薦 table_cache 128 ~ 256

沒錯, phpmyadmin 指示

Open_tables 2,048 The number of tables that are open.
Opened_tables 6,529 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

那麼就來修改 table_cache 吧!

set global table_cache=3000;

 

 

 

not finish!

參考網址:

memcached 真的很有意思 …

這個版本 support multiple instance

 * With this version of Memcached Gentoo now supports multiple instances.
 * To enable this you should create a symlink in /etc/init.d/ for each instance
 * to /etc/init.d/memcached and create the matching conf files in /etc/conf.d/
 * Please see Gentoo bug #122246 for more info

05929b98e034c6f1fb1ddd8e4f5d3c2b

1ed5bf4a492226219ef0f75806f020e2

MEMCACHED_BINARY="/usr/bin/memcached"
MEMUSAGE="64"
MEMCACHED_RUNAS="memcached"
MAXCONN="1024"
LISTENON="172.30.0.43"
PORT="11212"  <---- 改!
UDPPORT="${PORT}"
PIDBASE="/var/run/memcached/memcached"
MISC_OPTS=""

啟動後:

 9312 ?        Ssl    0:00 /usr/bin/memcached -d -p 11211 -U 11211 -l 172.30.0.43 -m 64 -c 1024 -u memcached -P /var/run/memcached/memcached-.memcached1.pid
 9366 ?        Ssl    0:00 /usr/bin/memcached -d -p 11212 -U 11212 -l 172.30.0.43 -m 64 -c 1024 -u memcached -P /var/run/memcached/memcached-.memcached2.pid
 9420 ?        Ssl    0:00 /usr/bin/memcached -d -p 11213 -U 11213 -l 172.30.0.43 -m 64 -c 1024 -u memcached -P /var/run/memcached/memcached-.memcached3.pid
 9474 ?        Ssl    0:00 /usr/bin/memcached -d -p 11214 -U 11214 -l 172.30.0.43 -m 64 -c 1024 -u memcached -P /var/run/memcached/memcached-.memcached4.pid
 

下 , netstat -t -u -an 可以看到 ….

c295476c11cc99e65c30b962974254b9

它的 memcache client API for php 有新舊兩個版, 之前只參考到 PHP 官網的 manual 寫的 function 用了它的舊 API call , 造成 connection 不會自動斷線, 需要改用新的才行.

另外, memcache 改用 UDP protocol 比較省 CPU cost , 不過 memcached 的 CPU cost 本來就很低, 所以省的部份是在 memcache client 端, 也就是前台 web server 的 CPU cost 可以省一些.

這個就是 "隱藏版" 的 manual , 竟然要到 cvs 去才看得到 ORZ

http://cvs.php.net/viewvc.cgi/pecl/memcache/README?revision=1.3.2.1&view=markup

initial revision

memcached module for PHP

————————

This module requires zlib library, used for on-the-fly data (de)compression.

Also, you’ll need memcached to use it =)

The memcached website is here:

http://www.danga.com/memcached/

You will probably need libevent to install memcached:

You can download it here: http://www.monkey.org/~provos/libevent/

New API in 3.0

————————

Version 3 introduces a new class "MemcachePool" which implements the new API, the

old class "Memcache" is still retained (but is deprecated) with the same interface

for backwards compatibility. Please note that you need a new memcached version to

use the CAS, default value to increment/decrement, append and prepend, and binary

protocol features.

New INI directives are available to allow control over protocol, redundancy and hash

strategy selection. These are

# The binary protocol results in less traffic and is more efficient

# for the client and server to generate/parse

memcache.protocol = {ascii, binary} # default ascii

# When enabled the client sends requests to N servers in parallel, resulting in

# a somewhat crude reduncancy or mirroring, suitable when used as a session

# storage.

#

# If data integrity is of greater importance a real replicating memcached

# backend such as "repcached" (http://sourceforge.net/projects/repcached/) is

# recommended

memcache.redundancy = <int> # default 1

memcache.session_redundancy = <int> # default 2

# Hash strategy and function selection. The consistent hashing strategy

# is now the default as it allows servers to be added and removed from

# the pool without resulting in all or most keys being re-mapped to

# other server (ie. voiding the cache)

memcache.hash_strategy = {standard, consistent} # default consistent

memcache.hash_function = {crc32, fnv} # default crc32

The directives are used by the MemcachePool constructor so you can instantiate

several pools with different settings by using ini_set() creativly. For example

ini_set(‘memcache.protocol’, ‘binary’);

$binarypool = new MemcachePool();

$binarypool->addServer(…)

ini_set(‘memcache.protocol’, ‘ascii’);

ini_set(‘memcache.redundancy’, ‘2’);

$redundantpool = new MemcachePool();

$redundantpool->addServer(…)

ini_set(‘memcache.redundancy’, ‘1’);

The new interface looks like

class MemcachePool() {

bool connect(string host, int tcp_port = 11211, int udp_port = 0, bool persistent = true, int weight = 1, int timeout = 1, int retry_interval = 15)

bool addServer(string host, int tcp_port = 11211, int udp_port = 0, bool persistent = true, int weight = 1, int timeout = 1, int retry_interval = 15, bool status = true)

bool setServerParams(string host, int tcp_port = 11211, int timeout = 1, int retry_interval = 15, bool status = true)

/**

* Supports fetching flags and CAS values

*/

mixed get(mixed key, mixed &flags = null, mixed &cas = null)

/**

* Supports multi-set, for example

* $memcache->set(array(‘key1’ => ‘val1’, ‘key2’ => ‘val1’), null, 0, 60)

*/

bool add(mixed key, mixed var = null, int flag = 0, int exptime = 0)

bool set(mixed key, mixed var = null, int flag = 0, int exptime = 0)

bool replace(mixed key, mixed var = null, int flag = 0, int exptime = 0)

/**

* Compare-and-Swap, uses the CAS param from MemcachePool::get()

*/

bool cas(mixed key, mixed var = null, int flag = 0, int exptime = 0, int cas = 0)

/**

* Prepends/appends a value to an existing one

*/

bool append(mixed key, mixed var = null, int flag = 0, int exptime = 0)

bool prepend(mixed key, mixed var = null, int flag = 0, int exptime = 0)

/**

* Supports multi-key operations, for example

    * $memcache->delete(array(‘key1’, ‘key2’))

    */

    bool delete(mixed key, int exptime = 0)

    /**

    * Supports multi-key operations, for example

    * $memcache->increment(array(‘key1’, ‘key2’), 1, 0, 0)

    *

    * The new defval (default value) and exptime (expiration time) are used

    * if the key doesn’t already exist. They must be supplied (even if 0) for

    * this to be enabled.

    */

    mixed increment(mixed key, int value = 1, int defval = 0, int exptime = 0)

    mixed decrement(mixed key, int value = 1, int defval = 0, int exptime = 0)

    /**

    * Assigns a pool-specific failure callback which will be called when

    * a request fails. May be null in order to disable callbacks. The callback

    * receive arguments like

    *

    * function mycallback($host, $tcp_port, $udp_port, $error, $errnum)

    *

    * Where $host and $error are strings or null, the other params are integers.

    */

    bool setFailureCallback(function callback)

    }

在這邊看到 兩個 memcache php client 的比較表 : http://code.google.com/p/memcached/wiki/PHPClientComparison

PHP Client Comparison

There are primarily two clients used with PHP. One is the older, more widespread pecl/memcache and the other is the newer, less used, more feature rich pecl/memcached.

Both support the basics such as multiple servers, setting vaules, getting values, increment, decrement and getting stats.

Here are some more advanced features and information.

pecl/memcache pecl/memcached
First Release Date 2004-06-08 2009-01-29 (beta)
Actively Developed? Yes Yes
External Dependency None libmemcached
Features
Automatic Key Fixup1 Yes No
Append/Prepend No Yes
Automatic Serialzation2 Yes Yes
Binary Protocol No Optional
CAS No Yes
Compression Yes Yes
Communication Timeout Connect Only Various Options
Consistent Hashing Yes Yes
Delayed Get No Yes
Multi-Get Yes Yes
Session Support Yes Yes
Set/Get to a specific server No Yes
Stores Numerics Converted to Strings Yes
  1. pecl/memcache will convert an invalid key into a valid key for you. pecl/memcached will return false when trying to set/get a key that is not valid.
  2. You do not have to serialize your objects or arrays before sending them to the set commands. Both clients will do this for you.