my.cnf and MySQL Tuning

You have an urgent performance case in a MySQL system and you have to get several low hanging fruits fast. What should you do?


MySQL Tuner is Your First Try!
MySQL Tuner is an open source Perl script that analyzes your system and let you know regarding hot spots. You can find a nice example of its results bellow:

-------- Performance Metrics -------------------------------------------------
[--] Total buffers: 34.0M global + 2.6M per thread (1000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.6G (16% of installed RAM)
[OK] Slow queries: 0% (141/99M)
[!!] Highest connection usage: 100%  (1001/1000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/5.0G

my.cnf is the First Answer!
my.cnf, MySQL configuration file is probably the first place to check. In this file you configure the small items that make the difference between a local store webpage to the next hit in the web application. The first place to take a look at is your my.cnf file that is located at /etc/my.cnf.


Where Can I Find my.cnf Examples?
Lucky us, MySQL provides out of the box 5 my.cnf best practices for different machine size. These files are located at /usr/share/doc/mysql-server-5.X.XX/ and in includes: my-small.cnf, my-medium.cnf, my.large.cnf, my-huge.cnf and my-innodb-heavy-4G.cnf. Yet, although the naming, these configurations are targeted to small systems and none will fits even a 16GB RAM machine (How large? take a look at the comment in the head of my-huge.cnf: "This is for a large system with memory of 1G-2G").


So What Should I Do?
Go over the sample configurations and analyze what you need to optimize to meet your actual needs. Don't forget to check your current system configuration using SHOW VARIABLES before getting into the cold water. Some of the following hints will help you meet your target:

1. key-buffer: 
1. Check using SHOW GLOBAL STATUS that the key_reads/key_read_requests ratio is less than 0.01 to avoid disk fetch.
2. If you fail to meet this ratio, enlarge the key-buffer parameter.
2. sort_buffer_size: 128-512MB, will be allocated for sort by each thread.
3. read_buffer_size: 8M, will be allocated for sequential scan by each thread.  
4. write_buffer_size: 8M, will be allocated for plain index scans by each thread.
5. thread_concurrency: should be equal to 2XAvailable Cores.

What Next?

Well it's time to scan the SQL statements that takes too long:

1. Enable the slow query log in the my.cnf: log-slow-queries=/home/mysqld-slow.log 
2. Set the minimum processing time to be included in the log: long_query_time = 1
3. Analyze the results: tail -n 1000 /home/mysqld-slow.log
4. Verify there is no "connection leakage": there are not too many open connections in the database. Check it using SHOW PROCESSLIST;
5. Analyze locking using SHOW STATUS LIKE 'Table%'; and SHOW ENGINE INNODB STATUS if you use InnoDB as your database engine.

Keep Performing,

Moshe Kaplan

How to Replicate Part of the Tables in MySQL

When you build a DWH or another intensive processing on a database slave copy, you may not want to replicate the whole database. 


How can you replicate only some of the tables or even part of existing tables to a slave?
In General there are 4 ways to replicate data:

1. Database Definition: You can define what database names will or won't be replicated. You can place all the tables that need to be replicated in one database and all the other in another one. That way you can replicate only the first database.
2. Tables Definition: You can define what specific table names you can replicate and what not.
3. Part of a Table: Replication by SELECT limitation is supported starting from version 5.1.21 instantly. Therefore, you can easily replicate only several columns (replication discards non existing columns if they do not exist in the slave). In earlier versions, you could perform Vertical Sharding, where you could keep some of the columns in one table, and the other in another table. Then you may replicate only one of the tables.
4. Part of a Table: Replication by WHERE limitation is not supported instantly. Therefore, you cannot easily replicate only some of the rows from a single table. Yet, you can perform Horizontal Sharding, where you can keep rows groups in different tables. Then you may replicate only some of the tables to your slave.

When Should You Avoid Partial Replication?

If your slave is being used as a copy for DRP or HA, you may still want it to fully match your master. If the copy is done for specific processing and you want to avoid unneeded replication, this is definitely the right tool for you.

Syntax is the King

Most of the syntax is performed in the slave and it is detailed in Sunny Walia post.

Keep Performing,

Moshe Kaplan

MySQL Partitioning. One step before Sharding.

Many of you may consider Sharding to meet your large scale database demands. However, in some cases it seems too early since you may not have the needed bandwidth in the current phase or you just consider to adapt a Sharding COTS solution like Scalebase and you need a solution for mid term.


I don't have time so what should I do?
The answer is simple: you may choose MySQL built-in mechanism named MySQL Partitioning. 
This mechanism partitions your table using one of 4 different rule types. It main solution is keeping all this process hidden from the applicative DBA and the software engineer. Try one of this methods:

1. Range Partitioning: partition by column values. Most fit for known ranges like archive by date range.
2. List Partitioning: similar to the above based on discrete values. Most fit for archive by years.
3. Hash Partitioning: partition by hash function defined by user. Most fit when data ranges are that are unknown, but you know the spread of them. Should be used when you partition a table according to a foreign key. Please notice that in any case, the hashed key must be included in table primary key.
4. Key Partitioning: similar to Hash, but this time the MySQL Server takes care of the function. Most fit that the data distribution is uniform. Partition key should include the primary key.

How do I declare that?

Using DDL. just add few more lines to your CREATE TABLE statement and you will receive the partitioning. For example to split by year use the following statement:

CREATE TABLE grades (

...    

year INT NOT NULL,

...

)

PARTITION BY LIST(year) (

    PARTITION p2009 VALUES IN (2009),

    PARTITION p2010 VALUES IN (2010),

    PARTITION p2011 VALUES IN (2011),

    PARTITION p2012 VALUES IN (2012)

);

What do I get?

Mayflower has presented very nice numbers with 200M records table partitioned to 400 parts. They reached 4000 INSERT/UPDATE and 6000 SELECT statements per second.

Bottom Line

If don't have a lot of time and you need an instant solution, go for Partitioning

Keep Performing,

Moshe Kaplan

MySQL DRP Done Right

When your systems are up and running and money is getting into the bank, it is the best time to ask yourself: What if my system fails? What if my data center (yes, even your cloud one) goes down?


The Instant DRP Solution
The instant solution is preparing a backup data center. Then you should make sure that the web and application servers will be ready to take over when needed. Yet, what about taking care of your data? How will the database be managed across two different data centers?


Many cloud, web, mobile and service providers base their systems on MySQL. Therefore, this post is dedicated to best MySQL DRP strategies..


Fail and Then Manually Recover
The most simple and easy to implement method is the Master-Slave schema. This method supports:

1. "Master": A single database server that serves for write and read.
2. "Slaves" One or more servers that serves for read only.

When the master fails, you can promote one of the slaves to become the master.
This method can also be used to reduce stress on the master and enable various partitioning modes.


Fail and Recover Automatically
A more complex way to implement, but faster to recover (if nothing goes wrong) is implementing the Master-Master schema. In this schema both servers serve as masters and recover when one fails. Please notice that this method is a little bit tricky and you should avoid conflicts since the conflict resolution is poor:

1. You should need to either avoid auto increment fields or make sure that only one server is an active master at a time.
2. You should avoid same record modified by both parties.

Do It the Cloud Way
Cloud providers propose these days cloud based database (PaaS). These solutions remove the database management headache from your task list. Amazon for example provides the Rational Database Service (Amazon RDS). RDS provides a high available managed MySQL instance that supports instant recovery to any point in time. This service is more expensive than having your own MySQL instance, but if you are a medium shop, it may be the best offer for you.


Bottom Line
Select your MySQL DRP method based on your resources, recovery needs and the effort you willing to allocate. Choose Master-Slave for novice, Master-Master for experts and the cloud way to avoid the headache.


Keep Performing,
Moshe Kaplan 


P.S feel free to contact for DRP and MySQL consulting

Faster Website? CDN Might be the Answer!

Why Our Japanese Users are Angry?
A common question when you setup a new SaaS or just a fancy website is "Why do my remote users have hard time with my application?" while when I'm in a physical location that is close to my hosting provider I get a relatively good user experience.


It's the TCP/IP Doctor Watson
Window Size is a significant parameter in the TCP/IP stack. This parameter controls how much data can be sent by either party before receiving an ACK from the other. If your Window Size is set to 8KB and the round trip (RTT) between you and the server is 20ms, you could get 400KB/s (1sec/20ms*8KB) or ~4Mbps. 


What If You Were in the Other Side of the Globe?
Well, if your RTT is more like 250ms, you could hardly get 32KB/sec (1sec/250ms*8KB), meaning that you could download a 1MB image in 30sec... Not something to proudly report back home.


CDN. The Question is: How Can I Solve This?
CDN (Content Delivery Network) helps bringing web content closer to the end user. This way it maximizes the throughput controlled by TCP/IP Window size.


How Does CDN Work?

1. The CDN provider places many edge servers around the world (as many as dozens of thousands of locations in some cases).
2. The CDN provider hosts your DNS record in a DNS that supports Anycast. Anycast enables the same DNS record to point to different locations around the world based on the network topology.
3. The end user is directed to the closest edge server based on the Anycast DNS.
4. The edge server tries to serve the file if it is cached on it (images, JavaScript files, static HTML, Streaming Video or just a static copy of your favorite news site are good examples).
5. If the requested file is dynamic or not cached, the edge server requests the file from the origin server using a large TCP window size. This way the 230ms RTT (250ms-20ms) is done in a much larger Window Size (say 64KB) and the throughput will be much larger.

Bottom Line
CDN can help you better your end user experience, use it when needed.


If you need assistance, don't hesitate to communicate,
Keep Performing,
Moshe Kaplan

DRP, Cloud and Some Tips

\One of the most popular use cases for cloud computing is DRP.


Why DRP?
DRP is one of the costly operations in systems life cycle. Not only you need to keep a similar instance of every server and device in your backup site. You also need to make sure that these copies remained in a similar condition to the original one, so when things will get wrong, you could restore.


What Should Have You Done in a Perfect World?
If only your system could work in an Active/Active mode so that every penny you pay will result in another action that results in your bottom line. But till you reach this target, let see how can you keep risks low, and costs affordable as well.


Few Initial Tips:

1. Keep your data out side of the applications servers: data should be stored in the database, files should be stored in external storage (Amazon S3) or CDN.
2. Replicate your static files (php and etc) using websync.

Keep Performing,
Moshe Kaplan

MySQL. An Internet Standard.

As you probably already know, last week I gave a presentation at the Database2011 conference on how MySQL become an Internet Standard?

Database2011 my sql sharding

View more presentations from Moshe Kaplan.

The answer is simple: great community and good support and the proof of the pudding is in the eating: everyone uses it including Facebook, Twitter and Google (if you want to hear other opinions try follow the xaprb.com comments flow).


MySQL Limitiations
Yet, if you will explore the MySQL capabilities you may find out that it lucks real multi threading capabilities. its table size is limited to effective number of 50-100M rows, its SELECT performance is limited to 50 statements on a single table per second and its INSERT performance is limited to 700 INSERT statements per second (based on standard Amazon instance and InnoDB engine).
So, how can the Internet industry with dozens of thousands of SQL statements per second in a medium site use such a limited database?


Answers
The answer is simple: Use Sharding. Sharding is a method for slashing your tables into smaller ones and storing parts of the data in each one of them. There are several common strategies including:

1. Vertical Sharding: Tear a table into two, store only few columns in first table and the rest in the second table.
2. Horizontal Sharding: Place each group of rows in another table. Horizontal Sharding can be implemented using various algorithms that include static hashing, hashing with directory mapping, key based directory mapping and signature based mapping.

The main issue with Sharding is reporting. Since direct grouping cannot be supported when rows are spread in various tables, you should use Map Reduce based solutions to accomplish this task.


Emerging Products
In the last year several new COTS Sharding products were introduced to the market. These products use two mechanisms in order to get over the MySQL limitations:

1. Gizzard and ScaleBase (founded by Industry experts Doron Levari and Liran Zelkha) use the MySQL Proxy mechanism in order to implement a load balancing and Sharding solutions in front of regular MySQL databases.
2. Xeround and Akiban implemented a new type of storage engine based on the MySQL Storage engine API.

Will MySQL remain an Internet standard in the future?
MySQL will keep being an Internet standard, unless unexpected decisions will be taken by Oracle that owns the firm. Unwise marketing decision could harm the community and may cause a major shift to other solutions including NoSQL.


Bottom Line
If you have a great internet idea, you have all the needed tools to start.


Keep Performing,
Moshe Kaplan