MySQL Performance: Replication, Partition and Memcached

3 main step to increase mysql performance:

1. Split Read and Write
2. Horizontally partition
3. Implement Memchache
4. Setup Replication

• You can spread your reads with replication, and that helps a lot, but you can’t spread writes (they have to process on all machines) and they’ll eventually consume all your resources. You’ll find yourself adding replicated slaves at an ever-increasing rate to make up for the diminishing returns each additional slave provides.

• The next logical step is to horizontally partition your dataset onto different master/slave clusters so you can spread your writes, and then teach your application to connect to the correct cluster depending on the data it needs.

• With memcached you can reduce your database reads to a mere fraction, leaving the databases to mainly do infrequent writes, and end up getting much more bang for your buck, since your databases won’t be blocking themselves doing ACID bookkeeping or waiting on writing threads.

How to setup Memcached server

Few Imp Links for detailed Study: http://www.danga.com/memcached/

Installing memcached

yum install memcached
yum install php5-memcache

Configure and Setting MemCache

• After installing these two packages next step is to configure memcached.conf file. ( /etc/memcached.conf )

• After that edit the memecached.ini file. ( /etc/httpd/conf.d/memcache.ini )

A sample memcache.ini file

; uncomment the next line to enable the module
extension=memcache.so
[memcache]
memcache.dbpath="/var/lib/memcache"
memcache.maxreclevel=0
memcache.maxfiles=0
memcache.archivememlim=0
memcache.maxfilesize=0
memcache.maxratio=0
session.save_handler = memcache
session.save_path = "tcp://192.168.1.1:11211?weight=1,tcp://192.168.1.2:11211"

Camand Line Memcache

memcached -d   ==> Start memcache as a background process
memcached -vv ==> Start memcached as a foreground process

Another method for checking whether memcached was enamble or not is to check the phpinfo() function